oracle中cute immediate的使用.doc

execute immediate的语法如下：execute immediate sql;execute immediate sql_select into var_1, var_2;execute immediate sql using in|out|in out bind_var_1, in|out|in out bind_var_2;execute immediate sql_select into var_1, var_2 using in|out|in out bind_var_1, in|out|in out bind_var_2;execute immediate sql returning into var_1;execute immediate sql bulk collect into indexed_var;用法实例：Declare v_table varchar2(20); v_sql Varchar2(100); Begin v_table:=hqf.testtable; v_sql:=select * from |v_table; dbms_output.put_line(v_table); dbms_output.put_line(v_sql); Execute Immediate v_sql; End;Declare v_sid Integer:=20020101; v_sql Varchar2(100); v_result Varchar2(50);Begin -v_sid:=12; v_sql:=Select loc into v_result from scott.dept d where d.deptno=:1 ; dbms_output.put_line(v_sql); -dbms_output.put_line(v_result); Execute Immediate v_sql Using 10 ; Commit;End;-动态命令的实用参考一（ select .into）-Execute immediate 命令在使用into关键字时与静态的plsql块中-into关键字使用方法上的区别。declarev_result varchar2(20);beginexecute immediate select dname from scott.dept d where d.deptno=:1 Into v_result -:1这是一个绑定变量Using 70;commit;dbms_output.put_line(结果为：|v_result);end;-绑定变量，通俗的讲就是一个占位的参数，而后用using中的常量或着变量去找它的相应Declarev_result Varchar2(20);Begin Select dname Into v_result From scott.dept d Where d.deptno=10; dbms_output.put_line(v_result);End;declarev_result varchar2(20);beginexecute immediate select dname from scott.dept d where d.deptno=:1 Into v_resultUsing 70;commit;dbms_output.put_line(结果为：|v_result);end;-动态命令的实用参考一（ insert .into）-Begin Execute Immediate insert into scott.dept values (:1,:2,:3) Using 70,IT,Beijing; Commit;End;declare l_depnam varchar2(20) := testing; l_loc varchar2(10) := Dubai; begin execute immediate insert into scott.dept values (:1, :2, :3) using 60, l_depnam, l_loc; commit;end;-尽享动态执行sql的execute immediate的命令使用的魅力。-对比静态的测试，和动态测试。Declarev_result Varchar2(20);Beginv_result:=mypackage.myfun1(10);dbms_output.put_line(v_result);mypackage.mypro1(20020101,v_result);dbms_output.put_line(v_result);End;-Select * From student s Where s.student_id=20020101;-Select * From testtable Where recordnumber=10;Declarev_result Varchar2(20);Begin -动态执行函数 Execute Immediate begin :1:=mypackage.myfun1(:2); end; Using Out v_result,In 10; Commit; dbms_output.put_line(v_result); -动态执行存储过程 Execute Immediate begin mypro1(:1,:2); end; Using In 20020101,Out v_result; Commit; dbms_output.put_line(v_result);End;-动态执行update语句实例：create or replace function f_testweekdayreturn integeris v_sql varchar(2000); i_sendcount integer;begin-_ | f_getweekday() | v_sql := update t_push_smstemp_inform set sendcount=sendcount+1 returning sen