PolarDB:Special usage

Examples

The following example describes how to extract data from a table to a collection variable and then insert the data into another table:

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

Sample result:

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

The following example describes how to use a collection variable to update a table:

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;

Sample result:

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

BULK COLLECT INTO

The BULK COLLECT INTO syntax is suitable for scenarios similar to those of the INTO syntax. The difference between the two syntaxes is that the INTO syntax is suitable for scalars or row variables and the BULK COLLECT INTO syntax is suitable for collection variables. The BULK COLLECT INTO syntax can be used to insert multiple rows of data from a table into a collection variable. When you use this syntax, you do not need to initialize collection variables. Even if the collection variable for which you use this syntax already contains elements, the variable is reinitialized before the variable is used. In addition to SELECT ... BULK COLLECT INTO mentioned in the preceding example, this syntax can also be used in the following scenarios.

The following example describes how to use 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;

  -- You can limit the number of FETCH operations by using a LIMIT keyword. This prevents an excessive number of FETCH operations.
  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;

Sample result:

NOTICE:  1 a
DO

The following example describes how to use RETURNING ... BULK COLLECT INTO:

DECLARE
  TYPE t_rowtype IS TABLE OF test%rowtype INDEX BY PLS_INTEGER;
  r t_rowtype;
BEGIN
  -- The RETURNING syntax is supported when the DELETE, INSERT, UPDATE, or EXECUTE IMMEDIATE statement is used.
  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;

Sample result:

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

FORALL

The FORALL statement has functions similar to the FOR .. LOOP statement. However, the statement has additional usage and limits. The statement has the following declaration syntax:

-- Traverse the range specified by the lower and upper bounds.
FORALL i IN lower_bound..upper_bound
  single SQL -- Only a single SQL or a dynamic SQL statement is allowed.

-- Traverse index arrays (only associative arrays whose indexes are numeric values are supported).
FORALL i IN INDICES OF collection
  single SQL

-- Traverse values (only collection variables whose element are of the numeric type are supported).
FORALL i IN VALUES OF collection
  single SQL

You can use a special associative array SQL%BULK_ROWCOUNT to access the number of rows affected by each statement that is executed, as shown in the following example:

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

  -- It is expected that two rows are updated for the first time and one row is updated for the second time.
  FOR i in SQL%BULK_ROWCOUNT.first..SQL%BULK_ROWCOUNT.last LOOP
    RAISE NOTICE '%', SQL%BULK_ROWCOUNT(i);
  END LOOP;
END;

Sample result:

NOTICE:  2
NOTICE:  1
DO

In most cases, the FORALL statement throws an exception the first time an SQL statement that is executed in the loop fails. If you declare an EXCEPTION block, the exception is handled. If you want to skip the SQL statement that throws an exception and continue the loop execution, make sure that statement-level transactions are enabled for your database and use the SAVE EXCEPTIONS syntax in the FORALL statement. In this case, the FORALL statement traverses all variables, executes all SQL statements, and then throws the exception FORALL_DML_ERROR. The following example describes how to save an exception and proceed with the execution:

FORALL i IN lower_bound..upper_bound SAVE EXCEPTIONS
  single SQL

If an exception occurs during the execution, you can use another special associative array SQL%BULK_EXCEPTIONS to access error information about each statement that fails. The following example describes how to view error information about an abnormal SQL statement:

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

-- Preprepares: Create a table with unique constraints.
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
  -- Under primary key constraints, the second and fourth entries failed to be inserted.
  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 insertion result.
SELECT * FROM test_unique;

Sample result:

-- The output of 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

-- The output of the SELECT statement.
 id 
----
  1
  2
  3
(3 rows)

INSERT/UPDATE

The following syntax is supported for record types in PL/SQL:

-- Insert record variables into the test table.
INSERT INTO test VALUES record;
-- Use record variables to update the test table.
UPDATE table SET ROW = record;

You can use a syntax similar to the preceding syntax to interact with a table for a collection variable whose elements are of the record type.

-- Clear the test table.
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
  -- Insert the elements of a collection variable into the test table.
  INSERT INTO test VALUES nt(1);
END;

-- View the insertion result.
SELECT * from test;

Sample result:

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

Use the special syntax to update a table.

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
  -- Use the elements of a collection variable to update the test table.
  UPDATE test SET ROW = nt(1);
END;

-- View the result.
SELECT * from test;

Sample result:

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