Oracle PL/SQL 테이블 함수

 

Table 함수는 컬렉션의 열을 추출할 수 있는 함수이다. 이를 이용하여 테이블처럼 쿼리할 수도 있으며, 조인도 가능하다.

Table 함수에서는 입력 값으로 스칼라 타입뿐 아니라, 컬렉션 타입(varry,neted table) REF커서 타입도 사용될수 있다.

쿼리 내  FROM 절에서 'TABLE'이라는 키워드로 접근 가능하며 반환 타입으로는  Nested Table 또는 Varray 컬렉션 타입이 사용된다.

 

Table 함수

테이블의 결과 값을 패키지나 함수 등을 이용하여 가공을 거친 후에 다시 화면에 SQL 쿼리 처럼 결과를 출력하고 싶을 경우 Table 함수가 유용하게 사용될 수 있다.

 

EMP 테이블의 결과를 컬렉션 변수를 이용해 가공한 후 해당 컬렉션의 결과를 테이블처럼 반환하는 Table  함수를 생성해 보겠다.

 

CREATE OR REPLACE TYPE EMP_OBJ_TYPE AS OBJECT(
    EMPNO NUMBER,
    ENAME VARCHAR2(10),
    SAL NUMBER(7,2)
    
    )

CREATE OR REPLACE TYPE EMP_TAB_TYPE AS TABLE OF EMP_OBJ_TYPE;

 

CREATE OR REPLACE FUNCTION GET_EMP_INFO_FUNC(P_DEPTNO NUMBER)
--RETURN 타입은 위에서 셍성한 컬렉션 타입임
RETURN EMP_TAB_TYPE
IS
    --컬렉션 변수 선언 및 초기화
    I_EMP_TAB EMP_TAB_TYPE := EMP_TAB_TYPE();
    I NUMBER := 1;
    
    BEGIN
        FOR C1 IN (SELECT EMPNO,ENAME,SAL * 1.2 AS SAL FROM EMP WHERE DEPTNO = P_DEPTNO)
            LOOP
                --컬렉션 요소 추가 후 값 입력
                --기 생성한 오브젝트 타입을 이용하여 컬렉션에 입력
                
                I_EMP_TAB.EXTEND;
                I_EMP_TAB(I) := EMP_OBJ_TYPE(C1.EMPNO, C1.ENAME, C1.SAL);
                
                I := I+1;
            END LOOP;
        --컬렉션 변수를 반환
        RETURN I_EMP_TAB;
    END;

컬렉션 타입으로 반환 하는 함수를 생성, 부서 번호를 입력하면 사원번호, 이름 , 샐러리에 1.2 를 곱한 결과 값을 컬렉션 타입으로 반환하는 간단한 함수이다. 

 

RETURN 타입은 반환하고자 하는 행 타입의 컬렉션 타입이다.

반한하고자 하는 데이터를 컬렉션 변수를 확장하면서 입력한 후 컬렉션 변수를 반환하도록 했다.

 

SELECT * FROM TABLE(GET_EMP_INFO_FUNC(10));

 

Pipelined Table 함수

PipelinedTable 함수는 Table 함수와 거의 동일하나 건별로 처리 후 바로 추출할 수 있는 부분 범위 처리가 가능한 함수이다.

다른 부분은 위에서 생성한 Table 함수와 동일하나 반환 타입에  PIPELINED 구문을 추가하고 RETURN 명령 대신 PIPE ROW 함수를 사용하면된다.

 

CREATE OR REPLACE FUNCTION GET_EMP_PIPE_FUNC(P_DEPTNO NUMBER)
--RETURN 구문에 PIPELINED 추가
RETURN EMP_TAB_TYPE PIPELINED
IS
    --컬렉션 변수 선언 및 추기화
    I_EMP_TAB EMP_TAB_TYPE := EMP_TAB_TYPE();
    I NUMBER := 1;
    
    BEGIN
        FOR C1 IN (SELECT EMPNO,ENAME,SAL*1.2 AS SAL FROM EMP WHERE DEPTNO = P_DEPTNO)
            LOOP
                --컬렉션 요소 추가 후 값 입력
                I_EMP_TAB.EXTEND;
                I_EMP_TAB(I) := EMP_OBJ_TYPE(C1.EMPNO,C1.ENAME,C1.SAL);
                --RETURN 함수 대신 piperow 함수 사용
                PIPE ROW (I_EMP_TAB(I));
                I := I+1;
            END LOOP;
       END;

 

RETURN 대신 PIPEROW 를 이용하여 한 건씩 반환되도록  처리 했다.

 

 

SQL 커서를 이용한 Table 함수

 

CREATE OR REPLACE PACKAGE EMP_INFO_PKG
IS
--REF 커서를 선언
    TYPE REF_CUR IS REF CURSOR;
    
    --반환할 레코드 선언
    TYPE EMP_REC_TYPE IS RECORD (
        EMPNO NUMBER,
        ENAME VARCHAR2(10),
        SAL NUMBER(7,2)
        
        );
        
        --위의 레코드 타입을 컬렉션 타입으로 선언
        TYPE EMP_TAB_TYPE IS TABLE OF EMP_REC_TYPE;
        
        --커서를 입력으로 하여 컬렉션 변수를 리턴하는 함수선언
        --위에서 선언한 컬렉션 타입을 사용함
        --반드시 PIPELINED를 명시해야함
        
        FUNCTION GET_INFO_FUNC(P_REFCUR REF_CUR)
        RETURN EMP_TAB_TYPE PIPELINED;
        
        END EMP_INFO_PKG;

 

CREATE OR REPLACE PACKAGE BODY EMP_INFO_PKG IS
--커서 사용을 위한 REF커서 지정(명세에서 생성한 ref_cur 변수 이용)
--PIPELINED 구문 추가가 필요함
FUNCTION GET_INFO_FUNC(P_REFCUR REF_CUR)
RETURN EMP_TAB_TYPE PIPELINED
IS
    --패키지 헤더에서 선언한 레코드 타입을 변수로 선언
     EMP_REC EMP_REC_TYPE;
     EMP_REC_TEMP EMP_REC_TYPE;
     
     BEGIN
        LOOP
            --REF 커서의 내용을 레코드 타입 변수에 입력
            FETCH P_REFCUR INTO EMP_REC_TEMP;
            EXIT WHEN P_REFCUR%NOTFOUND;
            
            --레코드 타입에 입력된 일부 값을 우선 출력;
            EMP_REC.EMPNO := EMP_REC_TEMP.EMPNO;
            PIPE ROW(EMP_REC); --> EMPNO 값만가지는 1row 를 즉시 return
            
            --나머지 값은 그다음 행에 출력
            EMP_REC.ENAME := EMP_REC_TEMP.ENAME;                                                            
            EMP_REC.SAL := EMP_REC_TEMP.SAL;
            PIPE ROW(EMP_REC);
            
            --레코드 타입 변수 초기화
            EMP_REC.EMPNO := NULL;
            EMP_REC.ENAME := NULL;
            EMP_REC.SAL := NULL;
            
            END LOOP;
            
            --커서를 close
            CLOSE P_REFCUR;
            
            --RETURN 하는 변수를 지정하지 않음
            --(loop)내에서 pipe row 함수를 이용하여 모두 Return 되었기 떄문
            
            END;
            
        END EMP_INFO_PKG;

REF커서를 이용하여 입력 받은 SQ을 수행후 PIPE ROW 함수를 이용하여 하나의 행을 두개의 행ㅇ로 나누어 출력하도록 했다.

 

SELECT *
FROM TABLE(EMP_INFO_PKG.GET_INFO_FUNC(CURSOR(SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO= 10)));

 

Table 함수와 조인

Table 함수는 테이블과 같이 출력이 가능하기 때문에 다른 테이블과 조인이가능하다. 다만 조인이 가능하기 위해서는 조인 컬럼이 Table 함수의 출력 칼럼으로 반드시 포함되어 있어야 한다. 또 한 TABLE 함수에 매개변수를 제공하는 테이블이 우선 기술되어야 한다.

 

            
SELECT * 
FROM TABLE (GET_EMP_PIPE_FUNC(DEPTNO)) T, DEPT E
WHERE E.DEPTNO = T.DEPTNO
AND E.DEPTNO = 10;

위와 같이 dept 테이블과 조인되도록 했더니 deptno 컬럼을 인식하지 못하고 에러가 발생했다. 

Table 함수 내에서 출력이 되지 않는 deptno 컬럼이 사용되어 발생한 오류이다. 이를 해결하기 위해 패키지에서 deptno 칼럼이 함께 출려되도록 생성해야한다

 

우선 deptno 칼럼이 포함되도록 오브젝트 타입과 컬렉션 타입을 재생성한다. 

 

--컬렉션 타입 삭제(오브젝트 타입보다 우선 삭제해야함)
DROP TYPE EMP_TAB_TYPE;
--오브젝트 타입 삭제
DROP TYPE EMP_OBJ_TYPE;

CREATE OR REPLACE TYPE EMP_OBJ_TYPE AS OBJECT(
    EMPNO NUMBER,
    ENAME VARCHAR2(10),
    SAL NUMBER(7,2),
    DEPTNO NUMBER
    );
    /
    COMMIT;
    /
CREATE OR REPLACE TYPE EMP_TAB_TYPE AS TABLE OF EMP_OBJ_TYPE;
    /

 

 

CREATE OR REPLACE FUNCTION GET_EMP_PIPE_FUNC(P_DEPTNO NUMBER)
    --RETURN 타입은 위에서 생성한 컬렉션 타입
    RETURN EMP_TAB_TYPE PIPELINED
    IS
        --컬렉션 변수 선언 및 초기화
        I_EMP_TAB EMP_TAB_TYPE := EMP_TAB_TYPE();
        I NUMBER := 1;
        
        BEGIN
            --입력 매개변수의 값이 DEPTNO 칼럼이 함꼐 출력되도록 수정함
            FOR C1 IN (SELECT EMPNO, ENAME,SAL * 1.2 AS SAL, DEPTNO FROM EMP WHERE DEPTNO = P_DEPTNO)
            LOOP
                --컬렉션 요소 추가 후 값 입력, DEPTNO 추가
                I_EMP_TAB.EXTEND;
                I_EMP_TAB(I) := EMP_OBJ_TYPE(C1.EMPNO,C1.ENAME,C1.SAL,C1.DEPTNO);
                
                --RETURN 함수대신 PIPE ROW 함수사용
                PIPE ROW(I_EMP_TAB(I));
                I := I + 1;
                END LOOP;
            END;
            /

다음으로 GET_EMP_PIPE_FUNC 함수에 DEPTNO 컬럼 값이 추가되어  반환되도록 수정했다. 

 

            
SELECT * 
FROM TABLE (GET_EMP_PIPE_FUNC(DEPTNO)) T, DEPT E
WHERE E.DEPTNO = T.DEPTNO
AND E.DEPTNO = 10;

 

 

 

DEPT_NO 칼럼을 추가했음에도 불구하고 동일한 에러가 발생했다 . 선행 집합의 결과를 이용해야 할 경우에는 Table 함수 테이블이 후순위로 기술되어야 하기 때문이다.

 

 

SELECT *
FROM DEPT E,
    TABLE(GET_EMP_PIPE_FUNC(DEPTNO)) T
    WHERE E.DEPTNO = T.DEPTNO
    AND E.DEPTNO = 10;
    

테이블 순서를 변경하였더니 정상적으로 수행이 되었다 Table 함수를 사용하여 조인이 필요한 경우에는 조인되는 칼럼이 포함되어야 하고,

조인 순서도 유의해야 정상적으로 주의해야한다.

 

 

 

멀티 칼럼을 반환하는 스칼라 서브쿼리

 

스칼라 서브쿼리는 함수와 같기 때문에 하나의 행과 하나의 칼럼 값만 추출할 수 있습니다. 그러나 CAST와 MULTISET  함수를 이용하여 Table 함수 사용 시 여러 칼럼의 값과 여러 행의 결과 집합을 추출할 수 있다.이를 이용하여 부분 범위 처리나 혹은 페이징 처리에도 유용하게 사용될 수 있다.

 

CAST와 MULTISET을 이용하여 컬렉션 변수로 반환하는 기본 구문이다.

CAST(MULTISET(<SELECT_STATEMENT>) AS <COLLETION_TYPE))

 

DEPTNO 가 10인 부서의 사원의 sal과 comm칼럼 값의 합계를 구하는 SQL이다.

 

    
SELECT D.DNAME,E.SAL, E.COMM
FROM (SELECT E.DEPTNO,SUM(E.SAL) SAL, SUM(E.COMM) COMM FROM EMP E GROUP BY DEPTNO) E,
        DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 10;

 

 

 

CREATE OR REPLACE TYPE EMP_OBJ_TYPE AS OBJECT (
    DEPTNO NUMBER(2),
    SAL NUMBER(10),
    COM NUMBER   )
    /
    COMMIT;

반환하고자 하는 컬럼으로 새로운 오브젝트 생성

 

 

CAST와 MULTISET 함수를 이용하여 EMP_TAB_TYPE 컬렉션 타입으로 변환하던 부분을, 스칼라 서브쿼리 내에서 EMP_OBJ_TYPE 오브젝트 타입을 이용해 직접 사용이 가능하다 . 이 방식을 사용할 경우에는 여러 컬럼의 값은 추출 가능하지만 하나의 행만 추출이 가능하다.

 

   
SELECT S.DNAME,
       S.EMP_SAL.SAL,
       S.EMP_SAL.COM
FROM (
      SELECT D.DNAME,
       (SELECT EMP_OBJ_TYPE(DEPTNO,SUM(SAL),SUM(COMM)) --> 위에서 정의한 TYPE 사용
        FROM EMP E 
        WHERE E.DEPTNO = D.DEPTNO
        GROUP BY DEPTNO ) AS EMP_SAL
        FROM DEPT D
        WHERE D.DEPTNO = 10) S;

 

 

DEPTNO 가 10인 사원의 sal,comm 값의 합계를 구하기 위해 스칼라 서브쿼리를 사용해봤다. 그러나 반환하려는 컬럼이 3개이기 때문에

emp_obj_type 라는 레코드 타입 변수에 값을 저장하도록 하고 메인 절에서 이 레코드 타입으 별칭(ALIAS) 인 EMP_SAL 를 이용하여 원하는 칼럼 값을 추출하도록 하였습니다.