oracle中execute 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:?????? 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