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 처리가 정상적으로 완료되었습니다.
 | |||||
|---|---|---|---|---|---|
[기업100%환급]SQL기초에서
Schema Object까지  |
총 | 5일 | 40시간 | 02-24 | |
[기업100%환급]PL/SQL,ORACLE
HINT,TUNING  |
총 | 5일 | 40시간 | 03-03 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 18일 | 54시간 | 03-10 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 8일 | 56시간 | 03-01 | |
| [주말저녁]SQL기초에서
Schema Object까지 |
총 | 10일 | 40시간 | 03-01 | |
댓글 없음:
댓글 쓰기