ストアード・プロシージャーを含めたアプリケーション設計は、別個のクライアントおよびサーバー・アプリケーションから構成されます。ストアード・プロシージャー と呼ばれるサーバー・アプリケーションは、サーバー上の共用ライブラリーまたはクラス・ライブラリーに含まれています。ストアード・プロシージャーは、データベースが存在するサーバー・インスタンスでコンパイルおよびアクセスする必要があります。クライアント・アプリケーション には、ストアード・プロシージャーへの CALL ステートメントが含まれています。 CALL ステートメントにより、ストアード・プロシージャーにパラメーターを渡したり、そこからパラメーターを戻したりできます。ストアード・プロシージャーおよびクライアント・アプリケーションは、別の言語で作成することができます。クライアント・アプリケーションは、ストアード・プロシージャーとは別のプラットフォームで実行できます。
クライアント・アプリケーションは、以下の作業を実行します。
注: | ストアード・プロシージャーは COMMIT または ROLLBACK ステートメントを出すことができますが、クライアント・アプリケーションが COMMIT または ROLLBACK を出すようにすることをお勧めします。これにより、クライアント・アプリケーションがストアード・プロシージャーによって戻されるデータを評価できるようにし、トランザクションをコミットするかロールバックするかを決定できます。 |
なお、上記のいずれのステップにおいても SQL ステートメントをコード化できます。
ストアード・プロシージャーが呼び出されると、以下の作業が実行されます。
注: | ストアード・プロシージャーは COMMIT または ROLLBACK ステートメントを出すことができますが、クライアント・アプリケーションが COMMIT または ROLLBACK ステートメントを出すようにすることをお勧めします。これにより、クライアント・アプリケーションがストアード・プロシージャーによって戻されるデータを評価できるようにし、トランザクションをコミットするかロールバックするかを決定できます。 |
ストアード・プロシージャーは、クライアント・アプリケーションに呼び出されると実行されます。サーバー・プロシージャーが処理を終了すると、制御はクライアントに戻されます。複数のストアード・プロシージャーを 1 つのライブラリーに入れておくことができます。
この章では、以下のパラメーター・スタイルを使って、ストアード・プロシージャーを作成する方法を説明します。
前述のパラメーター・スタイルについて、 CREATE PROCEDURE ステートメントを使って各ストアード・プロシージャーを登録する必要があります。 CREATE PROCEDURE ステートメントは、ストアード・プロシージャーごとに、プロシージャー名、引き数、位置、およびパラメーター・スタイルを指定します。パラメーター・スタイルを指定することにより、 DB2 ファミリー間でのストアード・プロシージャー・コードの移植性とスケーラビリティーが向上します。
DB2 ユニバーサル・データベースのバージョン 6 以前の DB2 バージョンのみでサポートされているストアード・プロシージャーのパラメーター・スタイル (DB2DARI および DB2GENERAL) を使用する場合、 付録 C, DB2DARI および DB2GENERAL ストアード・プロシージャーと UDF を参照してください。
クライアント・アプリケーションは、ストアード・プロシージャーを呼び出す前にいくつかのステップを実行します。まずデータベースに接続し、 SQLDA 構造またはホスト変数の宣言、割り振り、初期化を行う必要があります。 SQL CALL ステートメントは、一連のホスト変数または SQLDA 構造を受け入れることができます。 SQL CALL ステートメントおよび SQLDA 構造の詳細については、 SQL 解説書 を参照してください。クライアント・アプリケーションでの SQLDA 構造の使用については、 付録 C, DB2DARI および DB2GENERAL ストアード・プロシージャーと UDF を参照してください。
必要な入力ホスト変数をストアード・プロシージャーのクライアント側で割り振るための手順を以下に示します。
ストアード・プロシージャーのクライアント部分を作成する際には、ホスト変数を入出力の両方に使用することによって、可能なかぎり多くのホスト変数を多重定義するようにします。これにより、複数のホスト変数を処理するための効率が高くなります。たとえば、SQLCODE をクライアントからストアード・プロシージャーに戻す場合には、 INTEGER として宣言された入力ホスト変数を使用して SQLCODE を戻すようにしてください。
注: | これらの構造の記憶域は、データベース・サーバー上に割り振らないでください。データベース・マネージャーは、クライアント・アプリケーションが割り振った記憶域に基づいて自動的に重複記憶域を割り振ります。ストアード・プロシージャー側の入出力パラメーターに対する記憶域ポインターを変えないでください。ポインターをローカルに作成された記憶域ポインターと置き換えようとすると、 SQLCODE -1133 (SQLSTATE 39502) のエラーが発生します。 |
データベースのロケーションに保管されているストアード・プロシージャーは、 SQL の CALL ステートメントを使用して呼び出すことができます。 CALL ステートメントの詳細な説明については、SQL 解説書 を参照してください。ストアード・プロシージャーを呼び出す場合、CALL ステートメントを使用することをお勧めします。
クライアント・アプリケーションは、ストアード・プロシージャーを呼び出す前に、データベース接続が行われたかどうかを確認しなければなりません。そうしないと、エラーが戻されます。データベース接続およびデータ構造の初期化の後、クライアント・アプリケーションがストアード・プロシージャーを呼び出し、要求されるデータをすべて渡します。アプリケーションはデータベースから切り離されます。なお、上記のいずれのステップにおいても SQL ステートメントをコード化できます。
ストアード・プロシージャーは、SQL CALL ステートメントにより呼び出され、クライアント・アプリケーションによって渡されたデータを使用して実行されます。データベース・マネージャーのストアード・プロシージャーを CREATE PROCEDURE ステートメントに登録する際のパラメーター・スタイルによって、ストアード・プロシージャーがクライアント・アプリケーションからデータを受け取る方法が決まります。
CREATE PROCEDURE ステートメントを使用するには、以下を宣言しなければなりません。
CREATE PROCEDURE では以下も宣言しなければなりません。
CREATE PROCEDURE ステートメントについては、 SQL 解説書 の中からさらに多くの情報を得ることができます。そこでは、DB2 ファミリーとの互換性を考慮した完全な構文やオプションを記載しています。 CREATE PROCEDURE ステートメントの一般的な使用法は以下のとおりです。
固有の数のパラメーターを受け入れるプロシージャーに同じ名前を使用することによってのみ、ストアード・プロシージャーを多重定義することができます。 DB2 はデータ・タイプを区別しないため、パラメーターのデータ・タイプに基づいてストアード・プロシージャーを多重定義することはできません。
たとえば、次の 2 つの CREATE PROCEDURE ステートメントを発行すると、それぞれ 1 つのパラメーターと 2 つのパラメーターを受け入れるため、有効です。
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER) ... CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER, IN VAR2 INTEGER) ...
一方、次の例では 2 番目のストアード・プロシージャーは登録されません。なぜなら、同じ名前の最初のストアード・プロシージャーとパラメーターの数が同じだからです。
CREATE PROCEDURE OVERLOADFAIL (IN VAR1 INTEGER) ... CREATE PROCEDURE OVERLOADFAIL (IN VAR2 VARCHAR(15)) ...
明示パラメーター は、 CREATE PROCEDURE ステートメントのパラメーター・リストで明示的に宣言するパラメーターです。暗黙パラメーター は、 DB2 によって自動的に提供されるパラメーターです。たとえば、 PARAMETER STYLE GENERAL WITH NULLS ストアード・プロシージャーは、明示パラメーターのヌル標識の配列を自動的に提供します。ストアード・プロシージャーを作成する際には、ストアード・プロシージャーの明示パラメーターおよび暗黙パラメーターの両方を考慮する必要があります。クライアント・アプリケーションを作成する際には、ストアード・プロシージャーの明示パラメーターを処理するだけで済みます。明示パラメーターはすべて IN、OUT、または INOUT パラメーターのいずれかとして宣言する必要があり、名前と SQL データ・タイプを一緒に宣言する必要があります。 CREATE PROCEDURE ステートメントの例については、 変数宣言と CREATE PROCEDURE の例を参照してください。
CREATE PROCEDURE ステートメントの EXTERNAL 文節により、データベース・マネージャーはストアード・プロシージャーを含んでいるライブラリーの位置を認識します。ライブラリーの絶対パス (Java ストアード・プロシージャーの場合は jar 名) を指定しない場合、データベース・マネージャーは関数ディレクトリー を検索します。 関数ディレクトリー とは、オペレーティング・システムに定義されたディレクトリーで、以下のようなものです。
instance_name\function にストアード・プロシージャーが見つからない場合、 DB2 は PATH および LIBPATH 環境変数で定義されたディレクトリーを検索します。
たとえば、 DB2 が C:\sqllib ディレクトリーにインストールされており、 DB2INSTPROF レジストリーが設定されていない Windows 32 ビットの関数ディレクトリーは、次のようになります。
C:\sqllib\function
注: | ライブラリー名にはストアード・プロシージャー名とは異なる名前を指定してください。 DB2 は、検索パスで同じ名前のライブラリーを見つけると、そのライブラリーと同じ名前のストアード・プロシージャーを FENCED DB2DARI プロシージャーとして実行します。 |
C 言語のストアード・プロシージャーの場合は、以下のように指定してください。
UNIX ベースのシステムでは、たとえば mymod!proc8 と指定すると、データベース・マネージャーは sqllib/function/mymod ライブラリーを指し、そのライブラリー内のエントリー・ポイント proc8 を使用します。 Windows 32 ビットおよび OS/2 オペレーティング・システムでは、 mymod!proc8 と指定すると、データベース・マネージャーは関数ディレクトリーから mymod.dll ファイルをロードし、ダイナミック・リンク・ライブラリー (DLL) 内の proc8() プロシージャーを呼び出します。
LANGUAGE JAVA ストアード・プロシージャーの場合は、以下の構文を使用してください。
[<jar-file-name>:]<class-name>.<method-name>
次のリストは Java ストアード・プロシージャーの EXTERNAL キーワードを定義します。
たとえば、MyPackage.MyClass.myMethod を指定する場合、データベース・マネージャーは、 MyPackage パッケージ内の MyClass クラスにある myMethod メソッドを使用します。ここで、コロン (:) 区切り文字の代わりに、ピリオド (.) 区切り文字が使用されているため、 DB2 は MyPackage が jar ファイルではなくてパッケージであることを認識します。 DB2 は、関数ディレクトリーで MyPackage パッケージを検索します。
関数ディレクトリーの詳細については、 位置を参照してください。
C/C++ の場合は、CREATE PROCEDURE ステートメントで LANGUAGE C を宣言します。 Java ストアード・プロシージャーの場合は、LANGUAGE JAVA を宣言します。 Windows 32 ビット オペレーティング・システム上で OLE ストアード・プロシージャーを使用する場合には、 LANGUAGE OLE を宣言します。 COBOL ストアード・プロシージャーの場合は、LANGUAGE COBOL を宣言します。 Fortran または REXX ストアード・プロシージャーの場合は、 DB2DARI ストアード・プロシージャーとして作成する必要があります。 DB2DARI ストアード・プロシージャーの作成の詳細については、付録 C, DB2DARI および DB2GENERAL ストアード・プロシージャーと UDF を参照してください。
PROGRAM TYPE SUB の C ストアード・プロシージャーは、引き数をサブルーチンとして受け入れます。数値データ・タイプ・パラメーターをポインターとして受け渡します。文字データ・タイプを適切な長さの配列として渡します。たとえば、次のような C ストアード・プロシージャーのシグニチャーは、 INTEGER、SMALLINT、および CHAR(3) タイプのパラメーターを受け入れます。
int storproc (sqlint32 *arg1, short *arg2, char arg[4])
Java のストアード・プロシージャーは、サブルーチンとしてしか引き数を受け入れません。 IN パラメーターは単純な引き数として渡します。 OUT および INOUT パラメーターは、単一要素の配列として渡します。たとえば、次のような Java のストアード・プロシージャーのシグニチャーは、 INTEGER タイプの IN パラメーター、SMALLINT タイプの OUT パラメーター、および CHAR(3) タイプの INOUT パラメーターを受け入れます。
int storproc (int arg1, short arg2[], String arg[])
C プログラムの main 関数のような引き数を受け入れるようにストアード・プロシージャーを作成するには、 CREATE PROCEDURE ステートメントでプログラム・タイプに MAIN を指定します。プログラム・タイプが MAIN のストアード・プロシージャーを作成する場合は、以下の仕様に準拠していなければなりません。
PROGRAM TYPE MAIN ストアード・プロシージャーでは、 DB2 は argv 配列の最初の要素の値 (argv[0]) をストアード・プロシージャーの名前に設定します。 argv 配列の残りの要素は、ストアード・プロシージャーの CREATE PROCEDURE ステートメントで宣言されるパラメーターと対応します。たとえば、次のような組み込み型の C ストアード・プロシージャーは、 argv[1] という 1 つの IN パラメーターを渡し、 argv[2] および argv[3] という 2 つの OUT パラメーターを戻します。
PROGRAM TYPE MAIN の例の CREATE PROCEDURE ステートメントは、次のようになります。
CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE, OUT errorcode INTEGER) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED READS SQL DATA PROGRAM TYPE MAIN EXTERNAL NAME 'spserver!mainexample'
次に示すストアード・プロシージャーのコード例では、 argv[1] の値を CHAR(8) のホスト変数 injob にコピーしてから、 SQLCODE を argv[3] として戻します。
EXEC SQL BEGIN DECLARE SECTION; char injob[9]; double outsalary; EXEC SQL END DECLARE SECTION; SQL_API_RC SQL_API_FN main_example (int argc, char **argv) { EXEC SQL INCLUDE SQLCA; /* argv[0] contains the procedure name, so parameters start at argv[1] */ strcpy (injob, (char *)argv[1]); EXEC SQL SELECT AVG(salary) INTO :outsalary FROM employee WHERE job = :injob; memcpy ((double *)argv[2], (double *)&outsalary, sizeof(double)); memcpy ((sqlint32 *)argv[3], (sqlint32 *)&SQLCODE, sizeof(sqlint32)); return (0); } /* end main_example function */
表 9 では、DB2 バージョン 7 の CREATE PROCEDURE ステートメントで使用できる、パラメーター・スタイル (横軸) と言語 (縦軸) の組み合わせを要約しています。
表 9. CREATE PROCEDURE: パラメーター・スタイルと言語の有効な組み合わせ
GENERAL, GENERAL WITH NULLS | JAVA | DB2SQL | DB2DARI | DB2GENERAL | |
---|---|---|---|---|---|
LANGUAGE C | Y | N | Y | Y | N |
LANGUAGE JAVA | N | Y | N | N | Y |
LANGUAGE OLE | N | N | Y | N | N |
LANGUAGE COBOL | Y | N | Y | N | N |
DB2 ユニバーサル・データベース (OS/390 版) の互換性の注意: GENERAL は SIMPLE と同等です。
PARAMETER STYLE GENERAL ストアード・プロシージャーは、 PROGRAM TYPE 文節の値で指示されている方法でパラメーターを受け入れます。次の例では、 PROGRAM TYPE SUBROUTINE を使用して 2 つのパラメーターを受け入れる PARAMETER STYLE GENERAL ストアード・プロシージャーを示します。
SQL_API_RC SQL_API_FN one_result_set_to_client (double *insalary, sqlint32 *out_sqlerror) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; EXEC SQL BEGIN DECLARE SECTION; double l_insalary; EXEC SQL END DECLARE SECTION; l_insalary = *insalary; *out_sqlerror = 0; EXEC SQL DECLARE c3 CURSOR FOR SELECT name, job, CAST(salary AS INTEGER) FROM staff WHERE salary > :l_insalary ORDER BY salary; EXEC SQL OPEN c3; /* Leave cursor open to return result set */ return (0); /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end one_result_set_to_client function */
DB2 ユニバーサル・データベース (OS/390 版) の互換性の注意: GENERAL WITH NULLS は、SIMPLE WITH NULLS と同様のものです。
PARAMETER STYLE GENERAL WITH NULLS というストアード・プロシージャーは、 PROGRAM TYPE 文節の値で指示されている方法でパラメーターを受け入れ、ヌル標識の配列で宣言されている各パラメーターに対して 1 つの要素を割り当てます。次の SQL は、 PROGRAM TYPE SUB を使用して、 INOUT パラメーター 1 つと OUT パラメーター 2 つを渡す PARAMETER STYLE GENERAL WITH NULLS ストアード・プロシージャーを登録します。
CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE, OUT errorCode INTEGER, OUT errorLabel CHAR(32)) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE GENERAL WITH NULLS NO DBINFO FENCED MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'spserver!inout_param'
次の C コードは、 GENERAL WITH NULLS ストアード・プロシージャーによって必要とされるヌル標識を宣言および使用する方法を示します。
SQL_API_RC SQL_API_FN inout_param (double *inoutMedian, sqlint32 *out_sqlerror, char buffer[33], sqlint16 nullinds[3]) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; if (nullinds[0] < 0) { /* NULL value was received as input, so return NULL output */ nullinds[0] = -1; nullinds[1] = -1; nullinds[2] = -1; } else { int counter = 0; *out_sqlerror = 0; medianSalary = *inoutMedian; strcpy(buffer, "DECLARE inout CURSOR"); EXEC SQL DECLARE inout CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM staff WHERE salary > :medianSalary ORDER BY salary; nullinds[1] = 0; nullinds[2] = 0; strcpy(buffer, "SELECT COUNT INTO numRecords"); EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff WHERE salary > :medianSalary; if (numRecords != 0) /* At least one record was found */ { strcpy(buffer, "OPEN inout"); EXEC SQL OPEN inout USING :medianSalary; strcpy(buffer, "FETCH inout"); while (counter < (numRecords / 2 + 1)) { EXEC SQL FETCH inout INTO :medianSalary; *inoutMedian = medianSalary; counter = counter + 1; } strcpy(buffer, "CLOSE inout"); EXEC SQL CLOSE inout; } else /* No records were found */ { /* Return 100 to indicate NOT FOUND error */ *out_sqlerror = 100; } } return (0); /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end inout_param function */
sqlint16 nullinds[n], (1) char sqlst[6], (2) char qualname[28], (3) char specname[19], (4) char diagmsg[71], (5)
DB2 はストアード・プロシージャーに以下の引き数を渡します。
DB2SQL は、LANGUAGE C または LANGUAGE COBOL オプションも指定している場合に限り、指定できます。たとえば、次の CREATE PROCEDURE ステートメントは、 PARAMETER STYLE DB2SQL ストアード・プロシージャーを登録します。
CREATE PROCEDURE DB2SQL_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE DB2SQL NO DBINFO FENCED READS SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'spserver!db2sqlexample'
次の規則を使用してストアード・プロシージャーを作成します。
たとえば、次の組み込み型の C ストアード・プロシージャーは、 PARAMETER STYLE DB2SQL ストアード・プロシージャーのコーディングを示します。
SQL_API_RC SQL_API_FN db2sql_example ( char injob[9], /* Input - CHAR(8) */ double *salary, /* Output - DOUBLE */ sqlint16 nullinds[2], char sqlst[6], char qualname[28], char specname[19], char diagmsg[71] ) { EXEC SQL INCLUDE SQLCA; if (nullinds[0] < 0) { /* NULL value was received as input, so return NULL output */ nullinds[1] = -1; /* Set custom SQLSTATE to return to client. */ strcpy(sqlst, "38100"); /* Set custom message to return to client. */ strcpy(diagmsg, "Received null input on call to DB2SQL_EXAMPLE."); } else { EXEC SQL SELECT (CAST(AVG(salary) AS DOUBLE)) INTO :outsalary INDICATOR :outsalaryind FROM employee WHERE job = :injob; *salary = outsalary; nullinds[1] = outsalaryind; } return (0); } /* end db2sql_example function */
次の組み込み型 C クライアント・アプリケーションは、 DB2SQL_EXAMPLE ストアード・プロシージャーを呼び出す CALL ステートメントを出します。この例では、CALL ステートメントの各パラメーターに対してヌル標識が含まれていることに注目してください。この例では in_jobind ヌル標識が 0 に設定されて、非 NULL 値が IN パラメーター (in_job というホスト変数で表される) のストアード・プロシージャーに渡されていることが示されています。 OUT パラメーターのヌル標識が -1 に設定されて、これらのパラメーターのストアード・プロシージャーには入力が渡されていないことが示されます。
int db2sqlparm(char out_lang[9], char job_name[9]) { int testlang; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for passing data to DB2SQL_EXAMPLE */ char in_job[9]; sqlint16 in_jobind; double out_salary = 0; sqlint16 out_salaryind; EXEC SQL END DECLARE SECTION; /********************************************************\ * Call DB2SQL_EXAMPLE stored procedure * \********************************************************/ testlang = strncmp(out_lang, "C", 1); if (testlang != 0) { /* Only LANGUAGE C procedures can be PARAMETER STYLE DB2SQL, so do not call the DB2SQL_EXAMPLE stored procedure */ printf("\nStored procedures are not implemented in C.\n" "Skipping the call to DB2SQL_EXAMPLE.\n"); } else { strcpy(procname, "DB2SQL_EXAMPLE"); printf("\nCALL stored procedure named %s\n", procname); /* out_salary is an OUT parameter, so set the null indicator to -1 to indicate no input value */ out_salaryind = -1; strcpy(in_job, job_name); /* in_job is an IN parameter, so check to see if there is any input value */ if (strlen(in_job) == 0) { /* in_job is null, so set the null indicator to -1 to indicate there is no input value */ in_jobind = -1; printf("with NULL input, to return a custom SQLSTATE and diagnostic message\n"); } else { /* in_job is not null, so set the null indicator to 0 to indicate there is an input value */ in_jobind = 0; } /* DB2SQL_EXAMPLE is PS DB2SQL, so pass a null indicator for each parameter */ EXEC SQL CALL :procname (:in_job:in_jobind, :out_salary:out_salaryind); /* DB2SQL stored procedures can return a custom SQLSTATE and diagnostic message, so instead of using the EMB_SQL_CHECK macro to check the value of the returned SQLCODE, check the SQLCA structure for the value of the SQLSTATE and the diagnostic message */ /* Check value of returned SQLSTATE */ if (strncmp(sqlca.sqlstate, "00000", 5) == 0) { printf("Stored procedure returned successfully.\n"); printf("Average salary for job %s = %9.2f\n", in_job, out_salary); } else { printf("Stored procedure failed with SQLSTATE %s.\n", sqlca.sqlstate); printf("Stored procedure returned the following diagnostic message:\n"); printf(" \"%s\"\n", sqlca.sqlerrmc); } } return 0; }
移植性を向上させるために、Java ストアード・プロシージャーは、 PARAMETER STYLE JAVA 規則を使って作成してください。 DB2GENERAL パラメーター・スタイルのストアード・プロシージャーの作成については、 付録 C, DB2DARI および DB2GENERAL ストアード・プロシージャーと UDF を参照してください。
DB2 ファミリー間での移植性を向上させるために、 LANGUAGE C ストアード・プロシージャーは GENERAL または GENERAL WITH NULLS パラメーター・スタイルを使って作成してください。 DB2DARI パラメーター・スタイルのストアード・プロシージャーを作成する場合は、 付録 C, DB2DARI および DB2GENERAL ストアード・プロシージャーと UDF を参照してください。
LANGUAGE C ストアード・プロシージャーで、パラメーター・タイプに GENERAL、 GENERAL WITH NULLS、または DB2SQL を指定している場合、追加のパラメーターを受け入れるようにストアード・プロシージャーを作成することもできます。 CREATE PROCEDURE ステートメントで DBINFO を指定すると、呼び出しパラメーターとともに、 DB2 クライアントに関する情報を含む DBINFO 構造をストアード・プロシージャーに渡すように、クライアント・アプリケーションに指示することができます。 DBINFO 構造には以下の値が含まれます。
DBINFO 構造の詳細については、DBINFO 構造を参照してください。
以下の例では、SAMPLE データベースを用いた仮定的なシナリオで使用する、ストアード・プロシージャーのソース・コードと CREATE PROCEDURE ステートメントを具体的に示します。
Java ストアード・プロシージャー GET_LASTNAME を作成するとします。これには empno (SQL タイプ VARCHAR) が与えられており、 SAMPLE データベースの EMPLOYEE 表から lastname (SQL タイプ CHAR) を戻します。 Java クラス StoredProcedure の getname メソッドとして、プロシージャーを作成します。このクラスは myJar という名前でインストールされた JAR ファイルに含まれています。最後に、C でコード化されたクライアント・アプリケーションで、ストアード・プロシージャーを呼び出します。
String empid; String name; ... #sql { SELECT lastname INTO :empid FROM employee WHERE empno=:empid }
CREATE PROCEDURE GET_LASTNAME (IN EMPID CHAR(6), OUT NAME VARCHAR(15)) EXTERNAL NAME 'myJar:StoredProcedure.getname' LANGUAGE JAVA PARAMETER STYLE JAVA FENCED READS SQL DATA
EXEC SQL BEGIN DECLARE SECTION; struct name { short int; char[15] } char[7] empid; EXEC SQL END DECLARE SECTION; ... EXEC SQL CALL GET_LASTNAME (:empid, :name);
次の例では、C ストアード・プロシージャー GET_MANAGER を作成するとします。これには deptnumb (SQL タイプ SMALLINT) が与えられており、 SAMPLE データベースの ORG 表から manager (SQL タイプ SMALLINT) を戻します。
EXEC SQL BEGIN DECLARE SECTION; short onevar = 0; EXEC SQL END DECLARE SECTION;
CREATE PROCEDURE GET_MANAGER (INOUT onevar SMALLINT) EXTERNAL NAME 'stplib!getman' LANGUAGE C PARAMETER STYLE GENERAL FENCED READS SQL DATA
short onevar = 0; ... #SQL { CALL GET_MANAGER (:INOUT onevar) };
ストアード・プロシージャーには SQL ステートメントを含めることができます。 CREATE PROCEDURE ステートメントを発行する際に、ストアード・プロシージャーに含まれている SQL ステートメントがあれば、そのタイプを指定する必要があります。ストアード・プロシージャーを登録する際に値を指定しない場合、データベース・マネージャーは MODIFIES SQL DATA を使用します。ストアード・プロシージャーで使用されている SQL のタイプを制限するには、以下の 4 つのオプションのいずれかを使用できます。
CREATE PROCEDURE ステートメントの詳細については、 SQL 解説書 を参照してください。
ネストされた ストアード・プロシージャーとは、別のストアード・プロシージャーを呼び出すものを指します。 DB2 アプリケーションでは、この技法の使用に関して次のような制約事項があります。
ネストされた SQL プロシージャーは、 1 つまたは複数の結果セットをクライアント・アプリケーションまたは呼び出し元プロシージャーに戻すことができます。 SQL プロシージャーからの結果セットをクライアント・アプリケーションに戻すには、 WITH RETURN TO CLIENT 文節を使用して DECLARE CURSOR ステートメントを出します。 SQL プロシージャーからの結果セットを呼び出し元に戻すには (ここで、呼び出し元はクライアント・アプリケーションまたは呼び出し元のストアード・プロシージャーを表す)、 WITH RETURN TO CALLER 文節を使用して DECLARE CURSOR ステートメントを出します。
ネストされた組み込み SQL ストアード・プロシージャー (C で作成されたもの) およびネストされた CLI ストアード・プロシージャーは、結果セットをクライアント・アプリケーションまたは呼び出し元のストアード・プロシージャーに戻すことはできません。ストアード・プロシージャーが終了するときに、ネストされた組み込み SQL ストアード・プロシージャーまたはネストされた CLI ストアード・プロシージャーが、ストアード・プロシージャーの終了時にカーソルを開いたままにする場合、 DB2 がカーソルをクローズします。ストアード・プロシージャーから結果セットを戻す方法についての詳細は、 ストアード・プロシージャーからの結果セットの戻りを参照してください。
ストアード・プロシージャーを作成する際には、以下の制約事項に従う必要があります。
OLE (オブジェクトのリンクと埋め込み) オートメーションは、 Microsoft Corporation の OLE 2.0 アーキテクチャーの一部です。 DB2 は、OLE オートメーション・オブジェクトの方式を外部ストアード・プロシージャーとして呼び出すことができます。 OLE オートメーションについては、 OLE オートメーション UDF の作成を参照してください。
OLE オートメーション・オブジェクトのコード化が終わったら、 CREATE PROCEDURE ステートメントを使用して、そのオブジェクトのメソッドをストアード・プロシージャーとして登録する必要があります。 OLE 自動化ストアード・プロシージャーを登録するには、 LANGUAGE OLE 文節付きの CREATE PROCEDURE ステートメントを発行します。外部名は、OLE 自動化オブジェクトを識別する OLE progID とメソッド名を ! (感嘆符) で区切った形になります。
次の CREATE PROCEDURE ステートメントは、 OLE オートメーション・オブジェクト "db2smpl.salary" にある "median" 方式の "median" という自動化ストアード・プロシージャーを登録します。
CREATE PROCEDURE median (INOUT sal DOUBLE) EXTERNAL NAME 'db2smpl.salary!median' LANGUAGE OLE FENCED PARAMETER STYLE DB2SQL
OLE メソッド・インプリメンテーションの呼び出し規則は、 C や C++ で作成された関数の呼び出し規則と同一です。
DB2 は、 SQL タイプと OLE オートメーション・タイプの間でタイプ変換を自動的に処理します。サポートされている OLE オートメーション・タイプおよび SQL タイプの間の DB2 マッピングのリストについては、 表 16 を参照してください。 SQL タイプから BASIC や C/C++ などの OLE プログラム言語への DB2 マッピングについては、 表 17 を参照してください。
DB2 と OLE オートメーション・ストアード・プロシージャーの間で受け渡しされるデータは、参照呼び出しとして受け渡しされます。 DB2 は、以前に参照された表に載っていない、 DECIMAL または LOCATORS などの SQL タイプ、ブールや CURRENCY などの OLE オートメーション・タイプを、サポートしません。 BSTR にマップされる文字とグラフィック・データは、データベース・コード・ページから UCS-2 (Unicode としても知られている、 IBM コード・ページ 13488) スキーマに変換されます。戻される際に、データはデータベース・コード・ページに変換し直されます。これらの変換は、データベース・コード・ページに関係なく起こります。データベース・コード・ページから UCS-2 に、および UCS-2 からデータベース・コード・ページに変換するコード・ページ変換テーブルがインストールされていない場合、 SQLCODE -332 (SQLSTATE 57017) を受け取ります。
以下に、OUT ホスト変数の使用方法を示すサンプル・プログラムを挙げます。クライアント・アプリケーションは、 SAMPLE データベース中の従業員の給与の中央値を調べるストアード・プロシージャーを呼び出します。 (中央値の定義は、値の半分がその上下にあるというものです。) 次にその給与の中央値は、OUT ホスト変数を使用してクライアント・アプリケーションに戻されます。
このサンプル・プログラムは、SAMPLE データベース中のすべての従業員の給与の中央値を計算します。中央値を計算する既存の SQL 列関数がないため、給与の中央値は以下のようなアルゴリズムによって繰り返し計算することができます。
ストアード・プロシージャーの技法およびブロック・カーソルのいずれも使用しないアプリケーションは、 図 5 で示されているように、ネットワークを経由して各給与を取り出さなければなりません。
図 5. ストアード・プロシージャーを使用しない場合の中央値の例
![]() |
第 n / 2 + 1 行の給与のみが必要であるため、アプリケーションはそれ以外のデータをすべて廃棄しますが、それはネットワークを経由して伝送された後のことです。
ストアード・プロシージャー技法を使用するアプリケーションは、ストアード・プロシージャーが不要なデータを処理したり破棄し、給与の中央値だけをクライアント・アプリケーションに戻すように設計できます。 図 6 ではこの機能が示されています。
図 6. ストアード・プロシージャーを使用する場合の OUT パラメーターの例
![]() |
OUT クライアントの説明は、 OUT ホスト変数クライアント・アプリケーションとストアード・プロシージャーの例を示します。 Java では以下のようなサンプル・プログラムを使用できます。
C では以下のようなサンプル・プログラムを使用できます。
CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラミング言語により異なります。
このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。
import java.sql.*; // JDBC classes (1) import java.math.BigDecimal; // BigDecimal support for packed decimal type class Spclient { static String sql = ""; static String procName = ""; static String inLanguage = ""; static CallableStatement callStmt; static int outErrorCode = 0; static String outErrorLabel = ""; static double outMedian = 0; static { try { System.out.println(); System.out.println("Java Stored Procedure Sample"); Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance(); } catch (Exception e) { System.out.println("\nError loading DB2 Driver...\n"); e.printStackTrace(); } } public static void main(String argv[]) { Connection con = null; // URL is jdbc:db2:dbname String url = "jdbc:db2:sample"; try { // connect to sample database // connect with default id/password con = DriverManager.getConnection(url); (2) // turn off autocommit con.setAutoCommit(false); (3) outLanguage(con); outParameter(con); inParameters(con); inoutParam(con, outMedian); resultSet(con); twoResultSets(con); allDataTypes(con); // rollback any changes to the database con.rollback(); (8) con.close(); } catch (Exception e) { try { con.close(); } catch (Exception x) { } e.printStackTrace (); } } // end main public static void outParameter(Connection con) throws SQLException { // prepare the CALL statement for OUT_PARAM procName = "OUT_PARAM"; sql = "CALL " + procName + "(?, ?, ?)"; callStmt = con.prepareCall(sql); // register the output parameter (4) callStmt.registerOutParameter (1, Types.DOUBLE); callStmt.registerOutParameter (2, Types.INTEGER); callStmt.registerOutParameter (3, Types.CHAR); // call the stored procedure (5) System.out.println ("\nCall stored procedure named " + procName); callStmt.execute(); // retrieve output parameters (6) outMedian = callStmt.getDouble(1); outErrorCode = callStmt.getInt(2); outErrorLabel = callStmt.getString(3); if (outErrorCode == 0) { (7) System.out.println(procName + " completed successfully"); System.out.println ("Median salary returned from OUT_PARAM = " + outMedian); } else { // stored procedure failed System.out.println(procName + " failed with SQLCODE " + outErrorCode); System.out.println(procName + " failed at " + outErrorLabel); } } }
#include <stdio.h> (1) #include <stdlib.h> #include <sql.h> #include <sqlda.h> #include <sqlca.h> #include <string.h> #include "utilemb.h" EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variable for stored procedure name */ char procname[254]; /* Declare host variables for stored procedure error handling */ sqlint32 out_sqlcode; (4) char out_buffer[33]; EXEC SQL END DECLARE SECTION; int main(int argc, char *argv[]) { EXEC SQL CONNECT TO sample; (2) EMB_SQL_CHECK("CONNECT TO SAMPLE"); outparameter(); EXEC SQL ROLLBACK; EMB_SQL_CHECK("ROLLBACK"); printf("\nStored procedure rolled back.\n\n"); /* Disconnect from Remote Database */ EXEC SQL CONNECT RESET; (8) EMB_SQL_CHECK("CONNECT RESET"); return 0; } int outparameter() { /********************************************************\ * Call OUT_PARAM stored procedure * \********************************************************/ EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for passing data to OUT_PARAM */ double out_median; EXEC SQL END DECLARE SECTION; strcpy(procname, "OUT_PARAM"); printf("\nCALL stored procedure named %s\n", procname); /* OUT_PARAM is PS GENERAL, so do not pass a null indicator */ EXEC SQL CALL :procname (:out_median, :out_sqlcode, :out_buffer); (5) (6) EMB_SQL_CHECK("CALL OUT_PARAM"); /* Check that the stored procedure executed successfully */ if (out_sqlcode == 0) (7) { printf("Stored procedure returned successfully.\n"); /***********************************************************\ * Display the median salary returned as an output parameter * \***********************************************************/ printf("Median salary returned from OUT_PARAM = %8.2f\n", out_median); } else { /* print the error message, roll back the transaction */ printf("Stored procedure returned SQLCODE %d\n", out_sqlcode); printf("from procedure section labelled \"%s\".\n", out_buffer); } return 0; }
import java.sql.*; // JDBC classes import COM.ibm.db2.jdbc.app.*; // DB2 JDBC classes import java.math.BigDecimal; // Packed Decimal class public class Spserver { public static void outParameter (double[] medianSalary, int[] errorCode, String[] errorLabel) throws SQLException (1) { try { int numRecords; int counter = 0; errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs // Get caller's connection to the database Connection con = DriverManager.getConnection("jdbc:default:connection"); errorLabel[0] = "GET CONNECTION"; String query = "SELECT COUNT(*) FROM staff"; errorLabel[0] = "PREPARE COUNT STATEMENT"; PreparedStatement stmt = con.prepareStatement(query); errorLabel[0] = "GET COUNT RESULT SET"; ResultSet rs = stmt.executeQuery(); // move to first row of result set rs.next(); // set value for the output parameter errorLabel[0] = "GET NUMBER OF RECORDS"; numRecords = rs.getInt(1); (3) // clean up first result set rs.close(); stmt.close(); // get salary result set query = "SELECT CAST(salary AS DOUBLE) FROM staff " + "ORDER BY salary"; errorLabel[0] = "PREPARE SALARY STATEMENT"; PreparedStatement stmt2 = con.prepareStatement(query); errorLabel[0] = "GET SALARY RESULT SET"; ResultSet rs2 = stmt2.executeQuery(); (2) while (counter < (numRecords / 2 + 1)) { errorLabel[0] = "MOVE TO NEXT ROW"; rs2.next(); (4) counter++; } errorLabel[0] = "GET MEDIAN SALARY"; medianSalary[0] = rs2.getDouble(1); (5) // clean up resources rs2.close(); stmt2.close(); con.close(); (6) } catch (SQLException sqle) { errorCode[0] = sqle.getErrorCode(); } } }
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlda.h> #include <sqlca.h> #include <sqludf.h> #include <sql.h> #include <memory.h> /* Declare function prototypes for this stored procedure library */ SQL_API_RC SQL_API_FN out_param (double *, sqlint32 *, char *); (1) EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for basic error-handling */ sqlint32 out_sqlcode; char buffer[33]; /* Declare host variables used by multiple stored procedures */ sqlint16 numRecords; double medianSalary; EXEC SQL END DECLARE SECTION; SQL_API_RC SQL_API_FN out_param (double *outMedianSalary, sqlint32 *out_sqlerror, char buffer[33]) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; int counter = 0; *out_sqlerror = 0; strcpy(buffer, "DECLARE c1"); EXEC SQL DECLARE c1 CURSOR FOR (2) SELECT CAST(salary AS DOUBLE) FROM staff ORDER BY salary; strcpy(buffer, "SELECT"); EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff; (3) strcpy(buffer, "OPEN"); EXEC SQL OPEN c1; strcpy(buffer, "FETCH"); while (counter < (numRecords / 2 + 1)) { EXEC SQL FETCH c1 INTO :medianSalary; (4) /* Set value of OUT parameter to host variable */ *outMedianSalary = medianSalary; (5) counter = counter + 1; } strcpy(buffer, "CLOSE c1"); EXEC SQL CLOSE c1; return (0); (6) /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end out_param function */
コード・ページに関する考慮事項は、サーバーにより異なります。
クライアント・プログラム (たとえばコード・ページ A を使用している) が、別のコード・ページ (たとえば、コード・ページ Z) を使用しているデータベースにアクセスするリモート・ストアード・プロシージャーを呼び出すと、以下のことが起きます。
そのため、データベースと同じコード・ページを使ってストアード・プロシージャーを実行しなければなりません。 すなわち、この例ではコード・ページ Z を使用しなければなりません。データベースと同じコード・ページを使ってサーバー・プロシージャーをプリコンパイル、コンパイル、そしてバインドしなければなりません。
注: | ストアード・プロシージャーのパラメーターがサーバーで FOR BIT DATA として定義されている場合、 SQLDA に明示的に指定されるかどうかにかかわりなく、 DB2 ユニバーサル・データベース (OS/390 版) または DB2 ユニバーサル・データベース (AS/400 版) への CALL ステートメントについては、変換は行われません。 (詳細については、SQL 解説書 にある SQLDA の項を参照してください。) |
このトピックの詳細については、異なるコード・ページ間での変換を参照してください。
ストアード・プロシージャーを C++ 言語で作成する場合には、以下の例のように extern "C" を使用してプロシージャー名を宣言できます。
extern "C" SQL_API_RC SQL_API_FN proc_name( short *parm1, char *parm2)
extern "C" は、C++ コンパイラーによる関数名のタイプ修飾 (すなわちマングル) を防止します。この宣言の場合を除き、ストアード・プロシージャーを呼び出す際には、関数名のタイプ修飾も組み込む必要があります。
パラメーター入力や出力によってグラフィック・データを送受信する、 C または C++ で書かれたストアード・プロシージャーはすべて、通常 WCHARTYPE NOCONVERT オプションを指定してプリコンパイルしなければなりません。これは、そのようなパラメーターによって渡されるグラフィック・データが、 wchar_t 処理コード形式ではなく、DBCS 形式であるとみなされるからです。 NOCONVERT を使用すると、ストアード・プロシージャー内の SQL ステートメントで操作されるグラフィック・データも DBCS 形式であるとみなされ、パラメーター・データの形式と一致します。
WCHARTYPE NOCONVERT を使用すると、グラフィック・ホスト変数とデータベース・マネージャーの間では文字変換は起こりません。グラフィック・ホスト変数を用いたデータは、無変換の DBCS 文字としてデータベース・マネージャーに送受信されます。 WCHARTYPE NOCONVERT を使用しなくても、ストアード・プロシージャー内の wchar_t 形式のグラフィック・データを操作できますが、入出力変換は手動で実行しなければなりません。
CONVERT は、FENCED ストアード・プロシージャー内で使用することができ、ストアード・プロシージャーのインターフェースを介してではなく、ストアード・プロシージャー内で、SQL ステートメント内のグラフィック・データに影響を及ぼします。 NOT FENCED ストアード・プロシージャーは、必ず NOCONVERT オプションを使用して作成してください。
要約すると、入力または出力パラメーターによってストアード・プロシージャーに渡したり、ストアード・プロシージャーから送られてくるグラフィック・データは、 WCHARTYPE オプションによってどのようにプリコンパイルされたかに関係なく、 DBCS 形式になります。
C アプリケーションにおけるグラフィック・データの処理については、 C および C++ でのグラフィック・ホスト変数の処理を参照してください。 EUC コード・セットおよびアプリケーションのガイドラインについては、 日本語および中国語 (繁体字) EUC および UCS-2 コード・セットに関する考慮事項、およびストアード・プロシージャーに関する考慮事項を参照してください。
アプリケーションが CONNECT TYPE 2 を使用して呼び出すストアード・プロシージャーは、動的にも静的にも COMMIT または ROLLBACK を発行できません。