Execute Immediate replaces the DBMS_SQL Package package in Oracle8i. It resolves and immediately performs dynamic SQL statements or non-running PL / SQL blocks. Dynamic creation and execute SQL statement performance exceeds, Execute Immediate's goal is to reduce corporate costs and Get high performance, it is relatively easy to encode compared to it. Although dbms_sql is still available, it is recommended to use Execute Immediate because it is gains above the package.
skills
1. EXECUTE IMMEDIATE will not submit a DML transaction execution, which should be explicitly submitted if the DML command is processed via Execute Immediate, then it needs to be explicitly submitted or as part of Execute Immediate itself. If you pass the execute immediate processing DDL command, it Submit all previously changed data
2. Do not support returning multi-line queries, this interaction will store records with a temporary table (refer to the example as follows) or with Ref Cursors.
3. When executing the SQL statement, do not use a semicolon, when performing a PL / SQL block, a semicolon is used in its tail.
4. These features are not detailed in the Oracle manual. The following example shows all possible aspects of Execute Immediate. I hope to bring you convenience.
5. For Forms Developers, when in PL / SQL 8.0.6.3. Version, Forms 6i cannot use this feature.
Execute Immediate Usage
1. Run the DDL statement in PL / SQL
Begin Execute Immediate 'Set Role All'; END;
2. Give dynamic statement pass (Using clause)
DECLARE L_DEPNAM VARCHAR2 (20): = 'Testing'; l_loc varcha2 (10): = 'Dubai'; Begin Execute Immediate 'Insert Into Dept Values (: 1,: 2,: 3)' Using 50, L_Depnam, L_Loc; Commit ;
3. Retrieval value from dynamic statement (INTO clause)
Declare l_cnt varcha2 (20); begin execute immediate 'select count (1) from EMP' INTO L_CNT; DBMS_OUTPUT.PUT_LINE (L_CNT);
4. Dynamic call routine. The bind variable parameters used in the routine must specify the parameter type. 黓黓 IN type, other types must be explicitly specified
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' TEN DBMS_OUTPUT.PUT_LINE ('Error'); end if; end;
5. Pass the return value to the PL / SQL record type; also available% ROWTYPE variables
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 is used in 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-line query options. Use the INSERT statement to fill a temporary table with the INSERT statement, and use the Temporary table to correct this shortage. 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; Commital;
For processing dynamic statements, Execute Immediate is easier and more efficient than previously possible. When intended to perform dynamic statements, it is more important to properly handle more important. It should be paid to capture all possible exceptions.
