EXECUTE IMMEDIATE

오라클에서 처음으로 만들어 본 Dynamic SQL, MS-SQL에서 많이 했던 작업이었는뎅..

뭐랄까 귀찮다고 해야 되나.. DDL이 procedure에서 안되게 만든 이유에 대해 생각해봐야 겠다.

 

CREATE OR REPLACE PROCEDURE sp_apply_off_apply_final

IS

   v_sql    LONG;

   v_temp   VARCHAR2 (500);

 

   CURSOR cur_exists_table

   IS

      SELECT table_name

      FROM   user_tables

      WHERE  table_name = 'TEMP_APPLY_FINAL';

 

   CURSOR cur_exists_index

   IS

      SELECT index_name

      FROM   user_indexes

      WHERE  index_name = 'IDX_TEMP_APPLY_FINAL1';

/*********************************************************************   NAME:       sp_apply_off_apply_final

   PURPOSE:

 

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        2006-12-15   lee yong hwan       1. Created this procedure.

 

   NOTES:

 

   Automatically available Auto Replace Keywords:

      Object Name:     sp_apply_off_apply_final

      Sysdate:         2006-12-15

      Date and Time:   2006-12-15, ¿ÀÈÄ 5:53:56, and 2006-12-15 ¿ÀÈÄ 5:53:56

      Username:        lee yong hwan (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)

 

**********************************************************************/

BEGIN

   DBMS_OUTPUT.ENABLE ();

   DBMS_OUTPUT.put_line ('confirm registr -->' || ' start...');

   DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));

 

-- drop temp table if exists

   OPEN cur_exists_table;

 

   FETCH cur_exists_table

   INTO  v_temp;

 

   IF cur_exists_table%FOUND

   THEN

      v_sql := 'DROP TABLE TEMP_APPLY_FINAL';

 

      EXECUTE IMMEDIATE v_sql;

   END IF;

 

   CLOSE cur_exists_table;

 

-- drop temp index if exists

   OPEN cur_exists_index;

 

   FETCH cur_exists_index

   INTO  v_temp;

 

   IF cur_exists_index%FOUND

   THEN

      v_sql := 'DROP INDEX IDX_TEMP_APPLY_FINAL1';

 

      EXECUTE IMMEDIATE v_sql;

   END IF;

 

   CLOSE cur_exists_index;

 

-- create temp table

   v_sql :=

         'CREATE GLOBAL TEMPORARY TABLE TEMP_APPLY_FINAL ( '

      || 'D_YEAR    NUMBER(4), '

      || 'I_COURSE  NUMBER(10), '

      || 'Q_PERNUM  NUMBER(3), '

      || 'I_MBRCD   NUMBER(10), '

      || 'RNO       VARCHAR2(3 BYTE) '

      || ') '

      || 'ON COMMIT DELETE ROWS';

 

   EXECUTE IMMEDIATE v_sql;

 

-- create temp index

   v_sql :=

         'CREATE INDEX IDX_TEMP_APPLY_FINAL1 ON TEMP_APPLY_FINAL'

      || '(D_YEAR, I_COURSE, Q_PERNUM, I_MBRCD)';

 

   EXECUTE IMMEDIATE v_sql;

 

-- insert into temp table

   v_sql :=

         'INSERT INTO TEMP_APPLY_FINAL '

      || 'SELECT b.d_year, b.i_course, b.q_pernum, c.i_mbrcd, a.rno '

      || 'FROM   edcu.regist a, lms.tlm_period b, lms.tlm_member c '

      || 'WHERE  TO_CHAR (b.i_course) = rgu || LPAD (rcode1, 5, ''0'') '

      || 'AND    a.rbuter = TO_CHAR (b.d_pstt, ''YYYYMMDD'') '

      || 'AND    a.rgaji = TO_CHAR (b.d_pend, ''YYYYMMDD'') '

      || 'AND    a.rjumin = c.i_jumin';

 

   EXECUTE IMMEDIATE v_sql;

 

   -- insert or update

   v_sql :=

         'MERGE INTO lms.tlm_stdnum a '

      || 'USING (SELECT pkg_transfer_common.fn_get_tlm_stdnum_seq AS i_crsstudnumcd, '

      || 'a.i_detappcd, a.i_mbrcd, a.i_lecappcd, a.d_year, '

      || 'a.i_course, a.q_pernum, a.i_lecjob, a.i_lecfinishst, '

      || 'b.rno AS o_studnum '

      || 'FROM   lms.tld_lecapp a, temp_apply_final b '

      || 'WHERE  a.d_year = b.d_year '

      || 'AND    a.i_course = b.i_course '

      || 'AND    a.q_pernum = b.q_pernum '

      || 'AND    a.i_mbrcd = b.i_mbrcd) b '

      || 'ON (a.i_detappcd = b.i_detappcd) '

      || 'WHEN MATCHED THEN '

      || 'UPDATE '

      || 'SET a.i_mbrcd = a.i_mbrcd '

      || 'WHEN NOT MATCHED THEN '

      || 'INSERT (a.i_crsstudnumcd, a.i_detappcd, a.i_mbrcd, a.i_lecappcd, '

      || 'a.d_year, a.i_course, a.q_pernum, a.i_lecjob, '

      || 'a.i_lecfinishst, a.o_studnum) '

      || 'VALUES (b.i_crsstudnumcd, b.i_detappcd, b.i_mbrcd, b.i_lecappcd, '

      || 'b.d_year, b.i_course, b.q_pernum, b.i_lecjob, '

      || 'b.i_lecfinishst, b.o_studnum)';

 

   EXECUTE IMMEDIATE v_sql;

 

   DBMS_OUTPUT.put_line (   '   lms.tlm_stdnum '

                         || TO_CHAR (SQL%ROWCOUNT)

                         || '°³ Àû¿ë...');

   DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));

   COMMIT;

-- drop temp index

   v_sql := 'DROP INDEX IDX_TEMP_APPLY_FINAL1';

 

   EXECUTE IMMEDIATE v_sql;

 

-- drop temp table

   v_sql := 'DROP TABLE  TEMP_APPLY_FINAL';

 

   EXECUTE IMMEDIATE v_sql;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line ('error message : no data found...');

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('error code : ' || TO_CHAR (SQLCODE) || '...');

      DBMS_OUTPUT.put_line ('error message : ' || SQLERRM || '...');

      RAISE;

END sp_apply_off_apply_final;

/

 

 

Oracle Document

 

One of the nicest things about native dynamic SQL is its simplicity. Unlike DBMS_SQL, which has dozens of programs and lots of rules to follow, native dynamic SQL has been integrated into the PL/SQL language by adding one new statement, EXECUTE IMMEDIATE, and by enhancing the existing OPEN FOR statement.
The EXECUTE IMMEDIATE Statement
The EXECUTE IMMEDIATE statement executes a specified SQL statement immediately. You can use EXECUTE IMMEDIATE for any SQL statement of PL/SQL block, except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise it will be treated as either DML (Data Manipulation Languate -- SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.
When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.

Native dynamic SQL supports all SQL datatypes available in Oracle 8i. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, native dynamic SQL does not support dataypes that are specific to PL/SQL, such as Booleans, index-by tables, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.

Following are a few examples using the EXECUTE IMMEDIATE statement:

Create an index:
EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';

Create a stored procedure that will execute any DDL statement:
CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;
/
With execDDL in place, I can create that same index as follows:

execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');

Obtain the count of rows in any table, in any schema, for the specified WHERE clause:
CREATE OR REPLACE FUNCTION tabCount (
   tab IN VARCHAR2,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
   retval INTEGER;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT COUNT(*)
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' WHERE ' || NVL (whr, '1=1')
      INTO retval;
   RETURN retval;
END;
/

The OPEN FOR Statement
The OPEN FOR statement is not brand-new to PL/SQL in Oracle8i; it was first offered in Oracle7 to support cursor variables. Now the OPEN FOR statement allows the user to implement multiple-row dynamic queries. With DBMS_SQL, you go through a long series of steps to implement multi-row queries: parse, bind, define each column individually, execute, fetch, extract each column value individually.
For native dynamic SQL, Oracle took an existing feature and syntax—that of cursor variables—and extended it in a very natural way to support dynamic SQL. A cursor variable is a variable of type REF CURSOR, or referenced cursor. Here is an example of a declaration of a cursor variable based on a “weak” REF CURSOR (the sort you will use for native dynamic SQL):

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type;
A cursor variable points to a cursor object; it is, however, a variable. You can have more than one variable pointing to the same cursor object, you can assign one cursor variable to another, and so on. Once you have declared a cursor variable, you can assign a value to it by referencing it in an OPEN FOR statement:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type;
BEGIN
   OPEN cv FOR SELECT COUNT(guns) FROM charlton_heston_home;
In this example, the query is static—it is not contained in single quotes, and it is frozen at compilation time. That is the only way we have been able to work with cursor variables until Oracle8i. Now we can use the same syntax as before, but the query can be a literal or an expression, as in the following:

OPEN dyncur FOR SQL_string;
or, to show the use of a bind argument:
OPEN dyncur FOR
   'SELECT none_of_the_above FROM senate_candidates
     WHERE state = :your_state_here'
   USING state_in;
Once you have opened the query with the OPEN FOR statement, the syntax used to fetch rows, close the cursor variable and check to see that the attributes of the cursor are all the same as for static cursor variables and hard coded explicit cursors for that matter.

To summarize, there are two differences between the OPEN FOR statement for static and dynamic SQL:

The static version does not support the USING clause of NDS.

The static version requires a static SQL statement after the FOR keyword, whereas with native dynamic SQL, the SQL string is always either a literal string or an expression that evaluates to a string.