一時表のストアド・プロシージャでの使用

V5R2 からサポートされるようになったデータベースの新機能の中に GLOBAL TEMPORARY TABLE というものがあります。
iSeries Network (October 2005) にこの機能についての記事があったので、実際にどんなものかやってみました。

SQL ストアド・プロシージャについては今までデバッグの仕方についての記事を書いたくらいでしたが、V5R2 からの新機能には SQL でのストアド・プロシージャやトリガー、UDF/UDT などにフォーカスしたものが実はけっこうあります。
たとえば「SQL プロシージャー、SQL 関数、および SQL トリガーの中の ITERATE ステートメントとネストされた複合ステートメント」とかそういったものですね。

この GLOBAL TEMPORARY TABLE というものはどういう時に使うか、というと、アプリケーションの中で QTEMP ライブラリーにワークファイルを CRTPF を行うようなもので (実際に処理の内容はほぼそのようなものなのですが)、特別な使い方というわけではありません。他の SQL プラットフォームとの互換性、という面もあるでしょうね。

また、ストアド・プロシージャから他のストアド・プロシージャの結果セット (Result set) を受け取るような場合、SQL でできている場合や外部プログラムを使用した場合でやり方が異なってくることがあります。
こういった結果セットのやりとりの標準化にも使用できますね。


コーディング例

こちらで作成したサンプル用のデータベースを使用します。

まず最初にいわゆる普通の SQL ストアド・プロシージャで作成しています。
結果セットを一つ返すようになっています。

途中、コメントで変更してあるのは、'ALL' と引数にセットされても以下の SQL では対応できないので、チェックをはずしているというわけです。

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
        LANGUAGE SQL RESULT SETS 1
BEGIN
        DECLARE free_emp CURSOR WITH RETURN TO CALLER FOR
                    SELECT distinct firstnme AS fname, lastname AS lname,
                  workdept AS deptnum FROM employee e
                      WHERE workdept = dept AND
                                 (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                     WHERE e.empno = p.empno AND
                                     EMENDATE IS NOT NULL) AND
                                    empno NOT IN (SELECT empno FROM empprojact
                                                           WHERE EMENDATE IS NULL));

         /*     IF dept NOT IN ('D11', 'D21', 'ALL') THEN     */
                IF dept NOT IN ('D11', 'D21') THEN
                      SIGNAL SQLSTATE VALUE '75001'
                              SET MESSAGE_TEXT='Invalid department input'; 
        END IF;

        OPEN free_emp;
END; 

iSeries ナビゲーターの「SQL スクリプトの実行」で実行すると、こんな風に結果がでてきます。

上のストアド・プロシージャをそのまま単純に GLOBAL TEMPORARY TABLE を使用して書き直したのが↓になります。
検索条件毎に GLOBAL TEMPORARY TABLE を作成しています。

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
   LANGUAGE SQL
   BEGIN
              IF dept NOT IN ('D11', 'D21', 'ALL') THEN
                        SIGNAL SQLSTATE VALUE '75001'
                        SET MESSAGE_TEXT='Invalid department input';
              END IF;

      CASE
     WHEN dept='D11' THEN
     DECLARE Global Temporary Table free_emp AS
                  (SELECT distinct firstnme AS fname, lastname AS lname,
                   workdept AS deptnum FROM employee e
                        WHERE workdept = 'D11' AND
                                   (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

     WHEN dept='D21' THEN
     DECLARE Global Temporary Table free_emp AS
                       (SELECT distinct firstnme AS fname, lastname AS lname,
                        workdept AS deptnum FROM employee e
                        WHERE workdept = 'D21' AND
                                   (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

     ELSE
   DECLARE Global Temporary Table free_emp AS
                       (SELECT distinct firstnme AS fname, lastname AS lname,
                        workdept AS deptnum FROM employee e
                        WHERE     (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                       WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

    END CASE;

    END;

これは「SQL スクリプトの実行」で実行しても結果は返ってきません。

SESSION というスキーマを指定して (これが要は "QTEMP" のかわり、というわけですね。SESSION というスキーマは存在していません) 呼び出したストアド・プロシージャの中で作成した GLOBAL TEMPORARY TABLE をアクセスします。

さすがに SQL にパラメータを埋め込んで条件毎にハードコードする、というのはどうかな、というので、より一般的な、WHERE の指定のところだけを切り出し、あらかじめ定義しておいた SQL とその文字列を連結して GLOBAL TEMPORARY TABLE を作成するように変更したものが↓になります。

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
   LANGUAGE SQL
   BEGIN

      DECLARE s1 VARCHAR(1000);
      DECLARE deptsearch VARCHAR(24);

              IF dept NOT IN ('D11', 'D21', 'ALL') THEN
                        SIGNAL SQLSTATE VALUE '75001'
                        SET MESSAGE_TEXT='Invalid department input';
              END IF;

      CASE
      WHEN dept='D11' THEN
          SET deptsearch = ' WORKDEPT=''D11'' AND '; 
      WHEN dept='D21' THEN
          SET deptsearch = ' WORKDEPT=''D21'' AND ';
      ELSE 
          SET deptsearch = ' ';
      END CASE;

      SET s1 = 
         'DECLARE Global Temporary Table free_emp AS
                  (SELECT distinct firstnme AS fname, lastname AS lname,
                   workdept AS deptnum FROM employee e
                        WHERE '
        || deptsearch ||
                                 '  (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS';

    PREPARE dclstmt FROM s1;

    EXECUTE dclstmt;

    END; 

こちらの実行結果はいいですよね。同じなので。

使用例

使用例として以下のようなストアド・プロシージャが記事に載っていました。

CREATE PROCEDURE staff_project (IN start_date date, IN requestor_name CHAR(30), OUT empct INT)
   LANGUAGE SQL

   SET OPTION DBGVIEW=*SOURCE

   sp: BEGIN

      DECLARE emp_name CHAR(30);
      DECLARE mgr_name CHAR(30);

              IF (start_date < current date) THEN
                        SIGNAL SQLSTATE VALUE '75002'
                        SET MESSAGE_TEXT='Invalid start date input';
              END IF;

      CALL Get_Free_Employees('ALL');
      SET empct=0;

      FOR loop_var AS
        free_list CURSOR FOR SELECT fname, lname, deptnum
                                                   FROM session.free_emp

       DO
         SET emp_name = fname || ' ' || lname;
         SET mgr_name = 
        (SELECT fname|| ' ' lname FROM employee
                   WHERE job ='MANAGER' AND workdept=deptnum);
     
       CALL send_enquiry_email(requestor_name, emp_name, mgr_name, start_date);

       SET empct = empct+1;

       END FOR;
    
    END sp; 

記事に載っていたトリガーでの使用例です。
CREATE TRIGGER する時には Get_Free_Employees というストアド・プロシージャとその実行結果としての GLOBAL TEMPORARY TABLE (ここでは session.free_emp) が必要になります。

CREATE TRIGGER Free_Employee_Check BEFORE INSERT ON project

   FOR EACH ROW



BEGIN

      DECLARE free_emp_count INT;



      CALL sample.Get_Free_Employees('ALL');



      SET free_emp_count= (SELECT COUNT(*) FROM session.free_emp);



      IF (free_emp_count=0) THEN

              SIGNAL SQLSTATE VALUE '38001'

              SET MESSAGE_TEXT='Error-No Employees Available';

      END IF;

END;

一通り実行してみるのに使ったスクリプトの全文です。

SET SCHEMA SAMPLE;

DROP PROCEDURE Get_Free_Employees;

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
        LANGUAGE SQL RESULT SETS 1
BEGIN
        DECLARE free_emp CURSOR WITH RETURN TO CALLER FOR
                    SELECT distinct firstnme AS fname, lastname AS lname,
                  workdept AS deptnum FROM employee e
                      WHERE workdept = dept AND
                                 (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                     WHERE e.empno = p.empno AND
                                     EMENDATE IS NOT NULL) AND
                                    empno NOT IN (SELECT empno FROM empprojact
                                                           WHERE EMENDATE IS NULL));

         /*     IF dept NOT IN ('D11', 'D21', 'ALL') THEN     */
                IF dept NOT IN ('D11', 'D21') THEN
                      SIGNAL SQLSTATE VALUE '75001'
                              SET MESSAGE_TEXT='Invalid department input'; 
        END IF;

        OPEN free_emp;
END;

CALL SAMPLE.Get_Free_Employees('ALL');
CALL SAMPLE.Get_Free_Employees('D11');

DROP PROCEDURE Get_Free_Employees;

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
   LANGUAGE SQL
   BEGIN
              IF dept NOT IN ('D11', 'D21', 'ALL') THEN
                        SIGNAL SQLSTATE VALUE '75001'
                        SET MESSAGE_TEXT='Invalid department input';
              END IF;

      CASE
     WHEN dept='D11' THEN
     DECLARE Global Temporary Table free_emp AS
                  (SELECT distinct firstnme AS fname, lastname AS lname,
                   workdept AS deptnum FROM employee e
                        WHERE workdept = 'D11' AND
                                   (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

     WHEN dept='D21' THEN
     DECLARE Global Temporary Table free_emp AS
                       (SELECT distinct firstnme AS fname, lastname AS lname,
                        workdept AS deptnum FROM employee e
                        WHERE workdept = 'D21' AND
                                   (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

     ELSE
   DECLARE Global Temporary Table free_emp AS
                       (SELECT distinct firstnme AS fname, lastname AS lname,
                        workdept AS deptnum FROM employee e
                        WHERE     (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                       WITH DATA WITH REPLACE ON COMMIT DELETE ROWS;

    END CASE;

    END;

CALL SAMPLE.Get_Free_Employees('ALL');

SELECT * FROM SESSION.FREE_EMP;

DROP PROCEDURE Get_Free_Employees;

COMMIT;

CREATE PROCEDURE Get_Free_Employees (IN dept CHAR(3))
   LANGUAGE SQL
   BEGIN

      DECLARE s1 VARCHAR(1000);
      DECLARE deptsearch VARCHAR(24);

              IF dept NOT IN ('D11', 'D21', 'ALL') THEN
                        SIGNAL SQLSTATE VALUE '75001'
                        SET MESSAGE_TEXT='Invalid department input';
              END IF;

      CASE
      WHEN dept='D11' THEN
          SET deptsearch = ' WORKDEPT=''D11'' AND '; 
      WHEN dept='D21' THEN
          SET deptsearch = ' WORKDEPT=''D21'' AND ';
      ELSE 
          SET deptsearch = ' ';
      END CASE;

      SET s1 = 
         'DECLARE Global Temporary Table free_emp AS
                  (SELECT distinct firstnme AS fname, lastname AS lname,
                   workdept AS deptnum FROM employee e
                        WHERE '
        || deptsearch ||
                                 '  (CURRENT DATE > ALL (SELECT EMENDATE AS emp_enddate FROM empprojact p
                                       WHERE e.empno = p.empno AND
                                       EMENDATE IS NOT NULL) AND
                                      empno NOT IN (SELECT empno FROM empprojact
                                                             WHERE EMENDATE IS NULL)))
                        WITH DATA WITH REPLACE ON COMMIT DELETE ROWS';

    PREPARE dclstmt FROM s1;

    EXECUTE dclstmt;

    END;

CALL SAMPLE.Get_Free_Employees('ALL');

SELECT * FROM SESSION.FREE_EMP;

DROP PROCEDURE staff_project;

CREATE PROCEDURE staff_project (IN start_date date, IN requestor_name CHAR(30), OUT empct INT)
   LANGUAGE SQL

   SET OPTION DBGVIEW=*SOURCE

   sp: BEGIN

      DECLARE emp_name CHAR(30);
      DECLARE mgr_name CHAR(30);

              IF (start_date < current date) THEN
                        SIGNAL SQLSTATE VALUE '75002'
                        SET MESSAGE_TEXT='Invalid start date input';
              END IF;

      CALL Get_Free_Employees('ALL');
      SET empct=0;

      FOR loop_var AS
        free_list CURSOR FOR SELECT fname, lname, deptnum
                                                   FROM session.free_emp

       DO
         SET emp_name = fname || ' ' || lname;
         SET mgr_name = 
        (SELECT fname|| ' ' lname FROM employee
                   WHERE job ='MANAGER' AND workdept=deptnum);
     
       CALL send_enquiry_email(requestor_name, emp_name, mgr_name, start_date);

       SET empct = empct+1;

       END FOR;
    
    END sp;

CALL sample.Get_Free_Employees('ALL');

DROP TRIGGER Free_Employee_Check;

CREATE TRIGGER Free_Employee_Check BEFORE INSERT ON project

   FOR EACH ROW



BEGIN

      DECLARE free_emp_count INT;



      CALL sample.Get_Free_Employees('ALL');



      SET free_emp_count= (SELECT COUNT(*) FROM session.free_emp);



      IF (free_emp_count=0) THEN

              SIGNAL SQLSTATE VALUE '38001'

              SET MESSAGE_TEXT='Error-No Employees Available';

      END IF;

END;

[Top Pageに戻る]

Ads by TOK2