例: SQL 関数での行データ・タイプの使用

SQL 関数で行データ・タイプを使用して、レコード・データを構成、保管、または変更できます。

行データ・タイプに基づく変数を使用すると、表と同じフォーマットを持つ行の値を簡単に保持することができます。こうした仕方で使用すると、行変数を最初に使用する際に初期化するのに役立ちます。

以下は、Db2® CLP スクリプトの例です。このスクリプトには、表、行データ・タイプ、および関数を作成する SQL ステートメントが含まれています。作成される関数には、行変数、行参照、および UDF の呼び出しの宣言が組み込まれています。

CREATE TABLE t1 (deptNo VARCHAR(3),
                 reportNo VARCHAR(3),
                 deptName VARCHAR(29),
                 mgrNo VARCHAR (8),
                 location VARCHAR(128))@

 INSERT INTO t1 VALUES ('123', 'MM1', 'Sales-1', '0112345', 'Miami')@
 INSERT INTO t1 VALUES ('456', 'MM2', 'Sales-2', '0221345', 'Chicago')@
 INSERT INTO t1 VALUES ('789', 'MM3', 'Marketing-1', '0331299', 'Toronto')@

 CREATE TYPE deptRow AS ROW (r_deptNo VARCHAR(3),
                             r_reportNo VARCHAR(3),
                             r_depTName VARCHAR(29),
                             r_mgrNo VARCHAR (8),
                             r_location VARCHAR(128))@

 CREATE FUNCTION getLocation(theDeptNo VARCHAR(3),
                              reportNo VARCHAR(3),
                              theName VARCHAR(29))
  RETURNS VARCHAR(128)
  BEGIN

    -- Declare a row variable 
    DECLARE dept deptRow;

    -- Assign values to the fields of the row variable 
    SET dept.r_deptno = theDeptNo;
    SET dept.r_reportNo = reportNo;
    SET dept.r_deptname = theName;
    SET dept.r_mgrno = '';
    SET dept.r_location = '';

    RETURN
      (SELECT location FROM t1 WHERE deptNo = dept.r_deptno);

  END@					

VALUES (getLocation ('789', 'MM3','Marketing-1'))@

この CLP スクリプトを実行すると表が作成され、その表に行が挿入され、行データ・タイプと UDF が作成されます。

関数 getLocation は SQL UDF で、行変数を宣言し、入力パラメーター値を使用して値を行変数フィールドに割り当てます。この関数は、この関数が戻すスカラー値を定義する SELECT ステートメント内の行変数のフィールドの 1 つを参照します。

このスクリプトの最後で VALUES ステートメントが実行されると、UDF が呼び出され、スカラー戻り値が戻されます。

以下は、CLP からこのスクリプトを実行した際の出力です。

CREATE TABLE t1 (deptNo VARCHAR(3), reportNo VARCHAR(3), 
 deptName VARCHAR(29), mgrNo VARCHAR (8), location VARCHAR(128))
Db20000I  The SQL command completed successfully.

INSERT INTO t1 VALUES ('123', 'MM1', 'Sales-1', '0112345', 'Miami')
Db20000I  The SQL command completed successfully.

INSERT INTO t1 VALUES ('456', 'MM2', 'Sales-2', '0221345', 'Chicago')
Db20000I  The SQL command completed successfully.

INSERT INTO t1 VALUES ('789', 'MM3', 'Marketing-1', '0331299', 'Toronto')
Db20000I  The SQL command completed successfully.

CREATE TYPE deptRow AS ROW (r_deptNo VARCHAR(3), r_reportNo VARCHAR(3), r_depTNa
me VARCHAR(29), r_mgrNo VARCHAR (8), r_location VARCHAR(128))
Db20000I  The SQL command completed successfully.

CREATE FUNCTION getLocation(theDeptNo VARCHAR(3),
                            reportNo VARCHAR(3),
                            theName VARCHAR(29))
 RETURNS VARCHAR(128)
 BEGIN
   DECLARE dept deptRow;
   SET dept.r_deptno = theDeptNo;
   SET dept.r_reportNo = reportNo;
   SET dept.r_deptname = theName;
   SET dept.r_mgrno = '';
   SET dept.r_location = '';
 RETURN
   (SELECT location FROM t1 WHERE deptNo = dept.r_deptno);
 END
Db20000I  The SQL command completed successfully.

VALUES (getLocation ('789', 'MM3','Marketing-1'))

1

--------------------------------------------------------------------------------
------------------------------------------------
Toronto


  1 record(s) selected.