Example: Row data type use in a CLP script

The following Db2 CLP script demonstrates the use of the row data type and its related operations. It includes demonstrations of:

• Creating row data types
• Creating a table
• Creating a procedure that includes:
  • Row data type declarations
  • Inserting values to a type that include some row field values
  • Updating row values based on a row field value
  • Selecting values into a row field value
  • Assigning a row value to a row
  • Assigning row field values to a parameter
• Calling the procedure
• Dropping the row data types and table

-- Creating row types

CREATE TYPE row01 AS ROW (c1 INTEGER)@ 

CREATE TYPE empRow AS ROW (id INTEGER, name VARCHAR(10))@  

CREATE TABLE employee (id INTEGER, name VARCHAR(10))@  

CREATE procedure proc01 (OUT p0 INTEGER, OUT p1 INTEGER) 
BEGIN   
  DECLARE v1, v2 row01;   
  DECLARE emp empRow;    

  -- Assigning values to row fields   
  SET v1.c1 = 5;   
  SET (emp.id, emp.name) = (v1.c1 + 1, 'James');    

  -- Using row fields in DML   
  INSERT INTO employee 
  VALUES (v1.c1, 'Beth'), (emp.id, emp.name);   

  UPDATE employee 
  SET name = 'Susan' where id = v1.c1;    

  -- SELECT INTO a row field   
  SELECT id INTO v2.c1 
  FROM employee 
  WHERE name = emp.name;    

  -- Row level assignment   
  SET v1 = v2;    

  -- Assignment to parameters   
  SET (p0, p1) = (v1.c1, emp.id); 

END@  

CALL proc01(?, ?)@ 

SELECT * FROM employee@  

DROP procedure proc01@ 

DROP TABLE employee@  

-- Dropping row types 
DROP TYPE empRow@ 

DROP TYPE row01@

This script can be saved and run from a Db2 Command Line by issuing the following:

DB2 -td@ -vf <filename>;

The following is the output of running the script:

CREATE TYPE row01 AS ROW (c1 INTEGER)
DB20000I  The SQL command completed successfully.

CREATE TYPE empRow AS ROW (id INTEGER, name VARCHAR(10))
DB20000I  The SQL command completed successfully.

CREATE TABLE employee (id INTEGER, name VARCHAR(10))
DB20000I  The SQL command completed successfully.

CREATE procedure proc01 (OUT p0 INTEGER, OUT p1 INTEGER) 
   BEGIN DECLARE v1, v2 row01;
   DECLARE emp empRow;
   SET v1.c1 = 5;
   SET (emp.id, emp.name) = (v1.c1 + 1, 'James');
   INSERT INTO employee  VALUES (v1.c1, 'Beth'), (emp.id, emp.name); 
   UPDATE employee  SET name = 'Susan' where id = v1.c1;
   SELECT id INTO v2.c1 FROM employee WHERE name = emp.name;
   SET v1 = v2;
   SET (p0, p1) = (v1.c1, emp.id);
END

DB20000I  The SQL command completed successfully.

CALL proc01(?, ?)

  Value of output parameters
  --------------------------
  Parameter Name  : P0
  Parameter Value : 6

  Parameter Name  : P1
  Parameter Value : 6

  Return Status = 0

SELECT * FROM employee

ID          NAME      
----------- ----------
          5 Susan     
          6 James     

  2 record(s) selected.


DROP procedure proc01
DB20000I  The SQL command completed successfully.

DROP TABLE employee
DB20000I  The SQL command completed successfully.

DROP TYPE empRow
DB20000I  The SQL command completed successfully.

DROP TYPE row01
DB20000I  The SQL command completed successfully.