EXECUTE Immediate Usage Summary _oracle

It resolves and immediately executes a dynamic SQL statement or a pl/sql block created by the runtime. Dynamic creation and execution of SQL statements ahead of time, the goal of execute immediate is to reduce enterprise costs and achieve higher performance, which is fairly easy to encode compared to the past. Although Dbms_ SQL is still available, but it is recommended that execute IMMEDIATE be used because it gains a benefit on top of the package.
--Use skills
1. Execute immediate will not submit a DML transaction execution and should explicitly commit
If the DML command is processed through execute immediate,
Then 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 '
into l_cnt;
Dbms_output.put_line (L_CNT);
End
4. Dynamic invocation routines. The parameter type must be specified for the binding variable parameter 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); 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. 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 '
into EMPDTL;
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 where deptno =: 1 '
Into L_nam, L_loc
Using L_dept;
End
7. Multiline query options. Populate the temporary table with an INSERT statement for this option.
Use a temporary table for further processing, or you can use ref cursors 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 previously possible.
When attempting to execute a dynamic statement, it is more important to handle the exception appropriately. Attention should be paid to capturing all possible exceptions.