#110. PL/SQL강좌, 동적SQL(Dynamic SQL),컴파일 타임에 존재하지 않는 데이터베이스 객체를 참조하는 경우,PLSQL교육

7.1.3 컴파일 타임에 존재하지 않는 데이터베이스 객체를 참조하는 경우

CREATE OR REPLACE PROCEDURE dynamictname(

       month VARCHAR2,

       year VARCHAR2) IS

    TYPE cur_type IS REF CURSOR;

    c cur_type;

    query_str VARCHAR2(200);

    v_gcode NUMBER;

    v_saleamt NUMBER;

BEGIN

    query_str := 'SELECT gcode, saleamt FROM sale_' || month ||'_'|| year

      || ' WHERE gcode = :gcode';

    OPEN c FOR query_str USING 9933;

    LOOP

        FETCH c INTO v_gcode, v_saleamt;

        EXIT WHEN c%NOTFOUND;

        -- 비지니스 로직 처리는 여기에서

    END LOOP;

    CLOSE c;

END;

/

#109. PL/SQL강좌, 동적SQL(Dynamic SQL)이란?,SQL문을 동적으로 생성,PLSQL교육

7.1.2 Dynamic SQL 사용하기(SQL문을 동적으로 생성)

<!--[if !supportLists]-->n  <!--[endif]-->런타임중에 쿼리의 검색조건이나 정렬순서, 힌트등을 지정 할 수 있다.

-- EMP 테이블에서 10번 부서원들의 이름, 급여 출력

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2    TYPE EmpCurTyp IS REF CURSOR;

  3    myCur EmpCurTyp;

  4    stmt_str VARCHAR2(2000);

  5    v_ename emp.ename%TYPE;

  6    v_sal emp.sal%TYPE;

  7  BEGIN

  8    stmt_str := 'SELECT ename, sal FROM emp WHERE deptno = :1';

  9    OPEN myCur FOR stmt_str USING 10;

 10

 11  LOOP

 12    FETCH myCur INTO v_ename, v_sal;

 13    EXIT WHEN myCur%NOTFOUND;

 14    DBMS_OUTPUT.PUT_LINE(v_ename || ',' || v_sal);

 15  END LOOP;

 16  CLOSE myCur;

 17  END;

 18  /

CLARK,3404.4

KING,6946.8

MILLER,2257.5

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

-- 아래는 INSERT 예제이다.

SQL> DECLARE

  2    stmt_str VARCHAR2(200);

  3    v_empno NUMBER := 4790;

  4    v_deptno NUMBER := 30;

  5    v_ename VARCHAR2(20) := 'OJC';

  6    v_sal NUMBER(4) := 3500;

  7    location VARCHAR2(10);

  8  BEGIN

  9    stmt_str := 'INSERT INTO emp(empno, ename, sal, deptno) VALUES

 10                (:empno, :ename, :sal, :deptno)';

 11    EXECUTE IMMEDIATE stmt_str

 12      USING v_empno, v_ename, v_sal, v_deptno;

 13  END;

 14  /

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

SQL> select empno, ename ,sal, deptno from emp where empno = 4790;

     EMPNO ENAME             SAL     DEPTNO

---------- ---------- ---------- ----------

      4790 OJC              3500         30

-- RETURNING구 사용예제이다.

SQL> DECLARE

  2    stmt_str  VARCHAR2(200);

  3    v_loc     dept.loc%TYPE := 'GURODIGITAL';

  4    v_deptno   NUMBER := 10;

  5    deptname  VARCHAR2(20);

  6  BEGIN

  7    stmt_str := 'UPDATE dept2

  8                 SET loc = :newloc

  9                 WHERE deptno = :deptno

 10                 RETURNING dname INTO :dname';

 11    EXECUTE IMMEDIATE stmt_str

 12      USING v_loc, v_deptno,OUT deptname;

 13

 14    DBMS_OUTPUT.PUT_LINE(deptname);

 15  END;

 16  /

ACCOUNTING

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

#108. PL/SQL강좌, 동적SQL(Dynamic SQL)이란? 개요,정의,동적 SQL을 이용하여 DDL을 실8행,PLSQL교육

7. Dynamic SQL

7.1 Dynamic SQL 이란?

<!--[if !supportLists]-->n  <!--[endif]-->PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있지만 Dynamic SQL을 사용한다면 SQL문의 최종완성을 런타임중에 할 수 있다는 장점이 있다. 결국 동적으로 SQL문을 만들어 내는 것이다.

<!--[if !supportLists]-->n  <!--[endif]-->DBMS_SQL Package로 Dynamic SQL문의 사용을 가능하게 했는데 이는 Database Object의 Name을 Runtime에 줄 수 있을 뿐 아니라 DDL문장을 기술할 수도 있는 장점이 있다.　　

<!--[if !supportLists]-->n  <!--[endif]-->다음과 같은 경우 Dynamic SQL을 사용한다.

- DDL을 사용하는 경우

- SQL문장을 동적으로 만들어 내는 경우(검색조건, 정렬조건, 힌트구문)

    - 컴파일타임에 존재하지 않는 DB Object를 참조하는 경우(테이블의 이름을 동적으로 생성)

- PL/SQL블록에서 다른 프로시저등을 호출하는 경우

7.1.1 동적 SQL을 이용하여 DDL을 실행

<!--[if !supportLists]-->n  <!--[endif]-->Dynamic SQL을 이용하면  CREATE, DROP, GRANT, REVOKE 등을 사용할 수 있다.

<!--[if !supportLists]-->n  <!--[endif]-->ALTER SESSION, SET명령어등 SCL(Session Control Language)을 사용할 수 있다.

<!--[if !supportLists]-->n  <!--[endif]-->Dynamic SQL을 이용하면 SELECT절에 TABLE구를 사용할 수 있다.

SQL> DECLARE

  2  BEGIN

  3      EXECUTE IMMEDIATE 'create table x ( x varchar2(1))' ;

  4

  5      DBMS_OUTPUT.PUT_LINE('Table Created.');

  6  END;

  7  /

Table Created.

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

SQL> select * from x;

선택된 레코드가 없습니다.

SQL> desc x;

 이름                                      널?      유형

 ----------------------------------------- -------- --------------------------

 X                                                  VARCHAR2(1)

-- 아래는 동적SQL을 이용하여 SELECT절에 TABLE구를 사용하는 예제이다.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TYPE type_emp AS OBJECT (empno NUMBER, ename VARCHAR2(20))

  2  /

유형이 생성되었습니다.

SQL> CREATE TYPE t_emplist AS TABLE OF type_emp

  2  /

유형이 생성되었습니다.

SQL> CREATE TABLE dept_temp (deptno NUMBER, emps t_emplist)

  2      NESTED TABLE emps STORE AS emp_table;

테이블이 생성되었습니다.

SQL> INSERT INTO dept_temp VALUES (

  2      10,

  3      t_emplist(

  4          type_emp(1, 'SCOTT'),

  5          type_emp(2, 'TIGER')));

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO dept_temp VALUES (

  2      20,

  3      t_emplist(

  4          type_emp(1, 'JCLEE'),

  5          type_emp(2, 'ORACLEJAVACOMMUNITY')));

1 개의 행이 만들어졌습니다.

SQL> DECLARE

  2      v_deptno dept.deptno%TYPE;

  3      v_empno emp.empno%TYPE;

  4      v_ename  emp.ename%TYPE;

  5  BEGIN

  6      -- 아래 TABLE구는 동적SQL로만 실행 가능하다.

  7      EXECUTE IMMEDIATE 'SELECT d.deptno, e.empno, e.ename

  8                         FROM dept_temp d, TABLE(d.emps) e

  9                         WHERE d.deptno = 10 AND e.empno = 1'

 10              INTO v_deptno, v_empno, v_ename;

 11      DBMS_OUTPUT.PUT_LINE(v_deptno || ':' || v_empno || ':' || v_ename);

 12  END;

 13  /

10:1:SCOTT

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