ロジックの格納
 
このページをシェアする                  
ロジックの格納
この章では、将来使用するために SQL プロシージャを格納する方法とトリガーを作成する方法について説明します。ストアド ビューの詳細については、第 16 章 データの取得を参照してください。
以下の項目について説明します。
•ストアド プロシージャ
•SQL 変数ステートメント
•SQL 制御ステートメント
•SQL トリガー
ストアド プロシージャ
ストアド プロシージャを使用して、論理的に関連付けされたプログラミング ステップを一般的なプロセスにグループ化し、次にそのプロセスを 1 つのステートメントで呼び出すことができます。また、パラメーターを渡すことによって、異なる値でこのプロセスを実行できます。
SQL ストアド プロシージャを呼び出すと、ホスト言語のプログラムと SQL エンジンとの間で内部的な通信を行うことなく、プロシージャ全体が実行されます。ストアド プロシージャを単独で呼び出したり、ほかのプロシージャまたはトリガーの本体の一部として呼び出すことができます。トリガーの詳細については、SQL トリガーを参照してください。
ストアド プロシージャ内の SQL 変数ステートメントを使用して、ステートメントからステートメントへ値を内部に格納することができます。これらのステートメントの詳細については、SQL 変数ステートメントを参照してください。
ストアド プロシージャ内の SQL 制御ステートメントを使用して、プロシージャの実行フローを制御することができます。これらのステートメントの詳細については、SQL 制御ステートメントを参照してください。
ストアド プロシージャと位置付け更新
ストアド プロシージャと位置付け更新の例を次に示します。
DROP PROCEDURE curs1
CREATE PROCEDURE curs1 (in :Arg1 char(4)) AS
BEGIN
   DECLARE :alpha char(10) DEFAULT 'BA';
   DECLARE :beta INTEGER DEFAULT 100;
 
   DECLARE degdel CURSOR FOR
      SELECT degree, cost_per_credit FROM tuition
      WHERE Degree = :Arg1 AND cost_per_credit = 100
   FOR UPDATE;
   OPEN degdel;
   FETCH NEXT FROM degdel INTO :alpha,:beta
   DELETE WHERE CURRENT OF degdel;
   CLOSE degdel;
END
 
CALL curs1('BA')
ストアド プロシージャの宣言
ストアド プロシージャを定義するには、CREATE PROCEDURE ステートメントを使用します。
CREATE PROCEDURE EnrollStudent (in :Stud_id integer, in :Class_Id integer);
 
BEGIN
INSERT INTO Enrolls VALUES (:Stud_id, :Class_Id, 0.0);
END
ストアド プロシージャ名の最大サイズは 30 文字です。パラメーター リストの両側には小かっこを付ける必要があり、パラメーター名は有効な SQL 識別子とすることができます。
ストアドプロシージャは、辞書内で一意名を持っていなければなりません。
CREATE PROCEDURE ステートメントの構文の詳細については、『SQL Engine Reference』の CREATE PROCEDURE を参照してください。
ストアド プロシージャの呼び出し
ストアド プロシージャを定義するには、CREATE ステートメントを使用します。
CALL EnrollStudent (274410958, 50);
すべてのパラメーターに値を定義する必要があります。CALL ステートメントで関連する引数を使用するか、または CREATE PROCEDURE ステートメントの関連するデフォルトの句を使用して、パラメーターに値を指定することができます。CALL ステートメント内のパラメーターの引数値は、関連するデフォルト値に優先します。
以下の 2 つの方法のいずれかで、CALL ステートメントに呼び出し値を指定できます。
•場所引数－プロシージャが作成されたときのリスト内のパラメーターの序数の位置に基づいて、パラメーター値を暗黙に指定することができます。
•キーワード引数－値が割り当てられているパラメーターの名前を使用して、パラメーター値を明示的に指定できます。
場所またはキーワードの引数リストでは、パラメーター値を 2 回指定できません。同一呼び出しで場所引数とキーワード引数の両方を使用する場合、キーワード引数は場所引数を介して値を受け取るパラメーターを参照することはできません。キーワード引数を使用する場合、同じパラメーター名を 2 度使用することはできません。
構文の情報については、『SQL Engine Reference』の CALL を参照してください。
ストアド プロシージャの削除
ストアド プロシージャを削除するには、DROP PROCEDURE ステートメントを使用します。
DROP PROCEDURE EnrollStudent;
構文の情報については、『SQL Engine Reference』の DROP PROCEDURE を参照してください。
SQL 変数ステートメント
SQL 変数は SET を使用して、ステートメント間でアクセス可能な値を割り当てます。SET ステートメントはストアド プロシージャ内で使用できます。これらのステートメントは代入ステートメントと呼ばれます。
プロシージャ所有の変数
ストアド プロシージャ内で定義する SQL 変数は、プロシージャ所有の変数です。適用範囲は変数が宣言されているプロシージャなので、そのプロシージャ内でのみ変数を参照できます。プロシージャが別のプロシージャを呼び出す場合、呼び出し側プロシージャのプロシージャ所有の変数は被呼び出し側プロシージャで直接使用できません。代わりに、その変数をパラメーターで渡す必要があります。同じストアド プロシージャでプロシージャ所有の変数を 2 回以上宣言することはできません。
ストアド プロシージャの本体が複合ステートメントの場合、そのプロシージャで宣言された SQL 変数名は、そのプロシージャのパラメーター リスト内のパラメーター名と同じにすることはできません。詳細については、複合ステートメントを参照してください。
代入ステートメント
代入ステートメントは、SQL 変数の値を初期化または変更します。値は、定数、演算子、この SQL 変数、および他の SQL 変数に関連する計算式とすることができます。
SET :CourseName = 'HIS305';
値の式は、SELECT ステートメントとすることもできます。
SET :MaxEnrollment = (SELECT Max_Size FROM Class
WHERE ID = classId);
構文の情報については、『SQL Engine Reference』の SET を参照してください。
SQL 制御ステートメント
ストアド プロシージャの本体内でのみ制御ステートメントを使用できます。これらのステートメントは、プロシージャの実行を制御します。制御ステートメントは以下のとおりです。
•複合ステートメント（BEGIN...END）
•IF ステートメント（IF...THEN...ELSE）
•LEAVE ステートメント
•Loop ステートメント（LOOP および WHILE）
複合ステートメント
複合ステートメントは、ほかのステートメントをグループ化します。
BEGIN
DECLARE :NumEnrolled INTEGER;
DECLARE :MaxEnrollment INTEGER;

DECLARE :failEnrollment CONDITION
   FOR SQLSTATE '09000';

SET :NumEnrolled = (SELECT COUNT (*)
   FROM Enrolls
   WHERE Class_ID = classId);

SET :MaxEnrollment = (SELECT Max_Size
   FROM Class
   WHERE ID = classId);

IF (:NumEnrolled >= :MaxEnrollment) THEN
   SIGNAL :failEnrollment ELSE
   SET :NumEnrolled = :NumEnrolled + 1;
END IF;
END
ストアド プロシージャまたはトリガーの本体内で複合ステートメントを使用できます。詳細については、SQL トリガーを参照してください。
ほかの複合ステートメント内に複合ステートメントをネストできますが、最も外側の複合ステートメントだけに DECLARE ステートメントを取り込むことができます。
複合ステートメントの構文の詳細については、『SQL Engine Reference.』の BEGIN [ATOMIC] を参照してください。
IF ステートメント
IF ステートメントは、条件の真の値に基づいて条件付き実行を行います。
IF (:counter = :NumRooms) THEN
LEAVE Fetch_Loop;
END IF;
構文の情報については、『SQL Engine Reference』の IF を参照してください。
LEAVE ステートメント
LEAVE ステートメントは、複合ステートメントまたは Loop ステートメントから離れることによって実行を続けます。
LEAVE Fetch_Loop
LEAVE ステートメントは、ラベル付き複合ステートメント内またはラベル付き Loop ステートメント内に現れるはずです。LEAVE ステートメントからのステートメント ラベルは、LEAVE を含むラベル付きステートメントのラベルと同じでなければなりません。このラベルは、対応ラベルと呼ばれています。
メモ： 複合ステートメントには、Loop ステートメントを取り込むことができます。Loop ステートメントを埋め込むことができるので、LEAVE ステートメントのステートメント ラベルは埋め込みループのラベルまたはストアド プロシージャの本体のラベルに一致します。
構文の情報については、『SQL Engine Reference』の LEAVE を参照してください。
LOOP ステートメント
LOOP ステートメントは、ステートメント ブロックの実行を繰り返します。
FETCH_LOOP:
LOOP
FETCH NEXT cRooms INTO CurrentCapacity;

IF (:counter = :NumRooms) THEN
   LEAVE FETCH_LOOP;
END IF;

SET :counter = :counter + 1;
SET :TotalCapacity = :TotalCapacity + :CurrentCapacity;
END LOOP;
SQL ステートメント リスト内の各ステートメントがエラーなく実行され、また、PSQL に LEAVE ステートメントが発生しないか、ハンドラーを呼び出す場合は、LOOP ステートメントの実行が繰り返されます。LOOP ステートメントは、与えられた条件が真である間に実行が継続されるという点で、WHILE ステートメントに似ています。
LOOP ステートメントに開始ラベルがある場合、このステートメントはラベル付き LOOP ステートメントと呼ばれます。終了ラベルを指定する場合、そのラベルは開始ラベルと同じでなければなりません。
構文の情報については、『SQL Engine Reference』の LOOP を参照してください。
WHILE ステートメント
WHILE ステートメントは、指定された条件が真である間にステートメント ブロックの実行を繰り返します。
FETCH_LOOP:
WHILE (:counter < :NumRooms) DO
   FETCH NEXT cRooms INTO :CurrentCapacity;
   IF (SQLSTATE = '02000') THEN
     LEAVE FETCH_LOOP;
   END IF;
   SET :counter = :counter + 1;
   SET :TotalCapacity = :TotalCapacity + :CurrentCapacity;
END WHILE;
PSQL は、ブール値の式を評価します。その値が真であれば、PSQL は SQL ステートメント リストを実行します。SQL ステートメント リスト内の各ステートメントがエラーなく実行され、また、LEAVE ステートメントが発生しない場合は、Loop ステートメントの実行が繰り返されます。ブール値の式が偽または不明である場合、PSQL は Loop ステートメントの実行を終了します。
WHILE ステートメントに開始ラベルがある場合、このステートメントはラベル付き WHILE ステートメントと呼ばれます。終了ラベルを指定する場合、そのラベルは開始ラベルと同じでなければなりません。構文の情報については、『SQL Engine Reference』の WHILE を参照してください。
SQL トリガー
トリガーは、データベースに対して一貫性のある規則を強制するために、テーブルに定義されたアクションのことです。トリガーは、ユーザーがそのテーブルで SQL データ変更ステートメントを実行するときに、DBMS に対して実行するアクションが適切かどうか確認する辞書オブジェクトです。
トリガーを宣言するには、CREATE TRIGGER ステートメントを使用します。
CREATE TRIGGER CheckCourseLimit;
トリガー名の最大サイズは 30 文字です。
トリガーを削除するには、DROP TRIGGER ステートメントを使用します。
DROP TRIGGER CheckCourseLimit;
トリガーを直接呼び出すことはできません。トリガーは、関連するトリガーを持つテーブル上の INSERT、UPDATE または DELETE アクションの結果として呼び出されます。構文の情報については、『SQL Engine Reference』の次のトピックを参照してください。
•CREATE TRIGGER
•DROP TRIGGER
•INSERT
•UPDATE
•DELETE
メモ： トリガーが回避されるのを防止するため、PSQL はトリガーを含むデータ ファイルをバウンド データ ファイルとして区別します。これにより、Btrieve ユーザーのアクセスが制限され、PSQL データベースでトリガーを発生させるアクションが実行されないようにします。詳細については、『SQL Engine Reference』を参照してください。
トリガーのタイミングと順序
トリガーは所定のイベントに対して自動的に実行するので、いつどのような順序でトリガーを実行するかを指定できることが大切です。トリガーを作成するときはその時機と順序を指定します。
トリガー アクションの時機の指定
トリガーに関連するイベントが発生すると、そのトリガーはイベントの前または後にトリガーを実行しなければなりません。たとえば、INSERT ステートメントがトリガーを呼び出した場合、トリガーは INSERT ステートメントの実行前または後に実行しなければなりません。
CREATE TABLE Tuitionidtable (primary key(id), id ubigint)#
CREATE TRIGGER InsTrig
   BEFORE INSERT ON Tuition
   REFERENCING NEW AS Indata
   FOR EACH ROW
   INSERT INTO Tuitionidtable VALUES(Indata.ID);
トリガー アクションの時機として BEFORE または AFTER を指定してください。トリガー アクションは、行ごとに 1 回実行します。BEFORE を指定すると、トリガーは行オペレーションの前に実行します。AFTER を指定すると、トリガーは行オペレーションの後に実行します。
メモ： PSQL は、RI の制約を設定することによってはトリガーを呼び出しません。また、RI の制約によってシステムがテーブル上でカスケードされた削除を行う可能性もある場合、テーブルでは DELETE トリガーが定義されないことがあります。
トリガー順序の指定
イベントが同じ指定時機に複数のトリガーを呼び出す場合があります。たとえば、INSERT ステートメントは、その実行後に実行するよう定義された複数のトリガーを呼び出すことができます。これらのトリガーは同時に実行できないので、トリガーの実行順序を指定する必要があります。
以下の CREATE TRIGGER ステートメントが 1 番を指定しているので、テーブルに対して定義された以降の BEFORE INSERT トリガーはすべて 1 より大きい一意の番号を得なければなりません。
CREATE TRIGGER CheckCourseLimit
BEFORE INSERT
ON Enrolls
ORDER 1
符号なし整数で順序の値を指定しますが、この整数はそのテーブル、時間およびイベントに対して一意でなければなりません。現在の順序に新しいトリガーを追加する可能性がある場合は、これに適応できるように、番号に空きを残しておくようにします。
トリガーの順序を指定しないと、トリガーはそのテーブル、時間およびイベントに対して現在定義されているトリガーの順序の値よりも大きい一意の順序の値で作成されます。
トリガー アクションの定義
トリガーアクションは、行ごとに 1 回実行します。トリガー アクションの構文は以下のとおりです。
CREATE TRIGGER InsTrig
   BEFORE INSERT ON Tuition
   REFERENCING NEW AS Indata
   FOR EACH ROW
   INSERT INTO Tuitionidtable VALUES(Indata.ID);
トリガー アクションに WHEN 句が含まれている場合、ブール式が真であれば、トリガーされた SQL ステートメントが実行します。式が真でなければ、トリガーされた SQL ステートメントは実行しません。WHEN 句が存在しないと、トリガーされた SQL ステートメントは無条件で実行します。
トリガーされた SQL ステートメントは、ストアド プロシージャ呼び出し（CALL procedure_name）などの単一の SQL ステートメントか、複合ステートメント（BEGIN...END）とすることができます。
メモ： トリガーのアクションはトリガーのタイトル テーブルを変更しないようにする必要があります。
トリガー アクションで、古い行イメージの列（DELETE または UPDATE の場合）または新規の行イメージの列（INSERT または UPDATE の場合）を参照しなければならない場合は、以下のようにトリガー宣言に REFERENCING 句を挿入する必要があります。
REFERENCING NEW AS N
REFERENCING 句を使用して、トリガーによって変更されるデータの情報を保持できます。