批处理动态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;

将Ａ与Ｘ绑定，当第二次出来不同名称时，与Ｂ绑定，以此类推