Oracle PL/SQL 동적 SQL

오라클은 런타임 시에 동적으로 SQ을 만들어 수행될 수 있도록 동적(Dynamic)SQL 기능을 지원한다.

 

이를 이용해 PL/SQL에서 직접 기술할 수 없는 SELECT 문이나  DML 문들에 대해 도적으로  SQ을 정의하여 실행 시킬수 있다. 이를 이용하여 조건에 따라 유연하게 SQL 문을 변경하여 실행시킬수 있다. 

 

DBMS_SQL 패키지에서 기본적으로 제공하는 함수와 프로시저는 다음과 같다.

 

타입	이름	설명
함수	OPEN_CURSOR	SQL문의 실행에 필요한 새로운 커서를 열고 커서 번호를 반환
함수	IS_OPEN	커서가 현재 열려 있으면 TRUE, 아니면 FALSE를 반환
프로시저	PARSE	SQL 문장을 확인하고 커서와 결합
프로시저	BIND_VARIABLE	SQL문장과 엽력 변수를 결합
프로시저	DESCRIBE_COLUMNS	추출하고자 하는 칼럼 정보를 지정
함수	EXECUTE	SQL문을 실행하고 처리된 행의 수를 반환(행의 수 반환은 INSERT,UPDATE,DELETE 인 경우에만 해당)
함수	FETCH_ROWS	커서로부터 행을 가져오고 실제로 가져온 행의 수를 반환 이결과 값들은 버퍼에 들어가며, column_value를 호출하여 읽어 들여야함
함수	EXECUTE_AND_FETCH	EXECUTE와 FETCH_ROWS를 동시에 수행하고 실제로 가져혼 해으이 수를반환
프로시저	VARIABLE_VALUE	주어진 변수의 값을 반환
프로시저	COLUMN_VALUE	FETCH_ROWS함수를 이용해 가져온 데이터의 값을 반환
프로시저	CLOSE_CURSOR	커서를 닫기

 

 DECLARE
        I_RWOS NUMBER;
        V_EMPNO NUMBER := 9000;
        G_CURSOR NUMBER := DBMS_SQL.OPEN_CURSOR;
        
        BEGIN
            --SQL 구문을 커서와 바인딩
            DBMS_SQL.PARSE(G_CURSOR,'INSERT INTO SEQ_NUM (NUM) VALUES (:1)', DBMS_SQL.NATIVE;
            
            --바인드 변수를 SQL 과 바인딩
            DBMS_SQL.BIND_VARIABLE(G_CURSOR, ':1',V_EMPNO);
            
            --SQL 실행
            I_ROWS := DBMS_SQL.EXECUTE(G_CUROSR);
            
            --커서 CLOSE
            DBMS_SQL.CLOSE(G_CUROSR);
            
            END;

 

 

DBMS_SQL를 이용한 SELECT 실행

DECLARE
    --오픈한 커서 포인터를 담을 변수 선언
    I_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
    
    I_QUERY VARCHAR2(4000);
    
    I_EMPNO EMP.EMPNO%TYPE;
    I_ENAME EMP.ENAME%TYPE;
    I_SAL EMP.SAL%TYPE;
    I_STATUS INTEGER;
    
    BEGIN
        I_QUERY := 'SELECT EMPNO,ENAME,SAL
                    FROM EMP
                    WHERE DEPTNO =: B1';
                    
    --SQL을 파싱
    DBMS_SQL.PARSE(I_THECURSOR, I_QUERY,DBMS_SQL.NATIVE);
    
    --바인드 변수를 SQL과 바인딩
    DBMS_SQL.BIND_VARIABLE(I_THECURSOR,':B1',10);
    
    --CURSOR로부터 추출된 칼럼의 값을 받는 변수를 지정
    --커서명, 상대위치, 변수명 ,길이 지정
    
    DBMS_SQL.DEFINE_COLUMN(I_THECURSOR,1,I_EMPNO);
    DBMS_SQL.DEFINE_COLUMN(I_THECURSOR,2,I_ENAME,20);
    DBMS_SQL.DEFINE_COLUMN(I_THECURSOR,3,I_SAL);
    
    --Sql 문을 실행
    I_STATUS := DBMS_SQL.EXECUTE(I_THECURSOR);
    
    --CURSOR로부터 ROW를 FETCH
    WHILE(DBMS_SQL.FETCH_ROWS(I_THECURSOR) >0)LOOP
    
    --결괄를 변수에 저장
    DBMS_SQL.COLUMN_VALUE(I_THECURSOR,1,I_EMPNO);
    DBMS_SQL.COLUMN_VALUE(I_THECURSOR,2,I_ENAME);
    DBMS_SQL.COLUMN_VALUE(I_THECURSOR,3,I_SAL);
    
    DBMS_OUTPUT.PUT_LINE('EMPNO :' || I_EMPNO || 'ENAME : '|| I_ENAME || 'I_SAL : ' || I_SAL);
    END LOOP;
    
    DBMS_SQL.CLOSE_CURSOR(I_THECURSOR);
    EXCEPTION
        WHEN OTHERS THEN
        RAISE;
        
        END;

 

 

 

 

 

 

위의 구문은 조회하려는 컬럼의 정보를 미리 알고 있을경우에 사용가능 한 방법이다. 동적으로 SQL을 만들어 컬럼 구성이 배번 바뀔 경우에는 위의 방법이나  NDS 를 이용해서는 구현이 불가능하다.

 

이런경우에는 DBMS_SQL 패키지의 DESCRIBE_COLUMNS 함수를 이용하여 구현이 가능하다. 우선 DESCRIBE_COLUMNS 함수를 이용하여 컬럼 정보를 확인한 후에 ForLoop와 함께  DEFINE_COLUMN 함수와 COLUMN_VALUE 함수를 이용하면 동적으로 Fetch 된 결과를 저장, 출력 시킬 수 있다.

 

DECLARE
    --오픈한 커서 포인터를 담을 변수 선언
    I_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
    
    I_QUERY VARCHAR2(4000);
    I_STAUTS INTEGER;
    I_COLUMNVALUE VARCHAR2(4000);
    
    --DESC_REC 구조체의 컬렉션 형태로 내부적으로 정의돤 타입임
    --반환되는 컬럼명을 저장하기 위해 컬렉션 타입임
    
    I_DESCTBI DBMS_SQL.DESC_TAB;
    I_COLCNT NUMBER;
    
BEGIN
    I_QUERY := 'SELECT EMPNO, ENAME, SAL
                FROM EMP
                WHERE DEPTNO =:B1';
                

--SQL 구문을 커서와 바인딩
DBMS_SQL.PARSE(I_THECURSOR, I_QUERY ,DBMS_SQL.NATIVE);
--바인드 변수를 SQL 과 바인딩
DBMS_SQL.BIND_VARIABLE(I_THECURSOR,':B1',10);
--SQL에서 추출하려는 컬럼 정보를 추우하여 I_DESCTBI(DBMS_SQL.DESC_TAB) 변수에 입력
--컬럼 객수와 컬렉션에 컬럼명을 저장함
DBMS_SQL.DESCRIBE_COLUMNS(I_THECURSOR, I_COLCNT,I_DESCTBI);

--커서로 부터 반환된 컬럼의 값을 받는 변수를 지정
FOR I IN 1..I_COLCNT LOOP
    DBMS_SQL.DEFINE_COLUMN(I_THECURSOR,I,I_COLUMNVALUE,4000);
    END LOOP;
    
    --SQL문 실행
    I_STAUTS := DBMS_SQL.EXECUTE(I_THECURSOR);
    
    --CURSOR로부터 ROW를 FETCH 
    WHILE  (DBMS_SQL.FETCH_ROWS(I_THECURSOR) >0 ) LOOP
    
    FOR I IN 1.. I_COLCNT LOOP
    --결과를 변수에 반환
    DBMS_SQL.COLUMN_VALUE(I_THECURSOR,I,I_COLUMNVALUE);
    DBMS_OUTPUT.PUT_LINE(RPAD(I_DESCTBI(I).COL_NAME,30)
                        || ':' ||
                        I_COLUMNVALUE);
                        
                        END LOOP;
                    END LOOP;
        
        DBMS_SQL.CLOSE_CURSOR(I_THECURSOR);
        
        EXCEPTION
            WHEN OTHERS THEN
            RAISE;
            
            END;