软件开发培训班 >> 编程开发 >> SQL


批处理 动态sql

　　本文标签：批处理,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;
将Ａ与Ｘ绑定，当第二次出来不同名称时，与Ｂ绑定，以此类推