9 Oracle動的SQL
この章では、アプリケーションに柔軟性や機能性をもたらす高度なプログラミング技法である動的SQLの使用方法について説明します。動的SQLの長所と短所を比較した後、実行時にSQL文をその場で受け入れて処理するプログラムを記述する方法を、単純なものから複雑なものまで4つ紹介します。それぞれの方法の要件および制限事項、さらに実行するジョブに対する適切な方法の選択方法についても説明します。
内容は次のとおりです。
9.1 動的SQL
ほとんどのデータベース・アプリケーションでは、ある特定のジョブが実行されます。たとえば、ユーザーに従業員番号の入力を要求して、その後EMPおよびDEPTの表の行を更新するという単純なプログラムがあります。この場合は、プリコンパイル時にUPDATE文の構成がわかっています。つまり、変更する表、それぞれの表および列に定義されている制約、更新する列、それぞれの列のデータ型がわかっています。
しかし、アプリケーションによっては、様々なSQL文を実行時に受け入れ(または作成し)、処理する必要があります。たとえば汎用レポート・ライターでは、生成するレポートについてそれぞれ別のSELECT文を作成する必要があります。この場合、文の構成は実行時までわかりません。このような文は実行のたびに異なる可能性があります。このような文を動的SQL文と呼びます。
静的SQL文とは異なり、動的SQL文はソース・プログラム内には埋め込まれません。そのかわり、これらの文は実行時にプログラムに入力される(またはプログラムによって作成される)文字列に格納されます。動的SQL文は対話形式で入力できるのみでなく、ファイルから読み込むこともできます。
9.2 動的SQLの長所および短所
通常の埋込みSQLプログラムと比べると、動的に定義されたSQL文を受け入れて処理するホスト・プログラムの方が柔軟性は高くなります。動的SQL文は、SQLの知識がほとんどないユーザーでも対話形式で作成できます。
たとえば、SELECT文、UPDATE文またはDELETE文のWHERE句内で使用する検索条件の入力をユーザーに要求する単純なプログラムがあります。さらに複雑なプログラムでは、SQL処理、表およびビューの名前、列の名前などを表示されているメニューからユーザーが選択できるようになります。このように、動的SQLを使用すると柔軟性に富んだアプリケーションを記述できます。
ただし、動的問合せの中には複雑なコーディング、特殊なデータ構造体の使用および実行時の処理の増加が必要なものもあります。処理時間が増えることは支障がない場合もありますが、動的SQLの概念および方法を完全に理解するまではコーディングが難しく感じられることもあります。
9.4 動的SQL文の要件
動的SQL文を表す文字列は、有効なDML SQL文またはDDL SQL文のテキストを含む必要がありますが、EXEC SQL句、ホスト言語のデリミタまたは文の終了記号は含みません。
ほとんどの場合、この文字列にはダミーのホスト変数が含まれます。これらはSQL文内に実際のホスト変数のための場所を確保します。ダミーのホスト変数は単なるプレースホルダ(場所を確保するもの)であるため、宣言する必要はなく、任意の名前を付けられます(ハイフンは使用できません)。たとえば、Oracleでは次の2つの文字列は区別されません。
'DELETE FROM EMP WHERE MGR = :MGRNUMBER AND JOB = :JOBTITLE' 'DELETE FROM EMP WHERE MGR = :M AND JOB = :J'
9.5 動的SQL文の処理
一般にアプリケーション・プログラムでは、SQL文のテキストおよびその文で使用するホスト変数の値をユーザーが入力する必要があります。そのSQL文はOracleにより解析されます。解析では、SQL文が構文規則に従っているか、有効なデータベース・オブジェクトを参照しているかについて調べられます。また、データベース・アクセス権限のチェック、必要なリソースの確保および最適なアクセス・パスの検索も行われます。
次に、Oracleはホスト変数をSQL文にバインドします。これにより、Oracleはホスト変数のアドレスを取得し、その値の読取りまたは書込みを実行できます。
文が問合せの場合、SELECT変数を定義すると、OracleによってFETCHが実行され、すべての行が取り出されます。カーソルがクローズします。
この後、OracleでSQL文が実行されます。つまり、そのSQL文の要求(表からの行の削除など)をOracleが実行します。
これらのホスト変数に別の値を指定すると、このSQL文を繰り返し実行できます。
9.6 動的SQLの使用方法
この項では、動的SQL文の定義に使用できる4つの方法を紹介します。まずそれぞれの方法の機能および制限事項を簡単に説明した後、適切な方法を選択するためのガイドラインを示します。詳細な使用方法は、以降の項を参照してください。
この4つの方法は番号が大きくなるに従って対象が広がるようになっています。つまり方法2は方法1を包含し、方法3は方法1と方法2を包含するというようになります。ただし、「適切な使用方法」のように、それぞれの方法は特定の種類のSQL文を処理するのに適しています。
表9-1 適切な使用方法
| 方法 | SQL文の種類 |
|---|---|
|
1 |
入力ホスト変数のない非問合せ。 |
|
2 |
入力ホスト変数の数がわかっている非問合せ。 |
|
3 |
選択リスト項目の数および入力ホスト変数の数がわかっている問合せ。 |
|
4 |
選択リスト項目の数または入力ホスト変数の数が不明な問合せ。 |
選択リスト項目には、列名や式を使用します。
9.6.2 方法2
この方法では、動的SQL文を受け入れるかまたは作成し、PREPAREおよびEXECUTEコマンドを使用してその文を処理できます。SQL文は問合せであってはなりません。入力ホスト変数のプレースホルダの数と入力ホスト変数のデータ型はプリコンパイル時にわかっている必要があります。たとえば次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (ENAME, JOB) VALUES (:EMPNAME, :JOBTITLE)' 'DELETE FROM EMP WHERE EMPNO = :EMPNUMBER'
方法2では、PREPAREを1回コールすることによりSQL文の解析を1回行うことができます。ホスト変数に異なる値を指定して、このSQL文を複数回実行できます。RELEASE_CURSOR=YESと指定されている場合は、複数回実行することはできません。実行のたびに文を準備しなおす必要があるためです。
9.6.3 方法3
この方法では、動的問合せを受け入れるか、または作成し、DECLARE、OPEN、FETCHおよびCLOSEカーソル・コマンドとともにPREPAREコマンドを使用してその問合せを処理できます。選択リスト項目の数、入力ホスト変数に対するプレースホルダの数、および入力ホスト変数のデータ型は、プリコンパイル時にわかっている必要があります。たとえば、次のホスト文字列は有効です。
'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :DEPTNUMBER'
9.6.4 方法4
この方法では、動的SQL文を受け入れるかまたは作成し、記述子(「方法4の使用方法」を参照)を使用してその文を処理できます。選択リスト項目の数、入力ホスト変数に対するプレースホルダの数、および入力ホスト変数のデータ型は、実行時まで不明でもかまいません。たとえば次のホスト文字列はこのカテゴリに該当します。
'INSERT INTO EMP (unknown) VALUES (unknown)' 'SELECT unknown FROM EMP WHERE DEPTNO = 20'
方法4は、選択リスト項目の数または入力ホスト変数の数が不明な動的SQL文を実行するときに必要です。
9.6.5 ガイドライン
4つの方法はどれも、動的SQL文を文字列に格納する必要があり、この文字列は、ホスト変数または引用符で囲んだリテラルであることが必要です。SQL文を文字列に格納する場合は、キーワードEXEC SQLおよび文の終了記号は省略してください。
方法2および方法3では、プリコンパイル時に入力ホスト変数に対するプレースホルダの数および入力ホスト変数のデータ型がわかっている必要があります。
方法の番号が大きくなるほどアプリケーションへの制約は少なくなりますが、コードの記述が難しくなります。通常は、最も簡単な方法を使用してください。ただし、動的SQL文を繰返し実行する場合、方法1では実行のたびに再解析されるので、これを避けるために方法2を使用してください。
方法4は最も柔軟性に富んでいますが、複雑なコード記述方法および動的SQLの概念の完全な理解が求められます。通常、方法4を使用するのは、方法1、2または3を使用できない場合のみです。
図9-1の決定論理を参考にして、適切な方法を選択してください。
9.6.5.1 一般的なエラーの回避
動的SQL文を文字配列に格納する場合は、その配列に空白を埋め込んでからSQL文を格納してください。こうして余分な文字をクリアします。別のSQL文を格納するために配列を再利用するときに、この処理が重要となります。原則として、SQL文を格納する前に必ずホスト文字列を初期化(または再初期化)してください。
ホスト文字列にはヌル終端文字を使用しないでください。Oracleでは、ヌル終端文字は文字列の終了マーカーとはみなされません。SQL文の一部として扱われます。
動的SQL文をVARCHAR変数に格納する場合は、VARCHAR変数の長さを正しく設定(または再設定)してからPREPARE文およびEXECUTE IMMEDIATE文を実行してください。
EXECUTEを実行すると、SQLCAのSQLWARN警告フラグはリセットされます。したがって、無条件の更新(これはWHERE句を指定しなかった場合に発生します)などの誤りを検出するには、PREPARE文を実行してからEXECUTE文を実行するまでの間にSQLWARNフラグをチェックする必要があります。
図9-1に、正しい方法を選択する方法を示します。
9.7 方法1の使用方法
最も単純な動的SQL文では、結果が成功か失敗のどちらかで、ホスト変数は使用されません。次に例を示します。
'DELETE FROM table_name WHERE column_name = constant' 'CREATE TABLE table_name ...' 'DROP INDEX index_name' 'UPDATE table_name SET column_name = constant' 'GRANT SELECT ON table_name TO username'
9.7.1 EXECUTE IMMEDIATE文
方法1では、SQL文を解析すると、EXECUTE IMMEDIATEコマンドを使用してすぐに実行します。コマンドの後には、実行するSQL文を含む文字列(ホスト変数またはリテラル)が続きますが、この文は問合せにしないでください。
EXECUTE IMMEDIATE文の構文は次のとおりです。
EXEC SQL EXECUTE IMMEDIATE { :HOST-STRING | STRING-LITERAL }END-EXEC.
次の例では、ユーザーが入力するSQL文をホスト変数SQL-STMTに格納しています。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 SQL-STMT PIC X(120);
EXEC SQL END DECLARE SECTION END-EXEC.
...
LOOP.
DISPLAY 'Enter SQL statement: ' WITH NO ADVANCING.
ACCEPT SQL-STMT END-EXEC.
* -- sql_stmt now contains the text of a SQL statement
EXEC SQL EXECUTE IMMEDIATE :SQL-STMT END-EXEC.
NEXT.
...
EXECUTE IMMEDIATEは入力されているSQL文を実行するたびに解析するため、方法1は1回しか実行しない文に最も適しています。通常、データ定義文は、このカテゴリに入ります。
9.7.2 例
次のプログラムは、UPDATE文のWHERE句で使用する検索条件の入力をユーザーに求め、方法1を使用してUPDATE文を実行します。
...
* THE RELEASE_CURSOR=YES OPTION INSTRUCTS PRO*COBOL TO
* RELEASE IMPLICIT CURSORS ASSOCIATED WITH EMBEDDED SQL
* STATEMENTS. THIS ENSURES THAT Oracle DOES NOT KEEP PARSE
* LOCKS ON TABLES, SO THAT SUBSEQUENT DATA MANIPULATION
* OPERATIONS ON THOSE TABLES DO NOT RESULT IN PARSE-LOCK
* ERRORS.
EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC.
*
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VALUE "SCOTT".
01 PASSWD PIC X(10) VALUE "TIGER".
01 DYNSTMT PIC X(80).
EXEC SQL END DECLARE SECTION END-EXEC.
01 UPDATESTMT PIC X(40).
01 SEARCH-COND PIC X(40).
...
DISPLAY "ENTER A SEARCH CONDITION FOR STATEMENT:".
MOVE "UPDATE EMP SET COMM = 500 WHERE " TO UPDATESTMT.
DISPLAY UPDATESTMT.
ACCEPT SEARCH-COND.
* Concatenate SEARCH-COND to UPDATESTMT and store result
* in DYNSTMT.
STRING UPDATESTMT DELIMITED BY SIZE
SEARCH-COND DELIMITED BY SIZE INTO DYNSTMT.
EXEC SQL EXECUTE IMMEDIATE :DYNSTMT END-EXEC.9.8 サンプル・プログラム6: 動的SQL方法1
このプログラムは、動的SQL方法1を使用して、表の作成、行の挿入、挿入のコミットおよび表の削除を行います。
*****************************************************************
* Sample Program 6: Dynamic SQL Method 1 *
* *
* This program uses dynamic SQL Method 1 to create a table, *
* insert a row, commit the insert, then drop the table. *
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. DYNSQL1.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
* INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE
* THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS
* INFORMATION AVAILABLE TO THE PROGRAM.
EXEC SQL INCLUDE SQLCA END-EXEC.
* INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE
* THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS
* INFORMATION AVAILABLE TO THE PROGRAM.
EXEC SQL INCLUDE ORACA END-EXEC.
* THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF
* THE ORACA.
EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
* THE RELEASE_CURSOR=YES OPTION INSTRUCTS PRO*COBOL TO
* RELEASE IMPLICIT CURSORS ASSOCIATED WITH EMBEDDED SQL
* STATEMENTS. THIS ENSURES THAT ORACLE DOES NOT KEEP PARSE
* LOCKS ON TABLES, SO THAT SUBSEQUENT DATA MANIPULATION
* OPERATIONS ON THOSE TABLES DO NOT RESULT IN PARSE-LOCK
* ERRORS.
EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VALUE "SCOTT".
01 PASSWD PIC X(10) VALUE "TIGER".
01 DYNSTMT PIC X(80) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
* DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS.
01 ORASLNRD PIC 9(9).
PROCEDURE DIVISION.
MAIN.
* BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS.
EXEC SQL WHENEVER SQLERROR GOTO SQLERROR END-EXEC.
* SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR
* OCCURS.
MOVE 1 TO ORASTXTF.
* CONNECT TO ORACLE.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: " WITH NO ADVANCING.
DISPLAY USERNAME.
DISPLAY " ".
* EXECUTE A STRING LITERAL TO CREATE THE TABLE. HERE, YOU
* GENERALLY USE A STRING VARIABLE INSTEAD OF A LITERAL, AS IS
* DONE LATER IN THIS PROGRAM. BUT, YOU CAN USE A LITERAL IF
* YOU WISH.
DISPLAY "CREATE TABLE DYN1 (COL1 CHAR(4))".
DISPLAY " ".
EXEC SQL EXECUTE IMMEDIATE
"CREATE TABLE DYN1 (COL1 CHAR(4))"
END-EXEC.
* ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT.
* SET THE -LEN PART TO THE LENGTH OF THE -ARR PART.
MOVE "INSERT INTO DYN1 VALUES ('TEST')" TO DYNSTMT-ARR.
MOVE 36 TO DYNSTMT-LEN.
DISPLAY DYNSTMT-ARR.
DISPLAY " ".
* EXECUTE DYNSTMT TO INSERT A ROW. THE SQL STATEMENT IS A
* STRING VARIABLE WHOSE CONTENTS THE PROGRAM MAY DETERMINE
* AT RUN TIME.
EXEC SQL EXECUTE IMMEDIATE :DYNSTMT END-EXEC.
* COMMIT THE INSERT.
EXEC SQL COMMIT WORK END-EXEC.
* CHANGE DYNSTMT AND EXECUTE IT TO DROP THE TABLE.
MOVE "DROP TABLE DYN1" TO DYNSTMT-ARR.
MOVE 19 TO DYNSTMT-LEN.
DISPLAY DYNSTMT-ARR.
DISPLAY " ".
EXEC SQL EXECUTE IMMEDIATE :DYNSTMT END-EXEC.
* COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL COMMIT RELEASE END-EXEC.
DISPLAY "HAVE A GOOD DAY!".
DISPLAY " ".
STOP RUN.
SQLERROR.
* ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING
* ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR.
DISPLAY SQLERRMC.
DISPLAY "IN ", ORASTXTC.
MOVE ORASLNR TO ORASLNRD.
DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC.
* DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP
* SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
* ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL ROLLBACK RELEASE END-EXEC.
STOP RUN.9.9 方法2の使用方法
方法1では1ステップで実行し、方法2では2ステップに分けて実行します。動的SQL文(問合せは不可)は、まず準備(名前の指定および解析)され、次に実行されます。
方法2では、SQL文の中にホスト変数および標識変数のプレースホルダを使用できます。このSQL文は一度PREPAREすると、ホスト変数に別の値を指定して繰り返しEXECUTEできます。また、MODE=ANSIと指定しなかった場合も、COMMITまたはROLLBACKの後でSQL文を再度PREPAREする必要はありません(ログオフして再接続する場合は除きます)。
PREPARE文の構文は次のとおりです。
EXEC SQL PREPARE STATEMENT-NAME FROM { :HOST-STRING | STRING-LITERAL } END-EXEC.
PREPAREは、このSQL文を解析して名前を指定します。
STATEMENT-NAMEは、ホスト変数やプログラム変数ではなく、プリコンパイラが使用する識別子であるため、COBOL文では定義しないでください。これはEXECUTEの対象としてプリコンパイルされたSQL文を示しています。
EXECUTE文の構文は次のとおりです。
EXEC SQL
EXECUTE STATEMENT-NAME [USING HOST-VARIABLE-LIST]
END-EXEC.
HOST-VARIABLE-LISTの構文を次に示します。
:HOST-VAR1[:INDICATOR1] [, HOST-VAR2[:INDICATOR2], ...]
解析したSQL文は、それぞれの入力ホスト変数に指定済の値を使用してEXECUTEによって実行されます。次の例では、入力SQL文にプレースホルダnが組み込まれています。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 EMP-NUMBER PIC S9(4) COMP VALUE ZERO.
...
01 DELETE-STMT PIC X(120) VALUE SPACES.
...
EXEC SQL END DECLARE SECTION END-EXEC.
01 WHERE-STMT PIC X(40).
01 SEARCH-COND PIC X(40).
...
MOVE 'DELETE FROM EMP WHERE EMPNO = :N AND ' TO WHERE-STMT.
DISPLAY 'Complete this statement's search condition:'.
DISPLAY WHERE-STMT.
ACCEPT SEARCH-COND.
* Concatenate SEARCH-COND to WHERE-STMT and store in DELETE-STMT
STRING WHERE-STMT DELIMITED BY SIZE
SEARCH-COND DELIMITED BY SIZE INTO
DELETE-STMT.
EXEC SQL PREPARE SQLSTMT FROM :DELETE-STMT END-EXEC.
LOOP.
DISPLAY 'Enter employee number: ' WITH NO ADVANCING.
ACCEPT EMP-NUMBER.
IF EMP-NUMBER = 0
GO TO NEXT.
EXEC SQL EXECUTE SQLSTMT USING :EMP-NUMBER END-EXEC.
NEXT.
方法2では、プリコンパイル時に入力ホスト変数のデータ型がわかっている必要があります。最後の例では、EMP-NUMBERがPIC S9(4) COMP型で宣言されています。Oracleでは、内部NUMBERデータ型へのデータ型変換がすべてサポートされているので、入力ホスト変数をPIC X(4)型またはCOMP-1型で宣言することもできます。
9.9.1 USING句
SQL文EXECUTEが完了すると、USING句の入力ホスト変数が、対応するプリコンパイルされた動的SQL文中のプレースホルダに置き換えられます。
PREPAREの後の動的SQL文中のプレースホルダはすべて、USING句のホスト変数に対応している必要があります。このため、PREPAREの後の文で同じプレースホルダが複数回使用されている場合は、それぞれがUSING句の中のホスト変数に対応している必要があります。USING句のホスト変数のうち1つでも配列がある場合は、すべてのホスト変数が配列であることが必要です。それ以外の場合は、レコードが1つだけ処理されます。
プレースホルダの名前とホスト変数の名前が一致している必要はありません。ただし、PREPAREの後の動的SQL文中のプレースホルダの順序は、USING句の対応するホスト変数の順序と一致している必要があります。
関連項目
9.10 サンプル・プログラム7: 動的SQL方法2
このプログラムは、動的SQL方法2を使用して、EMP表に2行挿入し、挿入した行を削除します。
*****************************************************************
* Sample Program 7: Dynamic SQL Method 2 *
* *
* This program uses dynamic SQL Method 2 to insert two rows *
* into the EMP table, then delete them. *
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. DYNSQL2.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
* INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE THROUGH
* WHICH ORACLE MAKES RUNTIME STATUS INFORMATION (SUCH AS ERROR
* CODES, WARNING FLAGS, AND DIAGNOSTIC TEXT) AVAILABLE TO THE
* PROGRAM.
EXEC SQL INCLUDE SQLCA END-EXEC.
* INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE THROUGH
* WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS INFORMATION
* AVAILABLE TO THE PROGRAM.
EXEC SQL INCLUDE ORACA END-EXEC.
* THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF
* THE ORACA.
EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VALUE "SCOTT".
01 PASSWD PIC X(10) VALUE "TIGER".
01 DYNSTMT PIC X(80) VARYING.
01 EMPNO PIC S9(4) COMPUTATIONAL VALUE 1234.
01 DEPTNO1 PIC S9(4) COMPUTATIONAL VALUE 10.
01 DEPTNO2 PIC S9(4) COMPUTATIONAL VALUE 20.
EXEC SQL END DECLARE SECTION END-EXEC.
* DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS.
01 EMPNOD PIC 9(4).
01 DEPTNO1D PIC 9(2).
01 DEPTNO2D PIC 9(2).
01 ORASLNRD PIC 9(9).
PROCEDURE DIVISION.
MAIN.
* BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS.
EXEC SQL WHENEVER SQLERROR GOTO SQLERROR END-EXEC.
* SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR
* OCCURS.
MOVE 1 TO ORASTXTF.
* CONNECT TO ORACLE.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE.".
DISPLAY " ".
* ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT. BOTH
* THE ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY. NOTE
* THAT THE STATEMENT CONTAINS TWO HOST VARIABLE PLACEHOLDERS,
* V1 AND V2, FOR WHICH ACTUAL INPUT HOST VARIABLES MUST BE
* SUPPLIED AT EXECUTE TIME.
MOVE "INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:V1, :V2)"
TO DYNSTMT-ARR.
MOVE 49 TO DYNSTMT-LEN.
* DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST
* VARIABLES.
DISPLAY DYNSTMT-ARR.
MOVE EMPNO TO EMPNOD.
MOVE DEPTNO1 TO DEPTNO1D.
DISPLAY " V1 = ", EMPNOD, " V2 = ", DEPTNO1D.
* THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH A
* STRING CONTAINING A SQL STATEMENT. THE STATEMENT NAME IS
* A SQL IDENTIFIER, NOT A HOST VARIABLE, AND THEREFORE DOES
* NOT APPEAR IN THE DECLARE SECTION.
* A SINGLE STATEMENT NAME MAY BE PREPARED MORE THAN ONCE,
* OPTIONALLY FROM A DIFFERENT STRING VARIABLE.
EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC.
* THE EXECUTE STATEMENT EXECUTES A PREPARED SQL STATEMENT
* USING THE SPECIFIED INPUT HOST VARIABLES, WHICH ARE
* SUBSTITUTED POSITIONALLY FOR PLACEHOLDERS IN THE PREPARED
* STATEMENT. FOR EACH OCCURRENCE OF A PLACEHOLDER IN THE
* STATEMENT THERE MUST BE A VARIABLE IN THE USING CLAUSE.
* THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE
* STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR
* MULTIPLE TIMES IN THE USING CLAUSE. THE USING CLAUSE MAY
* BE OMITTED ONLY IF THE STATEMENT CONTAINS NO PLACEHOLDERS.
* A SINGLE PREPARED STATEMENT MAY BE EXECUTED MORE THAN ONCE,
* OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES.
EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO1 END-EXEC.
* INCREMENT EMPNO AND DISPLAY NEW INPUT HOST VARIABLES.
ADD 1 TO EMPNO.
MOVE EMPNO TO EMPNOD.
MOVE DEPTNO2 TO DEPTNO2D.
DISPLAY " V1 = ", EMPNOD, " V2 = ", DEPTNO2D.
* REEXECUTE S TO INSERT THE NEW VALUE OF EMPNO AND A
* DIFFERENT INPUT HOST VARIABLE, DEPTNO2. A REPREPARE IS NOT
* NECESSARY.
EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO2 END-EXEC.
* ASSIGN A NEW VALUE TO DYNSTMT.
MOVE "DELETE FROM EMP WHERE DEPTNO = :V1 OR DEPTNO = :V2"
TO DYNSTMT-ARR.
MOVE 50 TO DYNSTMT-LEN.
* DISPLAY THE NEW SQL STATEMENT AND ITS CURRENT INPUT HOST
* VARIABLES.
DISPLAY DYNSTMT-ARR.
DISPLAY " V1 = ", DEPTNO1D, " V2 = ", DEPTNO2D.
* REPREPARE S FROM THE NEW DYNSTMT.
EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC.
* EXECUTE THE NEW S TO DELETE THE TWO ROWS PREVIOUSLY
* INSERTED.
EXEC SQL EXECUTE S USING :DEPTNO1, :DEPTNO2 END-EXEC.
* ROLLBACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL ROLLBACK RELEASE END-EXEC.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY!".
DISPLAY " ".
STOP RUN.
SQLERROR.
* ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING
* ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR.
DISPLAY SQLERRMC.
DISPLAY "IN ", ORASTXTC.
MOVE ORASLNR TO ORASLNRD.
DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC.
* DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP
* SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
* ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL ROLLBACK RELEASE END-EXEC.
STOP RUN.9.11 方法3の使用方法
方法3は方法2に似ていますが、PREPARE文をカーソルの定義および処理に必要な文と結合する点で異なります。これによって、プログラムで問合せを受け入れて処理できます。動的SQL文が問合せである場合は、方法3または方法4を必ず使用してください。
方法3では、プリコンパイル時に問合せ選択リスト内の列の数および入力ホスト変数に対するプレースホルダの数がわかっている必要があります。ただし、表や列などのデータベース・オブジェクトの名前は、実行時に指定できます(ホスト変数と重複する名前は無効です)。問合せ結果を限定、分類およびソートする句(WHERE、GROUP BY、ORDER BYなど)も実行時に指定できます。
方法3では、埋込みSQL文を次のような順序で使用します。
EXEC SQL
PREPARE STATEMENTNAME FROM { :HOST-STRING | STRING-LITERAL }
END-EXEC.
EXEC SQL DECLARE CURSORNAME CURSOR FOR STATEMENTNAME END-EXEC.
EXEC SQL OPEN CURSORNAME [USING HOST-VARIABLE-LIST] END-EXEC.
EXEC SQL FETCH CURSORNAME INTO HOST-VARIABLE-LIST END-EXEC.
EXEC SQL CLOSE CURSORNAME END-EXEC.
次に、それぞれの文の実行内容を説明します。
9.11.1 PREPARE
PREPARE文は動的SQL文を解析し、名前を指定します。次の例では、PREPAREにより文字列SELECT-STMTに格納された問合せを解析し、その問合せにSQLSTMTの名前を付けます。
MOVE 'SELECT MGR, JOB FROM EMP WHERE SAL < :SALARY'
TO SELECT-STMT.
EXEC SQL PREPARE SQLSTMT FROM :SELECT-STMT END-EXEC.
一般的には、この問合せのWHERE句は実行時に端末から入力するか、またはアプリケーションによって生成されます。
識別子SQLSTMTはホスト変数でもプログラム変数でもありませんが、一意であることが必要です。sql_stmtは特定の動的SQL文を指定します。
次の文も有効です。
EXEC SQL
PREPARE SQLSTMT FROM 'SELECT MGR, JOB FROM EMP WHERE SAL < :SALARY'
END-EXEC.
%のワイルド・カードを使用した次のPREPARE文も有効です。
MOVE "SELECT ENAME FROM TEST WHERE ENAME LIKE 'SMIT%'" TO MY-STMT.
EXEC SQL
PREPARE S FROM MY-STMT
END-EXEC.9.11.2 DECLARE
DECLARE文は、カーソルに名前を指定し、これを特定の問合せに対応付けてカーソルを定義します。カーソルの宣言は、そのプリコンパイル・ユニット内でのみ有効です。前述の例では、次に示すように、DECLAREによりEMPCURSORの名前のカーソルを定義し、それをSQLSTMTに対応付けます。
EXEC SQL DECLARE EMPCURSOR CURSOR FOR SQLSTMT END-EXEC.
識別子SQLSTMTおよびEMPCURSORはホスト変数でもプログラム変数でもありませんが、一意であることが必要です。同じ文名を使用して2つのカーソルを宣言すると、Pro*COBOLはその2つのカーソル名を同義とみなします。たとえば次の文を実行したとします。
EXEC SQL PREPARE SQLSTMT FROM :SELECT-STMT END-EXEC.
EXEC SQL DECLARE EMPCURSOR FOR SQLSTMT END-EXEC.
EXEC SQL PREPARE SQLSTMT FROM :DELETE-STMT END-EXEC.
EXEC SQL DECLARE DEPCURSOR FOR SQLSTMT END-EXEC.
この場合、EMPCURSORをOPENしたときに処理対象となるのはDELETE-STMTに格納されている動的SQL文であって、SELECT-STMTに格納されている動的SQL文ではありません。
9.11.3 OPEN
OPEN文により、カーソルの割当て、入力ホスト変数のバインド、問合せの実行およびアクティブ・セットの識別を行います。さらにOPENにより、アクティブ・セットの最初の行にカーソルを位置付け、SQLCA内のSQLERRDの3番目の要素に保存される処理済行数を0 (ゼロ)に設定します。PREPAREされた動的SQL文中のプレースホルダは、USING句の対応する入力ホスト変数に置き換えられます。
前述の例では、次に示すようにOPENによってEMPCURSORを割り当て、ホスト変数SALARYをWHERE句に割り当てます。
EXEC SQL OPEN EMPCURSOR USING :SALARY END-EXEC.
9.12 サンプル・プログラム8: 動的SQL方法3
このプログラムは、動的SQL方法3を使用して、指定された部門の全従業員の名前をEMP表から取り出します。
*****************************************************************
* Sample Program 8: Dynamic SQL Method 3 *
* *
* This program uses dynamic SQL Method 3 to retrieve the names *
* of all employees in a given department from the EMP table. *
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. DYNSQL3.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
* INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE THROUGH
* WHICH ORACLE MAKES RUNTIME STATUS INFORMATION (SUCH AS ERROR
* CODES, WARNING FLAGS, AND DIAGNOSTIC TEXT) AVAILABLE TO THE
* PROGRAM.
EXEC SQL INCLUDE SQLCA END-EXEC.
* INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE THROUGH
* WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS INFORMATION
* AVAILABLE TO THE PROGRAM.
EXEC SQL INCLUDE ORACA END-EXEC.
* THE ORACA=YES OPTION MUST BE SPECIFIED TO ENABLE USE OF
* THE ORACA.
EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VALUE "SCOTT".
01 PASSWD PIC X(10) VALUE "TIGER".
01 DYNSTMT PIC X(80) VARYING.
01 ENAME PIC X(10).
01 DEPTNO PIC S9999 COMPUTATIONAL VALUE 10.
EXEC SQL END DECLARE SECTION END-EXEC.
* DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS.
01 DEPTNOD PIC 9(2).
01 ENAMED PIC X(10).
01 SQLERRD3 PIC 9(2).
01 ORASLNRD PIC 9(4).
PROCEDURE DIVISION.
MAIN.
* BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS.
EXEC SQL WHENEVER SQLERROR GO TO SQLERROR END-EXEC.
* SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR
* OCCURS.
MOVE 1 TO ORASTXTF.
* CONNECT TO ORACLE.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE.".
DISPLAY " ".
* ASSIGN A SQL QUERY TO THE VARYING STRING DYNSTMT. BOTH THE
* ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY. NOTE THAT
* THE STATEMENT CONTAINS ONE HOST VARIABLE PLACEHOLDER, V1,
* FOR WHICH AN ACTUAL INPUT HOST VARIABLE MUST BE SUPPLIED
* AT OPEN TIME.
MOVE "SELECT ENAME FROM EMP WHERE DEPTNO = :V1"
TO DYNSTMT-ARR.
MOVE 40 TO DYNSTMT-LEN.
* DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST
* VARIABLE.
DISPLAY DYNSTMT-ARR.
MOVE DEPTNO TO DEPTNOD.
DISPLAY " V1 = ", DEPTNOD.
DISPLAY " ".
DISPLAY "EMPLOYEE".
DISPLAY "--------".
* THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH A
* STRING CONTAINING A SELECT STATEMENT. THE STATEMENT NAME,
* WHICH MUST BE UNIQUE, IS A SQL IDENTIFIER, NOT A HOST
* VARIABLE, AND SO DOES NOT APPEAR IN THE DECLARE SECTION.
EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC.
* THE DECLARE STATEMENT ASSOCIATES A CURSOR WITH A PREPARED
* STATEMENT. THE CURSOR NAME, LIKE THE STATEMENT NAME, DOES
* NOT APPEAR IN THE DECLARE SECTION.
EXEC SQL DECLARE C CURSOR FOR S END-EXEC.
* THE OPEN STATEMENT EVALUATES THE ACTIVE SET OF THE PREPARED
* QUERY USING THE SPECIFIED INPUT HOST VARIABLES, WHICH ARE
* SUBSTITUTED POSITIONALLY FOR PLACEHOLDERS IN THE PREPARED
* QUERY. FOR EACH OCCURRENCE OF A PLACEHOLDER IN THE
* STATEMENT THERE MUST BE A VARIABLE IN THE USING CLAUSE.
* THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE
* STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR MULTIPLE
* TIMES IN THE USING CLAUSE. THE USING CLAUSE MAY BE
* OMITTED ONLY IF THE STATEMENT CONTAINS NO PLACEHOLDERS.
* OPEN PLACES THE CURSOR AT THE FIRST ROW OF THE ACTIVE SET
* IN PREPARATION FOR A FETCH.
* A SINGLE DECLARED CURSOR MAY BE OPENED MORE THAN ONCE,
* OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES.
EXEC SQL OPEN C USING :DEPTNO END-EXEC.
* BRANCH TO PARAGRAPH NOTFOUND WHEN ALL ROWS HAVE BEEN
* RETRIEVED.
EXEC SQL WHENEVER NOT FOUND GO TO NOTFOUND END-EXEC.
GETROWS.
* THE FETCH STATEMENT PLACES THE SELECT LIST OF THE CURRENT
* ROW INTO THE VARIABLES SPECIFIED BY THE INTO CLAUSE, THEN
* ADVANCES THE CURSOR TO THE NEXT ROW. IF THERE ARE MORE
* SELECT-LIST FIELDS THAN OUTPUT HOST VARIABLES, THE EXTRA
* FIELDS ARE NOT RETURNED. SPECIFYING MORE OUTPUT HOST
* VARIABLES THAN SELECT-LIST FIELDS RESULTS IN AN ORACLE ERROR.
EXEC SQL FETCH C INTO :ENAME END-EXEC.
MOVE ENAME TO ENAMED.
DISPLAY ENAMED.
* LOOP UNTIL NOT FOUND CONDITION IS DETECTED.
GO TO GETROWS.
NOTFOUND.
MOVE SQLERRD(3) TO SQLERRD3.
DISPLAY " ".
DISPLAY "QUERY RETURNED ", SQLERRD3, " ROW(S).".
* THE CLOSE STATEMENT RELEASES RESOURCES ASSOCIATED WITH THE
* CURSOR.
EXEC SQL CLOSE C END-EXEC.
* COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL COMMIT RELEASE END-EXEC.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY!".
DISPLAY " ".
STOP RUN.
SQLERROR.
* ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING
* ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR.
DISPLAY SQLERRMC.
DISPLAY "IN ", ORASTXTC.
MOVE ORASLNR TO ORASLNRD.
DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC.
* DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP
* SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
* RELEASE RESOURCES ASSOCIATED WITH THE CURSOR.
EXEC SQL CLOSE C END-EXEC.
* ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
EXEC SQL ROLLBACK RELEASE END-EXEC.
STOP RUN.
9.13 方法4の使用方法
この項では、概要のみ説明します。詳細は、「Oracle動的SQL: 方法4」を参照してください。
方法4では、LOBはサポートされません。LOBアプリケーションおよび他の新規アプリケーションにはすべてANSI動的SQLを使用してください。
方法3を使用したプログラムでも処理できない種類の動的SQL文があります。選択リスト項目の数や、入力ホスト変数のプレースホルダの数が実行時まで不明な場合、プログラムでは記述子を使用する必要があります。記述子とは、動的SQL文で変数の完全な記述を保持するためにプログラムおよびOracleが使用するメモリー領域です。
複数行を戻す問合せでは、選択された列値は宣言されている出力ホスト変数のリストに繰り返しFETCH INTOされます。この選択リストがわからないときは、プリコンパイル時にINTO句でホスト変数リストを作成できません。たとえば、次の問合せでは2つの列値が戻されます。
EXEC SQL
SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.
ただし、この選択リストをユーザーに定義させると、その問合せによって戻される列の数はわからなくなります。
9.13.1 SQLDAの必要性
このような種類の動的問合せを処理するには、プログラムでDESCRIBE SELECT LISTコマンドを発行するとともに、SQL記述子領域(SQLDA)というデータ構造体を宣言する必要があります。この構造体は、問合せ選択リストの列の記述を保持しているため、選択記述子とも呼ばれます。
同様に、動的SQL文に記述されている入力ホスト変数のプレースホルダの数が不明な場合には、プリコンパイル時にUSING句によるホスト変数リストの設定はできません。
このような動的SQL文を処理するには、プログラムはDESCRIBE BIND VARIABLESコマンドを発行し、入力ホスト変数に対するプレースホルダの記述を格納するためにバインド記述子という別の種類のSQLDAを宣言する必要があります。(入力ホスト変数はバインド変数とも呼ばれます。)
プログラム内にアクティブなSQL文が複数ある(たとえば、プログラムが複数のカーソルにOPENを使用している)ときは、それぞれの文に専用のSQLDA文が必要になります。ただし、非並行のカーソルではSQLDAを再利用できます。なお、1つのプログラム内のSQLDAの数に制限はありません。
9.13.2 DESCRIBE文
DESCRIBEは、選択リスト項目または入力ホスト変数の記述を保持するために記述子を初期化します。
選択記述子を指定すると、準備した動的問合せのそれぞれの選択リスト項目がDESCRIBE SELECT LIST文によってチェックされます。これによって、選択リスト項目の名前、データ型、制約、長さ、位取りおよび精度が決定されます。その後、この情報は選択記述子に格納されます。
バインド記述子を指定すると、DESCRIBE BIND VARIABLES文によって準備した動的SQL文内の各プレースホルダを調べ、その名前および長さ、対応する入力ホスト変数のデータ型を確認します。続いて、この情報がそのバインド記述子に格納されます。たとえば、プレースホルダ名を使用して、入力ホスト変数値の入力をユーザーに要求できます。
9.13.3 SQLDAの内容
SQLDAはホスト・プログラムのデータ構造体です。この構造体は選択リスト項目または入力ホスト変数の記述を保持します。
SQLDAはホスト言語によって異なりますが、一般的な選択SQLDAには、問合せ選択リストに関する次の情報が格納されます。
-
DESCRIBEできる列の最大数
-
DESCRIBEによって検出された列の実際の数
-
列値を格納するバッファのアドレス
-
列値の長さ
-
列値のデータ型
-
標識変数の値のアドレス
-
列名を格納するバッファのアドレス
-
列名を格納するバッファのサイズ
-
列名の現行の長さ
一般的なバインドSQLDAには、SQL文内の入力ホスト変数に関する次の情報が格納されています。
-
DESCRIBEできるプレースホルダの最大数
-
DESCRIBEによって検出されたプレースホルダの実際の数
-
入力ホスト変数のアドレス
-
入力ホスト変数の長さ
-
入力ホスト変数のデータ型
-
標識変数のアドレス
-
プレースホルダ名を格納するバッファのアドレス
-
プレースホルダ名を格納するバッファのサイズ
-
プレースホルダ名の現行の長さ
-
標識変数名を格納するバッファのアドレス
-
標識変数名を格納するバッファのサイズ
-
標識変数名の現行の長さ
9.13.4 方法4の実装
方法4では、一般に次の順序で埋込みSQL文を使用します。
EXEC SQL
PREPARE STATEMENT-NAME
FROM { :HOST-STRING | STRING-LITERAL }
END-EXE
EXEC SQL
DECLARE CURSOR-NAME CURSOR FOR STATEMENT-NAME
END-EXEC.
EXEC SQL
DESCRIBE BIND VARIABLES FOR STATEMENT-NAME
INTO BIND-DESCRIPTOR-NAME
END-EXEC.
EXEC SQL
OPEN CURSOR-NAME
[USING DESCRIPTOR BIND-DESCRIPTOR-NAME]
END-EXEC.
EXEC SQL
DESCRIBE [SELECT LIST FOR] STATEMENT-NAME
INTO SELECT-DESCRIPTOR-NAME
END-EXEC.
EXEC SQL
FETCH CURSOR-NAME
USING DESCRIPTOR SELECT-DESCRIPTOR-NAME
END-EXEC.
EXEC SQL CLOSE CURSOR-NAME END-EXEC.
選択記述子およびバインド記述子の両方を使用する必要はありません。問合せ選択リストの列の数がわかっていて、入力ホスト変数のプレースホルダの数がわからない場合は、方法4のOPEN文と次に示す方法3のFETCH文を併用できます。
EXEC SQL FETCH EMPCURSOR INTO :HOST-VARIABLE-LIST END-EXEC.
逆に、入力ホスト変数のプレースホルダの数がわかっていて、選択リストの列の数がわからない場合は、次に示す方法3のOPEN文と方法4のFETCH文を併用できます。
EXEC SQL OPEN CURSORNAME [USING HOST-VARIABLE-LIST] END-EXEC.
方法4では、問合せ以外のSQL文に対してEXECUTEを使用できるので注意してください。
9.14 DECLARE STATEMENT文の使用方法
方法2、3および4では、次の文を使用することが必要な場合があります。
EXEC SQL [AT dbname] DECLARE statementname STATEMENT END-EXEC.
dbnameおよびstatementnameはPro*COBOLが使用する識別子で、ホスト変数でもプログラム変数でもありません。
DECLARE STATEMENTによって動的SQL文の名前が宣言されます。すると、この動的SQL文はPREPARE、EXECUTE、DECLARE CURSORおよびDESCRIBEで参照できます。デフォルト以外のデータベースで動的SQL文を実行するときに、この文が必要になります。方法2での使用例を次に示します。
EXEC SQL AT remotedb DECLARE sqlstmt STATEMENT END-EXEC. EXEC SQL PREPARE sqltmt FROM :sqlstring END-EXEC. EXEC SQL EXECUTE sqlstmt END-EXEC.
この例では、remotedbによって、SQL文をどこでEXECUTEするかをOracleに指示します。
方法3および方法4では、次の例に示すようにDECLARE CURSOR文がPREPARE文の前にある場合にもDECLARE STATEMENTが必要です。
EXEC SQL DECLARE sqlstmt STATEMENT END-EXEC. EXEC SQL DECLARE empcursor CURSOR FOR sqlstmt END-EXEC. EXEC SQL PREPARE sqlstmt FROM :sqlstring END-EXEC.
一般的な文の順序は次のとおりです。
EXEC SQL PREPARE sqlstmt FROM :sqlstring END-EXEC. EXEC SQL DECLARE empcursor CURSOR FOR sqlstmt END-EXEC.
9.15 ホスト表の使用
ホスト表の使用方法は、静的SQLでも動的SQLでも同じです。たとえば、動的SQL方法2で入力ホスト表を使用する場合は、次の構文を使用します。
EXEC SQL EXECUTE statementname USING :HOST-TABLE-LIST END-EXEC.
HOST-TABLE-LISTは1つ以上のホスト表で構成されています。方法3の場合は、次の構文を使用します。
OPEN cursorname USING :HOST-TABLE-LIST END-EXEC.
方法3で出力ホスト表を使用する場合は、次の構文を使用します。
FETCH cursorname INTO :HOST-TABLE-LIST END-EXEC.
方法4では、オプションのFOR句を使用して、入力ホスト表または出力ホスト表のサイズをOracleに認識させる必要があります。この方法は、ホスト言語の補足を参照してください。
9.16 PL/SQLの使用方法
Pro*COBOLは、PL/SQLブロックを単一のSQL文として扱います。したがってSQL文と同様に、PL/SQLブロックを文字列のホスト変数またはリテラルに格納できます。PL/SQLブロックを文字列に格納するときは、キーワードEXEC SQL EXECUTE、キーワードEND-EXECおよび文の終了記号は省略してください。
ただし、Pro*COBOLによるSQLおよびPL/SQLの処理方法には、次の2つの相違点があります。
-
PL/SQLホスト変数は、入力ホスト変数または出力ホスト変数のいずれ(あるいはその両方)であっても、すべて入力ホスト変数の場合と同様に扱う必要があります。
-
PL/SQLブロックに格納できるSQL文の数には制限がないため、PL/SQLブロックからはFETCHできません。ただし、カーソル変数を使用して同様の機能を実装できます。
9.16.2 方法2の場合
PL/SQLブロック内の入力ホスト変数および出力ホスト変数の数がわかっていれば、方法2で通常どおりPL/SQL文字列をPREPAREおよびEXECUTEできます。
USING句にはすべてのホスト変数を指定する必要があります。このPL/SQL文字列のEXECUTEが完了すると、USING句内のホスト変数はPREPARE後の文字列内の対応するプレースホルダに置き換わります。Pro*COBOLでPL/SQLホスト変数がすべて入力ホスト変数として扱われても、値は正しく割り当てられます。入力(プログラム)値は入力ホスト変数に割り当てられ、出力(列)値は出力ホスト変数に割り当てられます。
PREPAREの後のPL/SQL文字列内のプレースホルダはすべて、USING句内のホスト変数に対応している必要があります。このため、PREPAREされた文字列で同じプレースホルダが複数回使用されている場合は、それぞれがUSING句内のホスト変数に対応している必要があります。
9.16.4 方法4の場合
PL/SQLブロックに数の不明な入力ホスト変数または出力ホスト変数が含まれている場合は、方法4を必ず使用します。
方法4を使用するには、すべての入力ホスト変数および出力ホスト変数について1つのバインド記述子を設定します。DESCRIBE BIND VARIABLESを実行すると、入力ホスト変数および出力ホスト変数に関する情報がそのバインド記述子に保存されます。PL/SQLホスト変数はすべて入力ホスト変数に対応付けられた方法で参照するため、DESCRIBE SELECT LISTを実行しても効果はありません。
方法4でのバインド記述子の詳細は、ホスト言語補足を参照してください。
動的SQL方法4では、タイプが「table」のパラメータを指定してホスト配列をPL/SQLプロシージャにバインドできません。
9.17 動的SQL文のキャッシング
文キャッシュは、セッションごとの文のキャッシュを提供および管理する機能です。文キャッシュによって、サーバーではカーソルが使用できる状態になり、文を再解析する必要はありません。文のキャッシングは、プリコンパイラ・アプリケーションで有効にでき、動的SQL文に依存するすべてのアプリケーションのパフォーマンス向上に役立ちます。パフォーマンスの向上は、動的文を再利用する際の解析のオーバーヘッドをなくすことで達成されます。プリコンパイラ・アプリケーションのユーザーは、新しいコマンドライン・オプションstmt_cache(文のキャッシュ・サイズ用)を使用することで、パフォーマンスの向上を実現できます。このオプションにより、動的文の文のキャッシュが有効になります。この新しいオプションを有効にすると、セッション作成時に文のキャッシュが作成されます。キャッシングは動的文に対してのみ適用され、静的文用のカーソル・キャッシュとこの新しい機能は共存します。
コマンドライン・オプションstmt_cacheには、0から65535の範囲で任意の値を指定できます。デフォルト(値0)で、文キャッシングは無効化されています。stmt_cacheオプションでは、アプリケーションにそれぞれの動的SQL文の予測数を保持するように設定できます。
例9-1 stmt_cacheオプションの使用
次の例は、stmt_cacheオプションの使用方法を示しています。このプログラムでは、表に行を挿入し、挿入した行をループ内のカーソルを使用して選択します。このプログラムのプリコンパイルにstmt_cacheオプションを使用すると、通常のプリコンパイルよりもパフォーマンスが向上します。
*****************************************************************
* stmtcache: *
* *
* NOTE: *
* When this program is used to measure the performance with and *
* without stmt_cache option, do the following changes in the *
* program, *
* 1. Increase ROWSCNT to high value, say 10000. *
* 2. Remove all the DISPLAY statements, usually which comsumes *
* significant portion of the total program execution time. *
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. stmtcache.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
* EMBEDDED COBOL (file "STMTCACHE.PCO")
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VARYING.
01 PASSWD PIC X(10) VARYING.
01 DYNSTMT PIC X(100) VARYING.
01 DYNSTMT2 PIC X(100) VARYING.
01 ENAME PIC X(10).
01 COMM PIC X(9).
EXEC SQL END DECLARE SECTION END-EXEC.
01 ROWSCNT PIC 9(4) COMP VALUE 10.
01 LOOPNO PIC 9(4).
01 STRINGFIELDS.
02 STR PIC X(18) VARYING.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
PERFORM LOGON.
MOVE "INSERT INTO BONUS (ENAME, COMM) VALUES (:A,:B)"
TO DYNSTMT-ARR.
MOVE 53 TO DYNSTMT-LEN.
DISPLAY "Inserts ", ROWSCNT, " rows into BONUS table.".
PERFORM INSDATA VARYING LOOPNO FROM 1 BY 1
UNTIL LOOPNO > ROWSCNT.
DISPLAY " ".
DISPLAY "Fetches the inserted rows from BONUS.".
DISPLAY " ENAME COMM".
MOVE "SELECT ENAME, COMM FROM BONUS WHERE COMM=:A"
TO DYNSTMT2-ARR.
MOVE 43 TO DYNSTMT2-LEN.
MOVE 1 TO LOOPNO.
* Loops for preparing and fetching ROWSCNT number of times
FETCHDATA.
* Do the prepare in the loop so that the advantage of
* stmt_caching is visible
EXEC SQL PREPARE S2 FROM :DYNSTMT2 END-EXEC.
EXEC SQL DECLARE C1 CURSOR FOR S2
END-EXEC.
EXEC SQL OPEN C1 USING :LOOPNO END-EXEC.
EXEC SQL WHENEVER NOT FOUND GO TO NOTFOUND END-EXEC.
GETROWS.
* Close the cursor so that the reparsing is not required for
* stmt_cache
EXEC SQL FETCH C1 INTO :ENAME, :COMM
END-EXEC.
DISPLAY ENAME, COMM.
GO TO GETROWS.
NOTFOUND.
EXEC SQL CLOSE C1 END-EXEC.
COMPUTE LOOPNO = LOOPNO + 1.
IF LOOPNO <= ROWSCNT THEN
GO TO FETCHDATA
END-IF.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
LOGON.
MOVE "scott" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "tiger" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
* Populates the host variable and insert into the table
INSDATA.
EXEC SQL PREPARE S1 FROM :DYNSTMT END-EXEC.
MOVE " " TO STR.
STRING "EMP_", LOOPNO INTO STR
END-STRING.
MOVE STR TO ENAME.
MOVE LOOPNO TO COMM.
EXEC SQL EXECUTE S1 USING :ENAME, :COMM END-EXEC.
* HANDLES SQL ERROR CONDITIONS
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.