EXECUTE CALL コマンドを使って、ストアド・プロシージャを実行します。
このストアド・プロシージャにはSQLステートメントが含まれます。この CALL コマンドは、*QUERY、*UPDATE、または*EXECUTE のいずれかのタイプを指定する CALLTYPE コマンドと共に使用されます。
コマンド | キーワード | 値 | 開発者用メモ |
|---|---|---|---|
EXECUTE | CALL | 値 | 必須。ストアド・プロシージャの名前。 |
CALLTYPE | *QUERY | 任意。ストアド・プロシージャで使用する SQL ステートメントのタイプを指定します。 | |
*UPDATE | ストアド・プロシージャの SQL ステートメントが更新、挿入または削除の場合、CALL によって (更新されたレコード数の) 行数が #JSMMSG フィールドに返されます。 | ||
*EXECUTE | このキーワードを指定した場合、ストアド・プロシージャでは何も返されません。 | ||
CALLSYNTAX | *JDBC | 任意。標準の JDBC 構文を使用することを示します。 | |
*ORACLE | Oracle の JDBC 構文を使用することを示します。 | ||
RETURN | *NONE | 任意。ストアド・プロシージャ/ファンクションからの戻り値のデータ・タイプを指定します。 値は何も返されません。 | |
*CHAR | |||
*STRING | |||
*SMALLINT | |||
*INTEGER | |||
*FLOAT | |||
*DOUBLE | |||
*DECIMAL | |||
*NUMERIC | |||
*ORACLECURSOR | 返される値は、Oracle クエリーの結果セットにアクセスする際に代替として使用されるカーソルになります。 |
パラメータは、作業リストやSET PARAMETER(*LIST)コマンドを使用してリモート・プロシージャに渡すことができます。この方法の詳細については、下記「リストと変数」を参照してください。
ストアド・プロシージャは結果セットの代替として単一の値を返すことも可能です。結果セットは、クエリーから返される1つ以上のレコードや値のリストですが、代替として返される値は、完了時にストアド・プロシージャが返す単一の値です。典型的なストアド・プロシージャ構文には、ストアド・プロシージャで実行する最後のコマンドの 1 つとして、RETURN またはこれに類するコマンド (ターゲット・データベースの構文により異なる) と関連フィールド名があります。この値は何でも構いませんが、一般的な例としては、選択または更新されたレコード数、最大値、フラグなどを含むフィールドがあります。
RETURN キーワードでは、返す値のデータ・タイプを指定できます。可能なデータ・タイプは、ターゲット・データベースとJDBCドライバーでサポート可能なコンテンツによって異なります。例えば、IBM i で現在サポートされるのは、ストアド・プロシージャから返される値のデータ・タイプ Integer のみになります。最終的には、ターゲット・データベースとJDBCドライバーで対応可能なデータ・タイプをユーザーの責任において把握してください。
ストアド・プロシージャ呼び出しによっては、実行されたことを伝える警告が返されるため、SET ONWARNING(*CONTINUE) コマンドを使用して警告チェックを無効にする必要があります。警告のエラーコード 466 (結果セットはプロシージャから利用可能) は自動的に無視され、警告として扱われません。
ストアド・プロシージャの作成例は後半に紹介されています。
Oracleデータベースは、データベース・ファンクションから値を返す際に使用される場合、JDBC構文を完全にサポートするわけではありません。Oracle構文でのみ機能します。これは、CALLSYNTAXを*ORACLEとして指定する必要があるためです。その結果、SQLServiceでは予想される事態とその対処方法を把握します。
Oracleには2種類の呼び出し可能なプログラムがあります。1つはストアド・プロシージャ、もう1つはファンクションです。ストアド・プロシージャは値を返すことができないため、値を返したい場合はファンクションを使用する必要があります。
Oracle JDBC ドライバーと Oracle データベースでは、Java JDBC の「"ResultSet resultSet = call.executeQuery ()"」メソッドをサポートしません。Oracle で結果セットを JDBC クライアントに返す場合、Oracle カーソルを返す Oracle ファンクションを作成する必要があり、JDBC の call.execute () を使用する必要があります。ターゲット・データベースが Oracle で、結果セットがストアド・プロシージャから返されると予想される場合、この EXECUTE コマンドの RETURN 値として *ORACLECURSOR を指定する必要があります。これはSQLServiceで使用され、どのレコードを返すかを決定します。カーソルが返されることを示すこの明示的な要件は、Oracleのみに関連します。他のデータベースでは、結果セットの取得に関してこのキーワードを使用する必要はありません。キーワードでこの値を使用する場合、カーソルが SQLService に返されるように Oracle ファンクションで 'RETURN cursorname' コマンドを使用する必要があります。
以下に、結果セットを作成するOracleデータベース・ファンクションの例を示します。カーソルが返されることに注意してください。
本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、他の媒体でこのトピックについて詳しく調べることをお勧めします。
CREATE OR REPLACE FUNCTION "SYSTEM"."TEST" ( PARAM1 IN CHAR, PARAM2 IN CHAR ) RETURN SYS_REFCURSOR AS CURSOR_1 SYS_REFCURSOR ;BEGINOPEN CURSOR_1 FOR SELECT * FROM MYTABLE WHERE FLD_1='AB';RETURN CURSOR_1 ;END;
SQLService を使用してこのファンクションを実行する場合は、次の EXECUTE コマンドのようなコマンドが使用できます。:
SET PARAMETER(*LIST) #WRKLST(PARM1,PARAM2)EXECUTE CALL("TEST(?,?)") CALLTYPE(*EXECUTE) CALLSYNTAX(*ORACLE) RETURN(*ORACLECURSOR) #WRLST(COL1,COL2)
IBM i データベースのストアド・プロシージャを作成するには、ソース・メンバーを編集し、メンバー・タイプ TXT を使用する必要があります。
以下に、IBM i の 3 つのストアド・プロシージャの例を示します。最初の行は、ストアド・プロシージャの名前を指定することに注意してください。例えば、最初の行はCALLSELECTと呼ばれ、その場所はJSMJDBCライブラリになります。2番目と3番目の例は、値が返される状況を示しています。
本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、このトピックについて詳しく調べることをお勧めします。IBM InfocentreおよびRedbooksにはこのトピックに関する情報が豊富にあるため、これらの参照から始めると良いかもしれません。どちらもIBM Webサイトで入手できます。
****************** データ開始 *************************************CREATE PROCEDURE JSMJDBC/CALLSELECT(IN CODE CHAR (10))LANGUAGE SQLREADS SQL DATARESULT SETS 1 BEGINDECLARE SQLCODE INTEGER DEFAULT 0;DECLARE SQLSTATE CHAR (5) DEFAULT '00000'; DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT ID,NAME,AGE FROM JSMJDBC/TBLNAME WHERE ID = CODE; OPEN C1; END****************** データ終了 ************************************* ****************** データ開始 *************************************CREATE PROCEDURE JSMJDBC/CALLUPDATE(IN CODE CHAR (10))LANGUAGE SQLMODIFIES SQL DATA BEGINDECLARE SQLCODE INTEGER DEFAULT 0;DECLARE SQLSTATE CHAR (5) DEFAULT '00000';DECLARE num_records INTEGER; UPDATE JSMJDBC/TBLNAME SET SALARY=12000.43 WHERE ID = CODE; GET DIAGNOSTICS num_records = ROW_COUNT; RETURN num_records; END
****************** データ終了 ************************************* ****************** データ開始 *************************************CREATE PROCEDURE JSMJDBC/CALLEXECUT(IN CODE CHAR (10))LANGUAGE SQLMODIFIES SQL DATA BEGINDECLARE SQLCODE INTEGER DEFAULT 0;DECLARE SQLSTATE CHAR (5) DEFAULT '00000';DECLARE num_flag INTEGER DEFAULT 0; UPDATE JSMJDBC/TBLNAME SET SALARY=16000.26 WHERE ID = CODE; RETURN num_flag; END
****************** データ終了 *************************************
ストアド・プロシージャを作成するには、RUNSQLSTM コマンドを使用する必要があります。
例：
RUNSQLSTM SRCFILE(JSMJDBC/QCLSRC) SRCMBR(CALLSELECT) COMMIT(*NONE)
これらのストアド プロシージャを SQLService で実行する場合は、次のような EXECUTE コマンドを使用できます。
SET PARAMETER(*LIST) #WRKLST(PARM1)EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY)
SET PARAMETER(*LIST) #WRKLST(PARM1)EXECUTE CALL("CALLUPDATE(?)") CALLTYPE(*UPDATE) RETURN(*INTEGER)
SET PARAMETER(*LIST) #WRKLST(PARM1)EXECUTE CALL("CALLEXECUT(?)") CALLTYPE(*EXECUTE) RETURN(*INTEGER)
QUERYキーワード、SQLステートメントがSELECTの場合のPREPAREDキーワード、またはCALLTYPEに*QUERYが指定されたCALLキーワードのいずれかを使用する場合、結果セットが返されるようにするには、このコマンドで作業リストを提供します。
この作業リストには、クエリーから返されると予想されるフィールドが含まれます。リストはフィールドの順序で埋められます。SELECTステートメントは、このリストに示されるよりも多くのフィールドを実際に返すことができますが、列よりも多いリスト・フィールドは返されません。列の値は、resultSet.getString (列インデックス)メソッドを使用して結果セットから受け取ります。リスト・エントリー・フィールドはこの文字列値で設定され、Javaデータ・タイプからネイティブ・データ・タイプへの変換が行われます。
非常に大きなリストが返されると予想される場合、この作業リストを省略し、READコマンドを使用して結果セットにアクセスできます。
UPDATEキーワードは結果セットを返さないため、この状況で作業リストを指定する必要はありません。
CALLでCALLTYPEに*UPDATEを指定した場合、行数(更新されたレコードの数)が返されるため、この値を取得するには1列のリストを指定できます。
呼び出したプロシージャ(CALLコマンドを使用)にパラメータを渡す必要がある場合、パラメータを含む作業リストを指定する必要があります。この作業リストには、渡す必要があるパラメータと同じだけの列があります。作業リストの最初の行のみが使用されます。リストは、SET PARAMETER(*LIST)コマンドを使用して準備する必要があります。
この仕組みについての詳細は、以下の例と 5.31.12 SQLServiceの例 を参照してください。
SQLServiceサービスのSET、EXECUTE、READの各コマンドは、それぞれ緊密に関連しています。
* JSM コマンドおよびメッセージ・フィールドの定義DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)
* クエリから返される結果セットを含むフィールドとリストを定義DEFINE FIELD(#COL1) TYPE(*CHAR) LENGTH(010)DEFINE FIELD(#COL2) TYPE(*CHAR) LENGTH(020)DEFINE FIELD(#COL3) TYPE(*DEC) LENGTH(008) DECIMALS(0)DEF_LIST NAME(#WRKLST) FIELDS(#COL1 #COL2 #COL3) TYPE(*WORKING)
* リモート・プロシージャに渡すフィールトとリストを定義し、クエリ値を示すDEFINE FIELD(#PARAM1) TYPE(*CHAR) LENGTH(010)DEF_LIST NAME(#PARAMLST) FIELDS(#PARAM1) TYPE(*WORKING)CHANGE FIELD(#PARAM1) TO(A1001)ADD_ENTRY TO_LIST(#PARAMLST) * EXECUTE の実行時にリモート プロシージャに渡されるようにリストを設定するCHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(PARAM1)')USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #PARAMLST)
* プロシージャ呼び出し
CHANGE FIELD(#JSMCMD) TO('EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY) SERVICE_LIST(COL1,COL2,COL3)')USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLST)
* JSM コマンドおよびメッセージ・フィールドの定義Define Field(#JSMSTS) Type(*CHAR) Length(020)Define Field(#JSMMSG) Type(*CHAR) Length(256)Define Field(#JSMCMD) Type(*CHAR) Length(256)Define Field(#JSMHND) Type(*Char) Length(4)
* クエリから返される結果セットを含むフィールドとリストを定義Define Field(#COL1) Type(*CHAR) Length(010)Define Field(#COL2) Type(*CHAR) Length(020)Define Field(#COL3) Type(*DEC) Length(008) Decimals(0)Def_List Name(#WRKLST) Fields(#COL1 #COL2 #COL3) Type(*WORKING)
* リモート・プロシージャに渡すフィールトとリストを定義し、クエリ値を示すDefine Field(#PARAM1) Type(*CHAR) Length(010)Def_List Name(#PARAMLST) Fields(#PARAM1) Type(*WORKING)#PARAM1 := A1001Add_Entry To_List(#PARAMLST)
* EXECUTE 実行時、リモート・プロシージャに渡すフィールトとリストを設定する#JSMCMD := 'Set Parameter(*LIST) Service_List(PARAM1)'Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #PARAMLST)
* プロシージャ呼び出し
#JSMCMD := 'Execute("CallSelect(?)") CallType(*QUERY)'Use Builtin (JSM_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)