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