oracle12C--批量绑定(63)

• 定义
  • 批量绑定可以将操作结果返回到某一个集合中，也可以使数据库一次性接收多条SQL；
• 举个栗子
  • 示例1：更新时使用BULK COLLECT语句

DECLARE

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ;

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ;

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ;

v_ename ename_index ;

v_job job_index ;

v_sal sal_index ;

v_sql_statement VARCHAR2(200) ; --定义动态SQL

v_deptno emp.deptno%TYPE := 10 ; --查询10部门

BEGIN

v_sql_statement := 'UPDATE emp SET sal=sal*1.2 WHERE deptno=:dno ' ||

' RETURNING ename,job,sal INTO :ena, :ej, :es' ; --此时返回多行更新结果

EXECUTE IMMEDIATE v_sql_statement USING v_deptno

RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ;

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

/

本程序在定义更新的动态SQL语句后会同时影响到多行数据的更新，所以执行此SQL时，会利用BULK COLLECT INTO语句，将所有取得的数据一次性写入到指定的索引表集合中，这样只需要一次就可以取得所有的结果

• 示例2：查询时使用BULK COLLECT

DECLARE

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; --保存雇员姓名

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; --保存雇员职位

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; --保存雇员工资

v_ename ename_index ;

v_job job_index ;

v_sal sal_index ;

v_sql_statement VARCHAR2(200) ; --定义动态SQL

v_deptno emp.deptno%TYPE := 10 ; --查询10部门

BEGIN

v_sql_statement := 'SELECT ename,job,sal FROM emp WHERE deptno=:dno' ; --此时返回多行更新结果

EXECUTE IMMEDIATE v_sql_statement

BULK COLLECT INTO v_ename,v_job,v_sal

USING v_deptno ; --将多个结果一起返回

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

/

本程序采用了同样的方式将所有的查询结果通过BULK COLLECT INTO分别保存到3个索引表集合中，然后采用循环输出所查询出的数据

• 使用FORALL设置多个参数
  • 语法：

FORALL 索引变量 IN参数集合最小值 ..参数集合最大值
EXECUTE IMMEDIATE 动态SQL字符串
[USING 绑定参数| 绑定参数（索引）,.....]
[[RETURNING | RETURN] BULK COLLECT INTO 绑定参数集合,.....];

• 示例：通过FORALL设置多个参数

DECLARE

TYPE empno_nested IS TABLE OF emp.empno%TYPE ; --定义嵌套表

TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; --定义索引表

TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; --定义索引表

TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; --定义索引表

v_ename ename_index ; --保存删除后的姓名

v_job job_index ; --保存删除后的职位

v_sal sal_index ; --保存删除后的工资

v_empno empno_nested := empno_nested(7369,7566,7788) ; --定义要删除雇员编号

v_sql_statement VARCHAR2(200) ; --动态SQL

BEGIN

v_sql_statement := 'DELETE FROM emp WHERE empno=:eno ' || '

RETURNING ename,job,sal INTO :ena , :ej , :es' ; --删除数据SQL

FORALL x IN 1 .. v_empno.COUNT -- FORALL绑定多个变量

EXECUTE IMMEDIATE v_sql_statement USING v_empno(x)

RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ;

FOR x IN 1 .. v_ename.COUNT LOOP

DBMS_OUTPUT.put_line('雇员姓名：' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;

END LOOP ;

END ;

/

该程序通过FORALL操作向动态SQL语句中设置了多个参数（所有参数保存在v_empno嵌套表类型变量中），同时此操作也会返回多行要删除的数据信息，这些数据通过BULK COLLECT INTO分别保存在3个集合中