Example of using arrays in an SQL procedure
An example demonstrates many of the ways that you can use arrays in a native SQL procedure.
The example demonstrates how to:
- Create an associative array type.
- Create an ordinary array type.
- Create a stored procedure with arrays as parameters.
- Define arrays as SQL variables.
- Use the ARRAY_AGG built-in function in a cursor declaration, to assign the rows of a single-column result table to elements of an array. Use the cursor to retrieve the array into an SQL out parameter.
- Use an array constructor to initialize an array.
- Assign a constant or an expression to an array element.
- Use the UNNEST specification to generate the intermediate result table from an array for a subselect within an INSERT statement.
- Use the ARRAY_AGG built-in function to assign the rows of a single column result table to elements of an array, and then assign that array to an array SQL OUT parameter.
- Use the CARDINALITY built-in function to determine how many times to execute a WHILE loop.
- Use a parameter marker for an array variable and an array index in the WHERE clause of a SELECT statement.
- Use the ARRAY_AGG built-in function in the SELECT list of a SELECT INTO statement, and assign the resulting array to an array SQL OUT parameter.
- Update column values with array elements.
In this example, the pound sign (#) is used as the SQL terminator character.
--
-- CREATE ASSOCIATIVE ARRAY TYPES
--
CREATE TYPE CHARARRAY AS CHAR(10) ARRAY[VARCHAR(3)]#
CREATE TYPE BIGINTARRAY AS BIGINT ARRAY[INTEGER]#
--
-- CREATE ORDINARY ARRAY TYPES
--
CREATE TYPE INTARRAY AS INTEGER ARRAY[100]#
CREATE TYPE STRINGARRAY AS VARCHAR(10) ARRAY[100]#
--
-- CREATE TABLES THAT ARE USED IN SQL PROCEDURE PROCESSPERSONS
--
CREATE TABLE PERSONS (ID INTEGER, NAME VARCHAR(10))#
CREATE TABLE ARRAYTEST (CHARCOL CHAR(10), INTCOL INT)#
-- SQL PROCEDURE PROCESSPERSONS HAS THREE ARRAY PARAMETERS:
-- OUTSETARRAY IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE STRINGARRAY.
-- OUTSELECTWITHCURSOR IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE STRINGARRAY.
-- OUTSELECTWITHARRAYAGG IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE INTARRAY.
--
CREATE PROCEDURE PROCESSPERSONS(OUT OUTSETARRAY STRINGARRAY,
INOUT INT0 INT,
OUT OUTSELECTWITHCURSOR STRINGARRAY,
OUT OUTMAXCARDINALITY BIGINT,
OUT OUTSELECTWITHARRAYAGG INTARRAY)
ARRAYDEMO: BEGIN
-- DECLARE SQL VARIABLES OF ORDINARY ARRAY TYPES
DECLARE IDS_ORDARRAYVAR INTARRAY;
DECLARE INT_ORDARRAYVAR INTARRAY;
DECLARE NAMES_ORDARRAYVAR STRINGARRAY;
-- DECLARE SQL VARIABLES OF ASSOCIATIVE ARRAY TYPES
DECLARE CHAR_ASSOCARRAYVAR CHARARRAY;
DECLARE BIGINT_ASSOCARRAYVAR BIGINTARRAY;
-- DECLARE SCALAR SQL VARIABLES
DECLARE DECFLOAT_VAR DECFLOAT;
DECLARE BIGINT_VAR BIGINT;
DECLARE SMALLINT_VAR SMALLINT;
DECLARE INT_VAR INT DEFAULT 1;
DECLARE STMT_VAR CHAR(100);
-- DECLARE A CURSOR
DECLARE C2 CURSOR FOR S1;
--
-- THE RESULT TABLE OF CURSOR C1 IS AN ARRAY THAT IS POPULATED BY
-- RETRIEVING THE VALUES OF THE NAME COLUMN FROM TABLE PERSONS,
-- ORDERING THE VALUES BY ID, AND USING THE ARRAY_AGG FUNCTION
-- TO ASSIGN THE VALUES TO AN ARRAY.
--
DECLARE C1 CURSOR FOR SELECT ARRAY_AGG(NAME ORDER BY ID) FROM PERSONS
WHERE NAME LIKE 'J%';
--
-- USE ARRAY CONSTRUCTORS TO INITIALIZE ARRAYS
--
SET IDS_ORDARRAYVAR = ARRAY[5,6,7];
SET NAMES_ORDARRAYVAR = ARRAY['BOB', 'ANN', 'SUE'];
SET CHAR_ASSOCARRAYVAR['001']='1';
SET CHAR_ASSOCARRAYVAR['002']='2';
SET CHAR_ASSOCARRAYVAR['003']='3';
SET CHAR_ASSOCARRAYVAR['004']='4';
SET CHAR_ASSOCARRAYVAR['005']='5';
SET CHAR_ASSOCARRAYVAR['006']='6';
SET INT_ORDARRAYVAR = ARRAY[1,INTEGER(2),3+0,4,5,6] ;
SET BIGINT_ASSOCARRAYVAR[1] = 9;
SET BIGINT_ASSOCARRAYVAR[3] = 10;
SET BIGINT_ASSOCARRAYVAR[5] = 11;
SET BIGINT_ASSOCARRAYVAR[7] = 12;
SET BIGINT_ASSOCARRAYVAR[9] = 13;
--
-- ASSIGN A CONSTANT TO AN ARRAY ELEMENT.
--
SET IDS_ORDARRAYVAR[4] = 8;
--
-- ASSIGN AN EXPRESSION TO AN ARRAY ELEMENT.
--
SET IDS_ORDARRAYVAR[5] = 8 * 4 ;
--
-- ASSIGN AN ARRAY ELEMENT TO ANOTHER ARRAY ELEMENT. USE AN EXPRESSION
-- TO IDENTIFY THE TARGET ARRAY ELEMENT.
--
SET NAMES_ORDARRAYVAR[1+INT_VAR] = NAMES_ORDARRAYVAR[5] ;
--
-- POPULATE THE PERSONS TABLE WITH AN INSERT STATEMENT WITH A SUBSELECT:
-- - USE UNNEST TO RETRIEVE VALUES FROM AN ARRAY INTO AN INTERMEDIATE RESULT
-- TABLE.
-- - INSERT THE VALUES FROM THE INTERMEDIATE RESULT TABLE INTO
-- THE PERSONS TABLE.
--
INSERT INTO PERSONS(ID, NAME)
(SELECT T.I, T.N FROM UNNEST(IDS_ORDARRAYVAR, NAMES_ORDARRAYVAR) AS T(I, N));
--
-- USE THE ARRAY_AGG FUNCTION TO CREATE AN ARRAY FROM THE RESULT
-- TABLE OF A SELECT. THEN ASSIGN THAT ARRAY TO AN SQL OUT PARAMETER.
--
SET OUTSETARRAY = (SELECT ARRAY_AGG(NAME ORDER BY ID)
FROM PERSONS
WHERE NAME LIKE '%O%');
--
-- USE THE CARDINALITY FUNCTION TO CONTROL THE NUMBER OF TIMES THAT
-- AN INSERT STATEMENT IS EXECUTED TO POPULATE TABLE ARRAYTEST
-- WITH ARRAY ELEMENTS.
--
SET SMALLINT_VAR = 1;
WHILE SMALLINT_VAR <= CARDINALITY(INT_ORDARRAYVAR) DO
INSERT INTO ARRAYTEST VALUES
(CHAR_ASSOCARRAYVAR[SMALLINT_VAR],
INT_ORDARRAYVAR[SMALLINT_VAR]);
SET SMALLINT_VAR = SMALLINT_VAR+1;
END WHILE;
--
-- DYNAMICALLY EXECUTE AN SQL SELECT STATEMENT WITH A PARAMETER MARKER
-- FOR AN ARRAY, AND A PARAMETER MARKER FOR THE ARRAY INDEX.
--
SET INT_VAR = 3;
SET STMT_VAR =
'SELECT INTCOL FROM ARRAYTEST WHERE INTCOL = ' ||
'CAST(? AS INTARRAY)[?]';
PREPARE S1 FROM STMT_VAR;
OPEN C2 USING INT_ORDARRAYVAR, INT_VAR;
FETCH C2 INTO INT0;
CLOSE C2;
--
-- USE A CURSOR TO FETCH AN ARRAY THAT IS CREATED WITH THE ARRAY_AGG FUNCTION
-- INTO AN ARRAY SQL OUT PARAMETER.
--
OPEN C1;
FETCH C1 INTO OUTSELECTWITHCURSOR;
CLOSE C1;
--
-- RETURN THE MAXIMUM CARDINALITY OF AN ARRAY USING THE MAX_CARDINALITY
-- FUNCTION, AND STORE THE VALUE IN AN SQL VARIABLE.
--
SET OUTMAXCARDINALITY = MAX_CARDINALITY(INT_ORDARRAYVAR);
--
-- IN A SELECT INTO STATEMENT, USE THE ARRAY_AGG FUNCTION TO
-- ASSIGN THE VALUES OF COLUMN INTCOL TO ARRAY ELEMENTS, AND ASSIGN
-- THOSE ELEMENTS TO ARRAY OUT PARAMETER OUTSELECTWITHARRAYAGG.
--
SELECT ARRAY_AGG(INTCOL) INTO OUTSELECTWITHARRAYAGG FROM ARRAYTEST;
--
-- IN AN UPDATE STATEMENT, ASSIGN ARRAY ELEMENTS TO COLUMNS.
--
SET SMALLINT_VAR = 1;
WHILE SMALLINT_VAR <= CARDINALITY(INT_ORDARRAYVAR) DO
UPDATE ARRAYTEST
SET CHARCOL =
CHAR_ASSOCARRAYVAR[SMALLINT_VAR], INTCOL = INT_ORDARRAYVAR[SMALLINT_VAR];
SET SMALLINT_VAR = SMALLINT_VAR +1;
END WHILE;
END#