Viewing Arrays with Stored Procedures
Go Up to Using Stored Procedures
If a table contains columns defined as arrays, you cannot view the data in the column with a simple SELECT statement, since only the array ID is stored in the table. Arrays can be used to display array values, as long as the dimensions and data type of the array column are known in advance.
For example, in the employee database, the JOB table has a column named LANGUAGE_REQ containing the languages required for the position. The column is defined as an array of five VARCHAR(15).
In isql, if you perform a simple SELECT statement, such as:
SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY, LANGUAGE_REQ FROM JOB;
part of the results look like this:
JOB_CODE JOB_GRADE JOB_COUNTRY LANGUAGE_REQ ======= ======== ============ ========= . . . Sales 3 USA <null> Sales 3 England 20:af SRep 4 USA 20:b0 SRep 4 England 20:b2 SRep 4 Canada 20:b4 . . .
To view the contents of the LANGUAGE_REQ column, use a stored procedure, such as the following:
CREATE PROCEDURE VIEW_LANGS
RETURNS (code VARCHAR(5), grade SMALLINT, cty VARCHAR(15),
lang VARCHAR(15))
AS
DECLARE VARIABLE i INTEGER;
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY
FROM JOB
WHERE LANGUAGE_REQ IS NOT NULL
INTO :code, :grade, :cty
DO
BEGIN
i = 1;
WHILE (i <= 5) DO
BEGIN
SELECT LANGUAGE_REQ[:i] FROM JOB
WHERE ((JOB_CODE = :code) AND (JOB_GRADE = :grade)
AND (JOB_COUNTRY = :cty)) INTO :lang;
i = i + 1;
SUSPEND;
END
END
END ;
This procedure, VIEW_LANGS, uses a FOR … SELECT loop to retrieve each row from JOB for which LANGUAGE_REQ is not NULL. Then a WHILE loop retrieves each element of the LANGUAGE_REQ array and returns the value to the calling application (in this case, isql).
For example, if this procedure is invoked with:
SELECT * FROM VIEW_LANGS;
the output is:
CODE GRADE CTY LANG ===== ===== ============ ========= Eng 3 Japan Japanese Eng 3 Japan Mandarin Eng 3 Japan English Eng 3 Japan Eng 3 Japan Eng 4 England English Eng 4 England German Eng 4 England French . . .
This procedure can easily be modified to return only the language requirements for a particular job, when passed JOB_CODE, JOB_GRADE, and JOB_COUNTRY as input parameters.