Oracle12C--批量操作(三十四)

• 使用FORALL批量绑定
  • PL/SQL的操作会与SQL进行交互，通过PL/SQL执行一条更新语句时，SQL会将更新后的数据返回给PL/SQL，用户才会在PL/SQL中获取新的数据；
    • 所以，当更新大批量数据时，会降低性能；
  • 示例1：通过PL/SQL程序块执行多条数据表更新

DECLARE TYPE emp_varray IS VARRAY(8) OF emp.empno%TYPE ; v_empno emp_varray := emp_varray(7369,7566,7788,7839,7902) ; BEGIN FOR x IN v_empno.FIRST .. v_empno.LAST LOOP UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ; END LOOP ; END ; /

本SQL，将需要更新的雇员编号定义到了可变数组中，之后采用循环的方式为更新数据

• FORALL语句，可将集合中的数据进行批量绑定，将多条SQL一次性发送到数据库，语法如下：

FORALL 变量 IN 集合初值 .. 集合最高值 SQL语句;

• 示例2：利用FORALL向数据库一次性发出多条语句

DECLARE TYPE empno_varray IS VARRAY(8) OF emp.empno%TYPE ; v_empno empno_varray := empno_varray(7369,7566,7788,7839,7902) ; BEGIN FORALL x IN v_empno.FIRST .. v_empno.LAST UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ; FOR x IN v_empno.FIRST .. v_empno.LAST LOOP DBMS_OUTPUT.put_line('雇员编号：' || v_empno(x) || '更新操作受影响的数据行为：' || SQL%BULK_ROWCOUNT(x)) ; END LOOP ; END ; / 执行效果： 雇员编号：7369更新操作受影响的数据行为：1 雇员编号：7566更新操作受影响的数据行为：1 雇员编号：7788更新操作受影响的数据行为：1 雇员编号：7839更新操作受影响的数据行为：1 雇员编号：7902更新操作受影响的数据行为：1

分析： 更新完成后，可利用"SQL%BULK_ROWCOUNT(x)"取得本操作所影响到的数据行数

• BULK COLLECT批量接收数据
  • BULK COLLECT与FORALL相反，主要用来批量接收数据
  • 示例1：批量接收查询数据

DECLARE TYPE ename_varray IS VARRAY(8) OF emp.ename%TYPE ; v_ename ename_varray ; BEGIN SELECT ename BULK COLLECT INTO v_ename FROM emp WHERE deptno=10 ; FOR x IN v_ename.FIRST .. v_ename.LAST LOOP DBMS_OUTPUT.put_line('10部门雇员姓名：' || v_ename(x)) ; END LOOP ; END ; / 运行效果： 10部门雇员姓名：CLARK 10部门雇员姓名：KING 10部门雇员姓名：MILLER

分析： 本sql定义了一个ename字段类型的可变数组，然后使用BULK COLLECT语句将10部门中的所有雇员的姓名保存到此数组的v_ename中

• 示例2：例1只是获取表中一个字段，此例将多个字段的数据一起保存，可利用嵌套表完成

DECLARE TYPE dept_nested IS TABLE OF dept%ROWTYPE ; v_dept dept_nested ; BEGIN SELECT * BULK COLLECT INTO v_dept FROM dept ; -- 将雇员表全部数据拷贝到嵌套表之中 FOR x IN v_dept.FIRST .. v_dept.LAST LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept(x).deptno || '，名称：' || v_dept(x).dname || '，位置：' || v_dept(x).loc) ; END LOOP ; END ; /

运行结果： 部门编号：10，名称：ACCOUNTING，位置：NEW YORK 部门编号：20，名称：RESEARCH，位置：DALLAS 部门编号：30，名称：SALES，位置：CHICAGO 部门编号：40，名称：OPERATIONS，位置：BOSTON