Use of execute immediate in Oracle (Select/insert/update/delete) (RPM)

ExecuteThe syntax for immediate is as follows:ExecuteImmediate'SQL';ExecuteImmediate'Sql_select'  intovar_1, var_2;ExecuteImmediate'SQL'Using[In|out|in out]Bind_var_1,[In|out|in out]bind_var_2;ExecuteImmediate'Sql_select'  intovar_1, var_2 using[In|out|in out]Bind_var_1,[In|out|in out]bind_var_2;ExecuteImmediate'SQL'Returning intovar_1;ExecuteImmediate'SQL' BulkCollect intoIndexed_var;

--A practical reference to dynamic commands (select.. INTO)--The Execute Immediate command is used when using the INTO keyword with a static plsql block--The To keyword uses a different method. DeclareV_resultvarchar2( -);beginExecuteImmediate'Select Dname from Scott.dept D where D.deptno=:1'  intoV_result--: 1 This is a binding variableUsing -;Commit;d Bms_output.put_line ('The result is:'||v_result);End;--binding variables, in layman's words, is a placeholder parameter, and then use the constant or variable to find its correspondingDeclareV_resultVarchar2( -);BeginSelectDname intoV_result fromScott.dept DWhereD.deptno=Ten;d Bms_output.put_line (v_result);End;DeclareV_resultvarchar2( -);beginExecuteImmediate'Select Dname from Scott.dept D where D.deptno=:1'  intov_resultusing -;Commit;d Bms_output.put_line ('The result is:'||v_result);End;--Practical reference for dynamic commands (insert: INTO)--BeginExecuteImmediate'INSERT INTO scott.dept values (: 1,:2,:3)'Using -,'IT','Beijing';Commit;End;DeclareL_depnamvarchar2( -) := 'Testing'; L_locvarchar2(Ten) := 'Dubai';beginExecuteImmediate'INSERT INTO scott.dept values (: 1,: 2,: 3)'using -, L_depnam, L_loc;Commit;End;---Enjoy dynamic execution of SQL execute immediate commands using the charm. --contrast static tests, and dynamic tests. DeclareV_resultVarchar2( -);BeginV_result:=MYPACKAGE.MYFUN1 (Ten);d bms_output.put_line (V_result); Mypackage.mypro1 (20020101, V_result);d bms_output.put_line (v_result);End;--Select * FROM student s Where s.student_id=20020101;---select * from TestTable Where recordnumber=10;DeclareV_resultVarchar2( -);Begin--Dynamic Execution FunctionsExecuteImmediate'begin:1:=mypackage.myfun1 (: 2); end;'Using out V_result,inch Ten;Commit;d Bms_output.put_line (v_result);--dynamic execution of stored proceduresExecuteImmediate'begin Mypro1 (: 1,:2); end;'Usinginch 20020101, out V_result;Commit;d Bms_output.put_line (v_result);End;--To dynamically execute an instance of an UPDATE statement:Create or Replace functionF_testweekdayreturn integer isV_sqlvarchar( -); I_sendcountinteger;begin--_ ' | | f_getweekday () | | 'V_sql:= 'Update T_push_smstemp_inform Set sendcount=sendcount+1 returning Sendcount into:1';Executeimmediate v_sql using out i_sendcount;return 0; exception whenOthers Thenrollback;return 0;EndF_testweekday;

http://blog.csdn.net/tanshi/article/details/7083964

Use of execute immediate in Oracle (Select/insert/update/delete) (RPM)