2014년 3월 31일 월요일

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지) DBMS_SQL, DYNAMIC SQL 이란? ,[오라클교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지) DBMS_SQL, DYNAMIC SQL 이란? ,[오라클교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지)

DBMS_SQL, DYNAMIC SQL 이란?   
   
PL/SQL Binding Compile시에 일어나므로 Database Object Name Compile시에 고정되어야 하는 등의 제한이 있다. PL/SQL 2.1(RDBMS 7.1)이후 Version에서는 DBMS_SQL Package Dynamic SQL Statement의 사용을 가능하게 했는데 이는 Database Object Name Runtime에 줄 수 있을 뿐 아니라 DDL문장을 기술할 수도 있는 장점이 있다.  
 
 * Function Open_Cursor   
  : SQL문의 실행에 필요한 새로운 Cursor를 열고 Cursor ID NumberReturn 한다.   

 * Function Is_Open   
  : 주어진 Cursor가 현재 Open되어 있으면 TRUE, 아니면 FALSE Return한다.   

 * Procedure Parse   
  : Statement Check하고 Cursor와 결합시킨다.   

 * Procedure Bind_Variable   
  : Program내에서 Data를 저장한 Placeholder의 값을 제공하는 역할을 한다.   

 * Procedure Define_Column   
  : Cursor로부터 Select Column의 값을 받는 변수를 지정한다.   

 * Function Execute   
  : SQL문을 실행하고 처리된 Row의 수를 Return한다. (Insert, Update, Delete인 경우에만   
    해당)   

 * Function Fetch_Rows   
  : Cursor로부터 Row Fetch하고 실제로 Fetch Row의 수를 Return한다. Row들은 Buffer에 들어가며, Column_Value를 호출하여 읽어들여야 한다.    

 * Function Execute_And_Fetch   
  : Execute Fetch Row를 동시에 수행하고 실제로 Fetch Row의 수를 Return한다.   

 * Procedure Variable_Value   
  : 주어진 변수의 값을 Return한다.   

 * Procedure Column_Value   
  : Fetch_Rows에 의해 Fetch Data의 값을 Return한다.   

 * Procedure Close_Cursor   
  : Cursor를 닫는다.  

Dynamic SQL은 사용자의 입력에 의해 SQL문이 작성되거나 다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우인데 Oracle에서의 Dynamic SQL EXECUTE IMMEDIATE 절을 사용하여 embedded dynamic sql을 구현하는 방법과 DBMS_SQL 패키지를 이용하여 동적 SQL을 구현할 수 있습니다.

아래는 두 방법을 이용하여 동적 SQL을 구현한 예제 입니다.

SQL> conn scott/tiger
연결되었습니다.

SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
2 sql_stmt varchar2(4000);
3 BEGIN
4 EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
5 END;
6 /

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

SQL>-- EXECUTE IMMEDIATE를 실행하기 위해서는 create any table 권한이 필요…!

SQL> exec CREATE_TABLE1;
BEGIN CREATE_TABLE1; END;

*
1행에 오류:
ORA-01031: 권한이 불충분합니다
ORA-06512: "SCOTT.CREATE_TABLE1", 4에서
ORA-06512: 1에서


SQL> conn / as sysdba
연결되었습니다.


SQL> grant create any table to scott;

권한이 부여되었습니다.

SQL> conn scott/tiger
연결되었습니다.

SQL> exec CREATE_TABLE1;

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

SQL> desc x;
이름 널? 유형
----------------------------------------- -------- -----------------------

A NUMBER


SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
2 cur integer;
3 rc integer;
4 BEGIN
5 cur := DBMS_SQL.OPEN_CURSOR;
6 DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
7 rc := DBMS_SQL.EXECUTE(cur);
8 DBMS_SQL.CLOSE_CURSOR(cur);
9 END;
10 /

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


SQL> drop table x;

테이블이 삭제되었습니다.

SQL> exec CREATE_TABLE2;

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

SQL> desc x;
이름 널? 유형
----------------------------------------- -------- ------

Y DATE

SQL> -- 다음은 bind variable을 사용한 예제 입니다.

SQL> CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
2 v_cursor integer;
3 v_dname char(20);
4 v_rows integer;
5 BEGIN
6 v_cursor := DBMS_SQL.OPEN_CURSOR;
7 DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS
_SQL.V7);
8 DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
9 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
10 v_rows := DBMS_SQL.EXECUTE(v_cursor);
11 loop
12 if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
13 exit;
14 end if;
15 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
16 DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
17 end loop;
18 DBMS_SQL.CLOSE_CURSOR(v_cursor);
19 EXCEPTION
20 when others then
21 DBMS_SQL.CLOSE_CURSOR(v_cursor);
22 raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcod
e||' '||sqlerrm);
23 END;
24 /

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

SQL> exec DEPARTMENTS(10);

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

SQL> set serveroutput on

SQL> exec DEPARTMENTS(10);
Deptartment name: RESEARCH
Deptartment name: SALES
Deptartment name: OPERATIONS

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

아래는 DBMS_SQL을 이용하여 REF CURSOR를 사용한 예제이다.

DBMS_SQL ref_cursor지원,DBMS_SQL Dydnamic SQL

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

오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr)

평일주간(9:30~18:30) 개강
(4/07)[기업100%환급]SQL기초에서 Schema Object까지
(4/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(4/14)C#4.0,ADO.NET,Network 프로그래밍
(4/14)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/14)[기업100%환급]PL/SQL,ORACLE HINT,TUNING

평일야간(19:00~22:00) 개강
(4/01)안드로이드개발자과정
(4/04)웹퍼블리싱 마스터
(4/04)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(4/04)C#,ASP.NET마스터
(4/07)SQL초보에서실전전문가까지
(4/08)Spring3.X, MyBatis, Hibernate실무과정

주말(10:00~18:00) 개강
(4/05)웹퍼블리싱 마스터
(4/05)닷넷실무자를위한WPF개발자과정
(4/05)Spring3.X, MyBatis, Hibernate실무과정
(4/05)SQL초보에서실전전문가까지
(4/12)C#,ASP.NET마스터
(4/12)안드로이드개발자과정
(4/12)JAVA기초에서실무까지


댓글 없음:

댓글 쓰기