PLSQL - FORALL在数据批量处理中的使用

在PLSQL中,PLSQL块/子程序由PLSQL引擎处理,而其中的SQL语句则由PLSQL引擎发送至SQL引擎处理,后者处理完毕后再向前者返回数据,两者之间的通信称为上下文切换。过多的上下文切换将带来过量的性能负载,FORALL和BULK COLLECT子句则可批量处理数据,从而减少这方面的性能负载。

一、FORALL与DML语句的简单结合

当PLSQL中的DML语句加上FORALL子句就可以一次性将语句和数据发送至SQL引擎处理,处理结果也会一次性反馈给PLSQL引擎。

CREATE TABLE cux_employees(empno NUMBER, ename VARCHAR2(40));
/
DECLARE
  TYPE empno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE ename_tbl_type IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;

  t_empno empno_tbl_type;
  t_ename ename_tbl_type;

  l_limit NUMBER := 5000;
  l_len   NUMBER := length(l_limit);
  l_sql   VARCHAR2(240);
BEGIN
  --模拟出现有两个大量数据的表变量
  FOR k IN 1 .. l_limit
  LOOP
    t_empno(k) := k;
    t_ename(k) := 'EMP' || lpad(k,
                                l_len,
                                '0');
  END LOOP;

  --INSERT语句搭配FORALL
  FORALL k IN 1 .. l_limit
    INSERT INTO cux_employees
      (empno
      ,ename)
    VALUES
      (t_empno(k)
      ,t_ename(k));

  --UPDATE语句搭配FORALL
  --这里要注意:SET节中不允许使用循环变量!
  FORALL k IN 1 .. l_limit
    UPDATE cux_employees
       SET ename = REPLACE(ename, 'EMP', 'EMP_No.')
     WHERE empno = t_empno(k);

  --DELETE语句搭配FORALL
  FORALL k IN floor(l_limit / 2) + 1 .. l_limit
    DELETE FROM cux_employees WHERE empno = t_empno(k);

  --FORALL语句也可以搭配动态SQL实现批量DML操作,例如:
  l_sql := 'INSERT INTO cux_employees(empno, ename) VALUES(:1, :2)';
  FORALL k IN floor(l_limit / 2) + 1 .. l_limit
    EXECUTE IMMEDIATE l_sql USING t_empno(k), t_ename(k);
END;

二、SAVE EXCEPTIONS和SQL%BULK_EXCEPTIONS属性

批量DML虽然是一次性将指令和数据传送至SQL引擎,但在SQL引擎中仍然是一条条执行的,如果在处理过程中发生异常,则整个批量处理会中断,同时抛出这个异常。使用SAVE EXCEPTIONS关键字可以使在过程中即便发生异常,也能绕过异常继续,以保证整个批量处理中没有异常的处理全部执行,最终抛出一个异常,代码-24381。顾名思义,记录下来的异常则可以通过SQL%BULK_EXCEPTIONS属性查询:SQL%BULK_EXCEPTIONS是一个记录集合,每条记录都由ERROR_INDEX和ERROR_CODE两个字段组成,前者是批量处理中发生异常的迭代编号(对应着FORALL的循环变量),后者是对应异常的ORACLE错误代码;而SQL%BULK_EXCEPTIONS.COUNT则是批量处理中的异常个数了。

TRUNCATE TABLE cux_employees;
ALTER TABLE cux_employees ADD CONSTRAINT cux_employees_u1 UNIQUE(empno);
ALTER TABLE cux_employees MODIFY(empno NOT NULL);
/
DECLARE
  TYPE empno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE ename_tbl_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;

  t_empno empno_tbl_type;
  t_ename ename_tbl_type;

  errors_in_array_dml EXCEPTION;
  PRAGMA EXCEPTION_INIT(errors_in_array_dml, -24381);
BEGIN
  FOR k IN 1 .. 10 LOOP
    t_empno(k) := k;
    t_ename(k) := 'EMP' || lpad(k, 3, '0');
  END LOOP;

  --制造一些问题数据
  t_empno(3) := NULL;
  t_empno(5) := 10;
  t_ename(7) := rpad(t_ename(7), 41, '.');

  --将数据批量插入表中
  FORALL k IN 1 .. 10 SAVE EXCEPTIONS
    INSERT INTO cux_employees
      (empno, ename)
    VALUES
      (t_empno(k), t_ename(k));

  COMMIT;
EXCEPTION
  WHEN errors_in_array_dml THEN
    dbms_output.put_line('批量DML中发生了' || SQL%bulk_exceptions.count || '个错误');
    FOR k IN 1 .. SQL%bulk_exceptions.count LOOP
      dbms_output.put_line('第' || k || '个错误发生在第' || SQL%BULK_EXCEPTIONS(k)
                           .error_index || '行DML:' ||
                           SQLERRM(-sql%BULK_EXCEPTIONS(k).error_code));
      --注意%BULK_EXCEPTIONS中的error_code不带负号
    END LOOP;
END;

上例的运行结果是:

批量DML中发生了3个错误
第1个错误发生在第3行DML:ORA-01400: 无法将 NULL 插入 ()
第2个错误发生在第7行DML:ORA-12899: 列  的值太大 (实际值: , 最大值: )
第3个错误发生在第10行DML:ORA-00001: 违反唯一约束条件 (.)

三、SQL%BULK_ROWCOUNT属性

SQL%BULK_ROWCOUNT也是为FORALL设计的,SQL%BULK_ROWCOUNT是一个数字集合,用于存储FORALL中第N次DML所产生影响的实际行数,没有产生影响就返回0,若产生影响,影响了几行就返回几;SQL%BULK_ROWCOUNT的索引和FORALL的循环变量是一一对应的。

DECLARE
  TYPE deptno_tbl_type IS TABLE OF NUMBER;

  t_deptno deptno_tbl_type := deptno_tbl_type(10, 40);
BEGIN
  FORALL k IN 1 .. t_deptno.count
    UPDATE emp SET sal = sal * 1.5 WHERE deptno = t_deptno(k);

  FOR i IN 1 .. t_deptno.count LOOP
    dbms_output.put_line('第' || i || '次更新实际影响了' || SQL%BULK_ROWCOUNT(i) ||
                         '行数据.');
  END LOOP;
END;

上例的运行结果是

第1次更新实际影响了3行数据.
第2次更新实际影响了0行数据.

四、INDICES OF选项

如果使用FORALL操作一个索引不连续的数组,那么循环变量的上下限则无法确定,此时需要使用INDICES OF选项,可使循环变量直接在存在的索引当中遍历。

DECLARE
  TYPE deptno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  t_deptno deptno_tbl_type;
BEGIN
  t_deptno(3) := 10;
  t_deptno(8) := 30;
  t_deptno(10) := 40;

  FORALL k IN INDICES OF t_deptno
    UPDATE emp SET sal = sal * 1.5 WHERE deptno = t_deptno(k);

  FOR i IN t_deptno.first .. t_deptno.last LOOP
    IF t_deptno.exists(i)
    THEN
      dbms_output.put_line('第' || i || '次更新实际影响了' || SQL%BULK_ROWCOUNT(i) ||
                           '行数据.');
    END IF;
  END LOOP;
END;

上例的运行结果是

第3次更新实际影响了3行数据.
第8次更新实际影响了6行数据.
第10次更新实际影响了0行数据.

五、VALUES OF选项

VALUES OF选项可以让我们指定FORALL循环变量遍历的数据,不仅可以无序,甚至可以反复。简单来说就是在一个数组中按照我们希望的遍历顺序将索引数存入,VALUES OF就可以将数组中的数据作为循环变量遍历的范围。由于数据相当于赋值给了循环变量,所以这个数组应当是PLS_INTEGER或BINARY_INTEGER元素的数组,而且要保证这个数组中不能有NULL值,否则会引起FORALL报错ORA-22160,即便使用SAVE EXCEPTIONS也会使整个FORALL不执行,因为这不是到SQL引擎才抛出的错误。

CREATE TABLE cux_male_employees (empno NUMBER, ename VARCHAR2(40));
CREATE TABLE cux_female_employees (empno NUMBER, ename VARCHAR2(40));
/
DECLARE
  TYPE emp_rcd_type IS RECORD(
     empno  NUMBER
    ,ename  VARCHAR2(40)
    ,gender CHAR(1));
  TYPE emp_tbl_type IS TABLE OF emp_rcd_type INDEX BY BINARY_INTEGER;
  TYPE index_tbl_type IS TABLE OF BINARY_INTEGER;

  t_emp        emp_tbl_type;
  t_male_emp   index_tbl_type := index_tbl_type();
  t_female_emp index_tbl_type := index_tbl_type();
BEGIN
  --模拟出t_emp中存储了不同的员工信息
  t_emp(1).empno := 1;
  t_emp(1).ename := 'YUSUF';
  t_emp(1).gender := 'M';
  t_emp(2).empno := 2;
  t_emp(2).ename := 'FATIMAH';
  t_emp(2).gender := 'F';
  t_emp(3).empno := 3;
  t_emp(3).ename := 'HAMUZA';
  t_emp(3).gender := 'M';

  --将男性和女性员工的索引分开加入对应的关联数组中
  FOR k IN t_emp.first .. t_emp.last LOOP
    IF t_emp(k).gender = 'M'
    THEN
      t_male_emp.extend;
      t_male_emp(t_male_emp.last) := k;
    ELSIF t_emp(k).gender = 'F'
    THEN
      t_female_emp.extend;
      t_female_emp(t_female_emp.last) := k;
    END IF;
  END LOOP;

  --将男性和女性员工的信息分别存入表中
  FORALL m IN VALUES OF t_male_emp
    INSERT INTO cux_male_employees
      (empno, ename)
    VALUES
      (t_emp(m).empno, t_emp(m).ename);

  FORALL f IN VALUES OF t_female_emp
    INSERT INTO cux_female_employees
      (empno, ename)
    VALUES
      (t_emp(f).empno, t_emp(f).ename);

  COMMIT;
END;

上例的运行结果是

SQL> SELECT * FROM cux_male_employees;
 
     EMPNO ENAME
---------- ----------------------------------------
         1 YUSUF
         3 HAMUZA
 
SQL> SELECT * FROM cux_female_employees;
 
     EMPNO ENAME
---------- ----------------------------------------
         2 FATIMAH

 

  • 2
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值