SQL プロシージャー内での配列の使用例
ネイティブ SQL プロシージャー内で配列を使用する方法の多くを例示します。
以下の方法について例示します。
- 結合配列型を作成します。
- 通常の配列型を作成します。
- パラメーターとして配列を指定したストアード・プロシージャーを作成します。
- 配列を SQL 変数として定義します。
- カーソル宣言内で ARRAY_AGG 組み込み関数を使用して、単一列の結果表の行を配列のエレメントに割り当てます。 カーソルを使用して、配列を SQL OUT パラメーター内に取り出します。
- 配列コンストラクターを使用して、配列を初期化します。
- 定数または式を配列エレメントに割り当てます。
- UNNEST 指定を使用して、INSERT ステートメント内の副選択に関する配列から中間結果表を生成します。
- ARRAY_AGG 組み込み関数を使用して、単一列の結果表の行を配列のエレメントに割り当ててから、その配列を ARRAY SQL OUT パラメーターに割り当てます。
- CARDINALITY 組み込み関数を使用して、WHILE ループを実行する回数を決めます。
- SELECT ステートメントの WHERE 節で、配列変数および添字のパラメーター・マーカーを使用します。
- SELECT INTO ステートメントの SELECT リスト内で ARRAY_AGG 組み込み関数を使用し、結果の配列を ARRAY SQL OUT パラメーターに割り当てます。
- 配列エレメントを使用して列値を更新します。
以下の例では、ポンド記号 (#) が SQL 終止符文字として使用されています。
--
-- 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#