Dynamic SQL uses Oracle 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] ...]; below is its use processing DDL operation (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 EI statement, you have to bring a USING clause. ); 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 Use open-for, fetch and close statements to handle multi-line query dynamic processing SELECT statement step: Define Cursor -> Open Cursor -> Cycle extraction data -> Close Cursor definition: Type Cursortype is Ref cursor; cursor_variable cursortype; Open: Open Cursor_Variable FOR dynamic_string [USING bind_argument [, bing_argument] ...]; extract: FETCH cursor_variable INTO {var1 [, var2] ... | recor_var}; closed: CLOSE cursor_variable; display a particular sector employees 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 the EXECUTE IMMEDIATE, the syntax is: 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. Grammatical FORALL INDEX IN LOWER BOUND..upper Bound
Execute immediate dynamic_string
USING BIND_ARGUMENT | BIND_ARGUMNET (INDEX)
[, bind_argument | bind_argumnet (index)] ...
[{Returning | Return} Bulk Collect
INTO BIND_ARGUMENT [, BIND_ARGUMENT ...]]];
