Symfoware Server V11.0.x SQLリファレンス

5.1 ストアドプロシジャの概要

機能

ストアドプロシジャとは、複数のSQL文で記述された一連の処理手続きです。ストアドプロシジャはサーバに登録し、クライアントからCALL文で呼出して実行します。この処理手続きの定義体をプロシジャルーチンと呼びます。

プロシジャルーチンでは変数を扱うことができます。プロシジャルーチン内で使用する変数をSQL変数と呼びます。SQL変数はアプリケーションの埋込み変数と異なりデータベースのデータ型を持つため、変数の取扱いが簡単です。例えば、VARCHAR型の変数にデータを代入する場合、SQL埋込みCプログラムのように長さを設定する必要はなく直接変数に代入することができます。また、SQL変数はNULL値を設定することができるため、プロシジャルーチン内では標識変数を指定する必要はありません。

プロシジャルーチンは、INSERT文やSELECT文などのデータ操作文の他にSQL制御文を指定することができます。SQL制御文には、SQL変数に値を代入するSET文、条件に応じて処理の流れを制御するIF文やWHILE文、無条件に処理を分岐するGOTO文などがあります。これらのSQL制御文をプロシジャルーチンに指定して実行することで、アプリケーションのような処理をサーバで行うことができます。

プロシジャルーチンとアプリケーションのデータの受渡しはプロシジャルーチンのパラメタで行います。アプリケーションでは、プロシジャルーチンのパラメタに対応してCALL文の引数に定数および埋込み変数を指定してデータの受渡しを行います。

プロシジャルーチン内では、ハンドラを使用することできます。ハンドラとは、プロシジャルーチン実行中にエラーが発生した際に実行されるサブルーチンです。プロシジャルーチン実行中にエラーが発生した際にハンドラが呼び出され、特定の処理を行い、プロシジャルーチンの処理を回復することができます。ハンドラの有効範囲は複合文内です。

記述形式

構文の構成

参照項番

• データ型 → “2.2 データ型”

• 定数 → “2.1.2 定数”

• 日本語文字列 → “2.1.3 トークン”

一般規則

• CALL文を指定したプロシジャルーチンを定義する場合、呼出し先のルーチンはあらかじめ定義されていることが必要です。

• 複合文中のSQL変数宣言、カーソル宣言、条件宣言およびハンドラ宣言により宣言されたSQL変数、カーソル、条件およびハンドラは、その複合文の中でのみ有効です。宣言されたSQL変数、カーソル、条件およびハンドラは複合文の終了時に破棄されます。

• パラメタリスト上のパラメタ宣言のパラメタ名とSQL変数宣言のSQL変数名には、同一名は指定できません。

• 同名のパラメタ名のパラメタ宣言は指定できません。

• 同名のSQL変数名のSQL変数宣言は指定できません。

• 同名のカーソル名のカーソル宣言は指定できません。

• 同名の条件名の条件宣言は指定できません。

• プロシジャルーチン内のSQL文で、SQLSTATE値の例外コードが40の例外事象（ロールバック例外）が発生した場合、プロシジャルーチンはそこで処理を終了し、呼出し元のアプリケーションに復帰します。このとき、トランザクションはロールバックされます。

• プロシジャルーチン内においてOBJECT型の表でサイズが32K以上のBLOB型の列は扱えません。

• プロシジャルーチン定義に行識別子“ROW_ID”は指定できません。

• BEGINとENDの間にENDが現われた場合、文法エラーとはならず複合文の処理を終了します。

• 処理手続き全体の長さは無制限です。

パラメタモード

• パラメタモードには、プロシジャの受渡しに使うパラメタのモードを指定します。パラメタモードがINの場合には、そのパラメタはプロシジャルーチンの入力に使われ、パラメタモードがOUTの場合には、そのパラメタはプロシジャルーチンの出力に使われ、パラメタモードがINOUTの場合には、そのパラメタはプロシジャルーチンの入出力に使われます。

• パラメタリスト上のパラメタ宣言のパラメタ名とSQL変数宣言のSQL変数名は、同一名を指定してはいけません。

• パラメタモードがINのパラメタは相手指定には指定できません。つまり、パラメタモードがINのパラメタはその値を参照することはできますが、値を設定することはできません。

• パラメタモードがOUTのパラメタは値指定には指定できません。つまり、パラメタモードがOUTのパラメタには値を設定することはできますが、参照することはできません。

• パラメタモードがINOUTのパラメタは値指定および相手指定に指定できます。つまり、SQL変数と同様に値の設定および参照することができます。

• パラメタモードがINおよびINOUTのパラメタは、プロシジャルーチンの実行開始時に呼出し元のCALL文の引数に指定された値を取り込みます。このとき、代入エラーが発生した場合、プロシジャルーチンは実行されずにCALL文は異常終了します。

SQL変数宣言のDEFAULT句

• SQL変数宣言にDEFAULT句が指定されている場合、プロシジャルーチンの実行開始時にDEFAULT句で指定された値がSQL変数に設定されます。

• DEFAULT句に指定する値は、SQL変数のデータ型に対して代入可能である必要があります。代入時にエラーが発生した場合、プロシジャルーチンは実行されずにCALL文は異常終了します。

SQL手続き文

• SQL手続き文とは、プロシジャルーチンに定義することができるSQL文です。プロシジャルーチンに定義できるSQL手続き文を以下に示します。

  項目	文／要素
  SQLデータ操作文	CLOSE文
  	DECLARE CURSOR(カーソル宣言)
  	DELETE文:位置づけ
  	DELETE文:探索
  	FETCH文
  	INSERT文
  	OPEN文
  	Single row SELECT文(単一行SELECT文)
  	UPDATE文:位置づけ
  	UPDATE文:探索
  トランザクション管理文	COMMIT文
  	ROLLBACK文
  SQL制御文	CALL文
  	GOTO文
  	IF文
  	LOOP文
  	LEAVE文
  	REPEAT文
  	SET文
  	WHILE文
  	WHENEVER文
  	SIGNAL文
  	RESIGNAL文

• 複合文中のSQL手続き文に指定したSQL変数およびカーソルは、複合文の局所宣言で宣言されている必要があります。

• プロシジャルーチン内の1つのSQL手続き文が1つのSQL文になります。つまり、プロシジャルーチン内のSQL文の実行でエラーが起きた場合は、エラーを起こしたSQL文が無効となります。ただし、SQLSTATE値の例外コードが40の場合は、トランザクションはロールバックされます。

• 複合文中のSQL手続き文の結果は、複合文の局所宣言で宣言されたSQLSTATE変数に設定されます。このSQLSTATE変数を使用することで以下のような条件判断を行うことができます。SQLSTATEによる例外事象に対する処理の切りわけを使用例に示します。

• プロシジャルーチン内のSQL文でエラーが発生した場合、SQLSTATE値が40003の例外事象(処理時間オーバまたは強制終了)の場合は、プロシジャルーチンはそこで処理を終了し、呼出し元のアプリケーションに復帰します。

• SQL手続き文は複数指定することができます。これを、“SQL文リスト”と呼びます。

• トリガ動作の延長で呼び出されたプロシジャルーチン内でSQLSTATE値の例外コードが40の例外が発生した場合、CALL文は異常終了し、トリガを起動する元となったSQL文を含むトランザクションが自動的にロールバックされます。

• トリガ動作の延長で呼び出されたプロシジャルーチンではトランザクション管理文を実行することはできません。トランザクション管理文を実行すると実行エラー（禁止されているSQL文の実行)になります。

• 複合文内のSQL手続き文で例外が発生したとき、複合文内にハンドラ宣言が1つ以上指定されている状態で、かつ複合文内に適切なハンドラが見つからなかった場合、プロシジャルーチンを呼び出したCALL文は異常終了します。このとき、複合文内で発生した例外事象が返却されます。

SQL制御文

• SQL制御文には以下に示すような処理の流れを制御する文があります。

  • IF文は条件に応じて処理を切り分けます。

  • LOOP文は無条件に処理を繰り返し、LEAVE文で繰り返し処理から抜け出します。また、LOOP文には識別用に文ラベルを指定します。これはどのLOOP文から抜け出すかをLEAVE文で指定するために使われます。

  • REPEAT文とWHILE文は条件によって処理を繰り返します。REPEAT文とWHILE文は、繰り返し条件以外にLEAVE文で抜け出すことができます。この場合はREPEAT文またはWHILE文に文ラベルを指定する必要があります。

  • GOTO文は無条件に処理を分岐します。分岐先は文ラベルで指定します。LOOP文、REPEAT文およびWHILE文の先頭に分岐する場合は、これらの開始ラベルをGOTO文に指定します。また、開始ラベルを持たないその他のSQL手続き文に分岐する場合は、SQL手続き文の前に文ラベルを指定しておく必要があります。

  • WHENEVER文はSQL手続き文の実行時に例外事象が発生した場合にとる動作を指定します。GOTO句が指定された場合は、指定された分岐先へ分岐します。

注釈定義(COMMENT)

• 注釈は256バイト以内の文字列(日本語記述可能)を指定することができます。

ルーチン名

• 作成するプロシジャルーチンの名前を指定します。

• ルーチン名には、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。

• ルーチン名は、スキーマ内で一意の名前であることが必要です。

• プロシジャルーチン定義がスキーマ定義に含まれる場合、ルーチン名のスキーマ名の修飾を省略した場合は、スキーマ定義で指定したスキーマ名で修飾したとみなされます。また、スキーマ名で修飾する場合は、スキーマ定義で指定したスキーマであることが必要です。

パラメタ名

• パラメタ名には、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。

• パラメタ名にはSQL変数宣言のSQL変数名と同一名を指定することはできません。

SQL変数名

• SQL変数名には、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。

• SQL変数名にはパラメタ宣言のパラメタ名と同一名を指定することはできません。

• SQL変数名に“SQLSTATE”と“SQLMSG”を指定した場合、プロシジャルーチン内の例外事象は“SQLSTATE”と“SQLMSG”のSQL変数に設定されます。設定された値を呼出し元に返却するには、パラメタモードがOUTもしくはINOUTのパラメタに値を設定して、呼出し元のCALL文の引数に渡す必要があります。

文ラベル(開始ラベルおよび終了ラベル)

• 複合文の先頭の文ラベルは“開始ラベル”、終端の文ラベルは“終了ラベル”と呼びます。

• 文ラベルには、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。

• 開始ラベルと終了ラベルは同じラベル名を指定する必要があります。

• 開始ラベルを省略した場合、複合文の開始ラベルはルーチン名と仮定されます。したがって、終了ラベルにはルーチン名を指定します。

• ラベル名は、複合文内の他のラベル名と重複できません。

条件宣言

• 条件宣言は、特定のSQLSTATE値に条件名を定義することができます。

• 条件宣言は、有効範囲内である複合文中において、1つのSQLSTATE値には1つの条件宣言しか定義できません。

• 条件名に定義するSQLSTATE値は、0～9およびA～Zまでの任意の文字を5桁で指定します。不当な値を指定した場合、定義エラーとなります。

• 条件名に定義するSQLSTATE値に、例外コード00のSQLSTATE値を指定することはできません。

ハンドラ宣言

• 複合文中にハンドラ宣言とWHENEVER文の両方を指定することはできません。

• ハンドラ外のSQL制御文からハンドラ動作に指定された文ラベルに分岐することはできません。

• ハンドラ動作内にLEAVE文を指定してハンドラ動作を終了する場合、ハンドラ動作のBEGINからENDは文ラベルを省略することはできません。

• 条件値にSQLEXCEPTION, SQLWARNING または NOT FOUND を指定する場合、条件値リストにSQLSTATE値または条件名を指定することはできません。

• ハンドラ宣言の有効範囲内では、同じ条件を表現する条件値を他のハンドラ宣言に指定することはできません。

• 条件値リスト中に、同じ条件値を二度以上指定することはできません。

• 条件値リスト中に、同じ条件を表現するSQLSTATE値と条件名の両方とも指定することはできません。

• 条件値に指定するSQLSTATE値は、0～9およびA～Zまでの任意の文字を5桁で指定します。不当な値を指定した場合、定義エラーとなります。

• 条件値は、例外コード00のSQLSTATE値を指定することはできません。

条件名

• 条件名には、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。

条件値

• ハンドラ宣言を記述した複合文内において、条件値リストに指定された例外条件が発生すると、ハンドラが有効化され、ハンドラ動作に指定されたSQL手続き文が実行されます。

• 条件値にSQLEXCEPTIONが指定されると、複合文内のSQL手続き文の実行結果が例外コード00(正常終了)、02(データなし)、または01(警告)のいずれでもないとき、ハンドラが関連付けられます。

• 条件値にSQLWARNINGが指定されると、複合文内のSQL手続き文の実行結果が例外コード01(警告)のとき、ハンドラが関連付けられます。

• 条件値にNOT FOUNDが指定されると、複合文内のSQL手続き文の実行結果が例外コード02(データなし)のとき、ハンドラが関連付けられます。

• 条件値に条件名が指定されると、複合文内のSQL手続き文の実行で、条件名または条件名に指定されたSQLSTATE値と同じ例外が発生したとき、ハンドラが関連付けられます。

• 条件値にSQLSTATE値が指定されると、複合文内のSQL手続き文の実行結果が同一のSQLSTATE値のとき、ハンドラが関連付けられます。

• 条件値にSQLEXCEPTION, SQLWARNING, NOT FOUNDのいずれかを指定した場合、そのハンドラ宣言は一般ハンドラ宣言と呼びます。それ以外は特有ハンドラ宣言と呼びます。複合文中に同じ例外コードの値に対する一般ハンドラ宣言と特有ハンドラ宣言がある場合、特有ハンドラ宣言がその条件値に関連付けられます。

ハンドラ型

• ハンドラ型にCONTINUEが指定された場合、以下の手順で実行されます。

  1. ハンドラ動作に指定されたSQL手続き文が実行されます。

  2. ハンドラ動作終了時、ハンドラ動作が正常終了したときは、例外条件を引き起こしたSQL手続き文の次の文から処理を再開します。ハンドラ動作で新たに例外が発生した場合、プロシジャルーチンの実行を終了し、呼出し側に例外事象が通知されます。

• ハンドラ型にEXITが指定された場合、以下の手順で実行されます。

  1. ハンドラ動作に指定されたSQL手続き文が実行されます。

  2. ハンドラ動作終了後、ハンドラ動作が正常終了したときは、プロシジャルーチンの実行は正常終了します。ハンドラ動作で新たに例外が発生した場合、プロシジャルーチンの実行を終了し、呼出し側に例外事象が通知されます。

• 例外コードが40（ロールバック例外）の例外条件が発生した場合、ハンドラ型の指定に関わらず、プロシジャルーチンは処理を終了し、呼出し元のアプリケーションに復帰します。このとき、トランザクションはロールバックされます。

その他の構文要素

その他の構文要素の説明は、“3.22 CREATE TABLE文(表定義)”を参照してください。

使用例

例1

ルーチン“在庫追加”は、指定した倉庫内の製品の在庫数量を追加し、追加後の在庫数量を呼出し元に返却する処理を行います。処理の流れを以下に示します。

• 指定された製品の在庫数量に指定された数量を加えます。

• 指定製品が存在しない場合は、新たに製品情報を追加します。

• 更新後の在庫数量を取り出し、呼出し元に返却します。

  CREATE PROCEDURE 在庫管理.在庫追加(
    IN  対象番号    SMALLINT,
    IN  対象製品名  NCHAR(10),
    IN  対象倉庫    SMALLINT,
    IN  追加数量    INTEGER,
    OUT 結果数量    INTEGER,
    OUT PRCSTATE    CHAR(5),
    OUT PRCMSG      CHAR(255)
  )
  BEGIN
    DECLARE SQLSTATE     CHAR(5);
    DECLARE SQLMSG       CHAR(255);
    
    UPDATE S1.在庫表 SET 在庫数量 = 在庫数量 + 追加数量
      WHERE 製品番号 = 対象番号 AND 製品名 = 対象製品名
        AND 倉庫番号 = 対象倉庫;
    IF SQLSTATE = '02000' THEN
       INSERT INTO S1.在庫表
         VALUES(対象番号,対象製品名,対象倉庫,追加数量);
       IF SQLSTATE <> '00000' THEN
          GOTO ERR_END;
       END IF;
    ELSEIF SQLSTATE <> '00000' THEN
         GOTO ERR_END;
    END IF;
    
    SELECT 在庫数量 INTO 結果数量 FROM S1.在庫表
      WHERE 製品番号 = 対象番号 AND 製品名 = 対象製品名
             AND 倉庫番号 = 対象倉庫;
    IF SQLSTATE <> '00000' THEN
       GOTO ERR_END;
    END IF;
    
    LEAVE 在庫追加;(注1)
    
  ERR_END:
          SET PRCSTATE = SQLSTATE;(注2)
          SET PRCMSG   = SQLMSG;
  END

  注1) 複合文の開始ラベル省略時は、ルーチン名が文ラベルとなる。

  注2) 予想外のエラーが発生した場合は、そのエラー情報を返却する。

例2

ハンドラ型CONTINUEおよび条件宣言の使用例を以下に示します。

CREATE PROCEDURE SCM1.PRC1(
  IN 伝票番号    INTEGER,
  IN 顧客コード  INTEGER,
  IN 顧客名      CHAR(40)
)
BEGIN
  DECLARE SQLSTATE    CHAR(5);
  DECLARE SQLMSG      CHAR(256);
  DECLARE NUM,RETRY   INTEGER;
  -- 条件宣言
  DECLARE UNIQUE_VIOLATION CONDITION FOR SQLSTATE'23000';
  -- ハンドラ1宣言
  DECLARE CONTINUE HANDLER FOR UNIQUE_VIOLATION
  BEGIN -- 動作に複合文を指定
    SET NUM   = NUM + 1;
    SET RETRY = 1;  -- retry flag
  END;
  -- ハンドラ2宣言
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN  -- その他の例外用ハンドラ
    RESIGNAL;  -- 呼出し側に例外事象を通知
  END;
  
  -- ルーチン本文
   SET NUM = 伝票番号;
  
LABEL1:
  SET RETRY = 0;
  INSERT INTO SCM1.TBL04 VALUES(NUM, 顧客コード, 顧客名);
  IF (RETRY <> 0) THEN
    GOTO LABEL1;
  END IF;
END

例3

ハンドラ型EXITの使用例を以下に示します。

CREATE PROCEDURE SCM1.PRC1(
  IN 伝票番号    INTEGER,
  IN 顧客コード  INTEGER,
  IN 顧客名      CHAR(40)
)
BEGIN
  DECLARE SQLSTATE    CHAR(5);
  DECLARE SQLMSG      CHAR(256);
  DECLARE NUM,RETRY   INTEGER;
  -- ハンドラ1宣言
  DECLARE CONTINUE HANDLER FOR SQLSTATE'23000'
  BEGIN -- 動作に複合文を指定
    SET NUM   = NUM + 1;
    SET RETRY = 1;  -- retry flag
  END;
  -- ハンドラ2宣言
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  
  -- ルーチン本文
  SET NUM = 伝票番号;
LABEL1:
  SET RETRY = 0;
  INSERT INTO SCM1.TBL04 VALUES(NUM, 顧客コード, 顧客名);
  IF (RETRY <> 0) THEN
    GOTO LABEL1;
  END IF;
END