Execute Immediate replaces the dbms_ SQL package in Oracle8i. It parses and immediately executes dynamic SQL statements or pl / SQL block. Advanced Performance in dynamically creating and executing SQL statements, Execute Immediate is designed to reduce enterprise costs and achieve high performance, which is relatively easy to code than before. Although dbms_ SQL is still available, it is recommended to use execute immediate because it gains more than the package.
Tips
1 . Execute Immediate will not commit a DML transaction execution and should be committed explicitly
If you use execute immediate to process DML commands, You need to submit the command explicitly or as part of execute immediate before it is completed. If you use execute immediate to process DDL commands, it submits all previously changed data.
2 . Multi-row query is not supported. This interaction uses a temporary table to store records (see the following example) or ref cursors.
3 . Do not use semicolons when executing SQL statements. / Use a semicolon at the end of an SQL block.
4 . These features are not covered in detail in the Oracle manual. The following example shows all possible ways to use execute immediate.
5 For forms developers / SQL 8.0 . 6.3 In. Version, forms 6i cannot use this function.
Execute Immediate usage example
1 . In PL / Run DDL statements in SQL
Begin
Execute Immediate ' Set role all ' ;
End ;
2 . Pass values to dynamic statements (using clause)
Declare
Rochelle depnam Varchar2 ( 20 ): = ' Testing ' ;
Rochelle Loc Varchar2 ( 10 ): = ' Dubai ' ;
Begin
Execute Immediate ' Insert into dept values (: 1,: 2,: 3) '
Using 50 , L_depnam, l_loc;
Commit ;
End ;
3 . Retrieve values from dynamic statements (into clause)
Declare
L_cnt Varchar2 ( 20 );
Begin
Execute Immediate ' Select count (1) from EMP '
Into Rochelle CNT;
Dbms_output.put_line (l_cnt );
End ;
4 . Dynamic call routine. The Bind Variable Parameter used in the routine must specify the parameter type. Alias is considered as the in type, and Other types must be explicitly specified.
Declare
Rochelle routin Varchar2 ( 100 ): = ' Gen2161.get _ rowcnt ' ;
Rochelle blnam Varchar2 ( 20 ): = ' EMP ' ;
L_cnt Number ;
L_status Varchar2 ( 200 );
Begin
Execute Immediate ' Begin ' | Rochelle routin | ' (: 2,: 3,: 4); end; '
Using In Rochelle blnam, 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 PL / SQL record type; also available % 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 the value. Into clause before the using clause
Declare
Rochelle dept pls_integer: = 20 ;
Rochelle Nam Varchar2 ( 20 );
Rochelle Loc Varchar2 ( 20 );
Begin
Execute Immediate ' Select dname, LOC from Dept where deptno =: 1 '
Into Rochelle Nam, Rochelle Loc
Using l_dept;
End ;
7 . Multi-row query option. This option is used to fill the temporary table with the insert statement, use the temporary table for further processing, or use ref cursors to correct this defect.
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 dynamic statement processing, Execute Immediate is easier and more efficient than previously used. It is more important to properly handle exceptions when you intend to execute dynamic statements. You should focus on capturing all possible exceptions.
Execute an SQL statement in the varchar2 type variable in the PL block of Oracle
For example:
Create or replace procedure sd_gt_jyz_tj_test (v_flag in varchar2, v_yxnx in varchar2, o_jyztj out number)
Is
TMP varchar2 (100): = '';
begin
If v_flag = 'D' then
TMP: = 'select count (*) from B $ comm_table where g3e_id '| v_yxnx;
execute immediate TMP into o_jyztj;
end if;
exception
when others then
o_jyztj: = 0;
end;
