Oracle Execute Immediate__oracle

Turn from: http://blog.sina.com.cn/s/blog_621a2bdf0100gayl.html

When we encounter a class of stored names and procedures that are similar to each other, you can use execute immediate to execute these stored procedures dynamically, and in this project I have a class of stored procedures that begin with "P_insertinto_", followed by a table name such as: P_insertinto _AC01, P_INSERTINTO_AC02, etc., used to give AC01, AC02 ... These tables are inserted into the data, and we can only execute these processes dynamically using execute immediate, which is much more convenient for speed and code writing.
This method has been studied half days, not easy ah, hehe .... , and eventually it was a success.
My understanding of the usage of execute immediate with an example, there is a stored procedure named: P_test, which has three parameters, namely two input parameters and one output parameter, the implementation process such as:
Declare
V_sql VARCHAR2 (1000);
C VARCHAR2 (1000);
A VARCHAR2 (1): = ' 1 ';
B Number: = 1;
Begin
-Of course, dynamic stored procedures can be implemented here, directly into the V_sql variable (here is a specified process p_test)
V_sql:= ' Begin P_test (: V1,:V2,:V3); end; ';
Execute immediate v_sql usingin ' 1 ', in ' 2 ', out C;
-or execute immediate v_sql using ' 1 ', ' 2 ', out C;
Dbms_output.put_line (c);
End
Above we implement the dynamic transfer parameters and dynamic stored procedures of the call method. It is noteworthy that: the default is In,in, out can not be province.
There is also a noteworthy place: each dynamic (execute immediate) implementation of the method has its own begin.........end; just do it, if there are more than one:
--The first
Begin
V_sql:= ' Begin P_test1 (: V1,:V2,:V3); end; ';
Execute immediate v_sql usingin ' 1 ', in ' 2 ', out C;
-or execute immediate v_sql using ' 1 ', ' 2 ', out C;
Dbms_output.put_line (c);
End
--A second
Begin
V_sql:= ' Begin P_TEST2 (: V1,:V2,:V3); end; ';
Execute immediate v_sql usingin ' 1 ', in ' 2 ', out C;
-or execute immediate v_sql using ' 1 ', ' 2 ', out C;
Dbms_output.put_line (c);
End
。。。。。。。
--Nth
Begin
--
End

Execute immediate replaces the previous Oracle8i Dbms_sqlpackage package. It parses and immediately executes a dynamic SQL statement or a pl/that is not created at run time SQL block. Dynamic creation and execution of SQL statements in advance, the goal of ExecuteImmediate is to reduce enterprise costs and achieve higher performance, which is fairly easy to code before. Although Dbms_sql is still available, it is recommended to use ExecuteImmediate, Because it gains on the package.

Use Tips

1. Execute immediate will not submit a DML transaction execution and should explicitly commit
If you are handling DML commands through execute immediate, you need to explicitly commit or as part of execute immediate yourself before you finish. If the DDL command is processed through execute immediate, it submits all previously changed data

2. Queries that return multiple rows are not supported, and this interaction uses a temporary table to store the records (refer to the example below) or use ref cursors.

3. When executing SQL statements, do not use semicolons, when the Pl/sql block is executed, with a semicolon at its tail.

4. In the Oracle manual, these features are not covered in detail. The following example shows all the possible aspects of using execute immediate. Hope to bring you convenience.

5. For forms developers, the forms 6i cannot use this feature when in Pl/sql 8.0.6.3. Version.

EXECUTE Immediate Usage Example

1. Run DDL statements in Pl/sql

Begin
Execute immediate ' set role all ';
End

2. Assign values to dynamic statements (USING clause)

Declare
L_depnam varchar2: = ' testing ';
L_loc Varchar2 (a): = ' Dubai ';
Begin
Execute immediate ' INSERT INTO dept values (: 1,: 2,:3) '
Using L_depnam, L_loc;
Commit
End

3. Retrieving values from dynamic statements (into clauses)

Declare
l_cnt varchar2 (20);
Begin
Execute immediate ' SELECT COUNT (1) from EMP '
intol_cnt;
Dbms_output.put_line (L_CNT);
End

4. Dynamic invocation routines. The parameter type must be specified for the binding variable argument used in the routine. 黓 thinks in type, other types must explicitly specify

Declare
L_routin varchar2 (MB): = ' gen2161.get_rowcnt ';
L_tblnam varchar2: = ' emp ';
L_CNT number;
L_status VARCHAR2 (200);
Begin
Execute immediate ' begin ' | | L_routin | | ' (: 2,: 3,: 4);
Using in L_tblnam, out l_cnt, in Outl_status;

If L_status!= ' OK ' then
Dbms_output.put_line (' error ');
End If;
End

5. Pass the return value to the Pl/sql record type; You can also use the%rowtype variable

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 '
INTOEMPDTL;
End

6. Pass and retrieve values. The INTO clause is used before 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 Wheredeptno =: 1 '
Into L_nam,l_loc
Using L_dept;
End

7. Multiline query options. Use the INSERT statement to populate the temporary table with the temporary table for further processing, or you can use Refcursors to correct this shortcoming.

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

For processing dynamic statements, EXECUTE immediate is easier and more efficient than it might have been before. When intent to execute dynamic statements, it is more important to handle the exception appropriately. Attention should be paid to capturing all possible exceptions.