Dynamic SQL and PL / SQL EXECUTE IMMEDIATE Options
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
