execute immediate 技巧

EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL

資料來源: 這裡

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

Usage tips

1. EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done.

If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly commit any changes that may have been done before or as part of the EXECUTE IMMEDIATE itself. If the DDL command is processed via EXECUTE IMMEDIATE, it will commit all previously changed data.

2. Multi-row queries are not supported for returning values, the alternative is to use a temporary table to store the records (see example below) or make use of REF cursors.

3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.

4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate. Hope it is handy.

5. For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3.
Example of EXECUTE IMMEDIATE usage

1. To run a DDL statement in PL/SQL.

BEGIN
 EXECUTE IMMEDIATE 'set role all';
END;

2. To pass values to a dynamic statement (USING clause).

DECLARE
 l_depnam VARCHAR2(20) := 'testing';
 l_loc    VARCHAR2(10) := 'Dubai';
BEGIN
 EXECUTE IMMEDIATE 'insert into dept values (:1, :2, :3)'
   USING 50, l_depnam, l_loc;
 COMMIT;
END;

3. To retrieve values from a dynamic statement (INTO clause).

DECLARE
 l_cnt    VARCHAR2(20);
BEGIN
 EXECUTE IMMEDIATE 'select count(1) from emp'
   INTO l_cnt;
 dbms_output.put_line(l_cnt);
END;

4. To call a routine dynamically: The bind variables used for parameters of the routine have to be specified along with the parameter type. IN type is the default, others have to be specified explicitly.

DECLARE
 l_routin   VARCHAR2(100) := 'gen2161.get_rowcnt';
 l_tblnam   VARCHAR2(20) := 'emp';
 l_cnt      NUMBER;
 l_status   VARCHAR2(200);
BEGIN
 EXECUTE IMMEDIATE 'begin ' || l_routin || '(:2, :3, :4); end;'
   USING IN l_tblnam, OUT l_cnt, IN OUT l_status;
 
 IF l_status != 'OK' THEN
    dbms_output.put_line('error');
 END IF;
END;

5. To return value into a PL/SQL record type: The same option can be used for %rowtype variables also.

DECLARE
 TYPE empdtlrec IS RECORD (empno  NUMBER(4),
                           ename  VARCHAR2(20),
                           deptno  NUMBER(2));
 empdtl empdtlrec;
BEGIN
 EXECUTE IMMEDIATE 'select empno, ename, deptno ' ||
                   'from emp where empno = 7934'
   INTO empdtl;
END;

6. To pass and retrieve values: The INTO clause should precede the USING clause.

DECLARE
 l_dept    PLS_INTEGER := 20;
 l_nam     VARCHAR2(20);
 l_loc     VARCHAR2(20);
BEGIN
 EXECUTE IMMEDIATE 'select dname, loc from dept where deptno = :1'
   INTO l_nam, l_loc
   USING l_dept ;
END;

7. Multi-row query option. Use the insert statement to populate a temp table for this option. Use the temporary table to carry out further processing. Alternatively, you may use REF cursors to by-pass this drawback.

DECLARE
 l_sal   PLS_INTEGER := 2000;
BEGIN
 EXECUTE IMMEDIATE 'insert into temp(empno, ename) ' ||
                   '          select empno, ename from emp ' ||
                   '          where  sal > :1'
   USING l_sal;
 COMMIT;
END;

EXECUTE IMMEDIATE is a much easier and more efficient method of processing dynamic statements than could have been possible before. As the intention is to execute dynamic statements, proper handling of exceptions becomes all the more important. Care should be taken to trap all possible exceptions.