In the first post in this series, I introduced the concept of metaprogramming in SQL using dynamic SQL and applied the technique to write a procedure that could generate comparison functions that compare two objects of any basic type. In the second post, I expanded the procedure to produce comparison functions that compare two objects of any ROW type. In today’s post, I will introduce the ARRAY type from DB2 9.7 and show how we can go about comparing two arrays, including arrays of ROW type objects.
The ARRAY Type
As described in the DB2 Information Center, the array type in DB2 9.7 is a user-defined data type consisting of an ordered set of elements of a single data type. There are various restrictions on what that single data type can be. Built-in types and ROW types are acceptable, but user-defined distinct types are not. There are two kinds of array types: ordinary arrays and associative arrays. Ordinary arrays have an upper bound on the number of elements and are indexed by ordinal position. Associative arrays have no such upper bound and they let you choose how they are indexed by providing a VARCHAR or INTEGER as the index.
Comparing Arrays
The ‘=’ operator cannot be used to compare two objects of ARRAY type. However, as we did for objects of ROW type in the last post, we will create a metaprogram that produces EQ functions for any ordinary ARRAY type we define. First, let’s define what it means for an ordinary array A to be considered equal to an ordinary array B:
- The length of array A must be equal to the length of array B.
- For each element in array A, the corresponding element by ordinal position in array B must be equal to that of array A.
Once again, let’s start with a program that compares two ordinary arrays of a specific type and we will later transform it into a metaprogram that can write comparison functions for any pair of ordinary arrays. First we need to compare the lengths (cardinalities) of the arrays:
<!--start_raw-->CREATE TYPE SIMPLEARRAY AS INTEGER ARRAY[100]@
CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY)
RETURNS INTEGER
READS SQL DATA
BEGIN
DECLARE ID INTEGER DEFAULT 1;
DECLARE RESULT INTEGER;
IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
SET RESULT = 1;<!--end_raw-->
Then, if the cardinalities match, we iterate over the elements of the arrays, comparing each one:
<!--start_raw--> WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
IF (LEFT[ID] = RIGHT[ID]) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
SET ID = ID + 1;
END WHILE;
ELSE
SET RESULT = 0;
END IF;
RETURN RESULT;
END@<!--end_raw-->
Before we transform this into a metaprogram, we will do our comparison using EQ instead of the ‘=’ operator. This way, it will work not just for elements that are of built-in type such as INTEGER elements but for ROW elements as well:
<!--start_raw-->CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY)
RETURNS INTEGER
READS SQL DATA
BEGIN
DECLARE ID INTEGER DEFAULT 1;
DECLARE RESULT INTEGER;
IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
SET RESULT = 1;
WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
SET RESULT = EQ(LEFT[ID], RIGHT[ID]);
SET ID = ID + 1;
END WHILE;
ELSE
SET RESULT = 0;
END IF;
RETURN RESULT;
END@<!--end_raw-->
Now, let’s make this a metaprogram. First, it will only work for SIMPLEARRAY objects:
<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_SIMPLEARRAY_EQ_FUNCTION() MODIFIES SQL DATA BEGIN DECLARE SQL VARCHAR(1024); SET SQL = 'CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY) '; SET SQL = SQL || 'RETURNS INTEGER '; SET SQL = SQL || 'READS SQL DATA '; SET SQL = SQL || 'BEGIN '; SET SQL = SQL || ' DECLARE ID INTEGER DEFAULT 1; '; SET SQL = SQL || ' DECLARE RESULT INTEGER; '; SET SQL = SQL || ' IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN '; SET SQL = SQL || ' SET RESULT = 1; '; SET SQL = SQL || ' WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO '; SET SQL = SQL || ' SET RESULT = EQ(LEFT[ID], RIGHT[ID]); '; SET SQL = SQL || ' SET ID = ID + 1; '; SET SQL = SQL || ' END WHILE; '; SET SQL = SQL || ' ELSE '; SET SQL = SQL || ' SET RESULT = 0; '; SET SQL = SQL || ' END IF; '; SET SQL = SQL || ' RETURN RESULT; '; SET SQL = SQL || 'END '; END@<!--end_raw-->
The final step is to generalize it to work for all ARRAY types:
<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(DATATYPE VARCHAR(128)) MODIFIES SQL DATA BEGIN DECLARE SQL VARCHAR(1024); SET SQL = 'CREATE OR REPLACE FUNCTION EQ(LEFT ' || DATATYPE || ', '; SET SQL = SQL || 'RIGHT ' || DATATYPE || ') '; SET SQL = SQL || 'RETURNS INTEGER '; SET SQL = SQL || 'READS SQL DATA '; SET SQL = SQL || 'BEGIN '; SET SQL = SQL || ' DECLARE ID INTEGER DEFAULT 1; '; SET SQL = SQL || ' DECLARE RESULT INTEGER; '; SET SQL = SQL || ' IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN '; SET SQL = SQL || ' SET RESULT = 1; '; SET SQL = SQL || ' WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO '; SET SQL = SQL || ' SET RESULT = EQ(LEFT[ID], RIGHT[ID]); '; SET SQL = SQL || ' SET ID = ID + 1; '; SET SQL = SQL || ' END WHILE; '; SET SQL = SQL || ' ELSE '; SET SQL = SQL || ' SET RESULT = 0; '; SET SQL = SQL || ' END IF; '; SET SQL = SQL || ' RETURN RESULT; '; SET SQL = SQL || 'END '; END@<!--end_raw-->
Now, we can write a program to use the CREATE_EQ_FUNCTION procedure to create a new EQ function for any ARRAY type and use it to compare two arrays:
<!--start_raw-->CALL CREATE_EQ_FUNCTION('SIMPLEARRAY')@
BEGIN
DECLARE ARRAY1 SIMPLEARRAY;
DECLARE ARRAY2 SIMPLEARRAY;
SET ARRAY1[1] = 1;
SET ARRAY2[1] = 1;
CALL DBMS_OUTPUT.PUT_LINE('Arrays are equal: ' || EQ(ARRAY1, ARRAY2));
SET ARRAY2[1] = 2;
CALL DBMS_OUTPUT.PUT_LINE('Arrays are equal: ' || EQ(ARRAY1, ARRAY2));
END@<!--end_raw-->
The program above has a result like the following:
<!--start_raw--> Return Status = 0 DB20000I The SQL command completed successfully. Arrays are equal: 1 Arrays are equal: 0<!--end_raw-->
Now we have written a CREATE_EQ_FUNCTION procedure for three distinct kinds of data types: simple types (built-in and user-defined distinct types), ROW types, and ARRAY types. In Metaprogramming in SQL (Part 4), we will look at how to use the METATYPE field of the SYSCAT.DATATYPES catalog view to write a single CREATE_EQ_FUNCTION that works on all three types we have encountered so far.