Oracle PL/SQL - 동적 SQL

	동적 SQL
사용 방법	실행 중 변경 가능한 문자열 변수 또는 문자열 상수로 제공된 SQL문을 실행
사용 예	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM emp' INTO v_cnt;
사용 가능한 SQL	모든 SQL문(DML, TCL, DDL, DCL)
장점	- 사전에 정의되지 않은 SQL을 실행 시에 확정하여 실행할 수 있다. - DML과 TCL외에도 DDL, DCL을 사용할 수 있다.
단점	프로그램을 작성하기가 복잡하다.
용도	- SQL이 몇 개 문장으로 고정될 수 없는 경우 - SQL이 실행될지 예측할 수 없는 경우

* 실행 방법

1. EXECUTE IMMEDIATE문을 사용하여 SQL을 실행하는 방법

2. 커서 변수를 사용하여 OPEN, FETCH, CLOSE문으로 SELECT문을 실행하는 방법

3. 오라클 내장 패키지인 DBMS_SQL을 사용하는 방법

 

1. EXECUTE IMMEDIATE문

- 모든 유형의 SQL문 동적 실행 가능

EXECUTE IMMEDIATE SQL문자열 [INTO 변수목록] [USING 바인드변수목록];

(예시)

DECLARE
    v_insert_stmt CONSTANT VARCHAR2(100) := 'INSERT INTO t VALUES(1, ''서울'')';
BEGIN 
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE t';
    -- 예외 발생 처리 구문
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;
    
    EXECUTE IMMEDIATE 'CREATE TABLE t(a NUMBER, b VARCHAR2(10))';
    
    EXECUTE IMMEDIATE v_insert_stmt;
    
    EXECUTE IMMEDIATE 'COMMIT';
END;

* [INTO 변수목록] : "INTO 레코드 변수"와 같이 사용하여 쿼리 결과를 변수에 저장할 수 있다.

 

* 바인드 변수: ':플레이스홀더명'으로 지정

(예시)

DECLARE
    v_query CONSTANT VARCHAR2(200) := 'SELECT COUNT(*)
                                        FROM emp
                                        WHERE deptno = :deptno
                                        AND job = :job';
    v_deptno emp.deptno%TYPE;
    v_cnt PLS_INTEGER;
BEGIN
    v_deptno := 20;
    
    EXECUTE IMMEDIATE v_query
        INTO v_cnt
        USING IN v_deptno, 'CLERK';
    DBMS_OUTPUT.PUT_LINE('COUNT = '||v_cnt);
END;

- USING절에 바인드 변수를 사용한다.

- IN: 입력 모드, 값이 PL/SQL에서 DB 서버로 전달된다. 입력 모드 변수의 값은 동적 SQL의 실행 전후에 변경이 없다. 모드를 생략할 경우 기본으로 사용된다.

- OUT: 출력 모드, 값이 DB 서버에서 PL/SQL로 전달된다.

- IN OUT: 입출력 모드, 값이 양방향으로 전달된다. 동적 SQL을 실행하기 전에 가지고 있던 값이 DB 서버로 전달되어 참조되며, DB서버에서 변경된 값이 다시 PL/SQL 변수로 되돌려진다.

 

- 다수를 사용할 경우, 변수명과 동일하지 않게 순서대로 대입한다.

 

2. DBMS_SQL

	의미
DBMS_SQL.DESC_TAB	DESC_REC 레코드 타입을 요소로 하는 Associative Array " TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER "
DBMS_SQL.DESC_REC	동적 쿼리의 단일 컬럼 정보를 담고 있는 레코드 타입
DBMS_SQL.OPEN_CURSOR	커서를 열고 번호를 반환받는다.
DBMS_SQL.PARSE(커서(번호), 쿼리, 언어)	SQL 파싱, 언어 파트에 DBMS_SQL.NATIVE를 입력한다.
DBMS_SQL.NATIVE
DBMS_SQL.BIND_VARIABLE	주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값을 설정해주는 프로시저. 변수는 이름으로 찾기 때문에, SQL 문장 내의 변수 이름과 파라미터로 주어진 이름을 동일하게 호출해야 한다.
DBMS_SQL.DESCRIBE_COLUMNS(커서(번호), 컬럼 갯수, 컬럼 정보 저장 테이블 객체)	컬럼 정보 저장 테이블 객체 = DBMS_SQL.DESC_TAB
DBMS_SQL.DEFINE_COLUMN(커서(번호), 컬럼 순서, 컬럼 유형, 컬럼 크기)	주어진 커서에서 FETCH될 컬럼의 타입을 정의하는 프로시저. SELECT문에서만 사용 가능
DBMS_SQL.EXECUTE	커서를 실행
DBMS_SQL.FETCH_ROWS	FETCH
DBMS_SQL.COLUMN_VALUE	주어진 커서에서 FETCH한 컬럼의 값을 원하는 변수로 가져오는 프로시저. FETCH한 후 데이터를 가져오는 데 사용.
DBMS_SQL.CLOSE	커서를 닫는다.

(예시)

DECLARE
    v_cursor_id NUMBER;
    v_sql_stmt VARCHAR2(4000) := Q'<SELECT *
                                    FROM emp
                                    WHERE deptno = :deptno
                                    AND hiredate >= TO_DATE(:hiredate, 'YYYY-MM-DD')>'; ' -- 주석처리가 되어 따옴표를 추가했고, 실제로 sql을 실행할 때 제거해야한다.
    
    TYPE vc_array IS TABLE OF VARCHAR2(100);
    v_bind_var vc_array;
    v_bind_val vc_array;
    v_ret   NUMBER;
    
    v_desc_tab DBMS_SQL.DESC_TAB;
    v_col_cnt   PLS_INTEGER;
    v_str_var   VARCHAR2(100);
    v_num_var   NUMBER;
    v_date_var  DATE;
    v_row_cnt   PLS_INTEGER;
BEGIN
    v_bind_var := vc_array('deptno', 'hiredate');
    v_bind_val := vc_array('10', '1981-07-01');
    
    -- OPEN CURSOR
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    
    -- PARSING
    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);
    
    -- BINDING
    FOR i IN 1 .. v_bind_var.COUNT
    LOOP
        DBMS_SQL.BIND_VARIABLE(v_cursor_id, v_bind_var(i), v_bind_val(i));
    END LOOP;
    
    -- DESCRIBE
    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_cnt, v_desc_tab);
    
    -- DEFINE COLUMN
    FOR i IN 1 .. v_col_cnt LOOP
        IF v_desc_tab(i).col_type = DBMS_SQL.NUMBER_TYPE THEN
            DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_num_var);
        ELSIF v_desc_tab(i).col_type = DBMS_SQL.DATE_TYPE THEN
            DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_date_var);
        ELSE
            DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_str_var, 100);
        END IF;
    END LOOP;
    
    v_ret := DBMS_SQL.EXECUTE(v_cursor_id);
    
    v_row_cnt := 0;
    
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0
    LOOP
        v_row_cnt := v_row_cnt + 1;
        DBMS_OUTPUT.PUT_LINE(v_row_cnt|| '번째 로우');
        FOR i IN 1 .. v_col_cnt LOOP
            IF (v_desc_tab(i).col_type = DBMS_SQL.NUMBER_TYPE) THEN
                DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_num_var);
                DBMS_OUTPUT.PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||' : '||v_num_var);
            ELSIF (v_desc_tab(i).col_type = DBMS_SQL.DATE_TYPE) THEN
                DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_date_var);
                DBMS_OUTPUT.PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||' : '||TO_CHAR(v_date_var, 'YYYY-MM-DD'));
            ELSE
                DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_str_var);
                DBMS_OUTPUT.PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||' : '||v_str_var);
            END IF;
        END LOOP;
    END LOOP;
    
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;

 

1. OPEN CURSOR

2. PARSING

3. BINDING

4. DESCRIBE

5. DEFINE COLUMN

6. EXECUTE

7. FETCH

8. CLOSE

 

※ 출처: 개발자를 위한 PL/SQL 프로그래밍