DBMS_SQL Package versus Native Dynamic SQL

원본출처 : http://211.209.69.159:8000/Orastudy/board.jsp?bbs=tiptech&pg=0&seq=368&act=view

This articles highlights the advantages of using the DBMS_SQL package and 
Native Dynamic SQL. Code examples of each method are also provided for 
illustration, but are not exhaustive. 

Beginning with Oracle 8.1.5, two methods are available for implementing dynamic 
SQL within PL/SQL - DBMS_SQL and Native Dynamic SQL. The DBMS_SQL package is a 
PL/SQL built-in which offers a programmatic API. Native dynamic SQL (available 
only in Oracle 8i) enables the placement of dynamic SQL statements directly 
into PL/SQL code. 

Supported statements include DML, DDL, SQL, and PL/SQL anonymous blocks. 

In order to implement native dynamic SQL, the COMPATIBLE initialization 
parameter must be set to 8.1.0 or higher. 


Advantages 
---------- 

The following highlights the advantages of each method: 

Advantages of Native Dynamic SQL 

?It is easier to implement because it can be used in the same manner as 
    static SQL is used within PL/SQL code. 
?There are no cumbersome set of procedures and functions which must 
    be invoked in a particular sequence. Therefore, the code is more 
    compact and maintainable. 
?Minimal data copying and procedure call overhead is involved. Statement 
    preparation, binding, and execution are bundled into a single step. 
?Support is provided for object types, collections, and REFs. 
?Support is provided for fetching into records. For example, rows 
    returned from a query can be directly fetched into PL/SQL records. 
?The SET ROLE command can be implemented using invoker's rights via the 
    AUTHID [CURRENT_USER] clause. The AUTHID clause is allowed only 
    in the header of a stand-alone function, standalone procedure, 
    package specification, or an object type specification. 

Advantages of DBMS_SQL Package 

?Support is provided for client-side programs. Each call made to the 
    DBMS_SQL from the client-side translates to a PL/SQL remote procedure 
    call (RPC). 
?The DESCRIBE facility is available using the DESCRIBE_COLUMNS procedure. 
    This is used to describe the columns from a cursor opened and parsed. 
    This is similar to the describe command in SQL*Plus. 
?Support for processing bulk SQL is provided. This allows multiple rows 
    of data to be selected, inserted, updated, or deleted in a single call, 
    reducing overhead and improving performance (Oracle8 and later only). 
?Support is provided for SQL statements larger than 32KB. 
?The ability to re-use parsed SQL statements using the PARSE procedure. 
    For example, when a SQL statement is parsed, it can be used with various 
    sets of bind arguments. 


Code Samples 
------------ 

The follow sets of code examples depict how to accomplish the same task using 
DBMS_SQL and Native Dynamic SQL. 

Prior to executing the code, create a dept_new table as follows: 
                                                                                             
    CREATE TABLE dept_new 
                      (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); 

Example of DBMS_SQL 

CREATE OR REPLACE PROCEDURE dbms_example 
    (deptnum IN dept_new.deptno%TYPE, 
      deptname IN dept_new.dname%TYPE, 
      location IN dept_new.loc%TYPE) IS 

    stmt_str varchar2(100); 
    rows_processed NUMBER; 
    cur_hdl NUMBER; 

BEGIN 
    stmt_str := 'INSERT INTO dept_new VALUES(:deptno, :dname, :loc)'; 
    cur_hdl := DBMS_SQL.OPEN_CURSOR; 
    DBMS_SQL.PARSE(cur_hdl,stmt_str,DBMS_SQL.NATIVE); 
    DBMS_SQL.BIND_VARIABLE(cur_hdl,':deptno',deptnum); 
    DBMS_SQL.BIND_VARIABLE(cur_hdl,':dname', deptname); 
    DBMS_SQL.BIND_VARIABLE(cur_hdl,':loc',location); 

    rows_processed := dbms_sql.execute(cur_hdl); 

    DBMS_SQL.CLOSE_CURSOR(cur_hdl); 
END; 
/ 

Example of Native Dynamic SQL 

CREATE OR REPLACE PROCEDURE native_example 
    (deptnum dept_new.deptno%TYPE, 
      deptname dept_new.dname%TYPE, 
      location dept_new.loc%TYPE) IS 

    stmt_str varchar2(100); 

BEGIN 
    stmt_str := 'INSERT INTO dept_new VALUES(:deptno, :dname, :loc)'; 
    EXECUTE IMMEDIATE stmt_str USING deptnum, deptname, location; 
END; 
/ 


Additional Note 
--------------- 

Bulk dynamic SQL can be simulated using native dynamic SQL by placing the 
bulk SQL within a BEGIN..END block and executing the block dynamically. 

The following is an example of Bulk SQL using Native Dynamic SQL: 

    1. First create the necessary tables: 

            CREATE TABLE bulk1 (ename VARCHAR2(50)) 
            CREATE TABLE bulk2 (ename VARCHAR2(50)) 

    2. Next, insert data into bulk1: 

            INSERT INTO bulk1 VALUES('MARY JANE'); 
            INSERT INTO bulk1 VALUES('JOHN DOE'); 
            INSERT INTO bulk1 VALUES('MICHAEL DAVIS'); 

    3. Next, create a VARRAY: 

            CREATE OR REPLACE TYPE name_array_type IS 
                VARRAY(100) of VARCHAR2(50); 
            / 

    4. Next, create the procedure: 

            CREATE OR REPLACE PROCEDURE copy_ename_column 
                (table1 VARCHAR2, table2 VARCHAR2) IS 
              ename_col NAME_ARRAY_TYPE; 

              BEGIN 
                    --bulk fetch the 'ename' column into a VARRAY of VARCHAR2s. 
                    EXECUTE IMMEDIATE 
                      'BEGIN 
                            SELECT ENAME BULK COLLECT INTO :tab 
                                FROM ' || table1 || '; 
                        END;' 
                  USING OUT ename_col; 

                  --bulk fetch the 'ename' column into another table. 
                  EXECUTE IMMEDIATE 
                      'BEGIN 
                          FORALL i IN :tab.first..:tab.last 
                              INSERT INTO ' || table2 || ' VALUES (:tab(i)); 
                    END;' 
                  USING ename_col.first, ename_col.last, ename_col; 
              END; 
            / 

    5. Next, execute the procedure: 

            SQL> exec copy_ename_column('bulk1','bulk2'); 

    6. Finally, verify results: 

            SQL> select * from bulk2;