posted by 구름너머
2004. 10. 15. 09:59
|
1. EXECUTE IMMEDIATE를 이용한 Dynamic SQL
Oracle 8i에서의 Dynamic SQL은 두가지 방법이 있습니다.
첫번째 방법은 "EXECUTE IMMEDIATE" 절을 사용하여 embedded dynamic sql을 구현하는
방법이고,
두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는 ref cursor의 확장된 개념으로
query를 위해 사용되어지는 방법 입니다.
여기서는 EXECUTE IMMEDIATE를 이용한 Dynamic SQL에 대해서 알아보겠습니다.
[Syntax]
EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record }]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...];
|
2. 간단하게 테이블을 생성하는 예제 입니다
첫번째 예제는 간단하게 테이블을 생성하는 예제 입니다.
==========================================================
CREATE OR REPLACE PROCEDURE dynamic_sql_01
IS
str varchar2(200);
BEGIN
str := 'CREATE TABLE total (total number)';
EXECUTE IMMEDIATE str;
END;
==========================================================
프로시저가 생성되었습니다.
-- 프로시저를 실행해서 테이블을 생성 합니다.
SQL> EXEC dynamic_sql_01;
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 생성된 테이블을 확인해 봅니다.
SQL> DESC total;
이름 널? 유형
----------------------------------------- -------- -----------
TOTAL NUMBER
* 프로시저 생성시 "ORA-01031: 권한이 불충분합니다" 에러가 발생하면
system유저로 접속을 해서 EXECUTE IMMEDIATE를 실행하는 유저에게
CREATE ANY TABLE 권한을 부여 합니다.
SQL> CONN system/manager
SQL> GRANT create any table TO scott;
|
3. 테이블 생성 후 INSERT 예제
두번째 예제는 TABLE_ROWS라는 테이블을 생성하고, 다이나믹 하게 테이블명을 입력하면
테이블명과 테이블에 등록된 데이터수를 TABLE_ROWS에 INSERT하고 출력하는 예제 입니다.
===============================================================
CREATE OR REPLACE PROCEDURE dynamic_sql_02
(v_table_name IN VARCHAR2)
IS
v_str VARCHAR2(200);
v_cnt NUMBER;
v_temp VARCHAR2(50);
CURSOR cur_exists IS
SELECT TABLE_NAME
FROM USER_TABLES
WHERE table_name = 'TABLE_ROWS';
BEGIN
OPEN cur_exists
FETCH cur_exists INTO v_temp;
-- 테이블이 존재하면 테이블을 삭제 합니다.
IF cur_exists%FOUND THEN
v_str := 'DROP TABLE table_rows';
EXECUTE IMMEDIATE v_str;
END IF;
-- 테이블 생성
v_str := 'CREATE TABLE table_rows (total number, table_name varchar2(50))';
EXECUTE IMMEDIATE v_str;
-- 데이터 카운트 조회
v_str := 'SELECT COUNT(*) cnt FROM '||v_table_name ;
EXECUTE IMMEDIATE v_str INTO v_cnt ;
-- 데이터 insert
v_str := 'INSERT INTO table_rows VALUES ('||v_cnt||', :A1 )';
EXECUTE IMMEDIATE v_str USING v_table_name;
DBMS_OUTPUT.PUT_LINE(' 테이블 명 : '||v_table_name||' 데이터 수 : '||v_cnt);
CLOSE cur_exists;
END;
/
===============================================================
프로시저가 생성되었습니다.
SQL> SET SERVEROUTPUT ON:
-- emp 테이블명과 테이블의 데이터카운트를 INSERT합니다.
SQL> EXEC dynamic_sql_02('emp');
테이블 명 : emp 데이터 수 : 14
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 정상적으로 처리되었는지 확인해 봅니다.
SQL> SELECT * FROM table_rows;
TOTAL TABLE_NAME
---------- --------------
14 emp
|
|
|
