#111. PL/SQL강좌, 동적SQL,Dynamic SQL,PL/SQL 블록에서 다른 프러시저, 함수 호출하기PLSQL교육

7.1.4 PL/SQL 블록에서 다른 프러시저, 함수 호출하기

SQL> CREATE OR REPLACE PROCEDURE create_dept (

  2    p_deptno IN dept2.deptno%TYPE,

  3    p_dname  IN dept2.dname%TYPE,

  4    p_loc    IN dept2.loc%TYPE

  5  )

  6  IS

  7  BEGIN

  8

  9    INSERT INTO dept2 values (

 10      p_deptno,

 11      p_dname,

 12      p_loc);

 13  END;

 14  /

프로시저가 생성되었습니다.

SQL> DECLARE

  2    plsql_block VARCHAR2(500);

  3    new_deptno  dept2.deptno%TYPE := 99;

  4    new_dname  dept2.dname%TYPE := 'SALES';

  5    new_loc   dept2.loc%TYPE := 'GURODIGITAL';

  6  BEGIN

  7    -- PL/SQL 프로그램 호출

  8    plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';

  9

 10    EXECUTE IMMEDIATE plsql_block

 11      USING new_deptno, new_dname, new_loc;

 12  END;

 13  /

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

SQL> SELECT * FROM DEPT2;

    DEPTNO  DNAME          LOC

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

        10 ACCOUNTING     GURODIGITAL

        20 RESEARCH        DALLAS

        30 SALES            CHICAGO

        40 OPERATIONS      BOSTON

        99 SALES            GURODIGITAL

 

#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 처리가 정상적으로 완료되었습니다.