Oracleストアド・プロシージャとトリガー
きおくてつづき
Oracleでは、ストレージ・プロシージャ(procedure)と呼ばれるデータベースでサブルーチンを定義できます.データ辞書に保存され、異なるユーザーとアプリケーション間で共有でき、プログラムの最適化と再利用が可能です.ストレージ・プロシージャを使用する利点は、(1)プロシージャがサーバ側で実行され、実行速度が速いことです.(2)プロセスが1回実行されるとコードはキャッシュに常駐し,以降の動作ではキャッシュからコンパイル済みコード実行を呼び出すだけでシステム性能が向上する.(3)データベースの安全を確保する.アプリケーション内のテーブルに直接アクセスすることを許可するのではなく、ユーザーにこれらのテーブルにアクセスするプロセスを許可することができます.テーブル以外の認可ユーザーは、プロシージャを通過しない限り、これらのテーブルにアクセスできません.(4)事前に実行する必要があるタスクを自動的に完了する.プロセスは、システムの起動時に自動的に実行することができ、システムの起動後に手動で操作する必要がなく、ユーザーの使用を大幅に便利にし、事前に実行する必要があるタスクを自動的に完了することができます.1.SQLコマンドストレージ・プロシージャの作成
そのうち:procedure_name:プロシージャ名です.識別子ルールを満たす必要があります.キーワードREPLACEは、プロシージャの作成時に同じ名前のプロシージャが既に存在する場合に再作成されることを示します.CREATEキーを使用する場合は、作成する前に既存のプロシージャを削除する必要があります.schema.:は、プロシージャが属するユーザー・スキーマを指定します.parameter:プロシージャのパラメータです.パラメータ名は識別子ルールに合致する必要があります.プロシージャを作成するときに、1つ以上のパラメータを宣言し、プロシージャを実行するときに対応するパラメータを指定します.Parameter_modeはパラメータのタイプであり,プロセスパラメータや関数パラメータと同様に,IN,OUT,IN OUTの3種類がある.①IN:パラメータがプロセスに入力されたことを示す;②OUT:パラメータがプロセス中に付与され、プロセス体の外部に伝達できることを示す.③IN OUT:このタイプを示すパラメータは、プロセスボディに値を伝えることもできるし、プロセスボディに値を割り当てることもできる.sql_statement:プロシージャに含まれるPL/SQL文を表します.2.ストアド・プロシージャを呼び出してストアド・プロシージャの名前を直接入力すると、定義済みのストアド・プロシージャを実行できます.
ここで、procedure_nameは呼び出すストレージ・プロシージャの名前であり、parameterはパラメータ値である.【例1】XSCJデータベースのXSテーブルからある人の総単位を照会し、総単位に基づいてコメントを書く.
【例2】統計表XSにおける男女の同窓生の数は、記憶過程で1つの入力パラメータと1つの出力パラメータを用いた.
プロシージャcount_を呼び出すnumの場合は、OUTタイプパラメータを定義して、次のように呼び出す必要があります.
プロシージャが不要になった場合は、メモリから削除して、使用するメモリリソースを解放します.
ここで、schemaはプロセスを含むユーザである.procedure_nameは削除するストレージ・プロシージャの名前です.
フリップフロップ
トリガ(trigger)は、テーブルと密接な関係にあるプロセスであり、テーブル内のデータを保護するために使用され、1つのベーステーブルが変更された場合(INSERT、UPDATE、DELETE)、トリガは自動的に実行され、例えば、トリガによって複数のテーブル間のデータの一貫性と完全性を実現することができる.トリガはアプリケーションとは無関係です.例えば、XSCJデータベースについてはXSテーブル、XS_KCテーブルとKCテーブルは、ある学号の学生のある課程成績を挿入する場合、その学号はXSテーブルにすでに存在し、課程番号はKCテーブルにすでに存在しなければならない.この場合、INSERTトリガを定義することによって上述の機能を実現することができる.フリップフロップのタイプは,(1)DMLフリップフロップの3種類である.Oracleは、DML(データ操作文)文でトリガーできます.DML操作の前または操作の後にトリガーできます.また、各行またはその文操作でトリガーできます.(2)フリップフロップに代わる.Oracleでは、2つ以上のテーブルで作成されたビューを直接操作できないため、代替トリガが与えられます.これは、Oracleがビュー操作に特化した処理方法です.(3)システムトリガ.Oracle 8 iでは、システムトリガと呼ばれる3つ目のタイプのトリガが用意されています.Oracleデータベースのクローズやオープンなど、Oracleデータベース・システムの時間内にトリガーできます.
一般的に、テーブルデータの操作には挿入、修正、削除があるため、データを維持するトリガはINSERT、UPDATE、DELETEに分けられる.各ベーステーブルは、(1)BEFORE INSERT;(2) BEFORE INSERT FOR EACH ROW;(3) AFTER INSERT;(4) AFTER INSERT FOR EACH ROW;(5) BEFORE UPDATE;(6) BEFORE UPDATE FOR EACH ROW;(7) AFTER UPDATE;(8) AFTER UPDATE FOR EACH ROW;(9) BEFORE DELETE;(10) BEFORE DELETE FOR EACH ROW;(11) AFTER DELETE;(12) AFTER DELETE FOR EACH ROW.1.構文フォーマット
2.トリガを作成する制限(1)コードサイズ.フリップフロップコードのサイズは32 K未満でなければなりません.(2)フリップフロップ内の有効な文はDML文を含んでもよいが、DDL文を含んではならない.ROLLBACK、COMMIT、SAVEPOINTも使えません.ただし、システムトリガにはCREATE、ALTER、DROP TABLE、ALTER…COMPILE文を使用できます.(3)LONG、LONG RAW、LOBの制限:①LONGまたはLONG RAWにデータを挿入できないこと;②LONGまたはLONG RAWからのデータは、char、varchar 2などの文字型に変換できますが、32 Kを超えてはいけません.③LONGまたはLONG RAWでは変数を宣言できません.④LONGまたはLONG RAW列では使用できません:NEWと:PARENT;⑤LOBの:NEW変数は変更できません.(4)参照パケット変数の制限.UPDATE文またはDELETE文で現在のUPDATE競合が検出された場合、Oracleでは、ROLLBACKをSAVEPOINTに実行し、更新を再起動します.これにより、成功するには複数回かかる場合があります.3.トリガーのトリガー順序Oracleのイベントに対するトリガーは16種類あり、これらは一定の順序で実行されます:(1)BEFORE文を実行するトリガー;(2)文の影響を受ける各行について:BEFORE文の行レベルトリガàを実行DML文àを実行してAFTER行レベルトリガを実行する.(3)AFTER文レベルトリガを実行する.4.DMLフリップフロップの作成フリップフロップは、プロシージャ名およびパッケージの名前とは異なり、別々の名前空間を持つため、フリップフロップ名はテーブル名またはプロシージャ名と同じ名前にすることができますが、同じschema(スキーマ)内のフリップフロップ名は同じではありません.DMLフリップフロップはテーブルレベルフリップフロップとも呼ばれ、あるテーブルに対してDML操作を行うとフリップフロップの実行がトリガーされるため名付けられた.【例1】XSCJデータベースに新しいテーブルXS_を追加するとするHISは、テーブル構造がテーブルXSと同じであり、XSテーブルから削除されたレコードを格納するために使用される.トリガを作成し、XSテーブルが1行削除されると、削除されたレコードをログテーブルXS_に書き込む.HIS中.
【例2】トリガを用いてデータベースXSCJのXSテーブルの挿入、更新、削除の3つの操作を行った後、対応するヒントを与える.
5.システム・トリガーの作成Oracle 8 iで開始したシステム・トリガーは、DDLまたはデータベース・システムでトリガーできます.DDLとは、CREATE、ALTER、DROPなどのデータ定義文を指す.データベース・システム・イベントには、データベース・サーバの起動または停止、ユーザーのログインと終了などが含まれます.
そのうち:ddl_event_List:1つ以上のDDLイベントを表し、イベント間はORで区切られます.database_event_List:1つ以上のデータベースイベントを表し、イベント間はORで区切られます.DATABASE:データベース・レベルのトリガを表し、scacheはユーザー・レベルのトリガを表します.Schemaはユーザスキームを表す.Trigger_body:フリップフロップのPL/SQL文.6.SQLコマンドによるトリガの削除
ここで、schemaはトリガのユーザー・スキーマを指定します.Trigger_name削除するトリガの名前を指定します.
Oracleでは、ストレージ・プロシージャ(procedure)と呼ばれるデータベースでサブルーチンを定義できます.データ辞書に保存され、異なるユーザーとアプリケーション間で共有でき、プログラムの最適化と再利用が可能です.ストレージ・プロシージャを使用する利点は、(1)プロシージャがサーバ側で実行され、実行速度が速いことです.(2)プロセスが1回実行されるとコードはキャッシュに常駐し,以降の動作ではキャッシュからコンパイル済みコード実行を呼び出すだけでシステム性能が向上する.(3)データベースの安全を確保する.アプリケーション内のテーブルに直接アクセスすることを許可するのではなく、ユーザーにこれらのテーブルにアクセスするプロセスを許可することができます.テーブル以外の認可ユーザーは、プロシージャを通過しない限り、これらのテーブルにアクセスできません.(4)事前に実行する必要があるタスクを自動的に完了する.プロセスは、システムの起動時に自動的に実行することができ、システムの起動後に手動で操作する必要がなく、ユーザーの使用を大幅に便利にし、事前に実行する必要があるタスクを自動的に完了することができます.1.SQLコマンドストレージ・プロシージャの作成
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name /* */
[ (parameter parameter_mode date_type , …n)] /* */
IS | AS
BEGIN
sql_statement /*PL/SQL ， */
END procedure_nameそのうち:procedure_name:プロシージャ名です.識別子ルールを満たす必要があります.キーワードREPLACEは、プロシージャの作成時に同じ名前のプロシージャが既に存在する場合に再作成されることを示します.CREATEキーを使用する場合は、作成する前に既存のプロシージャを削除する必要があります.schema.:は、プロシージャが属するユーザー・スキーマを指定します.parameter:プロシージャのパラメータです.パラメータ名は識別子ルールに合致する必要があります.プロシージャを作成するときに、1つ以上のパラメータを宣言し、プロシージャを実行するときに対応するパラメータを指定します.Parameter_modeはパラメータのタイプであり,プロセスパラメータや関数パラメータと同様に,IN,OUT,IN OUTの3種類がある.①IN:パラメータがプロセスに入力されたことを示す;②OUT:パラメータがプロセス中に付与され、プロセス体の外部に伝達できることを示す.③IN OUT:このタイプを示すパラメータは、プロセスボディに値を伝えることもできるし、プロセスボディに値を割り当てることもできる.sql_statement:プロシージャに含まれるPL/SQL文を表します.2.ストアド・プロシージャを呼び出してストアド・プロシージャの名前を直接入力すると、定義済みのストアド・プロシージャを実行できます.
EXEC[UTE] procedure_name[(parameter,…n)]ここで、procedure_nameは呼び出すストレージ・プロシージャの名前であり、parameterはパラメータ値である.【例1】XSCJデータベースのXSテーブルからある人の総単位を照会し、総単位に基づいてコメントを書く.
CREATE OR REPLACE PROCEDURE update_info
( xm in char )
AS
Xf number;
BEGIN
SELECT ZXF
INTO XF
FROM XS
WHERE XM=xm;
IF XF>60 THEN
UPDATE XS SET BZ=’ ’
WHERE XM=xm;
END IF;
IF XF<35 THEN
UPDATE XS SET BZ=’ ’
WHERE XM=xm;
END IF;
END update_info;
update_info ：
EXEC update_info(‘ ’);【例2】統計表XSにおける男女の同窓生の数は、記憶過程で1つの入力パラメータと1つの出力パラメータを用いた.
CREATE OR REPLACE PROCEDURE count_num
( sex IN char,
num OUT number )
AS
BEGIN
IF sex=’ ’ THEN
SELECT COUNT(XB) INTO num
FROM XS
WHERE XB=’ ’;
ELSE
SELECT COUNT(XB) INTO num
FROM XS
WHERE XB=’ ’;
END IF;
END count_num;プロシージャcount_を呼び出すnumの場合は、OUTタイプパラメータを定義して、次のように呼び出す必要があります.
DECLARE
man_num NUMBER;
BEGIN
count_num(‘ ’,man_num);
END;プロシージャが不要になった場合は、メモリから削除して、使用するメモリリソースを解放します.
DROP PROCEDURE [schema.] procedure_name;ここで、schemaはプロセスを含むユーザである.procedure_nameは削除するストレージ・プロシージャの名前です.
フリップフロップ
トリガ(trigger)は、テーブルと密接な関係にあるプロセスであり、テーブル内のデータを保護するために使用され、1つのベーステーブルが変更された場合(INSERT、UPDATE、DELETE)、トリガは自動的に実行され、例えば、トリガによって複数のテーブル間のデータの一貫性と完全性を実現することができる.トリガはアプリケーションとは無関係です.例えば、XSCJデータベースについてはXSテーブル、XS_KCテーブルとKCテーブルは、ある学号の学生のある課程成績を挿入する場合、その学号はXSテーブルにすでに存在し、課程番号はKCテーブルにすでに存在しなければならない.この場合、INSERTトリガを定義することによって上述の機能を実現することができる.フリップフロップのタイプは,(1)DMLフリップフロップの3種類である.Oracleは、DML(データ操作文)文でトリガーできます.DML操作の前または操作の後にトリガーできます.また、各行またはその文操作でトリガーできます.(2)フリップフロップに代わる.Oracleでは、2つ以上のテーブルで作成されたビューを直接操作できないため、代替トリガが与えられます.これは、Oracleがビュー操作に特化した処理方法です.(3)システムトリガ.Oracle 8 iでは、システムトリガと呼ばれる3つ目のタイプのトリガが用意されています.Oracleデータベースのクローズやオープンなど、Oracleデータベース・システムの時間内にトリガーできます.
一般的に、テーブルデータの操作には挿入、修正、削除があるため、データを維持するトリガはINSERT、UPDATE、DELETEに分けられる.各ベーステーブルは、(1)BEFORE INSERT;(2) BEFORE INSERT FOR EACH ROW;(3) AFTER INSERT;(4) AFTER INSERT FOR EACH ROW;(5) BEFORE UPDATE;(6) BEFORE UPDATE FOR EACH ROW;(7) AFTER UPDATE;(8) AFTER UPDATE FOR EACH ROW;(9) BEFORE DELETE;(10) BEFORE DELETE FOR EACH ROW;(11) AFTER DELETE;(12) AFTER DELETE FOR EACH ROW.1.構文フォーマット
CREATE OR REPLACE TRIGGER [schema.] trigger_name /* */
{ BEFORE∣AFTER∣INSTEAD OF }
{ DELETE [OR INSERTE] [OR UPDATE [ OF column,…n ]] /* */
ON [schema.] table_name∣view_name /* */
[ FOR EACH ROW [ WHEN(condition) ] ]
sql_statement[…n]2.トリガを作成する制限(1)コードサイズ.フリップフロップコードのサイズは32 K未満でなければなりません.(2)フリップフロップ内の有効な文はDML文を含んでもよいが、DDL文を含んではならない.ROLLBACK、COMMIT、SAVEPOINTも使えません.ただし、システムトリガにはCREATE、ALTER、DROP TABLE、ALTER…COMPILE文を使用できます.(3)LONG、LONG RAW、LOBの制限:①LONGまたはLONG RAWにデータを挿入できないこと;②LONGまたはLONG RAWからのデータは、char、varchar 2などの文字型に変換できますが、32 Kを超えてはいけません.③LONGまたはLONG RAWでは変数を宣言できません.④LONGまたはLONG RAW列では使用できません:NEWと:PARENT;⑤LOBの:NEW変数は変更できません.(4)参照パケット変数の制限.UPDATE文またはDELETE文で現在のUPDATE競合が検出された場合、Oracleでは、ROLLBACKをSAVEPOINTに実行し、更新を再起動します.これにより、成功するには複数回かかる場合があります.3.トリガーのトリガー順序Oracleのイベントに対するトリガーは16種類あり、これらは一定の順序で実行されます:(1)BEFORE文を実行するトリガー;(2)文の影響を受ける各行について:BEFORE文の行レベルトリガàを実行DML文àを実行してAFTER行レベルトリガを実行する.(3)AFTER文レベルトリガを実行する.4.DMLフリップフロップの作成フリップフロップは、プロシージャ名およびパッケージの名前とは異なり、別々の名前空間を持つため、フリップフロップ名はテーブル名またはプロシージャ名と同じ名前にすることができますが、同じschema(スキーマ)内のフリップフロップ名は同じではありません.DMLフリップフロップはテーブルレベルフリップフロップとも呼ばれ、あるテーブルに対してDML操作を行うとフリップフロップの実行がトリガーされるため名付けられた.【例1】XSCJデータベースに新しいテーブルXS_を追加するとするHISは、テーブル構造がテーブルXSと同じであり、XSテーブルから削除されたレコードを格納するために使用される.トリガを作成し、XSテーブルが1行削除されると、削除されたレコードをログテーブルXS_に書き込む.HIS中.
CREATE OR REPLACE TRIGGER del_xs
BEFORE DELETE ON XS FOR EACH ROW
BEGIN
INSERT INTO XS_HIS (XH,XM,ZYM,XB,CSSJ,ZXF,BZ)
VALUES(:OLD.XH,:OLD.XM, :OLD.ZYM, :OLD.XB, :OLD.CSSJ,
:OLD.ZXF, :OLD.BZ);
END del_xs;OLD ，NEW 。【例2】トリガを用いてデータベースXSCJのXSテーブルの挿入、更新、削除の3つの操作を行った後、対応するヒントを与える.
CREATE TRIGGER cue_xs
AFTER INSERT OR UPDATE OR DELETE ON XS FOR EACH ROW
DECLARE
Infor char(10);
BEGIN
IF INSERTING THEN
Infor:=’ ’;
ELSIF UPDATING THEN
Infor:=’ ’;
ELSE
Infor:=’ ’;
END IF;
INSERT INTO SQL_INFO VALUES(infor);
END cue_xs;5.システム・トリガーの作成Oracle 8 iで開始したシステム・トリガーは、DDLまたはデータベース・システムでトリガーできます.DDLとは、CREATE、ALTER、DROPなどのデータ定義文を指す.データベース・システム・イベントには、データベース・サーバの起動または停止、ユーザーのログインと終了などが含まれます.
CREATE OR REPLACE TRIGGER [scache.] trigger_name
{ BEFORE︱AFTER }
{ ddl_event_list︱databse_event_list }
ON { DATABASE︱[schema.] SCHEMA }
[when_clause]
tigger_bodyそのうち:ddl_event_List:1つ以上のDDLイベントを表し、イベント間はORで区切られます.database_event_List:1つ以上のデータベースイベントを表し、イベント間はORで区切られます.DATABASE:データベース・レベルのトリガを表し、scacheはユーザー・レベルのトリガを表します.Schemaはユーザスキームを表す.Trigger_body:フリップフロップのPL/SQL文.6.SQLコマンドによるトリガの削除
DROP TRIGGER [schema.] trigger_nameここで、schemaはトリガのユーザー・スキーマを指定します.Trigger_name削除するトリガの名前を指定します.