In the last post on this subject, we used dynamic SQL to generalize an integer comparison function into a stored procedure that builds comparison functions of any basic type. This served as a simple example, but did not really offer much benefit over using the ‘=’ operator. In today’s post, I will extend the same technique to make functions that compare ROW objects just as easily, even though the ‘=’ operator cannot be used on ROW objects.
First of all, what does it mean to say that two rows are identical? Let’s define row A to be equal to row B when:
- The types of row A and row B are the same
- Each field in row A has the same value as the same field in row B
The first requirement is satisfied by the strong typing provided by SQL PL. The second will require us to look inside each row for its individual fields to make a proper comparison. In an earlier post, I showed you an example of a comparison procedure for two objects of a ROW type called TEST_T. It looked like this:
<!--start_raw-->CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@
CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
BEGIN
IF (LEFT.ONE = RIGHT.ONE) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
END@<!--end_raw-->
With metaprogramming, we can generalize this into a procedure that creates row comparison functions for any ROW type. But first we need a way of discovering what fields make up a row. Fortunately, there is a catalog view called SYSCAT.ROWFIELDS that tells the name and type of every field in a row. Say we query this catalog view for the TEST_T type we created above:
<!--start_raw-->SELECT VARCHAR(FIELDNAME,10) FIELDNAME,
VARCHAR(FIELDTYPESCHEMA,10) FIELDTYPESCHEMA,
VARCHAR(FIELDTYPEMODULENAME,10) FIELDTYPEMODULENAME,
VARCHAR(FIELDTYPENAME,10) FIELDTYPENAME
FROM SYSCAT.ROWFIELDS
WHERE TYPESCHEMA = (VALUES CURRENT SCHEMA)
AND TYPEMODULENAME IS NULL
AND TYPENAME = 'TEST_T'<!--end_raw-->
We get a result like this:
<!--start_raw-->FIELDNAME FIELDTYPESCHEMA FIELDTYPEMODULENAME FIELDTYPENAME ---------- --------------- ------------------- ------------- ONE SYSIBM - INTEGER 1 record(s) selected.<!--end_raw-->
We’ll put some of this information (specifically, the fieldname) to use in our metaprogram, but let’s follow the process we followed last time and start by turning the above comparison procedure into a metaprogram with hardcoded inputs:
<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_TYPE_T_EQ_FUNCTION() MODIFIES SQL DATA BEGIN DECLARE SQL VARCHAR(1024); SET SQL = 'CREATE OR REPLACE FUNCTION EQ(LEFT TEST_T, RIGHT TEST_T) '; SET SQL = SQL || 'CONTAINS SQL '; SET SQL = SQL || 'RETURNS INTEGER '; SET SQL = SQL || 'BEGIN '; SET SQL = SQL || 'DECLARE RESULT INTEGER; '; SET SQL = SQL || 'IF (LEFT.ONE = RIGHT.ONE) THEN '; SET SQL = SQL || ' SET RESULT = 1; '; SET SQL = SQL || 'ELSE '; SET SQL = SQL || ' SET RESULT = 0; '; SET SQL = SQL || 'END IF; '; SET SQL = SQL || 'RETURN RESULT; '; SET SQL = SQL || 'END'; EXECUTE IMMEDIATE SQL; END@<!--end_raw-->
Next, using the SYSCAT.ROWFIELDS catalog view, we generalize the above function to take the ROW type as an argument. Since the type name needs to be qualified by the schema and the module name, we have an extra challenge to deal with. The module name is NULL when the type is not defined inside a module and NULLs cannot be compared using the ‘=’ operator. One solution is to write two queries of the SYSCAT.ROWFIELDS view, one that assumes the module name is non-NULL and a second that assumes the module name is NULL. We choose which query to use when we check whether the module name argument is NULL.
<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(TYPE_SCHEMA VARCHAR(128),
TYPE_MODULE_NAME VARCHAR(128),
TYPE_NAME VARCHAR(128))
MODIFIES SQL DATA
BEGIN
DECLARE SQL VARCHAR(1024);
DECLARE FULLYQUALIFIEDTYPE VARCHAR(386);
DECLARE AT_END INT DEFAULT 0;
DECLARE FIELD_NAME VARCHAR(128);
DECLARE ROWFIELDSCURSOR CURSOR FOR SELECT FIELDNAME
FROM SYSCAT.ROWFIELDS
WHERE TYPESCHEMA = TYPE_SCHEMA
AND TYPEMODULENAME IS NULL
AND TYPENAME = TYPE_NAME;
DECLARE ROWFIELDSMODCURSOR CURSOR FOR SELECT FIELDNAME
FROM SYSCAT.ROWFIELDS
WHERE TYPESCHEMA = TYPE_SCHEMA
AND TYPEMODULENAME = TYPE_MODULE_NAME
AND TYPENAME = TYPE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET AT_END = 1;
-- Types need to be qualified by their schema, and if they are in a module, by the module too.
IF (TYPE_MODULE_NAME IS NULL) THEN
SET FULLYQUALIFIEDTYPE = TYPE_SCHEMA || '.' || TYPE_NAME;
ELSE
SET FULLYQUALIFIEDTYPE = TYPE_SCHEMA || '.' || TYPE_MODULE_NAME || '.' || TYPE_NAME || ' ';
END IF;
SET SQL = 'CREATE OR REPLACE FUNCTION EQ(LEFT ' || FULLYQUALIFIEDTYPE;
SET SQL = SQL || ', RIGHT ' || FULLYQUALIFIEDTYPE || ') ';
SET SQL = SQL || 'READS SQL DATA ';
SET SQL = SQL || 'RETURNS INTEGER ';
SET SQL = SQL || 'BEGIN ';
SET SQL = SQL || 'DECLARE RESULT INTEGER; ';
SET SQL = SQL || 'IF (';
IF (TYPE_MODULE_NAME IS NULL) THEN
-- Iterate over the row fields to build the Boolean expression
OPEN ROWFIELDSCURSOR;
FETCH ROWFIELDSCURSOR INTO FIELD_NAME;
WHILE AT_END = 0 DO
SET SQL = SQL || 'LEFT.' || FIELD_NAME || ' = ' || 'RIGHT.' || FIELD_NAME;
FETCH ROWFIELDSCURSOR INTO FIELD_NAME;
IF (AT_END = 0) THEN
SET SQL = SQL || ' AND ';
END IF;
END WHILE;
CLOSE ROWFIELDSCURSOR;
ELSE
-- Iterate over the row fields to build the Boolean expression
OPEN ROWFIELDSMODCURSOR;
FETCH ROWFIELDSMODCURSOR INTO FIELD_NAME;
WHILE AT_END = 0 DO
SET SQL = SQL || 'LEFT.' || FIELD_NAME || ' = ' || 'RIGHT.' || FIELD_NAME;
FETCH ROWFIELDSMODCURSOR INTO FIELD_NAME;
IF (AT_END = 0) THEN
SET SQL = SQL || ' AND ';
END IF;
END WHILE;
CLOSE ROWFIELDSMODCURSOR;
END IF;
SET SQL = SQL || ') THEN ';
SET SQL = SQL || ' SET RESULT = 1; ';
SET SQL = SQL || ' ELSE ';
SET SQL = SQL || ' SET RESULT = 0; ';
SET SQL = SQL || 'END IF; ';
SET SQL = SQL || 'RETURN RESULT; ';
SET SQL = SQL || 'END';
CALL DBMS_OUTPUT.PUT_LINE(SQL);
EXECUTE IMMEDIATE SQL;
END@<!--end_raw-->
Notice that I put in a CALL DBMS_OUTPUT.PUT_LINE to print the SQL before the EXECUTE IMMEDIATE. This makes it easy to debug whether the metaprogram is writing the function properly by inspecting the code it produces. Once the above function is created, we can call it on our TEST_T type and use the resulting EQ function to compare two rows of type TEST_T, as in this example program:
<!--start_raw-->SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@
CREATE OR REPLACE MODULE TEST_MOD@
ALTER MODULE TEST_MOD PUBLISH
TYPE TEST_T AS ROW(TWO INTEGER)@
CALL CREATE_EQ_FUNCTION('KMCDONAL', NULL, 'TEST_T')@
CALL CREATE_EQ_FUNCTION('KMCDONAL', 'TEST_MOD', 'TEST_T')@
BEGIN
DECLARE T1 TEST_T;
DECLARE T2 TEST_T;
DECLARE T3 TEST_MOD.TEST_T;
DECLARE T4 TEST_MOD.TEST_T;
SET T1.ONE = 1;
SET T2.ONE = 1;
CALL DBMS_OUTPUT.PUT_LINE('EQ(t1, t2) = ' || EQ(t1, t2));
SET T2.ONE = 2;
CALL DBMS_OUTPUT.PUT_LINE('EQ(t1, t2) = ' || EQ(t1, t2));
SET T3.TWO = 1;
SET T4.TWO = 1;
CALL DBMS_OUTPUT.PUT_LINE('EQ(t3, t4) = ' || EQ(t3, t4));
SET T4.TWO = 2;
CALL DBMS_OUTPUT.PUT_LINE('EQ(t3, t4) = ' || EQ(t3, t4));
END@<!--end_raw-->
The above test produces the following output:
<!--start_raw-->DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. Return Status = 0 CREATE OR REPLACE FUNCTION EQ(LEFT KMCDONAL.TEST_T, RIGHT KMCDONAL.TEST_T) CONTAINS SQL RETURNS INTEGER BEGIN DECLARE RESULT INTEGER; IF (LEFT.ONE = RIGHT.ONE) THEN SET RESULT = 1; ELSE SET RESULT = 0; END IF; RETURN RESULT; END Return Status = 0 CREATE OR REPLACE FUNCTION EQ(LEFT KMCDONAL.TEST_MOD.TEST_T , RIGHT KMCDONAL.TEST_MOD.TEST_T ) CONTAINS SQL RETURNS INTEGER BEGIN DECLARE RESULT INTEGER; IF (LEFT.TWO = RIGHT.TWO) THEN SET RESULT = 1; ELSE SET RESULT = 0; END IF; RETURN RESULT; END DB20000I The SQL command completed successfully. EQ(t1, t2) = 1 EQ(t1, t2) = 0 EQ(t3, t4) = 1 EQ(t3, t4) = 0<!--end_raw-->
In Metaprogramming in SQL (Part 3), we will use the above EQ function to build a more powerful EQ function that can compare objects of the ARRAY type, which is one of the new types in DB2 9.7.