[ORACLE] SP에서 테이블 생성

프로시저에서 테이블 생성 방법(DBMS_SQL 패키지)
---- From . OracleClub.com

제 목 프로시저에서 테이블 생성 방법(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;');