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

Source: Internet
Author: User
Tags dname

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.