ストアド プロシージャとトリガー
このチュートリアルでは、右側の「このページの内容」で挙げているトピックの手順を通して、ODBC を使って Actian Zen にアクセスする際にサポートされる 2 つの高度な SQL 機能を学習します。
- ストアド プロシージャ
- SQL トリガー
ストアド プロシージャは、保存された SQL ステートメントの一種です。
SQL トリガーは、データの変更イベントがテーブルで発生すると自動的に実行される、特殊なストアド プロシージャです。
このチュートリアルの例では、ZENDB データベースに作成した Students テーブルを使用することに留意してください。テーブルのデータは、前のチュートリアル トピックを通じて挿入されています。
セットアップ
一連のチュートリアルを進めていれば、現在のテーブルは次のようになっています。
(1, 'John', 100, 'English') (2, 'Mary', 200, 'English') (3, 'Bob', 300, 'English') (4, 'Amy', 400, 'English') (5, 'Sam', 500, 'English') (6, 'Jill', 600, 'English')
このデータ セットをこのチュートリアルのレッスンに使用します。
新しい学生を挿入するストアド プロシージャを作成する
ストアド プロシージャは、いつでも実行できる保存された SQL ステートメントです。ストアド プロシージャは、ステートメント全体ではなく名前で呼び出すだけという点では、ビューに似ています。しかし、ビューとは異なり、ストアド プロシージャには、データベース内のテーブルやレコードを操作する SQL ステートメントのほとんどを含めることができます。
CREATE PROCEDURE ステートメントを使用して、学生と口座残高を追加する簡単なストアド プロシージャを作成します。
procedure_query="CREATE PROCEDURE Add_Student(); \
BEGIN \
INSERT INTO Students(Name, Account_Balance) \
VALUES ('John Doe', 0); \
END"
c.execute(procedure_query)
CREATE PROCEDURE は、学生を追加するためのプロシージャの名前を定義します。BEGIN 句と END 句は、プロシージャ自体の開始と終了を定義します。上の例は、標準の INSERT ステートメントとして認識されます。
これにより、Actian Zen データベースに新しいプロシージャ Add_Student が作成されます。このプロシージャをいつでも実行して、新しい学生を追加できます。これは Zen Control Center の［ストアド プロシージャ］の下に表示されます。
プロシージャを実行するには、次のように指定します。
c.execute("CALL Add_Student()")
もう一度 Students テーブルを見ると、新しいレコードが挿入されたことがわかります。
パラメーターを使用するストアド プロシージャを作成する
前の例には、Add_Student() を呼び出すたびに、同じ John Doe のレコードが挿入されるという問題があります。
誰のレコードを作成し、口座残高にいくら登録するかをプロシージャに指示できれば、より有用になるでしょう。それを行うために、パラメーターを使用するストアド プロシージャを作成することができます。ストアド プロシージャのパラメーターによって、実行時にプロシージャが使用する特定のデータを提供することが可能になります。
CREATE PROCEDURE ステートメントを使用して、Name 列および Account_Balance 列の変数またはパラメーターを使用する、新しいストアド プロシージャを作成します。
procedure_query="CREATE PROCEDURE Add_Student2 \
(in :Name CHAR(32), in :Amount INTEGER); \
BEGIN \
INSERT INTO Students(Name, Account_Balance) \
VALUES (:Name, :Amount); \
END"
c.execute(procedure_query)
この場合、"(in :Name CHAR(32), in :Amount INTEGER)" は、プロシージャで必要とされる 2 つのパラメーターを指定していることに注目してください。
名前に Mary Jane、口座残高に 500 を使用して実行するには、次のコマンドを実行します。
c.execute("CALL Add_Student2('Mary Jane', 500)")
Students テーブルを見ると、次のように表示されます。
詳細：
口座残高を増やす SQL トリガーを作成する
SQL トリガーは、指定したテーブルで SQL ステートメントが実行される前または後に実行するストアド プロシージャです。
CREATE TRIGGER ステートメントを使用して SQL トリガーを開始しますが、ステートメントの残り部分では、いくつかの新しい句を使用していることに注目してください。
以下に、トリガーを作成する簡単な例を示します。この例では、すべての新しい生徒レコードの口座残高に 100 を追加します。
trigger_query="CREATE TRIGGER Add_Balance \
AFTER INSERT ON Students \
REFERENCING NEW AS student \
FOR EACH ROW \
UPDATE Students SET \
Account_Balance = student.Account_Balance + 100 \
WHERE Id = student.Id;"
c.execute(trigger_query)
このクエリでは、新しい行が Students テーブルに挿入された後に SQL クエリを実行する、Add_Balance という新しいトリガーを作成しました。新しいレコードの値は student 変数として表され、作成された行（レコード）ごとにトリガーが UPDATE を実行して、現在の Account_Balance 値を 100 増やします。
Zen Control Center の［トリガー］の下に新しいトリガーが表示されます。
トリガーの動作を確認するために、新しいレコードを挿入します。
c.execute("INSERT INTO Students(Name, Account_Balance) VALUES ('Bill', 800)")
これが結果です。
Account_Balance として 800 を指定したにもかかわらず、結果は 900 になっています。これは、トリガーが INSERT 操作後に自動的に 100 を加算しているからです。
詳細：