示例：CLP 脚本中的行数据类型用法

以下 Db2 CLP 脚本演示行数据类型的用法及其相关操作。它包括下列各项的演示：

• 创建行数据类型
• 创建表
• 创建包括下列各项的过程：
  • 行数据类型声明
  • 将值插入到包括某些行字段值的类型中
  • 根据行字段值更新行值
  • 将一些值选择到行字段值中
  • 对行指定行值
  • 向参数指定行字段值
• 调用过程
• 删除行数据类型和表

-- 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@

可通过 Db2 命令行发出以下命令来保存和运行此脚本：

DB2 -td@ -vf <filename>;

以下是运行脚本的输出：

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.