PL/SQL Collections

PL/SQL RECORD:
A record is a group of related data items stored in fields, each with its own name and datatype. Suppose you have various data about an employee such as name, salary, and hire date. These items are logically related but dissimilar in type. A record containing a field for each item lets you treat the data as a logical unit. Thus, records make it easier to organize and represent information.

The attribute %ROWTYPE lets you declare a record that represents a row in a database table. However, you cannot specify the datatypes of fields in the record or declare fields of your own. The datatype RECORD lifts those restrictions and lets you define your own records.
Manipulating Records
The datatype RECORD letxs you collect information about the attributes of something.
The information is easy to manipulate because you can refer to the collection as a whole.

In the following example, you collect accounting figures from database tables assets and liabilities, then use ratio analysis to compare the performance of two subsidiary companies:

DECLARE
TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
Sub1_figs FiguresRec;
Sub2_figs FiguresRec;

FUNCTION acid_test (figs FiguresRec) RETURN REAL IS...
BEGIN
SELECT cash, notes ... INTO sub1_figs FROM assets, liabilities
WHERE assets.sub = 1 AND liabilities.sub = 1;

SELECT cash, notes ... INTO sub2_figs FROM assets, liabilities
WHERE assets.sub = 2 AND liabilities.sub = 2;

IF acid_test (sub1_figs) > acid_test (sub2_figs) THEN...
...
END;

Notice how easy it is to pass the collected figures to the function acid_test, which computes a financial ratio.
----------------------------------------------------------------------------------------------
In the example below, you fetch rows from database table flights into record flight_info. That way, you can treat all the information about a flight, including its passenger list, as a logical unit.

DECLARE
TYPE FlightRec IS RECORD (
flight_no NUMBER(3), gate CHAR(5), departure CHAR(15), arrival CHAR(15),
passengers PassengerList);

flight_info FlightRec;
CURSOR c1 IS SELECT * FROM flights;
seat_not_available EXCEPTION;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO flight_info;
EXIT WHEN c1%NOTFOUND;
FOR i IN 1...FLIGHT_info.passengers.LAST LOOP
IF flight_INFO.PASSENGERS (i).seat = ’NA’ THEN
dbms_output.put_LINE (flight_INFO.PASSENGERS (i).name);
RAISE seat_not_available;
END IF;
...
END LOOP;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN seat_not_available THEN
...
END;

PL/SQL TABLE:
In the example below, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into index-by table empnos. The previous values are overwritten.

DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;

BULK COLLECT / FORALL / DYNAMIC SQL:

CREATE OR REPLACE PROCEDURE bulkcollect_9i_demo (whr_in IN VARCHAR2 := NULL)
IS
TYPE numlist_t IS TABLE OF NUMBER;
TYPE namelist_t IS TABLE OF VARCHAR2 (100);
-- New Oracle9i pre-defined REF CURSOR type. This is equivalent to:
-- TYPE sys_refcursor IS REF CURSOR
emp_cv sys_refcursor;
empnos numlist_t;
enames namelist_t;
enames_updated namelist_t;
ename_filter namelist_t := namelist_t ('S%', 'E%', 'M%');
sals numlist_t;
l_count PLS_INTEGER;

bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
-- Bulk fetch with cursor variable
OPEN emp_cv FOR 'SELECT empno, ename FROM emp WHERE ' NVL (whr_in, '1=1');
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;

-- Bulk fetch with "implicit cursor"
EXECUTE IMMEDIATE 'SELECT sal FROM emp WHERE ' NVL (whr_in, '1=1')
BULK COLLECT INTO sals;
Dbms_Output.put_line (sals.COUNT);

-- Bulk, dynamic UPDATE
FORALL indx IN empnos.FIRST .. empnos.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1 WHERE empno = :employee_key '
'RETURNING ename INTO :names_updated'
USING empnos(indx) -- Must specify individual row with FORALL index
RETURNING BULK COLLECT INTO enames_updated; -- Specify collection as whole

-- Using SQL%BULK_ROWCOUNT: how many rows modified by each statement?
FORALL indx IN ename_filter.FIRST .. ename_filter.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1
WHERE ename LIKE :employee_filter'
USING ename_filter(indx);

FOR indx IN ename_filter.FIRST .. ename_filter.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Number of employees with names like "'
ename_filter(indx)
'" given a raise: ' SQL%BULK_ROWCOUNT(indx));
END LOOP;
END;
/