[펌]프로시저에서 테이블 생성 방법(DBMS_SQL 패키지)

▣ DBMS_SQL package의 구성

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를 닫는다.
 
 

▣ 프로시저에서 테이블 생성 예제

sys user로 접속해서 대상 유저한테 권한을 부여 합니다.
> @?/rdbms/admin/dbmssql.sql 
> grant create any table to [user_name];
> grant execute on dbms_sql to [user_name];
 
 
SQL> conn sys/change_on_install
연결되었습니다.

-- 권한의 부여
SQL> grant create any table to scott;
권한이 부여되었습니다.
 
SQL> grant execute on dbms_sql to scott;
권한이 부여되었습니다.
 
SQL> conn scott/tiger;
연결되었습니다.
 
 
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)

-- 프로시저의 생성
SQL> CREATE OR REPLACE PROCEDURE credb
         IS
 
                cursor1 INTEGER;
                dbdate VARCHAR2(10);
                credbsql VARCHAR2(100);
   
        BEGIN
 
                -- sysdate를 얻어옴
                SELECT to_char(sysdate,'RRRRMMDD')
                INTO dbdate
                FROM dual;
 
                -- CREATE TABLE명령어 생성
                credbsql := 'CREATE TABLE LOG' || dbdate || ' (a number, b varchar2(10))';
               
                --CREATE TABLE명령어 화면에 출력 
                DBMS_OUTPUT.PUT_LINE(credbsql);
 
                -- 테이블 생성
                cursor1 := DBMS_SQL.OPEN_CURSOR; 
                DBMS_SQL.PARSE(cursor1, credbsql, dbms_sql.v7);
                DBMS_SQL.CLOSE_CURSOR(cursor1);
               
        END;
        /
 프로시저가 생성 되었습니다.
 
 SQL>exec credb;
 CREATE TABLE LOG20010920 (a number, b varchar2(10))
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
테이블을 확인해 봅니다.
SQL>DESC LOG20010920;
  이름                                      널?      유형
 -------------------------------- -------- ---------------
 A                                                  NUMBER
 B                                                  VARCHAR2(10)
 
 LOG20010920 테이블이 생성된 것을 확인 할 수 있습니다.
 

▣ DBMS_SQL package를 이용한 프로 시져 생성의 예

SQL>CREATE OR REPLACE PROCEDURE test(txt varchar2)  AS

                c integer;
                rows integer;
 
        BEGIN
 
                c := DBMS_SQL.OPEN_CURSOR;
                DBMS_SQL.PARSE(c,'create or replace '|| txt ,DBMS_SQL.NATIVE);
                rows := DBMS_SQL.EXECUTE(c);
                DBMS_SQL.CLOSE_CURSOR(c);
        END;
 

SQL>exec test('procedure ttum(aa  number) AS  kk number; BEGIN kk:= 0 ; kk := kk + aa ; END ; ');
 
SQL> exec test ('procedure ttt1(aa number) as kk varchar2(10); begin select 1 into kk from dual; end;');