如何在存储过程以及JDBC中使用Array

最新推荐文章于 2021-12-22 11:10:06 发布

查理曼大帝

最新推荐文章于 2021-12-22 11:10:06 发布

阅读量3.9k

点赞数 1

分类专栏： JEE 文章标签：存储过程-Array db2

本文链接：https://blog.csdn.net/hintcnuie/article/details/73742549

版权

JEE 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

环境

数据库：DB2

语言： Java

协议： JDBC4.0 及以上

在存储过程中使用Array

## 定义Array 类型：
在存储过程的最上面，定义这个Array 类型

DROP SPECIFIC PROCEDURE EB1.S_INFO
@
CREATE TYPE NumsArray AS VARCHAR(15) ARRAY[20]
@
CREATE PROCEDURE EBIZ1.S_INFO(
    OUT poGenStatus               INTEGER
    ,IN  piNums               NumsArray
    ,IN  piOrg                CHAR(4)
)

## 在存储过程中使用Array
### 在Select 中使用Array作为单独一列
使用UNNEST 函数

SELECT cust_id
FROM customers, UNNEST(phoneList) as T(phone)
WHERE customers.phone = T.phone

在JDBC中使用Array

## 对于Input 类型参数
说明： For IN or INOUT parameters, use the DB2Connection.createArrayOf method (JDBC 3.0) or the Connection.createArrayOf method (JDBC 4.0 or later) to create a java.sql.Array object. Use the CallableStatement.setArray method or the CallableStatement.setObject method to assign a java.sql.Array object to an ARRAY stored procedure parameter.

    Array array = cnxn.createArrayOf("VARCHAR",
                    par.getNumsArray());
            cstmt.setArray(2, array);

## 对于Output类型参数
Connection con; CallableStatement cstmt; ResultSet rs; java.sql.Array inPhoneData; … cstmt = con.prepareCall("CALL GET_EMP_DATA(?,?)"); // Create a CallableStatement object cstmt.setObject (1, inPhoneData); // Set input parameter cstmt.registerOutParameter (2, java.sql.Types.ARRAY); // Register out parameters cstmt.executeUpdate(); // Call the stored procedure Array outPhoneData = cstmt.getArray(2); // Get the output parameter array System.out.println("Parameter values from GET_EMP_DATA call: "); String [] outPhoneNums = (String [])outPhoneData.getArray(); // Retrieve output data from the JDBC Array object // into a Java String array for(int i=0; i<outPhoneNums.length; i++) { System.out.print(outPhoneNums[i]); System.out.println(); }

其他未涉及到的Array 方法

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#