このステートメントは、 ストアード・プロシージャーをアプリケーション・サーバーに登録する場合に使用されます。
呼び出し
このステートメントは、アプリケーション・プログラムに組み込むか、 または動的 SQL ステートメントを使用して発行することができます。 このステートメントは、動的に準備可能な実行可能ステートメントです。 しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、 ステートメントを動的に準備することはできません (SQLSTATE 42509)。
このステートメントの許可 ID には、以下の特権が少なくとも 1 つ含まれている必要があります。
非分離のストアード・プロシージャーを作成するには、 ステートメントの許可 ID の特権に以下の特権の少なくとも 1 つが含まれている必要があります。
分離ストアード・プロシージャーを作成する場合、追加の権限や特権は必要ありません。
許可 ID の権限が不十分で、操作を実行できない場合には、エラー (SQLSTATE 42502) になります。
構文
>>-CREATE PROCEDURE---------------------------------------------> >----procedure-name--(--+----------------------------------------------+---)-> | .-,---------------------------------------. | | V .-IN----. | | '----+-------+---parameter-name--data-type---+-' +-OUT---+ '-INOUT-' >----*----+--------------------------+--*-----------------------> '-SPECIFIC--specific-name--' .- DYNAMIC RESULT SETS 0--------. (1) >-----+-------------------------------+---------*---------------> '-DYNAMIC RESULT SETS--integer--' .-MODIFIES SQL DATA--. .-NOT DETERMINISTIC--. >-----+--------------------+--*----+--------------------+--*----> | (2) | '-DETERMINISTIC------' +-NO SQL-------------+ +-CONTAINS SQL-------+ '-READS SQL DATA-----' (3) .-CALLED ON NULL INPUT------. >----+---------------------------+---*--------------------------> >-----+-LANGUAGE--+-C-----+--*----| external-procedure-options |--*--+> | +-JAVA--+ | | +-COBOL-+ | | '-OLE---' | '-LANGUAGE--SQL---*----| SQL-procedure-body |------------------' >-------------------------------------------------------------->< external-procedure-options .-FENCED-----. |---*---EXTERNAL--+----------------------+--*----+------------+-> '-NAME--+-'string'---+-' '-NOT FENCED-' '-identifier-' >----*---PARAMETER STYLE--+-DB2DARI------------+--*-------------> | (4) | +-DB2GENERAL---------+ +-GENERAL------------+ +-GENERAL WITH NULLS-+ +-DB2SQL-------------+ '-JAVA---------------' .-NO DBINFO--. >-----+------------------------+--*----+------------+--*--------| '-PROGRAM TYPE--+-SUB--+-' '-DBINFO-----' '-MAIN-' SQL-procedure-body |---SQL-procedure-statement-------------------------------------|
注:
説明
暗黙または明示の修飾子を含む名前と、パラメーターの数との組み合わせは、 カタログにすでに記述されているプロシージャーを指定するものであってはなりません (SQLSTATE 42723)。 非修飾名とパラメーターの数との組み合わせは、そのスキーマ内では当然固有ですが、 複数のスキーマ間で固有である必要はありません。
2 つの部分から成る名前を指定する場合、 "SYS" で始まる schema-name (スキーマ名) は使用できません。 使用した場合、エラー (SQLSTATE 42939) になります。
パラメーターのないプロシージャーも登録可能です。 この場合、指定するデータ・タイプがない場合でも、括弧はコーディングする必要があります。 たとえば、
CREATE PROCEDURE SUBWOOFER() ...
1 つのスキーマに同じ名前の 2 つのプロシージャーがある場合、 パラメーターの数をまったく同一にすることはできません。 このタイプの比較では長さ、精度、および位取りは考慮されません。 したがって、CHAR(8) と CHAR(35)、また DECIMAL(11,2) と DECIMAL (4,3) は、 それぞれ同じタイプとみなされます。 さらに、DECIMAL と NUMERIC などのように、 この目的で複数のタイプが同じタイプとして扱われることがあります。 シグニチャーが重複していると、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 番目のステートメントは失敗します。 その理由は、データ・タイプが異なっていてもプロシージャーのパラメーターの数が同じであるからです。
specific-name は、既存の procedure-name と同じであっても構いません。
修飾子の指定がない場合、procedure-name に使用された修飾子が使用されます。 修飾子を指定する場合は、 procedure-name の明示または暗黙の修飾子と同じにする必要があります。 そうでない場合、エラー (SQLSTATE 42882) になります。
specific-name の指定がない場合、 固有の名前がデータベース・マネージャーによって生成されます。 生成される固有の名前は、 SQL の後に文字のタイム・スタンプが続く名前です (SQLyymmddhhmmsshhn)。
上位互換またはファミリーの互換性のために、 値 RESULT SETS を DYNAMIC RESULT SETS の同義語として使うことができます。
この後の表は、SQL ステートメント (第 1 列に指定されているもの) を、 指定された SQL データ・アクセス指示を使ってストアード・プロシージャーで実行できるかどうかを示します。 NO SQL と定義されたストアード・プロシージャー内に実行可能な SQL ステートメントが出現すると、 SQLSTATE 38001 が戻されます。 その他の実行コンテキストの場合、 どのコンテキストでもサポートされていない SQL ステートメントは SQLSTATE 38003 を戻します。 CONTAINS SQL コンテキスト内で使えないその他の SQL ステートメントの場合は SQLSTATE 38004 が戻され、 READS SQL DATA コンテキストの場合は SQLSTATE 38002 が戻されます。 SQL プロシージャーの作成時に SQL データ・アクセス指示に一致しないステートメントがあると、 SQLSTATE 42895 が戻されることになります。
表 21. SQL ステートメントと SQL データ・アクセス指示
SQL ステートメント | NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA |
---|---|---|---|---|
ALTER... | N | N | N | Y |
BEGIN DECLARE SECTION | Y(1) | Y | Y | Y |
CALL | N | Y(4) | Y(4) | Y(4) |
CLOSE CURSOR | N | N | Y | Y |
COMMENT ON | N | N | N | Y |
COMMIT | N | N | N | N |
COMPOUND SQL | N | Y | Y | Y |
CONNECT(2) | N | N | N | N |
CREATE | N | N | N | Y |
DECLARE CURSOR | Y(1) | Y | Y | Y |
DECLARE GLOBAL TEMPORARY TABLE | N | Y | Y | Y |
DELETE | N | N | N | Y |
DESCRIBE | N | N | Y | Y |
DISCONNECT(2) | N | N | N | N |
DROP ... | N | N | N | Y |
END DECLARE SECTION | Y(1) | Y | Y | Y |
EXECUTE | N | Y(3) | Y(3) | Y |
EXECUTE IMMEDIATE | N | Y(3) | Y(3) | Y |
EXPLAIN | N | N | N | Y |
FETCH | N | N | Y | Y |
FREE LOCATOR | N | Y | Y | Y |
FLUSH EVENT MONITOR | N | N | N | Y |
GRANT ... | N | N | N | Y |
INCLUDE | Y(1) | Y | Y | Y |
INSERT | N | N | N | Y |
LOCK TABLE | N | Y | Y | Y |
OPEN CURSOR | N | N | Y | Y |
PREPARE | N | Y | Y | Y |
REFRESH TABLE | N | N | N | Y |
RELEASE CONNECTION(2) | N | N | N | N |
RELEASE SAVEPOINT | N | N | N | Y |
RENAME TABLE | N | N | N | Y |
REVOKE ... | N | N | N | Y |
ROLLBACK | N | Y | Y | Y |
ROLLBACK TO SAVEPOINT | N | N | N | Y |
SAVEPOINT | N | N | N | Y |
SELECT INTO | N | N | Y | Y |
SET CONNECTION(2) | N | N | N | N |
SET INTEGRITY | N | N | N | Y |
SET 特殊レジスター | N | Y | Y | Y |
UPDATE | N | N | N | Y |
VALUES INTO | N | N | Y | Y |
WHENEVER | Y(1) | Y | Y | Y |
注:
LANGUAGE OLE は、 DB2 (Windows 32 ビット オペレーティング・システム版) に保管されているストアード・プロシージャーに対してのみサポートされます。
NAME 文節の指定がない場合、"NAME procedure-name" が想定されます。
'string' オプションは、最大 254 文字のストリング定数です。 ストリングに使用される形式は、指定した LANGUAGE によって異なります。
指定する string は、 ライブラリー名と作成しているストアード・プロシージャーを実行するために データベース・マネージャーが呼び出すそのライブラリー中のプロシージャーです。 ライブラリー (およびそのライブラリー中のプロシージャー) は、 CREATE PROCEDURE ステートメントの実行時に存在している必要はありません。 ただし、プロシージャーが呼び出される時点では、 該当のライブラリーとそのライブラリー中の該当のプロシージャーは存在していなければならず、 またデータベース・サーバーのマシンからアクセス可能でなければなりません。
>>-'--+-library_id-------+---+-------------+---'--------------->< '-absolute_path_id-' '-!--proc_id--'
名前は、単一引用符で囲む必要があります。 単一引用符内に、余分なブランクを使用することはできません。
UNIX 系システムの library_id が 'myproc' の場合に、 データベース・マネージャーが /u/production から実行されていれば、データベース・マネージャーは ライブラリー /u/production/sqllib/function/unfenced/myfunc と /u/production/sqllib/function/myfunc からプロシージャーを見つけます。
OS/2 の Windows 32 ビット オペレーティング・システムの場合、関数ディレクトリーで library_id が見つからないと、 データベース・マネージャーは LIBPATH または PATH を調べ、分離 (fenced) として実行されます。
これらのディレクトリーのいずれかに存在しているストアード・プロシージャーは、 登録済み属性を使用しません。
たとえば、UNIX 系システムの場合、'/u/jchui/mylib/myproc' を指定すると、 データベース・マネージャーは /u/jchui/mylib を調べて myproc プロシージャーを探索します。
OS/2 の場合、Windows 32 ビット オペレーティング・システムの 'd:\mylib\myproc' を指定すると、データベース・マネージャーは d:\mylib ディレクトリーから myproc.dll ファイルをロードします。
絶対パスを指定すると、プロシージャーは分離 (fenced) プロシージャーとして実行され、 FENCED または NOT FENCED の属性は無視されます。
たとえば、UNIX 系システムで 'mymod!proc8' を指定すると、 データベース・マネージャーはライブラリー $inst_home_dir/sqllib/function/mymod を調べて、 そのライブラリー内の入り口点 proc8 を使用します。
OS/2 の場合、Windows 32 ビット オペレーティング・システムの 'mymod!proc8' を指定すると、 データベース・マネージャーは mymod.dll ファイルをロードして、 そのダイナミック・リンク・ライブラリー (DLL) の proc8() 関数を呼び出します。
ストリングの形式が正しくない場合には、エラー (SQLSTATE 42878) になります。
ストアード・プロシージャーの本体は、 マウントされてデータベースのすべての区分で使用可能なディレクトリーに入っていなければなりません。
指定する string には、 作成中のストアード・プロシージャーを実行するためにデータベース・マネージャーが呼び出す、 任意指定の jar ファイル、クラス識別子、およびメソッド識別子が含まれています。 クラス識別子とメソッド識別子は、 CREATE PROCEDURE ステートメントの実行時には存在している必要はありません。 jar_id を指定する場合、識別子は、 CREATE PROCEDURE ステートメントの実行時に存在していなければなりません。 ただし、プロシージャーを呼び出す時点では、 該当のクラス識別子とメソッド識別子が存在し、 データベース・サーバーのマシンからアクセス可能でなければなりません。 そうでない場合、エラー (SQLSTATE 42884) になります。
>>-'----+----------+--class_id--+-.-+---method_id--'----------->< '-jar_id :-' '-!-'
名前は、単一引用符で囲む必要があります。 単一引用符内に、余分なブランクを使用することはできません。
指定するストリングは、 ステートメントが作成しているストアード・プロシージャーを実行するためにデータベース・マネージャーが 呼び出す OLE のプログラム識別子 (progid) またはクラス識別子 (clsid)、 およびメソッド識別子 (method_id) です。 プログラム識別子またはクラス識別子、およびメソッド識別子は、 CREATE PROCEDURE ステートメントの実行時に存在している必要はありません。 ただし、関数を CALL ステートメントで使用する時点で、 メソッド識別子は存在していなければならず、 データベース・サーバーのマシンからアクセス可能でなければなりません。 そうでない場合、エラー (SQLSTATE 42724) になります。
>>-'--+-progid-+---!--method_id--'----------------------------->< '-clsid--'
名前は、単一引用符で囲む必要があります。 単一引用符内に、余分なブランクを使用することはできません。
progid は、データベース・マネージャーには解釈されず、 実行時に OLE に転送されるだけです。 指定する OLE オブジェクトは、作成可能である必要があり、 実行時バインディング (ディスパッチに基づくバインディングとも呼ばれる) をサポートしている必要があります。 規約では、progid は次のような形式になります。
<program_name>.<component_name>.<version>
これは規約でしかなく、厳密な規則ではないので、 progids をこれとは異なる形式にしてもかまいません。
{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}
ここで 'n' は英数字です。 clsid は、データベース・マネージャーには解釈されず、 実行時に OLE API に転送されるだけです。
ストアード・プロシージャーが FENCED として登録されると、 データベース・マネージャーは、その内部資源 (データ・バッファーなど) を隔離して、 そのプロシージャーからアクセスされないようにします。 すべてのプロシージャーは、 FENCED として実行するか NOT FENCED として実行するかの選択が可能です。 一般に、FENCED として実行されるプロシージャーは、 NOT FENCED として実行されるものと同じようには実行されません。
ストアード・プロシージャーが .../sqllib/function/unfenced ディレクトリー および .../sqllib/function ディレクトリー (UNIX 系システム)、 または ...\instance_name\function\unfenced ディレクトリー および ...\instance_name\function ディレクトリー (OS/2、 Windows 32 ビット オペレーティング・システムの場合) に入っている場合、 FENCED または NOT FENCED の登録属性 (および他のすべての登録属性) は無視されます。
注: | 十分に検査されていないプロシージャーに NOT FENCED を使用すると、 DB2 の保全性に危険を招く場合があります。 DB2 では、発生する可能性のある一般的な不注意による障害の多くに対して、 いくつかの予防措置がとられていますが、 NOT FENCED ストアード・プロシージャーが使用される場合には、完全な保全性を確保できません。 |
FENCED から NOT FENCED に変更するには、 プロシージャーを削除して再作成して、再登録する必要があります。 ストアード・プロシージャーを NOT FENCED として登録するには、SYSADM 権限、 DBADM 権限、または特殊な権限 (CREATE_NOT_FENCED) が必要です。 LANGUAGE OLE を指定した関数には、FENCED のみを指定できます。
DB2GENERAL の同義語として値 DB2GENRL が使用可能です。
ヌル標識がプログラムに直接渡されることはありません。
GENERAL の同義語として値 SIMPLE CALL が使用可能です。
GENERAL WITH NULLS の同義語として値 SIMPLE CALL WITH NULLS が使用可能です。
これは、LANGUAGE C、COBOL、または OLE を使用する場合にだけ、指定することができます。
PARAMETER STYLE JAVA プロシージャーでは、 DBINFO または PROGRAM TYPE 文節はサポートされていません。
パラメーターの受け渡しの詳細については、アプリケーション開発の手引き を参照してください。
PROGRAM TYPE のデフォルトは SUB です。 PROGRAM TYPE MAIN は、LANGUAGE C または COBOL で、 なおかつ PARAMETER STYLE GENERAL、GENERAL WITH NULLS、または DB2SQL の場合だけ有効です。
現在、この文節はストアード・プロシージャーの処理に影響を与えません。
値 NULL CALL は、上位互換またはファミリーの互換性のために、 CALLED ON NULL INPUT の同義語として使うことができます。
DBINFO を指定すると、以下の情報を含む構造がストアード・プロシージャーに渡されます。
構造の詳細、および構造がストアード・プロシージャーにどのようにして渡されるかについては、 アプリケーション開発の手引き を参照してください。
注
例
例 1: Java で書かれたストアード・プロシージャーのプロシージャー定義を作成します。 このプロシージャーは、パーツ番号を渡されて、パーツの価格と現在入手可能な数量を戻します。
CREATE PROCEDURE PARTS_ON_HAND (IN PARTNUM INTEGER, OUT COST DECIMAL(7,2), OUT QUANTITY INTEGER) EXTERNAL NAME 'parts.onhand' LANGUAGE JAVA PARAMETER STYLE JAVA
例 2: C で書かれたストアード・プロシージャーのプロシージャー定義を作成します。 このプロシージャーは、アセンブリー番号を渡されて、 アセンブリーを構成するパーツの数とパーツの合計価格、およびパーツ番号、数量、 各パーツの単価をリストする結果セットを戻します。
CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 NOT FENCED LANGUAGE C PARAMETER STYLE GENERAL
例 3: 社員の給与の中央値を戻す 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