2014년 6월 23일 월요일

[구로디지털단지/가산역오라클학원,오라클SQL추천교육,ORACLE실무초보자교육,재직자학원]Dynamic SQL(EXECUTE IMMEDIATE, DBMS_SQL패키지)

[구로디지털단지/가산역오라클학원,오라클SQL추천교육,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 Number를Return 한다.　　　

　* 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.oraclejavacommunity.com

평일주간(9:30~18:10) 개강
(6/30)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(6/30)[기업100%환급]안드로이드개발자과정
(6/30)[기업100%환급]SQL기초에서 Schema Object까지
(7/07)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/07)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(7/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 
(7/07)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정

평일야간(19:00~21:50) 개강
(6/24)Spring3.X, MyBatis, Hibernate실무과정
(6/26)SQL초보에서실전전문가까지
(7/01)안드로이드개발자과정
(7/01)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(7/02)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/02)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
(7/03)웹퍼블리싱 마스터
(7/15)MyBatis3.X, Hibernate4.X ORM실무과정
(7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지

주말(10:00~17:50) 개강
(6/28)Spring3.X, MyBatis, Hibernate실무과정
(6/28)안드로이드개발자과정
(6/28)실무예제로 배워보는 jQuery(개발자/디자이너를위한)
(6/28)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(7/05)SQL초보에서 Schema Object까지
(7/12)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/12)MyBatis3.X, Hibernate4.X ORM실무과정
(7/12)개발자를위한PLSQL,SQL튜닝,힌트
(7/13)C#,ASP.NET마스터

주말저녁(18:30~22:20) 개강
(6/28)JAVA,Network&WEB&Framework
(6/28)SQL기초에서실무까지

작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)