PL/SQLの基礎を4日でマスターする【３日目】

10593 ワード
plsql plsql テキストリンク

本日からは、ストアド・サブプログラムの内容を見ていきたいと思います。
具体的には、プロシージャ、ファンクション、トリガーの3つについて、実行方法・作成方法・特徴の3点について記述していきます。

0. Agenda

  1. Procedure
  2. Function
  3. Trigger

1.Procedure

■実行方法
プロシージャは、PL/SQLブロックから実行する方法とSQLPLUSから実行する方法があります。

PLSQLブロック
BEGIN
  <プロシージャ名>;
END;
SQLPLUS
EXCUTE <プロシージャ名>;

■作成方法

PRUCEDUR作成方法
/* 仕様部 */
CREATE OR REPLACE PROCEDURE triangle( base IN NUMBER DEFAULT 10, height IN NUMBER DEFAULT 20, area OUT NUMBER)
IS
/* 実行部 */
BEGIN
  area := (base * height)/2;
END;
/

/*実行例*/
DECLARE
  var NUMBER;
BEGIN
  var := 100;
  triangle(10,20,var);
  DBMS_OUTPUT.PUT_LINE(var);
END;
/

■特徴
プロシージャの戻り値の数は複数で、SQL内でプロシージャを使うことはできません。
また、トランザクション制御・更新処理(DML文の記述)は可能です。

ところで、プロシージャ実行時にUSER_ARGUMENTSビューを参照することで、サブプログラム内のパラメーター情報を参照することができます。

USER_ARGUMENTSビュー
SELECT * FROM USER_ARGUMENTS;

2.Function

■実行方法
FunctionはSQL文の中で実行できます。

Function文の実行
SELECT <Function名> FROM dual;

また、ファンクションは必ず一つ値を戻すので、戻り値を受けるための変数を用意して実行する必要があります。

変数を用意してFunctionを実行
/*実行例*/
DECLARE
  var NUMBER;
BEGIN
  var := triangle2(10,20);
  DBMS_OUTPUT.PUT_LINE(var);
END;
/

■作成方法

Function作成例
/* 仕様部 */
CREATE OR REPLACE FUNCTION triangle2( base IN NUMBER DEFAULT 10, height IN NUMBER DEFAULT 20)
RETURN NUMBER
IS
/* 実行部 */
BEGIN
  RETURN (base * height)/2;
END;
/

■特徴
ファンクションは必ず一つ戻り値を返し、SQL文のなかで使用することができます。
ただし、トランザクション制御や、DML文の記述をすることはできません。

■ストアド・アブプログラムの削除

ストアド・サブプログラムの削除
DROP PROCEDURE | FUNCTION <プログラム名> ;

3.Trigger

■実行方法
トリガーは指定したイベントが発生することにより起動します。
逆に言うと、トリガーは起動するタイミングによって種類を分けることができます。
トリガーの種類は以下の4つに分類されます。

種類 概要
DMLトリガー 表に対するDML操作(UPDATE,DELETE,INSERT)で起動する。
INSTEAD OFトリガー ビューに対するDML操作で起動する。
DDLトリガー DDL(CREATE、ALTER、DROP)で起動する。
イベントトリガー データベース処理や、特定ユーザーの処理で起動する。

またトリガーは、DML文に対して一度だけ起動する文トリガーと、DML文が影響を与える行ごとに起動する行トリガーの2種類があります。

■トリガーの作成

パラメーター 概要
BEFORE,AFTER トリガーが起動するタイミングを指定する
OF <列名> UPDATE文を指定した場合に指定することができる。列リストを指定すると、指定した列が更新された場合のみ、トリガーが起動する。
FOLLOWS 同じ表に同じタイプの複数のトリガーを作成する前に実行順序を指定する。
ENABLE,DISABLE 使用可能、または使用不可状態でトリガーを起動する。
FOR EACH ROW この句を指定した場合、トリガーが起動する単位が行トリガーとなる。この句を指定しない場合は文トリガーとなる。
WHEN句 トリガーを起動する条件を指定する。FOR EACH ROW句を指定した場合のみ指定できる。 
/* 仕様部 */
CREATE OR REPLACE TRIGGER emp_nomod
  BEFORE INSERT OR DELETE OR UPDATE/*[OF 列名]*/ ON emp
  /* FOLLOWS <トリガー名> */
  /* ENABLE | DIABLE */
  /* FOR EACH ROW [WHEN <起動条件>] */

/* 実行部 */
BEGIN
  IF (TO_CHAR(SYSDATE,'DY') = '日') THEN 
    IF DELETING THEN
      raise_application_error
        (-20325,'日曜日に削除は行えません');
    ELSIF INSERTING THEN 
      raise_application_error
        (-20326,'日曜日に入力は行えません');
    ELSIF UPDATING THEN
      raise_application_error
        (-20327,'日曜日に更新は行えません');
    ELSE
      raise_application_error
        (-20328,'日曜日にEMP表への変更はできません');
    END IF;
  ELSE NULL;
  END IF;
END;
/
トリガーの状態変更

/* トリガーの使用状態の変更 */
ALTER TRIGGER <> {ENABLE | DISABLE};

/* トリガーの使用状態を一括で変更する */
ALTER TABLE <テーブル名> {ENABLE | DISABLE} ALL TRIGGERS;

/* トリガーの管理 */
SELECT * FROM USER_TRIGGERS;

■特徴
トリガー内では、DDL文やトランザクション制御文を実行することができない。

【参考文献】
『プロとしてのOracle PL/SQL入門』 アシスト教育部 2017年 第3版