프로시저에서 테이블 및 프로시저 생성 방법

DBMS_SQL 패키지의 구성
  - FUNCTION OPEN_CURSOR : SQL문의 실행에 필요한 새로운 CURSOR를 열고 CURSOR ID number를 반환 한다.
  - FUNCTION IS_OPEN : 주어진 CURSOR가 현재 open되어 있으면 TRUE를, 아니면 FALSE를 반환 한다.
  - PROCEDURE PARSE : statement를 check하고 CURSOR와 결합시킨다.
  - PROCEDURE DEFINE_COLUMN : CURSOR로부터 SELECT된 COLUMN의 값을 받는 변수를 지정한다.
  - FUNCTION EXECUTE : SQL문을 실행하고 처리된 ROW의 수를 반환 한다. (INSERT, UPDATE, DELETE인 경우에만 해당)
  - FUNCTION FETCH_ROWS : CURSOR로부터 ROW를 FETCH하고 실제로 FETCH된 ROW의 수를 반환 한다. 이 ROW들은 BUFFER에 들어가며, column_value를 호출하여 읽어 들여야 한다.
  - FUNCTION EXECUTE_AND_FETCH : EXECUTE와 FETCH ROW를 동시에 수행하고 실제로 FETCH된 ROW의 수를 반환 한다.
  - PROCEDURE VARIABLE_VALUE : 주어진 변수의 값을 반환 한다.
  - PROCEDURE COLUMN_VALUE : FETCH_ROWs에 의해 FETCH된 data의 값을 반환 한다.
  - 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];
 
-- 권한 확인
   
SELECT * FROM USER_TAB_PRIVS;
 
-- 권한의 부여 
SQL> GRANT CREATE ANY TABLE TO scott;
권한이 부여되었습니다.
 
SQL> GRANT EXECUTE ON DBMS_SQL TO scott;
권한이 부여되었습니다.
 
SQL> CONN scott/tiger;
연결되었습니다.
 
-- DBMS_OUTPUT.PUT_LINE을 출력
SQL> SET SERVEROUTPUT ON ; 


-- 프로시저의 생성 
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))
 
-- 테이블을 확인해 봅니다. 
SQL> DESC LOG20010920;
  이름                널?      유형
 ----------- -------- ---------------
 A                    NUMBER
 B                   VARCHAR2(10) 
    
프로시저에서 프로시저 생성 예제

 
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;');
 

출처 : http://www.oracleclub.com/lecture/1146