Oracle Database TIP
프로시저에서 테이블 생성 방법(DBMS_SQL 패키지) 9 2 42,001

by 김정식 DBMS_SQL [2002.02.03]


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];
 

-- 권한의 부여 
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;');
 
    

- 강좌 URL : http://www.gurubee.net/lecture/1146

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 조성식 [2005.08.08 15:33:56]
오라클 10g에서 그대로 따라하면 다음과 같은 에러가 발생합니다.
10g라서 그런건지? 왜 그럴까요?

ORA-04067: 실행불가, package body "SYS.DBMS_SQL"가 존재하지 않습니다
ORA-06508: PL/SQL: 호출되는 lib 단위를 발견할 수 없습니다
ORA-06512: "UCMDBA.CREDB", 줄 22에서
ORA-06512: 줄 1에서

by 써니 [2008.04.13 18:37:23]
패키지 실행 권한이 없는 일반 유저이거나, 해당 패캐지가 설치되지 않은 것으로 추측해 봅니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입