InterBase クイック スタート：Part V - シンプルな実行可能プロシージャを作成する

提供: InterBase

InterBase クイック スタート：Part V - 応用トピック へ戻る


Image 025.jpg シンプルな実行可能プロシージャを作成する

  1. この演習で作成する実行可能プロシージャ、add_emp_proj は、CREATE EXCEPTION で定義する例外を利用します。次の文を実行して、UNKNOWN_EMP_ID 例外を作成します： 
    CREATE
EXCEPTION
  unknown_emp_id 'Invalid employee number or project ID.'
    これでこの例外を、トリガまたはストアド プロシージャ内の EXCEPTION 句で発生させられるようになりました。呼び出しアプリケーションは、関連付けられているエラー メッセージを受け取ります。
  2. 次の文を実行して、add_emp_proj ストアド プロシージャを作成します： 
    CREATE PROCEDURE ADD_EMP_PROJ (v_empno  SMALLINT,
                               v_projid CHAR(5))
AS
BEGIN
  INSERT INTO employee_project
             (emp_no,
              proj_id)
  VALUES
             (:v_empno,
              :v_projid);
  WHEN SQLCODE -530
  DO
    EXCEPTION
      unknown_emp_id;
END
    このプロシージャは、社員番号とプロジェクト ID を入力パラメータとして受け取り、その社員を指定されたプロジェクトへ、INSERT 文を使って追加します。 WHEN エラー処理文は、SQLCODE -530 （外部キー制約違反）をチェックし、もしエラーがあった場合には先に定義した例外を発生させます。
  3. プロシージャをテストするには、まず、次の文を入力します： 
    SELECT *
FROM   employee_project
WHERE  emp_no = 20

    社員 20 は、プロジェクト GUIDE に対してのみ割り当てられています。

    結果、次の図のようになっていると思います。

    TutorialProcedure3.png
  4. 次の文を実行することで、このプロシージャを実行することができます。 
    EXECUTE PROCEDURE add_emp_proj(20, 'DGPII')
  5. 機能しているかを確認するには、次の SELECT 文を実行します： 
    SELECT *
FROM   employee_project
WHERE  emp_no = 20

    社員 20が、DGPII プロジェクトと GUIDE プロジェクトに割り当てられていることが認できるでしょう。.

    結果、次の図のようになっていると思います。

    TutorialProcedure4.png
  6. ここで、存在しない社員をプロジェクトに追加してみましょう： 
    EXECUTE PROCEDURE add_emp_proj(999, 'DGPII')

    この文は失敗し、エラー メッセージが画面に表示されます。

    TutorialProcedure5.png

再帰プロシージャ

ストアド プロシージャは再帰処理をサポートしています。再起処理は強力なプログラミング技術で、企業の組織や機械のパーツといった階層構造をたどって繰り返しタスクを行う際に便利です。

Image 025.jpg 再帰プロシージャを作成する

この演習では、ストアド プロシージャ dept_budget を作成します。これは、部署番号を入力パラメータとして受け取り、部署の予算と、その会社の階層において、その部署の下にあるすべての部署を返します。 このプロシージャはまた、DECLARE VARIABLE 文で宣言されている、ローカル変数を使用します。

  1. 次の SQL 文を実行します： 
    CREATE PROCEDURE dept_budget (v_dno CHAR(3))
returns                      (total_budget NUMERIC(15, 2))
AS
  DECLARE
    variable sumb DECIMAL(12, 2);
    DECLARE
      variable rdno CHAR(3);
      DECLARE
        variable cnt INTEGER;
BEGIN
  total_budget = 0;
  SELECT budget
  FROM   department
  WHERE  dept_no = :v_dno
  INTO   
    :total_budget;
  SELECT count(budget)
  FROM   department
  WHERE  head_dept = :v_dno
  INTO   
    :cnt;
  IF (cnt = 0) THEN
    suspend;
  FOR
    SELECT dept_no
    FROM   department
    WHERE  head_dept = :v_dno
    INTO
      :rdno
  DO
    BEGIN
      EXECUTE PROCEDURE dept_budget :rdno returning_values :sumb;  
      total_budget = total_budget + sumb;
    END
END

    まず、プロシージャは部署の予算を、入力パラメータで指定された Department テーブルから取得し、それを total_budget 変数に格納します。それから、その部署の配下の部署の数を、COUNT 集計関数を使用して抽出します。 配下の部署がなければ、total_budget の値を SUSPEND と共に返します。 FOR SELECT … DO ループを使用して、このプロシージャはそれから各配下の部署の部署番号をローカル変数 rdno に取得し、次の文で自分自身を再帰的に呼び出します： 

    EXECUTE PROCEDURE dept_budget :rdno returning_values :sumb

    この文は、dept_budget を、入力パラメータ rdno で実行し、出力値を sumb に挿入します。EXECUTE PROCEDURE をプロシージャ内で使用する際、入力パラメータは括弧の中にいれず、また、出力値 RETURNING_VALUES キーワードの後に指定します。

  2. 部署 620 のすべての配下部署を含めた合計予算を検索するには、次の SQL 文を実行します： 
    EXECUTE PROCEDURE dept_budget(620)

    結果は次のとおりです：

    TutorialProcedure6.png

データ型変換について

dept_budget プロシージャの定義は、CHAR(3) を入力パラメータとして取ります。InterBase の自動型変換により、実際には整数を提供していますが、その整数は変換されていました。しかしながら、特定の制限もあります。たとえば、整数 000 を渡すことはできません。これは、CHAR(3) への変換が、文字列 '0' になってしまい、これは有効な部署番号ではないからです。これをテストするには、プロシージャを実行して、パラメータに整数 000 を指定し、その後、文字列 '000' を指定します。

その他のプロシージャ

そのほかにも数多くのプロシージャがあり、中には非常に複雑なものもあります。これらは、PROCS.SQL スクリプトで定義されています。これで、プロシージャの基礎的なことは理解できたと思われますので、少し時間をかけて、それらに試し使ってみることをお勧めします。PROCS.SQL スクリプトには、多くのインライン コメントがあり、コードが理解しやすいようになっています。

まとめ

これで、InterBase クイック スタート チュートリアルは完了です。 さらなるガイドおよびチュートリアルについては、InterBase ユーザー ガイドを参照してください。

「https://docwiki.embarcadero.com/InterBase/2020/j/index.php?title=InterBase_クイック_スタート：Part_V_-_シンプルな実行可能プロシージャを作成する&oldid=5221」から取得