Dynamic SQL use
Oracle is actually better than SQL Server, but there is absolutely no legend. This is the feeling of these days and knowing Oracle Lushan. It seems that many things have been so much, and I have to be perfect than I see. The first sentence says that Oracle's dynamic SQL is great, I have learned it. Record below.
Let's talk about what dynamics sql is, see this familiar: select * from a_table where a_variable = a_declarevalue; Refused sentence dynamics: select * from a_table where a_variable =: a_dynamicvalue; two sentences are obvious, the latter is more The placeholder, this variable starting with the colon can be flexibly performing different conditions of the WHERE statement. This is the advantage of dynamic SQL statements, the next function is its unique effort - performs DDL, DCL statements.
Dynamic SQL execution 1 execute immediate statement
Execute immediate dynamic_string
[INTO {Define_variable [, define_variable] ... | record}]
[Use [in | out | in out] bind_argument [, [in | OUT / IN OUT]
BIND_ARGUMNET] ...]
[{RETURNING | RETURN} INTO BING_ARGUMENT [, BIND_ARGUMENT] ...];
Here is its use
Handling DDL operations (Create, Alter, Drop)
Create or Replace Procedure Drop_table (Table_name Varchar2)
IS
SQL_STATEMET VARCHAR2 (100);
Begin
SQL_STATEMENT: = 'DROP TABLE' || Table_Name;
Execute immediate sql_statement;
END;
/
After establishing process DROP_TABLE, call as follows:
SQL> EXEC DROP_TABLE ('Worker')
Handling DCL operations (Grant Revoke)
SQL> CONN SYSTEM / MANAGER
Create or Replace Procedure Grant_Sys_Priv (Priv Varchar2, Username
VARCHAR2)
IS
SQL_STAT VARCHAR2 (100);
Begin
SQL_STAT: = 'GRANT "|| priv ||' to '|| username;
Execute Immediate SQL_STAT;
END;
/
transfer
SQL> EXEC GRANT_SYS_PRIV ('Create Session', 'Scott')
Handling DML operation (Insert Update Delete)
If the DML statement comes with a placeholder, then in the E i statement, you have to bring the USING clause. If the DML statement comes with a return clause, then the e i statement is ReturningInto clause
Examples of the processing single row query: DECLARE sql_stat VARCHAR2 (100); emp_record tbl% ROWTYPE; BEGIN sql-stat: = 'SELECT * FROM tbl WHERE tblno =: no'; EXECUTE IMMEDIATE sql_stat INTO emp_record USING & 1; dbms_output.put_line (emp_record. ENAME || Emp_Record.sal); END; /
2 Handle multi-line query using open-for, fetch and close statements
Dynamic processing SELECT statement step: Define the cursor -> Open Cursor -> Cycle extraction data -> Close Cursor definition: Type Cursortype is ref curd; cursor_variable cursorType; Open: Open cursor_variable for Dynamic_String [use bind_argument [, bing_argument] ...]; Extract: Fetch Cursor_Variable Into {var1 [, var2] ... | record_var}; Close: Close Cursor_Variable;
Display a specific sector employee names and salaries DECLARE TYPE empcurtype IS REF CURSOR; emp_cs empcurtype; emp_record emptable% ROWTYPE; sql_stat VARCHAR2 (100); BEGIN sql_stat: = 'select * from emptable where deptno =: dno'; OPEN emp_cs FOR sql_stat USING & dno ; Loop fetch EMP_CS INTO EMP_RECORD; EXIT WHEN EMP_CS% NotFound; dbms_output.put_line (EMP_RECORD.ENAME || EMP_RECORD.SAL); End loop; Close EMP_CS; END; /
3 Use a bulk dynamic SQL (9i) Bulk clause to speed up the processing speed of bulk data. There are three statements to support the method of BULK clauses. 1 Using Execute Immediate, syntax: Execute Immediate Dynamic_String [Bulk Collect Into Define_variable [, Define_Variable ...] [Using [in | OUT | IN OUT] BIND_ARGUMENT [, [IN | OUT / IN OUT]
BIND_ARGUMNET] ...] [{Returning | Return} Bulk Collect Into Return_Variable [, return_variable ...]]
For DML processing, multi-row clause, example: increase the amount of salary Declare ... begin SQL_STAT: = 'Update Emptbl Set Sal = Sal * (1 : Percent / 100)' || 'Where Deptno =: DNO '||' Returning Ename, Sal Into: Name,: Salary '
Execute Immediate SQL_STAT Using & Percent, & DNO Returning Bulk Collect Into Ename_Table, Sal_Table
For i in 1.ename_table.count loop dbms_output.put_line (ename_table (i) || SAL_TABLE (i)); end loop;
END; /
2 FETCH statement, syntax for fetch Dynamic_Cursorbulk Collect Into Define_variable [, DYFINE_VARIABLE ...];
3 FORALL statement. Suitable for DML, not applicable to dynamic SELECT statements. The FORALL statement is used in conjunction with E i. Syntax
FORALL INDEX IN LOWER BOUND..upper Bound
Execute Immediate Dynamic_StringUsing Bind_argument | BIND_ARGUMNET (INDEX)
[, bind_argument | bind_argumnet (index)] ...
[{Returning | Return} Bulk Collect
INTO BIND_ARGUMENT [, BIND_ARGUMENT ...]]];
*********************************************************** *********** Dynammed IMMediate Options Execute Immediate Options Execute Immediate Options Execute Immediate Instead of the previous Oracle8i DBMS_SQL Package package. It resolves and immediately performs dynamic SQL statements or non-running PLs. / SQL block. Dynamic creation and execution of SQL statement performance exceeds, Execute Immediate's goal is to reduce corporate costs and achieve high performance, compared to it is quite easy to encode. Although dbms_sql is still available, but recommend Execute Immediate because it is recommended The benefits obtained are above the package.
skills
1. EXECUTE IMMEDIATE will not submit a DML transaction execution, which should explicitly submit if the DML command is processed via Execute Immediate, then it needs to be explicitly submitted or
As part of Execute Immediate. If you pass Execute
IMMEDIATE handles DDL commands, which submitted all previously changed data
2. Do not support the returning multi-line queries, this interaction will store records with a temporary table (see example as follows) or
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 uses to Execute
IMMEDIATE may. I hope to bring you convenience.
5. For Forms developers, when in PL / SQL 8.0.6.3., Forms 6i cannot use this feature.
.
Execute Immediate Usage
1. Run the DDL statement based on PL / SQL Begin Execute IMMEDIATE 'SET ROLE All'; END;
2. Dynamic statement pass (USING clause) Declare l_depnam varcha2 (20): = 'Testing'; l_loc varcha2 (10): = 'Dubai'; Begin Execute Immediate 'Insert Into Dept Values (: 1,: 2, : 3) 'Using 50, L_DEPNAM, L_LOC; Commit; End;
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; the same also available% ROWTYPE variable Declare Type Empdtlrec is Record (Empno Number (4), Ename Varchar2 (20), DePdno Number (2)); Empdtl Empdtlrec; Begin Execute Immediate 'Select Empno, ENAME, Deptno' || 'from Emp where Empno = 7934' INTO Empdtl;
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.
