批处理 动态sql

１.

declare

type ref_cursor_type is ref cursor;

v_mycursor ref_cursor_type;

type id_list is table of integer;

type name_list is table of varchar2(30);

v_tabid id_list:=id_list();

v_tabname name_list:=name_list();

sql_str varchar2(200);

begin

–查询所以行,放在集合里

sql_str:=’select empno,ename from emp’;

sql_str:=sql_str||’ order by empno desc’;

execute immediate sql_str bulk collect into v_tabid,v_tabname;

for c in v_tabid.first..v_tabid.last loop

dbms_output.put_line(’empno为’||v_tabid(c)||’ 记录的name为’||v_tabname(c));

end loop;

dbms_output.put_line(‘———————————‘);

–更新(返回更新后的值)

sql_str:=’update emp set empno=1+empno,ename=”a” where rownum=1 returning empno,ename into :1,:2 ‘;

execute immediate sql_str returning bulk collect into v_tabid, v_tabname;

for c in v_tabid.first..v_tabid.last loop

dbms_output.put_line(’empno为’||v_tabid(c)||’ 记录的name为’||v_tabname(c));

end loop;

dbms_output.put_line(‘———————————‘);

–删除(返回被删除的行)

sql_str:=’delete from emp where rownum<=2 returning empno,ename into :1,:2 ‘;

execute immediate sql_str returning bulk collect into v_tabid, v_tabname;

for c in v_tabid.first..v_tabid.last loop

dbms_output.put_line(’empno为’||v_tabid(c)||’ 记录的name为’||v_tabname(c));

end loop;

dbms_output.put_line(‘———————————‘);

–插入(返回插入的行)

sql_str:=’insert into emp(empno,ename) values(1,”abc”) returning empno,ename into :1,:2 ‘;

execute immediate sql_str returning bulk collect into v_tabid, v_tabname;

for c in v_tabid.first..v_tabid.last loop

dbms_output.put_line(’empno为’||v_tabid(c)||’ 记录的name为’||v_tabname(c));

end loop;

dbms_output.put_line(‘———————————‘);

/* 批fetch

语法:

fetch dynamic_cursor

bulk collect into define_variable[,define_variable…]

*/

sql_str:=’select empno,ename from emp’;

sql_str:=sql_str||’ order by empno desc’;

open v_mycursor for sql_str;

–取

fetch v_mycursor bulk collect into v_tabid,v_tabname;

–关

close v_mycursor;

–输

for c in v_tabid.first..v_tabid.last loop

dbms_output.put_line(’empno为’||v_tabid(c)||’ 记录的name为’||v_tabname(c));

end loop;

dbms_output.put_line(‘———————————‘);

end;

２.－－－－－－－

forall

declare

/*批forall

语法：动态字符串必须为insert/update/delete,不能为select

forall index in lower..upper

execute immediate dynamic_string

using bind |bind(index)[,bind |bind(index)…]

[{returning|return} bulk collect into bind_argument[,bind_argument…]];

*/

type sal_list is table of number(8,2);

type name_list is table of varchar2(30);

type dept_list is varray(15) of integer;

v_depts dept_list:=dept_list(10,20,30,40,50,60,70,80);

v_tabsal sal_list:=sal_list();

v_tabname name_list:=name_list();

sql_str varchar2(200);

begin

sql_str:=’update emp set sal=sal*:arg1 where deptno=:arg2′;

sql_str:=sql_str||’ returning ename,sal into :arg3,:arg4′;

–给前面4个部门加薪1０％，并返回结果到集合.

forall j in 1..4

execute immediate sql_str

using 1.10,v_depts(j)

returning bulk collect into v_tabname,v_tabsal;

–显示结果

for j in v_tabname.first..v_tabname.last loop

dbms_output.put_line(‘雇员’||v_tabname(j)

||’　的薪水被提到’||v_tabsal(j));

end loop;

dbms_output.put_line(‘———————————‘);

–给后面4个部门加薪２０％，并返回结果到集合.

forall j in 5..8

execute immediate sql_str

using 1.20,v_depts(j)

returning bulk collect into v_tabname,v_tabsal;

–显示结果(用notfound判断是否有结果集）

if sql%notfound then

dbms_output.put_line(‘无数据更新’);

else

for j in v_tabname.first..v_tabname.last loop

dbms_output.put_line(‘雇员’||v_tabname(j)

||’　的薪水被提到’||v_tabsal(j));

end loop;

end if;

end;

３.用一个值绑定绑定名称相同的值.

把sql语句用begin end括起来就能实现

如:

execute immediate ‘begin calc_stats(:x,:x,:y,:x,:y); end;’ using a,b;

将ａ与ｘ绑定，当第二次出来不同名称时，与ｂ绑定，以此类推