Dynamic sql in Oracle 11g(DBMS_SQL) [ORACLEJAVA커뮤니티자바오라클교육강좌오라클자바교육강좌,자바교육오라클교육]

Dynamic sql in 11g(DBMS_SQL)

오라클자바커뮤니티에서 설립한  개발자실무교육6년차 오엔제이프로그래밍 실무교육센터

(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링, Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net)   www.onjprogramming.co.kr 

;’Oracle 11g Dynamic SQL 새 특징

<!--[if !supportLists]-->-       <!--[endif]-->CLOB에 의해 32k 보다 큰 SQL문장 허용(DBMS_SQL.PARSE)

<!--[if !supportLists]-->-       <!--[endif]-->REF CURSOR가 DBMS_SQL 커서로 변환이 가능

<!--[if !supportLists]-->-       <!--[endif]-->DBMS_SQL에서 모든 데이터 타입 지원

<!--[if !supportLists]-->-       <!--[endif]-->DBMS_SQL에서 사용자 정의 컬렉션 타입을 지원

[예제1] Dynamic SQL이 CLOB 컬럼에 저장되는 예제

SQL> set serveroutput on

SQL> DECLARE

  2

  3         v_sql    CLOB;

  4         v_cursor BINARY_INTEGER;

  5         v_dummy  INTEGER;

  6

  7      BEGIN

  8

  9       v_sql := 'DECLARE

 10                     v_variable VARCHAR2(10);

 11                  BEGIN

 12                     v_variable := ''Some Value'';';

 13

 14

 15       

 16        WHILE LENGTH(v_sql) <= 33000 LOOP

 17           v_sql := v_sql ||

 18                    ' /* comment || RPAD(''x'',4000,''x'') */ ' ||

 19                    CHR(10);

 20        END LOOP;

 21

 22       

 23        v_sql := v_sql ||

 24                 '   DBMS_OUTPUT.PUT_LINE(

 25                        ''Value of V_VARIABLE is ['' || v_variable || '']'');

 26                  END;';

 27

 28       

 29        DBMS_OUTPUT.PUT_LINE(

 30           'Length of CLOB is [' || LENGTH(v_sql) || ']');

 31

 32       

 33        v_cursor := DBMS_SQL.OPEN_CURSOR;

 34        DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

 35        v_dummy := DBMS_SQL.EXECUTE(v_cursor);

 36        DBMS_SQL.CLOSE_CURSOR(v_cursor);

 37

 38     END;

 39     /

Length of CLOB is [33145]

Value of V_VARIABLE is [Some Value]

PL/SQL 처리가 정상적으로 완료되었습니다.

[예제2] DBMS_SQL의 ref_cursor 지원

SQL> DECLARE

  2         TYPE emp_type IS TABLE OF emp%ROWTYPE

  3            INDEX BY PLS_INTEGER;

  4

  5         v_emps    emp_type;

  6         v_sql     CLOB;

  7         v_cursor  BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;

  8         v_rc      SYS_REFCURSOR;

  9        v_execute BINARY_INTEGER;

 10   BEGIN

 11        v_sql := 'SELECT * FROM emp WHERE job = :job';

 12

 13       

 14        DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

 15

 16       

 17        DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');

 18

 19        /* 커서문 실행 및 REF CURSOR변환... */

 20        v_execute := DBMS_SQL.EXECUTE(v_cursor);

 21        v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);

 22

 23        LOOP

 24           FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;

 25           FOR i IN 1 .. v_emps.COUNT LOOP

 26              DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);

 27           END LOOP;

 28           EXIT WHEN v_rc%NOTFOUND;

 29        END LOOP;

 30        CLOSE v_rc;

 31

 32     END;

 33     /

job의 값을 입력하십시오: SALESMAN

구  17:       DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');

신  17:       DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', 'SALESMAN');

Emp = ALLEN

Emp = WARD

Emp = MARTIN

Emp = TURNER

PL/SQL 처리가 정상적으로 완료되었습니다.

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=Oracle11&wr_id=4