5.5 Collections

---

5.5 Collections

In Oracle, a collection is an ordered group of elements, all of the same type; for example, the bonuses for a employees. Each element has a unique subscript that determines its position in the collection. Oracle v8.1 PL/SQL offers two kinds of collections: nested tables and varrays (short for variable-size arrays).

Collections can have only one dimension, and must be indexed by integers. Collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables, or between client-side applications and stored procedures.

5.5.1 Nested tables and varrays

To understand how nested tables and varrays can be converted to DB2, first let us address the difference between Oracle nested tables and varrays.

Items of type TABLE are called nested tables. Within the database, they can be viewed as one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. Nested table has no upper bound, while the size of variable arrays is fixed. The second important difference, the variable arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can be sparse (have nonconsecutive subscripts).

As DB2 does not support collections, the most generic way to convert a nested table is by using DB2 Declared Global Temporary Table (DGTT), where first column stores the value of the subscript, and the second column stores the value of Oracle nested table.

Note 

DB2 temporary tables are not similar to Oracle temporary tables. DB2 temporary tables are memory bound (provided sufficient memory is available), visible only to the connection that declares it, and exist only for as long as a connection is maintained (or dropped). If you disconnect, the table is automatically cleaned up.

Tip 

To use DGTTs, you must create a user temporary table space (none exists by default). In the simplest case, you can use:

 create user temporary tablespace usertemp1 managed by system using ('usertemp1') 

The size of the buffer pool associated with this tablespace will affect how memory-bound DGTTs are at runtime.

Let us clarify this with an example (Example 5-15) that filling nested table EmpList with names of the Employees for the given department from table emp_table.

Example 5-15: Oracle code using nested table

 DECLARE   TYPE EmpList IS TABLE OF emp_table.ename%TYPE ;   CURSOR c1 IS      SELECT emp_name      FROM emp_table      WHERE dept = v_dept;   EmpName emp_table.ename%TYPE;   empNum NUMBER; BEGIN   LOOP     FETCH c1 INTO EmpName;     WHEN c1%NOTFOUND EXIT;     empNum := empNum + 1;     EmpList(empNum):= EmpName;   END LOOP;   CLOSE c1; END; 

The same can be implemented in DB2 using DGTT as shown in Example 5-16.

Example 5-16: DB2 UDB code using DGTT

 DECLARE SQLCODE INT DEFAULT 0; DECLARE v_empname varchar(30); DECLARE v_num INT DEFAULT 0; DECLARE c1 CURSOR FOR         SELECT emp_name         FROM emp_table         WHERE dept = v_dept; DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp_list         (num integer, EmpName varchar(30))         WITH REPLACE         ON COMMIT PRESERVE ROWS         NOT LOGGED; OPEN c1;    WHILE (SQLCODE = 0) DO       FETCH c1 INTO v_empname;       SET v_num = v_num +1;       INSERT INTO SESSION.temp_emp_list          VALUES (v_num,v_empname);    END WHILE; CLOSE c1; 

Or better yet, the code is more efficient if converted as follows (Example 5-17).

Example 5-17: Efficient DB2 UDB code using DGTT

 DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp_list          (num integer,EmpName varchar(30)) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED; INSERT INTO session.temp_emp_list        SELECT row_number() over(), emp_name        FROM emp_table        WHERE dept = v_dept; 

To convert Oracle varrays, you can also use DGTT, or sometimes the redesign can help achieve the same functionality.

5.5.2 Bulk collect

In Oracle 8i and higher, you can fetch more then one row at a time into collection, using the BULK COLLECT clause. This clause is used as part of the SELECT INTO, FETCH INTO, or RETURNING INTO clause, and will retrieve rows from the query into indicated collections.

Example 5-15 on page 174 can be re-written using the BULK COLLECT clause as following:

    DECLARE        TYPE EmpList IS TABLE OF emp_table.ename%TYPE ;        CURSOR c1 IS           SELECT emp_name           FROM emp_table           WHERE dept = v_dept;    BEGIN        OPEN c1;        FETCH c1 BULK COLLECT INTO EmpList;        CLOSE c1;    END; 

or

    DECLARE       TYPE EmpList IS TABLE OF emp_table.ename%TYPE ;    BEGIN       SELECT emp_name BULK COLLECT INTO EmpList;    END; 

Oracle will treat SELECT INTO as implicit cursor. It will fetch the data starting at index 1, and successively overwrite elements in the output collection EmpList until it retrieved all requested rows.

To convert BULK COLLECT statement to DB2, you can use DB2 DGTT as shown in Example 5-16 on page 174, or in some cases you can use INSERT INTO (SELECT * FROM) statement, as it shows bellow (Example 5-18).

Example 5-18: DB2 UDB code using INSERT INTO

 DECLARE v_empname varchar(30); DECLARE v_num INT DEFAULT 0; DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp_list         (num INTEGER, EmpName VARCHAR(30))         WITH REPLACE         ON COMMIT PRESERVE ROWS         NOT LOGGED; INSERT INTO SESSION.temp_emp_list (    SELECT emp_name    FROM emp_table    WHERE detp = v_dept); 

For more information on the GLOBAL TEMPORARY TABLE refer to the DB2 UDB V8 manual SQL Reference Volume 2.

5.5.3 Passing result sets between procedures

In previous section, we discuss the general conversion principals for Oracle collection. This section demonstrates the special case of passing a multiple row result from one procedure to another.

It is often convenient to manipulate many variables at once as one unit. Oracle nested tables and varrays are frequently used to implement this kind of application.

For example, we need retrieve all employees from a specified department, who have an account code equal to 307, and pass the result to a PL/SQL block (it could as well be a client program). Here is a PL/SQL procedure that returns nested tables as the output parameter (Example 5-19).

Example 5-19: PL/SQL procedure returns nested table

 CREATE PACKAGE BODY AccountPackage AS   PROCEDURE AccountList(p_dept_code    IN     accounts.dept_code%TYPE,                         p_acct_id      IN     accounts.acct_id%TYPE,                         p_IDs          OUT    t_EmployeeIDTable,                         p_NumEmployees IN OUT NUMBER) IS       v_EmployeeID Employees.Emp_id%TYPE;       -- Local cursor to fetch the registered Employees.       CURSOR c_RegisteredEmployees IS         SELECT Emp_id           FROM Employees           WHERE dept_code = p_dept_code           AND acct_id = p_acct_id;     BEGIN       /* p_NumEmployees will be the table index. It will start at          0, and be incremented each time through the fetch loop.          At the end of the loop, it will have the number of rows          fetched, and therefore the number of rows returned in          p_IDs. */       p_NumEmployees := 0;       OPEN c_RegisteredEmployees;       LOOP         FETCH c_RegisteredEmployees INTO v_EmployeeID;         EXIT WHEN c_RegisteredEmployees%NOTFOUND;         p_NumEmployees := p_NumEmployees + 1;         p_IDs(p_NumEmployees) := vEmployeeID;       END LOOP;     END AccountList; END AccountPackage; 

Please note that type t_EmployeeIDTable should be declared within AccountPackage specification as follows:

    TYPE t_EmployeeIDTable IS TABLE OF Employees.Emp_id%TYPE; 

The AccountList procedure can be called from the following PL/SQL block:

    DECLARE      v_DeptEmployees   AccountPackage.t_EmployeeIDTable;      v_NumEmployees   BINARY_INTEGER := 20;    BEGIN      -- Fill the PL/SQL table with employees from dept 'BA'      AccountPackage.AccountList('BA', 307, v_DeptEmployees,v_NumEmployees);      -- Insert these employee into temp_table      FOR v_LoopCounter IN 1..v_NumEmployees      LOOP        INSERT INTO temp_table (num_col, char_col)          VALUES (v_DeptEmployees(v_LoopCounter),                   'In Department BA');      END LOOP;    END; 

Using nested table t_EmployeeIDTable, the results from cursor c_RegisteredEmployees are passed to the calling block as one unit or as one output variable.

DB2 has a different mechanism for processing multiple rows results. SQL procedure uses the following to return the result set to a caller:

• Specify DYNAMIC RESULT SETS clause in CREATE PROCEDURE statement.

• Declare the cursor using WITH RETURN clause.

• Keep the cursor open for the client application.

• Unlike Oracle, no parameter is required for the result set to be passed out of this procedure.

Example 5-20 shows how a SQL procedure can pass results from the same cursor to the calling application. The name of the cursor has been changed to adhere to the DB2 18 character limit.

Example 5-20: SQL Procedure returns multiple rows using CURSOR WITH RETURN

 CREATE PROCEDURE AccountPackage.AccountList ( IN p_dept_code CHAR(3),                                               IN p_acct_id SMALLINT ) LANGUAGE SQL RESULT SET 1 BEGIN     DECLARE SQLCODE INTEGER DEFAULT 0;     DECLARE c_RegisteredEmplo1 CURSOR WITH RETURN TO CALLER      FOR SELECT "EMP_ID"          FROM EMPLOYEES        WHERE "DEPT_CODE" = p_dept_code          AND "ACCT_ID" = p_acct_id;       OPEN c_RegisteredEmplo1; END 

There are two options for WITH RETURN clause:

• WITH RETURN TO CALLER (default): Use this option to return the result set to the invoker, whether the invoker is an application or another procedure.

• WITH RETURN TO CLIENT: Use this option to return the result set directly to the application, bypassing any intermediate nested routines.

To imitate Oracle example in full, let us convert a PL/SQL block that calls the AcountPackage.AccountList procedure to a DB2 SQL procedure as shown in Example 5-21.

Listing 5-21: DB2 Store procedure calls AccountPackage.AccountList

 CREATE PROCEDURE AccountPackage.CALL_AccountList ( )    LANGUAGE SQL BEGIN    DECLARE sqlcode  INT DEFAULT 0;    DECLARE v_empId  INT DEFAULT 0;    DECLARE v_empNum INT DEFAULT 0;    DECLARE v_empCnt INT DEFAULT 0;    DECLARE loc1     RESULT_SET_LOCATOR VARYING;            -- [1]    SET v_empNum = 20;    CALL AccountPackage.AccountList('BA',307);    ASSOCIATE RESULT SET LOCATOR( loc1) WITH                -- [2]            PROCEDURE  AccountPackage.AccountList;    ALLOCATE c1 CURSOR FOR RESULT SET loc1;                 -- [3] L1: LOOP     FETCH FROM c1 INTO v_empID;     IF (sqlcode = 100) or (v_empCnt > v_empNum)       THEN LEAVE L1;     ELSE       SET v_empCnt = v_empCnt + 1;       INSERT INTO temp_table (num_col, char_col)            VALUES (v_empId, 'IN DEPARTMENT ');     END IF;   END LOOP L1; END 

Note

To receive result sets in SQL procedures, it is necessary to:

[1]	DECLARE result set locators to the stored procedure expected to return these result sets.
[2]	ASSOCIATE result set locators to the stored procedure expected to return these result sets.
[3]	ALLOCATE each cursor expected to be returned to a result set locator.

Once this is done, rows can be fetched from the result sets. Please note that the cursor in this case plays the role of an Oracle nested table, and allows you to pass multiple variables (result set from cursor) as one unit.

---