CREATE PROCEDURE

FrontPageへ

• リファレンスガイドへ
  
  • Firebird SQLリファレンスへ
    
    

Firebird SQLリファレンス:CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, EXECUTE PROCEDURE, RECREATE PROCEDURE

CREATE PROCEDURE

　入出力のパラメータとアクションを定義し、ストアドプロシ−ジャを作成します。 DSQL,isqlで使用できます。
　この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。
　またSQLリファレンスの情報が少ないため、IB6 の Data Definition Guide からの 情報を元に、独自かつ大幅に補足しました。

構文

CREATE PROCEDURE name
    [(param <datatype> [, param <datatype> …])]
    [RETURNS <datatype> [, param <datatype> …])]
    AS <procedure_body> [terminator]

<procedure_body> = [<variable_declaration_list>]  <block>

IB6/Firebird 1.0:

<variable_declaration_list> =
    DECLARE VARIABLE var <datatype>;
    [DECLARE VARIABLE var <datatype>; …]

Firebird 1.5以降:(初期化できるようになった)

<variable_declaration_list> =
    DECLARE VARIABLE var <datatype> [{'=' | DEFAULT} value];
    [DECLARE VARIABLE var <datatype> [{'=' | DEFAULT} value]; …]

<block> =
    BEGIN
        <compound_statement>
        [<compound_statement> …]
    END

<compound_statement> = { <block> | statement; }

<datatype> = SMALLINT | INTEGER | BIGINT | FLOAT | DOUBLE PRECISION
    | {DECIMAL | NUMERIC} [(precision [, scale])]
    | {DATE | TIME | TIMESTAMP}
    | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
        [(int)] [CHARACTER SET charname]
    | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]

詳細の説明

　CREATE PROCEDURE により、新たなストアドプロシージャを定義します。 ストアドプロシージャは、自己完結のプロシージャ・トリガ記述言語によって記述 されたプログラムであり、データベースではメタデータの一部として格納されます。 ストアドプロシージャは入力された値に基づいて処理を行い、その結果を呼び出した プログラムに返します。

　プロシージャ・トリガ記述言語とは、すべての SQL データ操作文に加え、いくつか の強力な拡張がなされています。(IF … THEN … ELSE, WHILE … DO, FOR SELECT … DO,例外やエラーハンドリングなど。 PSQLと呼ばれることもあります。)

　プロシージャには2種類のタイプがあります。

• セレクトタイプのプロシージャ。アプリケーションは、テーブルやビューと同様に してSELECT文で使用できます。このタイプのプロシージャは、１つ以上の値を返す、 もしくはエラーを発生させなければなりません。
• 実行タイプのプロシージャ。アプリケーションは、EXECUTE PROCEDURE により直接 呼び出すことが出来ます。このタイプのプロシージャは、必ずしも値を返す必要は ありません。

　ストアドプロシージャの定義はヘッダと本体の２つで構成されており、AS によって分離されています。

• ヘッダの定義内容には、以下のようなものがあります。
  • ストアドプロシージャの名前。データベース内において、他のプロシージャや テーブル名と重ならない、一意な名前である必要があります。
  • 必要ならば、呼び出したプログラムから受取る入力パラメータとそのデータ型 で構成されたリスト。
  • RETURNSに続いて記述を行う、呼び出したプログラムが受取る出力パラメータと そのデータ型で構成されたリスト。
• 本体の定義には、以下のものが含まれます。
  • 必要ならば、ローカル変数とそのデータ型の宣言。
  • BEGIN で始まり END で括られる、プロシージャ・トリガ記述言語により記述された いくつかの文で構成されるブロック。ブロック内には、別のブロックを含むことが 出来ます。つまり、ブロックの多重ネストが可能となっています。
  • Firebird1.5では、変数の宣言時に値を初期化出来るようになりました。

重要 ストアドプロシージャの本体部分の記述では、終端文字としてセミコロン を使用します。このため、isqlによりストアドプロシージャの定義を行う場合は、 SET TERM により終端文字をセミコロン以外に変更する必要があります。また、 このような場合は、CREATE PROCEDURE 文を終了させた後に、終端文字を元の セミコロンに戻しましょう。

　データベースエンジンは、既存のストアドプロシージャの振る舞いに影響を与える データベースの変更を許しません。(例えば、DROP TABLE、DROP EXCEPTION)
　isqlの内部コマンド、SHOW PROCEDURES または SHOW PROCEDURE により、カレント データベースにおける、プロシージャの定義またはテキスト・パラメータを参照する ことが出来ます。


　プロシージャ・トリガ記述言語は、ストアドプロシージャやトリガの処理内容を 記述するためのプログラミング言語です。PSQLと呼称されている場合もあり、以下 のような機能を持っています。

• SQLのデータ操作文(INSERT,UPDATE,DELETE,単行SELECTなど)
• SQLの演算子や評価式。これは、ジェネレータやデータベースにリンクされた UDFなども含みます。
• 拡張SQL。代入文、コントロールフロー文、コンテキスト値(トリガ向け)、 イベントポスト文、例外およびエラーハンドリング文。

　下表に、ストアドプロシージャのための言語拡張をまとめます。

• EXIT,SUSPEND,最後のENDにより、ストアドプロシージャは処理を終了します。 ただし、SELECTにより実行したセレクトタイプのプロシージャがSUSPENDにより終了 した場合は、２行目以降のデータを取得するときに、SUSPENDに続く行から処理が 再開されます。次表にまとめましたので参照して下さい。
  

  タイプ	SUSPEND	EXIT	END
  セレクトタイプ	値を返して処理を一時中断し、アプリケーションに制御を戻す。FETCHなどの次行取得操作で処理を再開	最後のENDにジャンプ	制御をアプリケーションに戻し、SQLCODEに100をセット
  実行(EXECUTE)タイプ	最後のENDにジャンプ。処理再開不可	最後のENDにジャンプ	値を返して制御をアプリケーションに戻す

• select_statementでのSELECTの結果を受取るために、SELECT〜INTOを使用します。 ローカル変数や出力パラメータなどが指定できますが、変数であることを示すために コロン(:)を使用します。 WHERE節などでも変数が使用できますが、同様にコロンが必要です。
  

• トリガでは、２つのコンテキスト変数、NEW.〜, OLD.〜を使用できます。 OLD.変数は、UPDATE,DELETEされる列の現在または以前の値がセットされます。 NEW.変数は、INSER,UPDATEされる列の新しい値がセットされます。当然ながら、 NEW.はDELETEでは参照できません。これらのコンテキスト変数を使用することに より、値変更時に新旧の値を比較して処理を制御したりすることが可能となります。
  例えば、次のように使用出来ます。(new,oldが小文字になっているので注意)

  SET TERM !! ;
  CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
    AFTER UPDATE AS
    BEGIN
      IF (old.salary <> new.salary) THEN
        INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE,
          UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
          VALUES (old.emp_no, 'now', USER, old.salary,
            (new.salary - old.salary) * 100 / old.salary);
    END !!
  SET TERM ; !!

• EXECUTE PROCEDURE などにより、プロシージャから別のプロシージャを呼び出す ことが出来ます（ネスト可能）。また、自分自身を呼び出す、リカーシブルコール も可能です。ただし、ネストの深さは1000までに制限されています。
  

• WHENにより、エラーや例外をハンドリングできます。
  • ハンドルできるものは、自プロシージャ内での例外やエラー以外に、ネストした プロシージャで発生したものや、操作の結果実行されたトリガで発生したものも ハンドリングできます。
  • SQLのエラーは SQLCODEにエラーリポートが設定されます。次にしめすような形で ハンドルするエラーを指定します。SQLCODE -803 は、値重複のエラーです。

    SET TERM !!;
    CREATE PROCEDURE NUMBERPROC (A INTEGER, B INTEGER)
      RETURNS (E CHAR(60)) AS
      BEGIN
        BEGIN
          INSERT INTO TABLE1 VALUES (:A, :B);
          WHEN SQLCODE -803 DO
            E = 'Error Attempting to Insert in TABLE1 - Duplicate Value.';
        END;
      END!!
    SET TERM; !!

• データベースエンジンのエラーは GDSCODE にエラーりポートが設定されます。 SQLCODEと同様に、GDSCODE errorcode のような形で指定します。
• ANYの指定を行うと、すべてのエラーや例外がハンドリングされます。
• DOに続いて、実行したい単一の文もしくはブロックを記述します。
• ハンドル処理を複数記述したい場合、「WHEN〜DO〜;」を連ねて記述すること が出来ます。例えば次のような形です。

  /* Error Handling */
  WHEN SQLCODE -625 DO
  BEGIN
    IF ((:product <> 'software') OR (:product <> 'hardware') OR
    (:product <> 'other') OR (:product <> 'N/A')) THEN
    result = 'Enter product: software, hardware, other, or N/A';
  END
  WHEN SQLCODE -803 DO
    result = 'Could not insert into table - Duplicate Value';

• EXCEPTIONにより、既に定義済みの例外を発生させることが出来ます。WHENで ハンドリングしない場合には、アプリケーションに例外を伝えます。
  
  
• EXCEPTIONに関しては、Firebird1.5で機能が拡張されました。
  • 例外を発生させるときにメッセージを同時に定義できます。
    例1 :

    EXCEPTION E_EXCEPTION_1 'Error!';

    例2 :

    EXCEPTION E_EXCEPTION_2 'Wrong type for record with ID=' || new.ID;

    
    
  • 例外をキャッチの処理中に、再度例外を発生させることが出来ます。
    例1 : SQLエラーをキャッチしてユーザー定義の例外として送出

    BEGIN
      ...
      WHEN SQLCODE -802 DO
        EXCEPTION E_ARITH_EXCEPT;
      WHEN SQLCODE -802 DO
        EXCEPTION E_KEY_VIOLATION;
      WHEN ANY THEN
        EXCEPTION;
      END

    例2 : エラーのログを取得して、その例外をそのまま送出する。

    WHEN ANY DO
    BEGIN
      INSERT INTO ERROR_LOG (...) VALUES (SQLCODE, ...);
      EXCEPTION;
    END

    
    
  • 例外処理ブロックの中で、コンテキスト変数 SQLCODE,GDSCODE によりエラー番号 が整数値で取得出来るようになりました。エラーメッセージ生成などの処理に利用 できます。SQLCODEによりSQLエラー、GDSCODEによりデータベースエンジンのエラー が取得できます。

    BEGIN
      ...
      WHEN SQLCODE -802 DO
        EXCEPTION E_EXCEPTION_1;
      WHEN SQLCODE -803 DO
        EXCEPTION E_EXCEPTION_2;
      WHEN ANY DO
        EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE);
    END

    
    

• Firebird1.5以降では、EXECUTE STATEMENT により変数等の文字列で指定したDSQL文 を実行することができます。
  • 戻り値のない操作文(INSERT/UPDATE/DELETE)やDDL文(CREATE/DROP DATABASEなど) を実行する場合は、コマンド文字列を指定するだけで実行できます。

    CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100))
    AS
    DECLARE VARIABLE Sql VARCHAR(1024);
    DECLARE VARIABLE Par INT;
    BEGIN
      SELECT MIN(SomeField) FROM SomeTable INTO :Par;
      Sql = 'EXECUTE PROCEDURE' || Pname || '(';
      Sql = Sql || CAST(Par AS VARCHAR(20)) || ')';
      EXECUTE STATEMENT Sql;
    END

    
    
  • 戻り値を受取る必要がある SELECT では、INTO オプションを使用します。 この場合のSELECTは、単行取得タイプのみが実行できます。

    CREATE PROCEDURE DynamicSampleTwo (TableName VARCHAR(100))
    AS
    DECLARE VARIABLE Par INT;
    BEGIN
      EXECUTE STATEMENT 'SELECT MAX(CheckField) FROM' || TableName INTO :Par;
      IF (Par > 100) THEN
        EXCEPTION Ex_Overflow 'Overflow in ' || TableName;
    END

    
    
  • 複数行の結果を受取る SELECT では、FOR オプションを使用します。取得行毎に DOの処理が実行されます。

    CREATE PROCEDURE DynamicSampleThree (
      TextField VARCHAR(100),
      TableName VARCHAR(100))
    RETURNS (Line VARCHAR(32000))
    AS
    DECLARE VARIABLE OneLine VARCHAR(100);
    BEGIN
    Line = ’’;
    FOR EXECUTE STATEMENT
      'SELECT' || TextField || ' FROM ' || TableName INTO :OneLine
      DO
        IF (OneLine IS NOT NULL) THEN
          Line = Line || OneLine || ’ ’;
      SUSPEND;
    END

用例

　すべてisqlでの例です。そのため、SET TERM で終端文字を一時的に変更して います。

• 次のプロシージャSUB_TOT_BUDGETは、入力パラメータを１つ与えると、 テーブルDEPARTMENTの列HEAD_DEPTがその値である行を対象にして集計を行い、 結果を４つの戻り値（総計、平均、最小、最大）として返します。集計結果を 返すため、結果は１行のみとなります。SELECTのINTO節にも注目してください。 出力パラメータを直接指定していますが、SELECTのINTO節では変数を表すために コロン(:)を使用します。

/* Compute total, average, smallest, and largest department budget.
*   Parameters:
*       department id
*   Returns:
*      total budget
*      average budget
*      min budget
*      max budget */

SET TERM !! ;

CREATE PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3))
  RETURNS (tot_budget DECIMAL(12, 2), avg_budget DECIMAL(12, 2),
  min_budget DECIMAL(12, 2), max_budget DECIMAL(12, 2))
  AS
  BEGIN
    SELECT SUM(BUDGET), AVG(BUDGET), MIN(BUDGET), MAX(BUDGET)
      FROM DEPARTMENT
      WHERE HEAD_DEPT = :head_dept
      INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
    EXIT;
  END !!

SET TERM ; !!

• 次のプロシージャ ORG_CHARTは、セレクトタイプのプロシージャとなっており、 組織表を出力します。セレクトタイプなので、結果は複数の行で得られます。
  • プロシージャの定義です。ローカル変数として、mng_noとdnoを宣言しています。

    /* Display an org-chart.
    *
    * Parameters:
    * --
    * Returns:
    * parent department
    * department name
    * department manager
    * manager's job title
    * number of employees in the department */
    
    SET TERM !! ;
    
    CREATE PROCEDURE ORG_CHART
      RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
      MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
      AS
      DECLARE VARIABLE mngr_no INTEGER;
      DECLARE VARIABLE dno CHAR(3);
      BEGIN
        FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
        FROM DEPARTMENT D
        LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
        ORDER BY D.DEPT_NO
        INTO :head_dept, :department, :mngr_no, :dno
        DO
        BEGIN
          IF (:mngr_no IS NULL) THEN
          BEGIN
            MNGR_NAME = '--TBH--';
            TITLE = '';
          END
          ELSE
            SELECT FULL_NAME, JOB_CODE
              FROM EMPLOYEE
              WHERE EMP_NO = :mngr_no
              INTO :mngr_name, :title;
          SELECT COUNT(EMP_NO)
            FROM EMPLOYEE
            WHERE DEPT_NO = :dno
            INTO :emp_cnt;
          SUSPEND;
        END
      END !!
    
    SET TERM ; !!

  • ORG_CHARTを実行して結果を得たいときは、つぎのクエリを実行します。

    SELECT * FROM ORG_CHART

    　これにより、例えば次のような内容が出力されます。当然のことながら、表示内容 はテーブル(DEPARTMENT,D)の内容に依存します。またこのような罫線のある表が 出力されるわけではありませんので、その点は注意して下さい。

    HEAD_DEPT	DEPARTMENT	MGR_NAME	TITLE	EMP_CNT
    =================	=================	==============	====	==
    	Corporate Headquarters	Bender, Oliver H.	CEO	2
    Corporate Headquarters	Sales and Marketing	MacDonald, Mary S.	VP	2
    Sales and Marketing	Pacific Rim Headquarters	Baldwin, Janet	Sales	2
    Pacific Rim Headquarters	Field Office: Japan	Yamamoto, Takashi	SRep	2
    Pacific Rim Headquarters	Field Office: Singapore	--TBH--		0

    
    　ORG_CHARTプロシージャでの全情報を得るためにはSELECTによる実行が必要です。 もしも EXECUTE PROCEDURE により実行を行った場合は、最初の SUSPEND で実行が 終了してしまうので、最初の１行目のデータしか得ることが出来ません。(上表で 言えば、Corporate Headquarters,Bender, Oliver H.,CEO,2 の行のみが得られ ます。)
    

参照

　ALTER EXCEPTION , ALTER PROCEDURE , CREATE EXCEPTION , DROP EXCEPTION , [[DROP PROCEDURE>#drop_procedure] , EXECUTE PROCEDURE , SELECT

　これ以上の情報が欲しい場合は、Data Definition Guideの creating and using procedures を参照して下さい。

ALTER PROCEDURE

　既存のストアドプロシージャの定義を変更します。 DSQLとisqlで使用可能です。

構文

ALTER PROCEDURE name
    [(param <datatype> [, param <datatype> …])]
    [RETURNS (param <datatype> [, param <datatype> …])]
    AS <procedure_body> [terminator]

詳細の説明

　ALTER PROCEDURE により、既存のストアドプロシージャの削除を行わずに、処理の 内容などを変更できます。変更できるのは、入出力パラメータおよび処理本体です。

　パラメータやボディなどの定義に関しては、 CREATE PROCEDUREを参照して下さい。ALTERがCREATEに置き 換わるだけで、構文などはまったく同一です。

重要　入出力パラメータの数や型の変更は、慎重に行ってください。使用する アプリケーションが、それらに依存している場合が多いからです。

　プロシージャの定義を変更できるのは、作成者、SYSDBAユーザー、または OS の 管理者権限を持ったユーザーのみです。

　使用中のプロシ−ジャに変更を施そうとした場合、使用が終了するまで変更は 出来ません。

　ALTER PROCEDURE による変更を行った後に変更をコミットすることにより、変更 は反映されます。変更を行うことが出きれば、それを使用するアプリケーション の再コンパイルや再リンクを行わなくても、その変更は直ちに反映されます。

用例

isqlでの例です。

SET TERM !! ;

ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
  RETURNS (PROJ_ID VARCHAR(20)) AS
  BEGIN
    FOR SELECT PROJ_ID
      FROM EMPLOYEE_PROJECT
      WHERE EMP_NO = :emp_no
      INTO :proj_id
    DO
      SUSPEND;
  END !!

SET TERM ; !!

参照

　CREATE PROCEDURE , DROP PROCEDURE , EXECUTE PROCEDURE


　これ以上の情報が欲しい場合は、Data Definition Guideの creating and using procedures を参照して下さい。

DROP PROCEDURE

　データベースから、既存のストアドプロシージャを削除します。 DSQL,isqlで使用できます。

構文

DROP PROCEDURE name

詳細の説明

　DROP PROCEDURE により、既存のストアドプロシージャをデータベースから削除 出来ます。

　プロシージャが、他のプロシージャ、トリガ、ビューで使用されている場合、 削除は出来ません。また、プロシージャが現在使用中の場合も削除できません。

※　isql で SHOW PROCEDURE を実行することにより、プロシージャのリストや プロシージャの依存関係(プロシージャ、トリガ、例外、テーブル)が表示 出来ます。

　プロシージャを削除できるのは、作成者、SYSDBAユーザー、または OS の 管理者権限を持ったユーザーのみです。

用例

　isql でプロシージャを削除する例です。

DROP PROCEDURE GET_EMP_PROJ;

参照

　ALTER PROCEDURE , CREATE PROCEDURE , EXECUTE PROCEDURE

EXECUTE PROCEDURE

　ストアドプロシ−ジャを呼び出します。 SQL, DSQL, isql で使用できます。

構文

SQL 形式:

EXECUTE PROCEDURE [TRANSACTION transaction]
    name [:param [[INDICATOR]:indicator]]
    [, :param [[INDICATOR]:indicator] …]
    [RETURNING_VALUES :param [[INDICATOR]:indicator]
    [, :param [[INDICATOR]:indicator] …]];

DSQL 形式:

EXECUTE PROCEDURE name [param [, param …]]
    [RETURNING_VALUES param [, param …]]

isql 形式:(実行結果は表示される)

EXECUTE PROCEDURE name [param [, param …]]

詳細の説明

　EXECUTE PROCEDURE により、指定したストアドプロシージャを呼び出します。 そのプロシージャが入力パラメータを必要とするならば、ホスト言語変数または 定数で与えることが出来ます。出力パラメータは、SQL プログラムの場合は RETURNING_VALUE に記述したホスト言語変数に格納されます。isqlでは、出力 パラメータ受け取りの指定は出来ません。isqlでは結果が自動的に画面に表示 されます。
※　DSQLでは、入力パラメータが必要なときは入力ディスクリプションエリア、 出力パラメータが必要な場合は出力ディスクリプションエリアが必要です。

　埋め込みSQLの場合、入力パラメータおよび戻り値の各々の値がNULLであるかを 識別するためのインジケーター変数を付与できます。インジケーター変数に格納 されるのは整数値で、対応するパラメータがNULL/UNKNOWN値であるかを示して います。この値が0未満(負値)であれば NULL/UNKONOWN であることを表します。 逆に0以上(正値)であるならば、NULLやUNKNOWNではないことを表します。

※　複数行の結果を取得するタイプのストアドプロシージャを呼び出すには、 SELECTを使用してください。詳しくは、CREATE PROCEDURE を参照して下さい。

用例

• 埋め込みSQLで、プロシージャを実行する例です。入力パラメータは定数で、 戻り値(出力パラメータ)は変数で受取っています。

  EXEC SQL
    EXECUTE PROCEDURE DEPT_BUDGET 100 RETURNING_VALUES :sumb;

  
  

• 埋め込みSQLで、入出力パラメータ共に変数を使用しています。

  EXEC SQL
    EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;

  
  

参照

　ALTER PROCEDURE , CREATE PROCEDURE , DROP PROCEDURE

　これ以上の情報が欲しい場合、Embedded SQL Guide の indicator variables を参照して下さい。

RECREATE PROCEDURE

　既存のストアドプロシージャを破棄せずに、同じ名前で再作成できます。 SQL,DSQLで使用できます。

　構文はCREATE PROCEDUREとまったく同一です。