EXECUTE
EXECUTEコマンドは、リモート・データベースに対してSQLステートメントを実行する場合に使用されます。
条件付
EXECUTE --------- QUERY -----------値----------------------->
*SQLPARAMETER
>-- UPDATE ----------値----------------------->
*SQLPARAMETER
>-- PREPARED --------値----------------------->
*SQLPARAMETER
>-- CALL ------------値----------------------->
>-- CALLTYPE -------- *QUERY ---------------------->
*UPDATE
*EXECUTE
>-- CALLSYNTAX ------ *JDBC ----------------------->
*ORACLE
>-- RETURN ---------- *NONE -----------------------|
*CHAR
*STRING
*SMALLINT
*INTEGER
*FLOAT
*DOUBLE
*DECIMAL
*NUMERIC
*ORACLECURSOR
キーワード
|
QUERY |
このキーワードを使用して、結果セットがSQLステートメントから返されると予想されるデータベースへのクエリーを実行します。 キーワードは以下の2つのいずれかの方法で指定できます。 · このキーワードに直接SQLステートメントを指定します。例えば、SELECT '値','値' FROM tableなどになります。 · SETコマンドでPARAMETER(*SQL)キーワードを使用してSQLステートメントを準備した場合、値*SQLPARAMETERを使用します。 この詳細については、「コメント/警告」を参照してください。 |
|
UPDATE |
SQLのUPDATEを発行し、結果セットが返されると予測されない場合、このコマンドを使用します。 キーワードは以下の2つのいずれかの方法で指定できます。 · このキーワードに直接SQLステートメントを指定します。例えば、UPDATE table SET col1='値'、またはDELETE FROM table WHERE col1='値'などになります。 · SETコマンドでPARAMETER(*SQL)キーワードを使用してSQLステートメントを準備した場合、値*SQLPARAMETERを使用します。 この詳細については、「コメント/警告」を参照してください。 |
|
PREPARED |
このキーワードではSQLステートメントを準備できます。ほとんどの場合、プリペアドSQLステートメントでデータを使用できるようにする必要があります。このような場合、SET PARAMETER(*LIST)コマンドを使用して作業リストにこれらの値を準備する必要があります。 キーワードは以下の2つのいずれかの方法で指定できます。 · このキーワードに直接SQLステートメントを指定します。例えば、UPDATE table SET col1=?、またはDELETE FROM table WHERE col1=?のようになります。疑問符があることに注意してください。疑問符については、以下の「コメント/警告」で説明します。 SETコマンドでPARAMETER(*SQL)キーワードを使用してSQLステートメントを準備した場合、値*SQLPARAMETERを使用します。 |
|
CALL |
このキーワードを使用して、SQLステートメントを実行するストアド・プロシージャを呼び出します。このストアド・プロシージャはリモート・サーバーにあります。 この詳細については、「コメント/警告」を参照してください。 |
|
CALLTYPE |
このキーワードは、CALLコマンドで指定したストアド・プロシージャがあるSQLステートメントのタイプを指定する場合に使用されます。 このキーワードには以下の3つの値が可能です。 · *QUERY - ストアド・プロシージャのSQLステートメントがクエリーの場合、CALLは結果セットを作業リストに返します。 · *UPDATE - ストアド・プロシージャのSQLステートメントがUPDATE、INSERT、またはDELETEの場合、CALLは(更新されたレコード数の)行数を返します。 · *EXECUTE - このキーワードを指定した場合、ストアド・プロシージャでは何も返されません。 省略値は*QUERYです。 このキーワードを使用する場合に返されるリストの詳細コメントについては、以下の「リストと変数」を参照してください。 このキーワードと組み合わせて使用するのはCALLキーワードのみです。 |
|
CALLSYNTAX |
このキーワードは、JDBC構文とOracle JDBC構文のどちらを使用するかを指定する場合に使用されます。 このキーワードには以下の2つの値が可能です。 · *JDBC - 標準のJDBC構文を使用するには、この値を使用します。 · *ORACLE - Oracle JDBC構文を使用するには、この値を使用します。 このキーワードの使用の詳細については、以下の「コメント/警告」を参照してください。 このキーワードと組み合わせて使用するのはCALLキーワードのみです。 |
|
RETURN |
このキーワードは、ストアド・プロシージャ/ファンクションからの戻り値のデータ・タイプを指定する場合に使用されます。 以下の値が可能です。 · *NONE - 返される値はありません。 · *CHAR - データ・タイプCHARの値が返されます。 · *STRING - データ・タイプSTRINGRの値が返されます。 · *SMALLINT - データ・タイプSMALLINTの値が返されます。 · *INTEGER - データ・タイプINTEGERの値が返されます。 · *FLOAT - データ・タイプFLOATの値が返されます。 · *DOUBLE - データ・タイプDOUBLEの値が返されます。 · *DECIMAL - データ・タイプDECIMALの値が返されます。 · *NUMERIC - データ・タイプNUMERICの値が返されます。 · *ORACLECURSOR - 返される値は、Oracleクエリーの結果セットにアクセスする際に使用するカーソルになります。これは、Oracleドライバーを使用するOracleデータベースでのみ使用できます。 省略値は*NONEです。 このキーワードの使用の詳細については、以下の「コメント/警告」を参照してください。 このキーワードと組み合わせて使用するのはCALLキーワードのみです。 |
コメント/警告
EXECUTEコマンドは非常に強力です。このコマンドを最大限に活用するには、その機能を理解することが重要です。
以下に、各コマンドの使用タイミングと最適な使用方法について詳しく説明します。この内容をよく読んでから進めてください。
QUERY
リモート・データベースに対してクエリーを実行し、結果セットが返されることが予想される場合(例えば、SELECTから値が返される場合など)、通常、EXECUTE QUERY(SQLステートメント)コマンドを使用します。結果セットの取得方法については、以下の「リストと変数」を参照してください。
非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。
「CALL」キーワードと「PREPARED」キーワードも参照してください。
UPDATE
EXECUTE UPDATE(SQLステートメント)コマンドは、通常、INSERT、UPDATE、またはDELETEを使用してデータベースを更新する場合に使用されます。結果セットは返されません。したがって、この点を考慮すると、データにクエリーを実行する際にこのコマンドは使用されません。
非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。
「CALL」キーワードと「PREPARED」キーワードも参照してください。
PREPARED
同じSQLステートメントを複数回実行する場合、通常は実行回数を少なくするため、PREPAREDコマンドを使用することをお勧めします。
これを行うには、プリペアド・ステートメントをデータベースに送信して一度コンパイルします。つまり、ステートメントを実行するたびに、最初にコンパイルしなくてもすぐに実行できます。この方法を使用しない場合、UPDATEコマンドをループに入れる必要があり、ステートメントは毎回データベースへの接続を再確立しなければなりません。
プリペアドSQLステートメントはパラメータを指定せずに実行できますが、ほとんどの場合、ステートメントがパラメータを取る方が利点があります。パラメータは、SET PARAMETER(*LIST)コマンドと組み合わせて作成された作業リストによってSQLステートメントに渡されます。そのため、例えば、多数の社員の住所の詳細を1回で更新できます。この操作を行うには、プリペアドSQLを使用し、更新するデータのリストを渡す方法が最も効率的です。
非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。
プリペアドSQLステートメントがINSERTで始まり、VALUES(*CALC)またはVALUES(*ALL)で終わる場合、SQLステートメントの特殊な解析が行われます。*CALCオプションでは、列のバインディング・パラメータが計算され、ステートメントが修正されます。*ALLオプションの場合、各列のバインディング・パラメータが計算され、テーブルのその他の列は省略値を使用して挿入されます。
CALL
このキーワードにより、リモート・サーバーでストアド・プロシージャを呼び出すことができます。このストアド・プロシージャにはSQLステートメントが含まれます。CALLコマンドは、*QUERY、*UPDATE、または*EXECUTEのいずれかのタイプを指定するCALLTYPEコマンドと一緒に使用されます。
ストアド・プロシージャにより、SQLステートメントの実行がかなり効率的になります。例えば、多くのデータが返される場合、ネットワーク・トラフィックが軽減されます。このトピックについて詳しく調べることをお勧めします。IBM InfocentreおよびRedbooksにはこのトピックに関する情報が豊富にあり、どちらもIBM Webサイトで入手できます。
パラメータは、作業リストやSET PARAMETER(*LIST)コマンドを使用してリモート・プロシージャに渡すことができます。この方法については、以下の「リストと変数」を参照してください。
一部のストアド・プロシージャ呼び出しでは、実行されたことを伝える警告が返されるため、SET ONWARNING(*CONTINUE)コマンドを使用して警告チェックを無効にする必要があります。
このコマンドの例とストアド・プロシージャの作成方法については、SQLServiceの例に記載されています。ストアド・プロシージャの例はこのセクションの最後にあります。
CALLSYNTAX
Oracle JDBCドライバーは、データベース・ファンクションから値を返す際に使用される場合、JDBC構文を完全にサポートするわけではありません。このドライバーは独自のOracle構文を使用します。この点を考慮すると、このキーワードを使用して、標準のJDBC (この場合*JDBCを指定)と、Oracle JDBC (この場合*ORACLEを指定)のどちらを使用するかを指定する必要があります。これによって、どのスタイルのJDBCドライバーを用意する必要があるかがSQLServiceに伝えられます。
RETURN
ストアド・プロシージャは、戻り値を指定できます。これは、結果セットを返す場合とは異なります。結果セットは、クエリーから返される1つ以上のレコードや値のリストです。一方、戻り値は、完了後にストアド・プロシージャが返す単一の値です。典型的なストアド・プロシージャ構文には、ストアド・プロシージャで実行された最後のコマンドの1つとして、「RETURN」または類似のコマンド(ターゲット・データベースの構文に従う)と関連するフィールド名があります。この値はどのような値でも構いませんが、一部の一般的な例では、選択または更新されたレコードの数、最大値、一部のフラグを含むフィールドになります。
このRETURNキーワードでは、返された値のデータ・タイプを指定できます。可能なデータ・タイプは、ターゲット・データベースとJDBCドライバーでサポート可能な内容によって異なります。例えば、IBM i で現在サポートするのは、ストアド・プロシージャから返される値のデータ・タイプIntegerのみになります。最終的には、ターゲット・データベースとJDBCドライバーで対応可能なデータ・タイプをユーザーの責任で把握します。
このSQLServiceとOracleデータベースを併用する場合、「Oracleに関する注意事項」を参照してください。
Oracleに関する注意事項
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」コマンドを使用する必要があります。
さらに、JDBCクライアントでは、業界標準のJDBC構文ではなくOracle構文を使用して呼び出しを準備する必要があります。
以下に、Oracleファンクションの一例を示します。
SET PARAMETER(*LIST)コマンドを使用したパラメータの処理
EXECUTEコマンドとSET PARAMETER(*LIST)コマンドを組み合わせて実行する場合、データのリストは、リモート・データベースとSQLステートメントに渡されます。この方法を使用する場合、SQLステートメントでは、コマンドに挿入する必要がある値ごとに「?」を入れる必要があります。これらはパラメータ・バインディング・ポジションと呼ばれます。基本的に、リストの最初の列は最初の「?」がある場所に、2番目の列は2番目の「?」がある場所にといった具合に配置されます。サンプル・コードでは、この機能の適切な例が示されています。
SQL構文に関する注意事項
使用する構文はターゲット・データベースに関連するため、使用するJDBCドライバーの構文を把握する必要があることにも注意してください。
SQLステートメント内では文字列の値を引用府で囲む必要があります。例：
EXECUTE UPDATE(UPDATE TABLE SET COL1='値')
EXECUTE UPDATE(DELETE FROM TABLE WHERE COL1='値')
SQLステートメントに開き括弧や閉じ括弧がある場合、ステートメント全体を二重引用府で囲む必要があります。例：
EXECUTE PREPARED("INSERT INTO TABLE (COL1,COL2,COL3) VALUES(?,?,?)")
リストと変数
QUERYキーワード、SQLステートメントがSELECTの場合のPREPAREDキーワード、またはCALLTYPEに*QUERYが指定されたCALLキーワードのいずれかを使用する場合、結果セットが返されるようにするには、このコマンドで作業リストを提供します。
この作業リストには、クエリーから返されると予想されるフィールドが含まれます。リストはフィールドの順序で埋められます。SELECTステートメントは、このリストに示されるよりも多くのフィールドを実際に返すことができますが、列よりも多いリスト・フィールドは返されません。列の値は、resultSet.getString (列インデックス)メソッドを使用して結果セットから受け取ります。リスト・エントリー・フィールドはこの文字列値で設定され、Javaデータ・タイプからネイティブ・データ・タイプへの変換が行われます。
非常に大きなリストが返されると予想される場合、この作業リストを省略し、READコマンドを使用して結果セットにアクセスできます。
UPDATEキーワードは結果セットを返さないため、この状況で作業リストを指定する必要はありません。
CALLでCALLTYPEに*UPDATEを指定した場合、行数(更新されたレコードの数)が返されるため、この値を取得するには1列のリストを指定できます。
呼び出したプロシージャ(CALLコマンドを使用)にパラメータを渡す必要がある場合、パラメータを含む作業リストを指定する必要があります。この作業リストには、渡す必要があるパラメータと同じだけの列があります。作業リストの最初の行のみが使用されます。リストは、SET PARAMETER(*LIST)コマンドを使用して準備する必要があります。
この仕組みについて確認するには、以下の例とSQLServiceの例を参照してください。RDML、RDMLX、または3GLアプリケーションのサービス・コマンドに作業リストを指定する方法については、「Java Service Managerクライアント」を参照してください。
例
SQLServiceサービスのSET、EXECUTE、READの各コマンドは、それぞれ緊密に関連しています。そのため、「SQLServiceの例」の拡張例を確認することをお勧めします。
RDML
* Define the JSM command and message fields
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)
* Define the fields and list that will contains the result set returned from the query
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 the field and list that will be passed to the remote procedure to indicate what values to query
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)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(PARAM1)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #PARAMLST)
* Call procedure
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)
RDMLX
* Define the JSM command and message fields
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 the fields and list that will contains the result set returned from the query
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 the field and list that will be passed to the remote procedure to indicate what values to query
Define Field(#PARAM1) Type(*CHAR) Length(010)
Def_List Name(#PARAMLST) Fields(#PARAM1) Type(*WORKING)
#PARAM1 := A1001
Add_Entry To_List(#PARAMLST)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
#JSMCMD := 'Set Parameter(*LIST) Service_List(PARAM1)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #PARAMLST)
* Call procedure
#JSMCMD := 'Execute("CallSelect(?)") CallType(*QUERY) Service_List(COL1,COL2,COL3)'
Use Builtin (JSM_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)
IBM i のストアド・プロシージャの例：
IBM i データベースのストアド・プロシージャを作成するには、ソース・メンバーを編集し、メンバー・タイプTXTを使用する必要があります。
以下に、IBM i の3つのストアド・プロシージャの例を示します。最初の行は、ストアド・プロシージャの名前を指定することに注意してください。例えば、最初の行はCALLSELECTと呼ばれ、その場所はJSMJDBCライブラリになります。2番目と3番目の例は、値が返される状況を示しています。
本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、このトピックについて詳しく調べることをお勧めします。IBM InfocentreおよびRedbooksにはこのトピックに関する情報が豊富にあるため、これらの参照から始めると良いかもしれません。どちらもIBM Webサイトで入手できます。
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLSELECT(IN CODE CHAR (10))
LANGUAGE SQL
READS SQL DATA
RESULT SETS 1
BEGIN
DECLARE 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
****************** End of data ****************************************
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLUPDATE(IN CODE CHAR (10))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE 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
****************** End of data ****************************************
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLEXECUT(IN CODE CHAR (10))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE 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
****************** End of data ****************************************
ストアド・プロシージャを作成するには、RUNSQLSTMコマンドを使用する必要があります。例：
RUNSQLSTM SRCFILE(JSMJDBC/QCLSRC) SRCMBR(CALLSELECT) COMMIT(*NONE)
SQLServiceを使用してこれらのストアド・プロシージャを実行する場合は、以下のEXECUTEコマンドのようなコマンドを使用できます。
EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY)
EXECUTE CALL("CALLUPDATE(?)") CALLTYPE(*UPDATE) RETURN(*INTEGER)
EXECUTE CALL("CALLEXECUT(?)") CALLTYPE(*EXECUTE) RETURN(*INTEGER)
Oracleデータベース・ファンクションの例：
以下に、結果セットを作成するOracleデータベース・ファンクションの例を示します。これ自体は、カーソルを返すことに注意してください。
本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、他の媒体でこのトピックについて詳しく調べることをお勧めします。
CREATE OR REPLACE FUNCTION "SYSTEM"."TEST" ( PARAM1 IN CHAR, PARAM2 IN CHAR ) RETURN SYS_REFCURSOR AS CURSOR_1 SYS_REFCURSOR ;
BEGIN
OPEN CURSOR_1 FOR SELECT * FROM MYTABLE WHERE FLD_1='AB';
RETURN CURSOR_1 ;
END;
SQLServiceを使用してこのファンクションを実行する場合は、以下のEXECUTEコマンドのようなコマンドを使用できます。
EXECUTE CALL("TEST(?,?)") CALLTYPE(*EXECUTE) CALLSYNTAX(*ORACLE) RETURN(*ORACLECURSOR) SERVICE_LIST(COL1,COL2)