[ Oracle - Procedure Package_DBMS_SQL_사용법1 ]

 

Dynamic SQL

1.  Using The DBMS_SQL Package To Execute DDL Statements:   

 < Example 1 >

    Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.   

 CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS   

   cursor1 INTEGER;   

 BEGIN   

   cursor1 := dbms_sql.open_cursor;   

   dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);   

   dbms_sql.close_cursor(cursor1);   

 end;   

 /   

     

 SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');

 

 < Example 2 >

 CREATE or REPLACE PROCEDURE rows_greater_than (low_value  number) AS   

   cursor1  integer;   

   rows_processed  integer;   

   myempno number;   

   myename varchar2(20);   

 BEGIN   

   cursor1 := dbms_sql.open_cursor;   

   dbms_sql.parse (cursor1, 'select empno, ename from emp where empno > :x', dbms_sql.v7);   

   dbms_sql.bind_variable(cursor1, 'x', low_value);   

   dbms_sql.define_column (cursor1, 1, myempno);   

   dbms_sql.define_column (cursor1, 2, myename, 20);   

   rows_processed := dbms_sql.execute (cursor1);   

   loop   

     if dbms_sql.fetch_rows (cursor1) > 0 then   

       dbms_sql.column_value (cursor1, 1, myempno);   

       dbms_sql.column_value (cursor1, 2, myename);   

     else   

       exit;   

     end if;   

   end loop;   

   dbms_sql.close_cursor (cursor1);   

 EXCEPTION   

   WHEN OTHERS THEN   

     dbms_output.put_line(sqlerrm);   

     if dbms_sql.is_open (cursor1) then   

       dbms_sql.close_cursor (cursor1);   

     end if;   

 END;   

 /

 

Posted by 농부지기
,