CREATE PROCEDURE (SQL) ステートメント

CREATE PROCEDURE (SQL) ステートメントは、現行サーバーで SQL プロシージャーを定義します。

呼び出し

このステートメントは、アプリケーション・プログラムに組み込んだり、動的 SQL ステートメントを使用して発行したりすることができます。これは、DYNAMICRULES の実行動作がパッケージに効力を持つ場合にのみ、動的に準備できる実行可能ステートメントです (SQLSTATE 42509)。

許可

ステートメントの許可 ID によって保持されている特権には、少なくとも以下のいずれかの権限が含まれていなければなりません。

プロシージャーの暗黙または明示のスキーマ名が存在しない場合、データベースに対する IMPLICIT_SCHEMA 権限
プロシージャーのスキーマ名が既存のスキーマを指している場合、スキーマに対する CREATEIN 特権
プロシージャーのスキーマ名が既存のスキーマを指している場合、スキーマに対する SCHEMAADM 権限
DBADM 権限

ステートメントの許可 ID によって保持される特権には、プロシージャー本体に指定されている SQL ステートメントを呼び出すために必要なすべての特権も含まれていなければなりません。

既存のプロシージャーを置換するには、ステートメントの許可 ID が既存のプロシージャーの所有者でなければなりません (SQLSTATE 42501)。

グループ特権は、CREATE PROCEDURE (SQL) ステートメントで指定された表やビューに対しては考慮されません。

構文

data-type

built-in-type

anchored-data-type

default-clause

option-list

SQL-procedure-body

注:

¹ FOR BIT DATA 節とその後に続く他の列制約とは、任意の順序で指定できます。FOR BIT DATA 節をストリング単位の CODEUNITS32 とともに指定することはできません (SQLSTATE 42613)。

説明

OR REPLACE

プロシージャーの定義が現行のサーバー上に存在している場合に、そのプロシージャーの定義を置換するために指定します。既存の定義は、新しい定義がカタログ内で置換される前に効率的にドロップされます。ただし、プロシージャーに対して付与された特権は影響を受けないという例外があります。このオプションは、オブジェクトの所有者しか指定できません。このオプションは、プロシージャーの定義が現行のサーバー上に存在しない場合は無視されます。既存のプロシージャーを置換するには、新規定義の特定名およびプロシージャー名が旧定義の特定名とプロシージャー名と同じであるか、または新規定義のシグニチャーが旧定義のシグニチャーと一致していなければなりません。それ以外の場合は、新規プロシージャーが作成されます。

procedure-name

定義するプロシージャーの名前を指定します。この名前は、プロシージャーを指定する修飾または非修飾の名前です。 procedure-name の非修飾形式は SQL ID です。動的 SQL ステートメントでは、CURRENT SCHEMA 特殊レジスターが、修飾子のないオブジェクト名の修飾子として使用されます。静的 SQL ステートメントでは、QUALIFIER プリコンパイル/BIND オプションによって、修飾子のないオブジェクト名の修飾子が暗黙指定されます。修飾形式は、schema-name の後にピリオドと SQL ID が続きます。

暗黙または明示の修飾子を含む名前と、パラメーターの数との組み合わせは、カタログに既に記述されているプロシージャーを指定するものであってはなりません (SQLSTATE 42723)。非修飾名とパラメーターの数との組み合わせは、そのスキーマ内ではユニークですが、複数のスキーマ間でユニークである必要はありません。

2 つの部分からなる名前を指定する場合、schema-name を SYS で始めることはできません。違反すると、エラーが戻されます (SQLSTATE 42939)。

(IN | OUT | INOUT parameter-name data-type default-clause,...)

プロシージャーのパラメーターを指定し、各パラメーターのモード、名前、データ・タイプ、およびオプションのデフォルト値を指定します。このリストには、プロシージャーが予期する各パラメーターごとに 1 つの項目を指定する必要があります。

パラメーターのないプロシージャーも登録可能です。この場合、指定するデータ・タイプがない場合でも、括弧はコーディングする必要があります。以下に例を示します。

   CREATE PROCEDURE SUBWOOFER() ...

1 つのスキーマに同じ名前の 2 つのプロシージャーがある場合、パラメーターの数をまったく同一にすることはできません。シグニチャーが重複していると、SQL エラー (SQLSTATE 42723) になります。

例えば、以下のステートメントの場合、

   CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

2 番目のステートメントは失敗します。その理由は、データ・タイプが異なっていてもプロシージャーのパラメーターの数が同じだからです。

IN | OUT | INOUT

パラメーターのモードを指定します。

プロシージャーによってエラーが戻される場合、OUT パラメーターは未定義で、INOUT パラメーターは未変更です。

IN: パラメーターをプロシージャーの入力パラメーターとして指定します。プロシージャー内でパラメーターに加えられるすべての変更は、制御が戻されると SQL アプリケーションの呼び出しは行えなくなります。デフォルトは IN です。
OUT: パラメーターをプロシージャーの出力パラメーターとして指定します。
INOUT: パラメーターを、プロシージャーの入力および出力パラメーターの両方として指定します。

parameter-name

パラメーターの名前を指定します。パラメーター名は、プロシージャーでユニークでなければなりません (SQLSTATE 42734)。

data-type

パラメーターのデータ・タイプを指定します。構造化タイプまたは参照タイプを指定することはできません (SQLSTATE 429BB)。

built-in-type

組み込みデータ・タイプを指定します。BOOLEAN および CURSOR (表には指定できない) を除く各組み込みデータ・タイプの詳細な説明は、『CREATE TABLE』を参照してください。

BOOLEAN: Boolean を示します。
CURSOR: 基礎となるカーソルへの参照を示します。

anchored-data-type

データ・タイプを定義するために使用される別のオブジェクトを指定します。アンカー・オブジェクトのデータ・タイプには、データ・タイプを直接指定する際に (行の場合は行タイプを作成する際に) 適用されるのと同じ制限があります。

ANCHOR DATA TYPE TO

データ・タイプの指定にアンカー・データ・タイプを使用することを示します。

variable-name

グローバル変数を指定します。グローバル変数のデータ・タイプが、parameter-name のデータ・タイプとして使用されます。

table-name.column-name

既存の表またはビューの列名を指定します。列のデータ・タイプが、parameter-name のデータ・タイプとして使用されます。

ROW OF table-name または view-name

table-name で識別される表、または view-name で識別されるビューの列名および列データ・タイプを基にした名前とデータ・タイプを含むフィールドの行になるように指定します。parameter-name のデータ・タイプは、名前の付いていない行タイプです。

ROW OF cursor-variable-name

cursor-variable-name で識別されるカーソル変数のフィールド名およびフィールド・データ・タイプを基にした名前とデータ・タイプを含めて、フィールドの行を指定します。指定するカーソル変数は、以下のいずれかのエレメントでなければなりません (SQLSTATE 428HS)。

厳密に型付けされたカーソル・データ・タイプのグローバル変数
すべての結果列が名前指定されている select-statement を指定した CONSTANT 節を使用して作成または宣言された、緩やかに型付けされたカーソル・データ・タイプのグローバル変数

カーソル変数のカーソル・タイプが、名前指定された行タイプを使用して厳密に型付けされていない場合、parameter-name のデータ・タイプは、名前なしの行タイプになります。

array-type-name: ユーザー定義の配列タイプの名前を指定します。array-type-name がスキーマ名なしで指定される場合、SQL パスでスキーマを検索することにより、配列タイプは解決されます。

cursor-type-name: カーソル・タイプの名前を指定します。cursor-type-name がスキーマ名なしで指定される場合、SQL パスでスキーマを検索することにより、カーソル・タイプは解決されます。

distinct-type-name: 特殊タイプの名前を指定します。パラメーターの長さ、精度、および位取りは、それぞれ特殊タイプのソース・タイプの長さ、精度、および位取りになります。特殊タイプのパラメーターは、特殊タイプのソース・タイプとして受け渡されます。 distinct-type-name がスキーマ名なしで指定される場合、SQL パスでスキーマを検索することにより、特殊タイプは解決されます。

row-type-name: ユーザー定義の行タイプの名前を指定します。パラメーターのフィールドは、行タイプのフィールドです。 row-type-name がスキーマ名なしで指定される場合、SQL パスでスキーマを検索することにより、行タイプは解決されます。

DEFAULT

パラメーターのデフォルト値を指定します。デフォルト値は、定数、特殊レジスター、グローバル変数、式、またはキーワード NULL にすることができます。デフォルトとして指定できる特殊レジスターは、列のデフォルトに指定できる特殊レジスターと同じです (CREATE TABLE ステートメントの default-clause を参照)。他の特殊レジスターは、式を使用することによってデフォルトとして指定できます。

expression は、『式』で説明されているいずれかのタイプの式とすることができます。デフォルト値が指定されていない場合、パラメーターにデフォルト値がないため、対応する引数はプロシージャーの呼び出し時に省略できません。expression の最大サイズは 64K バイトです。

デフォルトの式は、SQL データを変更してはなりません (SQLSTATE 428FL または SQLSTATE 429BL)。式は、パラメーターのデータ・タイプに対して割り当ての互換性がなければなりません (SQLSTATE 42821)。

以下の状況では、デフォルトを指定できません。

INOUT または OUT パラメーターの場合 (SQLSTATE 42601)
ARRAY、ROW、または CURSOR タイプのパラメーターの場合 (SQLSTATE 429BB)

SPECIFIC specific-name

定義するプロシージャーのインスタンスに対する固有名を指定します。この特定名は、このソース派生プロシージャーを変更する場合、ドロップする場合、またはこのプロシージャーにコメントを付ける場合に使用することができます。これは、プロシージャーの呼び出しには使用できません。 specific-name の非修飾形式は SQL ID です。修飾形式は、schema-name の後にピリオドと SQL ID が続きます。暗黙または明示の修飾子も含めて、その名前が、アプリケーション・サーバーに存在する他のプロシージャー・インスタンスを指定するものであってはなりません。そうでない場合、エラー (SQLSTATE 42710) になります。

specific-name は、既存の procedure-name と同じにすることができます。

修飾子を指定しない場合、procedure-name に使用された修飾子が使用されます。修飾子を指定する場合は、 procedure-name の明示修飾子または暗黙修飾子と同じでなければなりません。そうでない場合、エラー (SQLSTATE 42882) になります。

specific-name の指定がない場合、固有名がデータベース・マネージャーによって生成されます。生成される固有名は、'SQL' の後に文字タイム・スタンプが続く名前です ('SQLyymmddhhmmssxxx')。

GET ROUTINE コマンドを使ってプロシージャーをアーカイブする予定の場合、specific-name の最大文字数が 18 文字であることを確認してください。

DYNAMIC RESULT SETS integer

プロシージャーから戻される結果セットの上限の見積もりを指定します。

MODIFIES SQL DATA、CONTAINS SQL、READS SQL DATA

このプロシージャーで実行できる SQL ステートメント、またはこのプロシージャーから呼び出されるすべてのルーチンで実行できる SQL ステートメントの種別を指定します。データベース・マネージャーによって、プロシージャーで実行される SQL ステートメント、およびプロシージャーから呼び出されるすべてのルーチンで実行される SQL ステートメントが、この指定と整合しているかどうかが検査されます。

各ステートメントの種別については、ルーチンおよびトリガー内で実行できる SQL ステートメントを参照してください。

デフォルトは MODIFIES SQL DATA です。

MODIFIES SQL DATA: このプロシージャーは、プロシージャーでサポートされていないステートメント以外のすべての SQL ステートメントを実行できることを指定します (SQLSTATE 38003 または 42985)。
CONTAINS SQL: データ・アクセス種別が CONTAINS SQL であるステートメントのみをプロシージャーで実行できるように指定します (SQLSTATE 38003、38004、または 42985)。
READS SQL DATA: データ・アクセス種別が READS SQL DATA または CONTAINS SQL であるステートメントをプロシージャーで実行できるように指定します (SQLSTATE 38002、38003、または 42985)。

コンパウンド SQL プロシージャー内で BEGIN ATOMIC 節を使用した場合、MODIFIES SQL DATA として定義しない限り、このプロシージャーを作成することはできません。

DETERMINISTIC または NOT DETERMINISTIC

この節は、同一の引数値に対してプロシージャーが常に同じ結果を戻すか (DETERMINISTIC)、それとも状態値に依存してプロシージャーの結果が影響を受けるか (NOT DETERMINISTIC) を指定します。つまり DETERMINISTIC が指定されたプロシージャーは、連続で同じ入力を指定して呼び出した場合に常に同じ結果を戻します。

現在、この節はプロシージャーの処理に影響を与えません。

CALLED ON NULL INPUT

CALLED ON NULL INPUT は、プロシージャーに常に適用されます。これは、任意の引数が NULL かどうかにかかわりなく、プロシージャーが呼び出されることを意味します。 OUT または INOUT パラメーターは、NULL 値を戻す場合も、通常の (NULL 以外の) 値を戻す場合もあります。 NULL の引数値の有無のテストはプロシージャーで行う必要があります。

COMMIT ON RETURN

プロシージャーからの戻り時に、コミットを発行するかどうかを示します。デフォルトは NO です。

NO: プロシージャーからの戻り時に、コミットを発行しません。
YES: 正の SQLCODE が CALL ステートメントによって戻された場合は、プロシージャーからの戻り時にコミットが発行されます。

コミット操作には、呼び出し側のアプリケーション・プロセスとプロシージャーによって実行される処理が組み込まれています。

プロシージャーが結果セットを戻す場合、この結果セットに関連付けられているカーソルは、コミットの後に使用可能になるように WITH HOLD で定義されていなければなりません。

AUTONOMOUS

プロシージャーが、それ自体の自律型トランザクション・スコープ内で実行することを示します。

INHERIT SPECIAL REGISTERS

このオプション節は、プロシージャーの更新可能な特殊レジスターが、呼び出しステートメントの環境からの初期値を継承するよう指定します。ネストされたオブジェクト (例えば、トリガーまたはビュー) に呼び出されるルーチンの場合、初期値は (オブジェクト定義から継承するのではなく) ランタイム環境から継承します。

特殊レジスターに対する変更が、プロシージャーの呼び出し元に戻されることはありません。

更新不能の特殊レジスター (日時特殊レジスターなど) は、現在実行中のステートメントのプロパティーを反映するので、デフォルト値に設定されます。

OLD SAVEPOINT LEVEL または NEW SAVEPOINT LEVEL

このプロシージャーが、セーブポイント名と影響について新しいセーブポイント・レベルを設定するかどうかを指定します。 OLD SAVEPOINT LEVEL がデフォルトの動作です。セーブポイント・レベルについて詳しくは、『SAVEPOINT』の説明にある『規則』の項を参照してください。

LANGUAGE SQL

この節は、プロシージャー本体が SQL 言語に書き込まれるように指定するのに使用します。

EXTERNAL ACTION または NO EXTERNAL ACTION

プロシージャーが、データベース・マネージャーによって管理されていないオブジェクトの状態を変更するアクションを取るか (EXTERNAL ACTION)、または取らないか (NO EXTERNAL ACTION) を指定します。デフォルトは EXTERNAL ACTION です。 NO EXTERNAL ACTION を指定した場合、プロシージャーが外部に影響を与えないことを前提とした最適化を、システムは使用できます。

PARAMETER CCSID

プロシージャーとやり取りされるすべてのストリング・データに使用されるコード化スキームを指定します。 PARAMETER CCSID 節を指定しない場合のデフォルトは、 Unicode データベースでは PARAMETER CCSID UNICODE、他のすべてのデータベースでは PARAMETER CCSID ASCII になります。

ASCII: ストリング・データがデータベース・コード・ページでエンコードされることを指定します。データベースが Unicode データベースの場合は、 PARAMETER CCSID ASCII を指定することはできません (SQLSTATE 56031)。
UNICODE: 文字データは UTF-8 で記述され、GRAPHIC データは UCS-2 で記述されることを指定します。データベースが Unicode データベースでない場合は、 PARAMETER CCSID UNICODE は指定できません (SQLSTATE 56031)。

SQL-procedure-body

SQL プロシージャーの本体である SQL ステートメントを指定します。

『コンパウンド SQL (コンパイル済み)』ステートメントの SQL-procedure-statement を参照してください。

規則

自律型ルーチンの制約事項: 自律型ルーチンは、結果セットを戻すことができないため、以下のデータ・タイプをサポートしません (SQLSTATE 428H2)。
- ユーザー定義のカーソル・タイプ
- ユーザー定義構造化タイプ
- IN、OUT、および INOUT パラメーターとしての XML
自律型の有効範囲内でカーソル・タイプのセッション変数を参照することはできません。
アンカー・データ・タイプの使用: アンカー・データ・タイプは次のオブジェクトを参照できません (SQLSTATE 428HS): ニックネーム、型付き表、型付きビュー、式ベースの索引に関連付けられた統計ビュー、宣言済み一時表、緩やかに型付けされたカーソルに関連付けられた行定義、データベース・コード・ページまたはデータベース照合とは異なるコード・ページまたは照合を使用するオブジェクト。
カーソル・タイプと行タイプの使用: カーソル・タイプまたは行タイプのパラメーターを使用するプロシージャーは、コンパウンド SQL (コンパイル済み) ステートメントからのみ呼び出すことができます (SQLSTATE 428H2)。例外として、JDBC を使用する Java アプリケーションだけは、カーソル・タイプの OUT パラメーターを使用するプロシージャーを呼び出すことができます。Java 外部プロシージャーからの呼び出しはサポートされていません。

注

まだ存在していないスキーマ名を用いてプロシージャーを作成すると、ステートメントの許可 ID に IMPLICIT_SCHEMA 権限がある場合に限り、そのスキーマが暗黙に作成されます。スキーマの所有者は SYSIBM になります。スキーマに対する CREATEIN 特権が PUBLIC に付与されます。
コンパウンド SQL (インライン化) ステートメント内から呼び出されるプロシージャーは、プロシージャー作成時に OLD SAVEPOINT LEVEL が指定またはデフォルト設定されていたとしても、NEW SAVEPOINT LEVEL を指定して作成されたかのように実行されます。
最初は無効になっているプロシージャーの作成: プロシージャー本体で参照されるオブジェクトが存在しないか無効にマークされている場合、あるいは定義者に、そのオブジェクトへのアクセス権が一時的に付与されていない場合でも、データベース構成パラメーター auto_reval が DISABLED に設定されていなければ、プロシージャーは正常に作成されます。このプロシージャーは無効とマークされ、次回に呼び出されたときに再度有効性が確認されます。
デフォルト値の設定: デフォルト値で定義されたプロシージャーのパラメーターは、このプロシージャーの呼び出し時に、それらのデフォルト値に設定されますが、このプロシージャーの呼び出し時に、値が対応する引数に提供されていないか、または DEFAULT で指定されている場合にのみ、このように設定されます。
特権: プロシージャーの定義者は、プロシージャーに対する WITH GRANT OPTION 付きの EXECUTE 特権と、プロシージャーをドロップする権利を常に与えられます。
従属パッケージの再バインド: すべての SQL プロシージャーには、従属パッケージが存在します。このパッケージは、REBIND_ROUTINE_PACKAGE プロシージャーを実行していつでも再バインドすることができます。明示的に従属パッケージを再バインドしても、無効なプロシージャーの再有効化は行われません。無効なプロシージャーは、自動再有効化を使用するか、または明示的に ADMIN_REVALIDATE_DB_OBJECTS プロシージャーを実行して再有効化する必要があります。プロシージャーの再有効化では、自動的に従属パッケージが再バインドされます。
代替構文: 以下の構文は、製品間の互換性のためにのみサポートされています。これらの代替は非標準であり、使用すべきではありません。
- DYNAMIC RESULT SETS の代わりに RESULT SETS を指定できます。
- CALLED ON NULL INPUT の代わりに NULL CALL を指定できます。
以下の構文はデフォルトの振る舞いとして受け入れられます。
- ASUTIME NO LIMIT
- NO COLLID
- STAY RESIDENT NO

例

社員の給与の中央値を戻す SQL プロシージャーを作成します。給与の中央値を超える給与を得ている全社員の氏名、肩書き、および給与の入った結果セットを戻します。

   CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
     DECLARE v_numRecords INT DEFAULT 1;
     DECLARE v_counter INT DEFAULT 0;

     DECLARE c1 CURSOR FOR
       SELECT CAST(salary AS DOUBLE)
         FROM staff
         ORDER BY salary;
     DECLARE c2 CURSOR WITH RETURN FOR
       SELECT name, job, CAST(salary AS INTEGER)
         FROM staff
         WHERE salary > medianSalary
         ORDER BY salary;

     DECLARE EXIT HANDLER FOR NOT FOUND
       SET medianSalary = 6666;

     SET medianSalary = 0;
     SELECT COUNT(*) INTO v_numRecords
       FROM STAFF;
     OPEN c1;
     WHILE v_counter < (v_numRecords / 2 + 1)
     DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
     CLOSE c1;
     OPEN c2;
   END