PolarDB:Special usage

SELECT ... BULK COLLECT INTO

The following example extracts all rows from test into a nested table variable and then inserts them into test_backup using FORALL:

-- Preparation
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;

Result:

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

FETCH cursor BULK COLLECT INTO

Use LIMIT to cap the number of rows fetched per call and avoid excessive memory use:

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 at most 1 row per call to control memory usage.
  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;

Result:

NOTICE:  1 a
DO

RETURNING ... BULK COLLECT INTO

Use this form to capture the rows affected by a DELETE, INSERT, UPDATE, or EXECUTE IMMEDIATE statement:

DECLARE
  TYPE t_rowtype IS TABLE OF test%ROWTYPE INDEX BY PLS_INTEGER;
  r t_rowtype;
BEGIN
  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;

Result:

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

Track rows affected per statement

SQL%BULK_ROWCOUNT is an associative array populated automatically after each FORALL run. Each entry holds the number of rows affected by the corresponding loop iteration.

The following example runs two iterations. The first iteration matches two rows (id = 1 appears twice in the table); the second matches one row:

-- Preparation
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);

  FOR i IN SQL%BULK_ROWCOUNT.FIRST..SQL%BULK_ROWCOUNT.LAST LOOP
    RAISE NOTICE '%', SQL%BULK_ROWCOUNT(i);
  END LOOP;
END;

Result:

NOTICE:  2
NOTICE:  1
DO

Handle exceptions with SAVE EXCEPTIONS

By default, FORALL raises an exception on the first failing statement and stops. To skip failed statements and continue the loop, add the SAVE EXCEPTIONS clause:

FORALL i IN lower_bound..upper_bound SAVE EXCEPTIONS
  single SQL

When FORALL_DML_ERROR is raised, inspect SQL%BULK_EXCEPTIONS to get details on every failed statement. Each entry has three fields:

For example, if a FORALL SAVE EXCEPTIONS loop runs five iterations and the second and fourth fail, then SQL%BULK_EXCEPTIONS.COUNT equals 2, SQL%BULK_EXCEPTIONS(1).ERROR_INDEX is 2, and SQL%BULK_EXCEPTIONS(2).ERROR_INDEX is 4.

The following example inserts five values into a table with a primary key constraint. The second and fourth inserts duplicate existing keys and fail:

-- Make sure that statement-level transactions are enabled.

-- Create a table with a primary key constraint.
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
  -- The 2nd and 4th iterations fail because of duplicate primary key values.
  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;

-- View the rows that were successfully inserted.
SELECT * FROM test_unique;

Result:

-- PL/SQL output
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 output
 id
----
  1
  2
  3
(3 rows)