execute immediate

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关键字使用方法上的区别。

declare

v_result varchar2(20);

begin

execute 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中的常量或着变量去找它的相应

Declare

v_result Varchar2(20);

Begin

Select dname Into v_result From scott.dept d Where d.deptno=10;

dbms_output.put_line(v_result);

End;

declare

v_result varchar2(20);

begin

execute immediate 'select dname from v_result

Using 70;

commit;

dbms_output.put_line('结果为：'||v_result);

end;

--动态命令的实用参考一（ insert ..into）

--

scott.dept d where d.deptno=:1' 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的命令使用的魅力。

--对比静态的测试，和动态测试。

Declare

v_result Varchar2(20);

Begin

v_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;

Declare

v_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_testweekday

return integer

is

v_sql varchar(2000);

i_sendcount integer;

begin

--_' || f_getweekday() || '

v_sql := 'update t_push_smstemp_inform. set sendcount=sendcount+1 returning sendcount into :1';

execute immediate v_sql using out i_sendcount;

return 0;

exception

when others then

rollback;

return 0;

end f_testweekday;