特殊用法

示例

以下是一个取出一张表里的数据到集合变量，并插入另一张表的示例。

-- 前置准备
CREATE TABLE test (id INT, name VARCHAR(10));
INSERT INTO test VALUES(1, 'a'), (2, 'b');
CREATE TABLE test_backup (id INT, name VARCHAR(10));

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  TYPE nt_type IS TABLE OF r_type;
  nt nt_type;
BEGIN
  SELECT * BULK COLLECT INTO nt FROM test;
  FORALL i IN INDICES OF nt
    INSERT INTO test_backup VALUES nt(i);
END;

SELECT * FROM test_backup;

结果显示如下：

 id | name 
----+------
  1 | a
  2 | b
(2 rows)

以下是一个利用集合变量更新表的示例。

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  TYPE nt_type IS TABLE OF r_type;
  nt nt_type := nt_type(r_type(1, 'c'), r_type(2, 'd'));

BEGIN
  FORALL i IN nt.first..nt.last
    UPDATE test_backup SET ROW = nt(i) WHERE id = nt(i).id;
END;

SELECT * FROM test_backup;

结果显示如下：

 id | name 
----+------
  1 | c
  2 | d
(2 rows)

BULK COLLECT INTO

BULK COLLECT INTO的使用场景与INTO类似，区别在于INTO的目标是标量或行变量，而BULK COLLECT INTO的目标是集合变量。它能够将表中的多行数据批量插入到集合变量中。使用该语法时，不需要初始化集合变量。即使目标的集合变量中已经含有元素，也会重新初始化后再使用。除了上述例子中的SELECT ... BULK COLLECT INTO以外，您还可以在以下场景中使用。

以下是一个FETCH cursor BULK COLLECT INTO的示例。

DECLARE
  TYPE nt_int IS TABLE OF test.id%TYPE;
  TYPE nt_char IS TABLE OF test.name%TYPE;
  nt1 nt_int;
  nt2 nt_char;
  CURSOR cur IS SELECT id, name FROM test;
BEGIN
  OPEN cur;

  -- 为了防止 FETCH 的数量过多，您可以通过 limit 关键字来限制它
  FETCH cur BULK COLLECT INTO nt1, nt2 limit 1;
  FOR i IN 1..nt1.COUNT LOOP
      RAISE NOTICE '% %', nt1(i), nt2(i); 
  END LOOP;

  CLOSE cur;
END;

结果显示如下：

NOTICE:  1 a
DO

以下是一个RETURNING ... BULK COLLECT INTO的示例。

DECLARE
  TYPE t_rowtype IS TABLE OF test%rowtype INDEX BY PLS_INTEGER;
  r t_rowtype;
BEGIN
  -- DELETE/INSERT/UPDATE/EXECUTE IMMEDIATE 时支持 RETURNING 语法
  UPDATE test SET id = id + 1 RETURNING * BULK COLLECT INTO r;
  FOR i IN r.FIRST..r.LAST LOOP
      RAISE NOTICE '%', r(i);
  END LOOP;
END;

结果显示如下：

NOTICE:  (2,a)
NOTICE:  (3,b)
DO

FORALL

FORALL语句的功能与FOR .. LOOP类似，但它有一些额外的用法和限制。其三种声明语法如下：

-- 遍历下界..上界的写法
FORALL i IN lower_bound..upper_bound
  single SQL  -- 仅允许使用单条 SQL 或动态 SQL 语句

-- 遍历索引的写法 (对于关联数组来说，仅支持使用索引为数值的关联数组)
FORALL i IN INDICES OF collection
  single SQL

-- 遍历值的写法 (仅支持使用元素类型为数值类型的集合变量)
FORALL i IN VALUES OF collection
  single SQL

您可以通过一个特殊的关联数组SQL%BULK_ROWCOUNT来访问每条成功执行语句影响的行数，示例如下。

-- 前置准备
DELETE FROM test;
INSERT INTO test VALUES (1, 'a'), (1, 'b'), (2, 'c');

DECLARE
  TYPE nt_type IS TABLE OF INT;
  nt nt_type := nt_type(1, 2);
BEGIN
  FORALL i IN 1..2
    UPDATE test set id = id + 10 WHERE id = nt(i);

  -- 预期第一次更新2行, 第二次更新1行
  FOR i in SQL%BULK_ROWCOUNT.first..SQL%BULK_ROWCOUNT.last LOOP
    RAISE NOTICE '%', SQL%BULK_ROWCOUNT(i);
  END LOOP;
END;

结果显示如下：

NOTICE:  2
NOTICE:  1
DO

正常情况下，FORALL语句会在循环执行的SQL语句第一次出现异常时抛出异常。如果您声明了EXCEPTION块，则会进入异常处理的流程。如果您想跳过抛出异常的SQL语句继续循环执行，您需要确认数据库已经打开语句级事务，并使用SAVE EXCEPTIONS的语法。此时，FORALL语句会遍历完所有的变量，执行完所有SQL，然后抛出FORALL_DML_ERROR异常。以下是一个保存异常并继续执行的示例。

FORALL i IN lower_bound..upper_bound SAVE EXCEPTIONS
  single SQL

如果在执行的过程中出现异常，您可以通过另一个特殊的关联数组SQL%BULK_EXCEPTIONS来访问每条出错语句的具体错误信息。以下是一个查看异常SQL错误信息的示例。

-- 保证语句级别事务功能已经打开

-- 前置准备, 创建一个具有唯一约束的表
CREATE TABLE test_unique (id INT primary key);

DECLARE
  TYPE nt_int IS TABLE OF INT;
  nt nt_int := nt_int(1, 1, 2, 2, 3);
BEGIN
  -- 在主键约束下, 第二条和第四条会插入失败
  FORALL i IN 1..nt.COUNT SAVE EXCEPTIONS
    INSERT INTO test_unique VALUES(nt(i));

EXCEPTION 
  WHEN FORALL_DML_ERROR THEN
    RAISE NOTICE 'SQLCODE: %, SQLERRM: %', SQLCODE, SQLERRM;

    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      RAISE NOTICE 'no: %', i;
      RAISE NOTICE 'err_index: %', SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
      RAISE NOTICE 'err_code: %', SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
      RAISE NOTICE 'err_msg: %', SQL%BULK_EXCEPTIONS(i).ERROR_MSG;
      RAISE NOTICE '---------------';
    END LOOP;

END;

-- 查看插入情况
SELECT * FROM test_unique;

结果显示如下：

-- PL/SQL 的输出
NOTICE:  SQLCODE: P0005, SQLERRM: save exceptions raised in FORALL statememt
NOTICE:  no: 1
NOTICE:  err_index: 2
NOTICE:  err_code: 23505
NOTICE:  err_msg: duplicate key value violates unique constraint "test_unique_pkey"
NOTICE:  ---------------
NOTICE:  no: 2
NOTICE:  err_index: 4
NOTICE:  err_code: 23505
NOTICE:  err_msg: duplicate key value violates unique constraint "test_unique_pkey"
NOTICE:  ---------------
DO

-- SELECT 的输出
 id 
----
  1
  2
  3
(3 rows)

INSERT/UPDATE

由于记录类型在PL/SQL中支持如下语法。

-- 使用记录变量插入 test 表
INSERT INTO test VALUES record;
-- 使用记录变量更新 test 表
UPDATE table SET ROW = record;

因此，对于元素类型为记录类型的集合变量，也可以使用类似的语法与表进行交互。

-- 清空 test 表
DELETE FROM test;

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  TYPE nt_type IS TABLE OF r_type;
  nt nt_type := nt_type(r_type(25, 'y'));
BEGIN
  -- 使用集合变量的元素插入 test 表
  INSERT INTO test VALUES nt(1);
END;

-- 查看插入结果
SELECT * from test;

结果显示如下：

 id | name 
----+------
 25 | y
(1 row)

使用特殊语法更新表。

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  TYPE nt_type IS TABLE OF r_type;
  nt nt_type := nt_type(r_type(26, 'z'));
BEGIN
  -- 使用集合变量的元素更新 test 表
  UPDATE test SET ROW = nt(1);
END;

-- 查看结果
SELECT * from test;

结果显示如下：

id | name 
----+------
 26 | z
(1 row)