4 埋込みSQLの使用方法
この章は、次の項で構成されています。
この章は、埋込みSQLプログラミングの基本技術を理解し、利用する上で役立ちます。ホスト変数、インジケータ変数、カーソル、カーソル変数、およびOracleデータの挿入、更新、選択、削除を行う基本的なSQLコマンドの使用方法について学習します。
4.1 ホスト変数の使用方法について
4.1.1 出力変数と入力変数
その使用方法によって、ホスト変数は出力変数または入力変数と呼ばれます。SELECT
文またはFETCH
文のINTO
句で使用されるホスト変数は、Oracleからの列値の出力を保持するので、出力ホスト変数と呼ばれます。Oracleでは、列値をINTO
句の対応する出力ホスト変数に割り当てます。
SQL文のその他のホスト変数の値は、プログラムがそれをOracleに入力するため、すべて入力ホスト変数と呼ばれます。たとえば、入力ホスト変数は、INSERT
文のVALUES
句とUPDATE
文のSET
句で使用します。また、WHERE
句、HAVING
句およびFOR
句でも使用します。実際に、値または式が使用可能であれば、1つのSQL文で複数の入力ホスト変数を使用できます。
ORDER
BY
句では、ホスト変数を使用使用できますが、定数またはリテラルとして扱われるため、ホスト変数のコンテンツには効果がありません。たとえば、次のようなSQL文があるとします。
EXEC SQL SELECT ename, empno INTO :name, :number FROM emp ORDER BY :ord;
ここでは、入力ホスト変数ordが含まれているように見えます。しかし、この句のホスト変数は、定数として扱われ、ordの値にかかわらず、順序付けは行われません。
入力ホスト変数を使用して、SQLキーワードまたはデータベース・オブジェクトの名前を指定することはできません。したがって、ALTER
、CREATE
およびDROP
などのデータ定義文(DDLとも呼ばれる)では、入力ホスト変数を使用できません。次の例では、DROP
TABLE
文は無効です。
EXEC SQL BEGIN DECLARE SECTION; table_name CHARACTER(30); EXEC SQL END DECLARE SECTION; display 'Table name? '; read table_name; EXEC SQL DROP TABLE :table_name; -- host variable not allowed
入力ホスト変数を含むSQL文をOracleで実行する前に、それらの入力ホスト変数に値を割り当てる必要があります。次の例を検討してください:
EXEC SQL BEGIN DECLARE SECTION; emp_number INTEGER; emp_name CHARACTER(20); EXEC SQL END DECLARE SECTION; -- get values for input host variables display 'Employee number? '; read emp_number; display 'Employee name? '; read emp_name; EXEC SQL INSERT INTO EMP (EMPNO, ENAME) VALUES (:emp_number, :emp_name);
INSERT
文のVALUES
句にある入力変数の前にコロンが付いていることに注意してください。
4.2 インジケータ変数の使用について
任意のホスト変数をオプションのインジケータ変数に関連付けることができます。標識変数に関連付けたホスト変数をSQL文内で使用するたびに、結果コードが対応する標識変数内に格納されます。したがって、インジケータ変数により、ホスト変数を監視できます。
インジケータ変数は、VALUES
句またはSET
句ではNULLを入力ホスト変数に割り当てるために、INTO
句では出力ホスト変数でNULLまたは切り捨てられた値を検出するために使用します。
4.2.2 出力変数
出力ホスト変数の場合、Oracleでインジケータ変数に割り当てられる値には、次の意味があります。
-
-2: Oracleでは切り捨てられた列値をホスト変数に割り当てましたが、数値が大きすぎるため、元の長さの列値をインジケータ変数に割り当てることができませんでした。
-
-1: 列値はNULLであるため、ホスト変数の値は不確定です。
-
0: Oracleでは列値をそのままの形でホスト変数に割り当てました。
-
> 0: Oracleでは切り捨てられた列値をホスト変数に割り当て、元の列の長さ(マルチバイトのグローバリゼーション・サポート・ホスト変数の場合、バイト単位ではなく文字単位で表される)をインジケータ変数に割り当て、SQLCAのSQLCODEをゼロに設定しました。
インジケータ変数は、宣言部では2バイト整数として定義する必要があり、SQL文では(キーワードINDICATORを使用しない場合)前にコロンを付け、関連付けられたホスト変数に追加する必要があります。
4.2.3 NULLの挿入
インジケータ変数を使用して、NULLを挿入できます。挿入の前に、NULLにする列ごとに、該当するインジケータ変数を次の例に示すように-1に設定します。
set ind_comm = -1; EXEC SQL INSERT INTO EMP (EMPNO, COMM) VALUES (:emp_number, :commission:ind_comm);
インジケータ変数ind_commは、NULLがCOMM
列に格納されるように指定します。
かわりに、次のようにNULLをハードコードすることもできます。
EXEC SQL INSERT INTO EMP (EMPNO, COMM) VALUES (:emp_number, NULL);
これは柔軟性が少なくなりますが、読みやすくなります。
通常、NULLは、次の例のように条件付きで挿入します。
display 'Enter employee number or 0 if not available: '; read emp_number; IF emp_number = 0 THEN set ind_empnum = -1; ELSE set ind_empnum = 0; ENDIF; EXEC SQL INSERT INTO EMP (EMPNO, SAL) VALUES (:emp_number:ind_empnum, :salary);
4.2.6 NULLの検査
WHERE
句のインジケータ変数を使用して、次のようにNULLがないか検査できます。
EXEC SQL SELECT ENAME, SAL INTO :emp_name, :salary FROM EMP WHERE :commission:ind_comm IS NULL ...
ただし、NULLを互いに、あるいは他の値と比較するために比較演算子を使用することはできません。たとえば、次のSELECT
文は、COMM
列に1つ以上のNULLが含まれていれば失敗します。
EXEC SQL SELECT ENAME, SAL INTO :emp_name, :salary FROM EMP WHERE COMM = :commission:ind_comm;
次の例は、値のいくつかがNULLの可能性がある場合に、値を比較して等しいかどうかを調べる方法を示しています。
EXEC SQL SELECT ENAME, SAL INTO :emp_name, :salary FROM EMP WHERE (COMM = :commission) OR ((COMM IS NULL) AND (:commission:ind_comm IS NULL));
4.3 基本的なSQL文
実行可能なSQL文を使用すれば、Oracleデータの問合せ、操作および制御ができ、表、ビュー、索引などのOracleオブジェクトの作成、定義およびメンテナンスができます。この章では、データ操作文(DML)とカーソル制御文を中心に取り上げます。次のSQL文により、Oracleデータの問合せと操作ができます。
-
SELECT
: 1つ以上の表から行を戻します。 -
INSERT
: 新しい行を表に追加します。 -
UPDATE
: 表内の行を変更します。 -
DELETE
: 表から行を削除します。
INSERT
、UPDATE
またはDELETE
などのデータ操作文を実行するときは、入力ホスト変数の値の設定以外に、文が成功するか失敗するかのみを考えます。これは、SQLCAを調べればわかります。(SQL文を実行すると、SQLCA変数が設定されます。)次の2通りの方法で調べることができます。
-
WHENEVER
文による暗黙的チェック -
SQLCA変数の明示的なチェック
または、MODE={ANSI|ANSI14}
の場合、状態変数SQLSTATE
またはSQLCODE
をチェックできます。詳細は、MODE={ANSI|ANSI14}の場合の状態変数の使用についてを参照してください。
ただし、SELECT
文(問合せ)を実行する場合は、戻されるデータの行も処理する必要があります。問合せは次のように分類できます。
-
行を戻さない問合せ(有無のみを調べる)
-
1行のみ戻す問合せ
-
複数行を戻す問合せ
複数の行を戻す問合せには、明示的に宣言されたカーソルまたはカーソル変数(または、ホスト配列の使用方法で説明されているホスト配列の使用)が必要です。明示カーソルの定義および制御は、次の埋込みSQL文で行います。
-
DECLARE
: カーソルを指定し、問合せと関連付けます。 -
OPEN
: 問合せを実行し、アクティブ・セットを識別します。 -
FETCH
: カーソルを進め、アクティブ・セット内の行を1行ずつ取得します。 -
CLOSE
: カーソルを無効にします(アクティブ・セットは未定義になります)。
後続の項では、まずINSERT
、UPDATE
、DELETE
と1行のSELECT
文のコードの方法を学習します。その後、複数行のSELECT
文に進みます。
4.3.1 行の選択
データベースに対する問合せは、一般的なSQL操作です。問合せを発行するには、SELECT
文を使用します。次の例では、EMP
表に問合せを行います。
EXEC SQL SELECT ENAME, JOB, SAL + 2000 INTO :emp_name, :job_title, :salary FROM EMP WHERE EMPNO = :emp_number;
キーワードSELECT
の後の列名と式により、選択リストが作成されます。例の選択リストには、3つの項目が含まれています。WHERE
句(および存在する場合はそれに続く句)で指定された条件のもと、OracleではINTO
句のホスト変数に列値を戻します。選択リスト内の項目数は、INTO
句内のホスト変数と同数であり、すべての戻り値を格納する場所があります。
最も簡単な場合、1つの問合せで1行が戻されると、その形式は前述の例のようになります(EMPNO
は一意キーです)。ただし、1つの問合せで複数行を戻すことができる場合、カーソルを使用してそれらの行をフェッチするか、行を選択してホスト配列に入れる必要があります。
1行のみを戻す問合せを作成したにもかかわらず、実際には複数行が戻される場合、その結果は、SELECT_ERROR
オプションをどのように指定するかによって異なります。SELECT_ERROR
=
YES
(デフォルト)の場合、複数行が戻されると、次のメッセージが表示されます。
ORA-01422: exact fetch returns more than requested number of rows
SELECT_ERROR=NO
の場合、1行が戻され、エラーは発生しません。
4.3.2 使用可能な句
SELECT
文では、次の標準的なSQL句をすべて使用できます。INTO
、FROM
、WHERE
、CONNECT
BY
、START
WITH
、GROUP
BY
、HAVING
、ORDER
BY
およびFOR
UPDATE
OF
です。
4.3.3 行の挿入
表またはビューに行を追加するには、INSERT
文を使用します。次の例では、EMP
表に1行追加します。
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES (:emp_number, :emp_name, :salary, :dept_number);
列リストで指定する各列は、INTO
句で指定した表のものである必要があります。VALUES
句では、挿入される値の行を指定しています。値には、定数、ホスト変数、SQL式または擬似列(USER
やSYSDATE
など)を指定できます。
VALUES
句の値の数は、列リストにある名前の数と同じにする必要があります。ただし、VALUES
句にCREATE
TABLE
で定義した表の各列の値が定義どおりの順序で含まれている場合は、列リストを省略してもかまいません。
4.3.4 副問合せの使用方法
-
SELECT
、UPDATE
およびDELETE
文のWHERE
、HAVING
およびSTART
WITH
句での比較の値を指定 -
CREATE
TABLE
またはINSERT
文で挿入する行のセットを定義 -
UPDATE
文のSET
句の値を定義
たとえば、ある表から別の表に行をコピーするには、INSERT
文のVALUES
句を次のように副問合せに置き換えます。
EXEC SQL INSERT INTO EMP2 (EMPNO, ENAME, SAL, DEPTNO) SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE JOB = :job_title;
中間結果を取得するために、INSERT
文で副問合せがどのように使用されているかに注意してください。
4.3.5 行の更新
表またはビューの指定した列の値を変更するには、UPDATE
文を使用します。次の例では、EMP
表のSAL
列とCOMM
列を更新します。
EXEC SQL UPDATE EMP SET SAL = :salary, COMM = :commission WHERE EMPNO = :emp_number;
オプションのWHERE
句を使用すれば、行が更新される条件を指定できます。WHERE句の使用方法を参照してください。
SET
句には、値を指定する必要のある1つ以上の行の名前をリストします。副問合せを使用すれば、次の例のように値を指定できます。
EXEC SQL UPDATE EMP SET SAL = (SELECT AVG(SAL)*1.1 FROM EMP WHERE DEPTNO = 20) WHERE EMPNO = :emp_number;
4.4 カーソル
1つの問合せで複数の行が戻される場合、カーソルを明示的に定義すれば、次のことができます。
-
問合せによって戻された最初の行以後の処理
-
現在どの行が処理されているかの追跡および記録
カーソルは、問合せによって戻された行の集合内で現在の行はどれかを示します。これによって、プログラムは一度に1行ずつ処理できます。次の文を使用して、カーソルの定義および操作を行います。
-
DECLARE
-
OPEN
-
FETCH
-
CLOSE
まず、DECLARE
文を使用して、カーソルに名前を付け、問合せに関連付けます。
OPEN
文により問合せが実行され、問合せの検索条件を満たす行がすべて識別されます。これらの行は、カーソルのアクティブ・セットと呼ばれる集合を形成します。このカーソルをオープンした後、これを使用して、関連付けられた問合せによって戻された行を取得できます。
アクティブ・セットの行は1行ずつ取り出されます(ホスト配列を使用していない場合)。アクティブ・セット内の現在の行を取得するには、FETCH
文を使用します。FETCH
文は、すべての行が取得されるまで繰り返し実行できます。
アクティブ・セットからの行の取得が完了したら、CLOSE
文でこのカーソルを無効にします。アクティブ・セットは未定義になります。
4.4.1 カーソルの宣言
次の例のように、DECLARE
文を使用して、カーソルに名前を付け、問合せに関連付けることで、カーソルを定義できます。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, EMPNO, SAL FROM EMP WHERE DEPTNO = :dept_number;
カーソル名は、ホスト変数やプログラム変数ではなく、プリコンパイラで使用される識別子であるため、宣言部では定義しないでください。したがって、あるプリコンパイル単位から別のプリコンパイル・ユニットにカーソル名を渡すことはできません。また、カーソル名にはハイフンは使用できません。長さは任意ですが、重要な意味があるのは先頭の31文字までです。ANSI互換性を維持するため、カーソル名は18字以内にしてください。
カーソルに関連付けられたSELECT
文には、INTO
句を指定できません。INTO
句と出力ホスト変数のリストは、FETCH
文で指定します。
DECLARE
文は宣言文のため、カーソルを参照する他のすべてのSQL文より(論理的にではなく)物理的に前に配置する必要があります。つまり、カーソルの前の参照はできません。次の例では、OPEN
文の位置が間違っています。
EXEC SQL OPEN emp_cursor; -- misplaced OPEN statement EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, EMPNO, SAL FROM EMP WHERE ENAME = :emp_name;
カーソル制御文はすべて、同じプリコンパイル・ユニット内で指定する必要があります。たとえば、Aファイルの中でカーソルを宣言して、それをBファイルでオープンすることはできません。
ホスト・プログラムでは、必要な数のカーソルを宣言できます。ただし、1つのファイルでは、すべてのDECLARE
文は一意である必要があります。つまり、カーソルの適用範囲は1つのファイル全体に及ぶため、1つのプリコンパイル・ユニット内には、たとえ別のブロックやプロシージャ内であっても、同じ名前のカーソルを2つ宣言することはできません。多数のカーソルを使用する場合は、MAXOPENCURSORS
オプションの指定が必要になることがあります。詳細は、MAXOPENCURSORSを参照してください。
4.4.2 カーソルのオープン
問合せを実行して、アクティブ・セットを識別するには、OPEN
文を使用します。次の例では、emp_cursorという名前のカーソルをオープンします。
EXEC SQL OPEN emp_cursor;
OPEN
により、カーソルはアクティブ・セットの最初の行の直前に位置付けられます。また、SQLCA内のSQLERRD(3)に保存されている処理済の行数もゼロに設定されます。ただし、この時点では実際に取り出される行はありません。それはFETCH
文によって行われます。
カーソルをオープンすると、問合せの入力ホスト変数は、カーソルを再オープンするまで再検査されません。つまり、アクティブ・セットは変更されません。アクティブ・セットを変更するには、カーソルを再オープンする必要があります。
通常、カーソルはクローズしてから再オープンする必要があります。ただし、MODE=ORACLE
を指定する場合(デフォルト)、カーソルをクローズしてから再オープンする必要はありません。これによってパフォーマンスが向上する可能性があります。詳細は、パフォーマンス・チューニングを参照してください。
OPENによって行われる作業量は、HOLD_CURSOR、RELEASE_CURSOR
およびMAXOPENCURSORS
の3つのプリコンパイラ・オプションの値によって決まります。詳細は、プリコンパイラ・オプションの使用についてを参照してください。
4.4.3 カーソルからのフェッチ
アクティブ・セットから行を取得して、結果を格納する出力ホスト変数を指定するには、FETCH
文を使用します。カーソルに関連付けられたSELECT
文には、INTO
句を指定できないことを思い出してください。INTO
句と出力ホスト変数のリストは、FETCH
文で指定します。次の例では、フェッチした行を3つのホスト変数に格納します。
EXEC SQL FETCH emp_cursor INTO :emp_name, :emp_number, :salary;
カーソルは、あらかじめ宣言し、オープンしておく必要があります。最初にFETCH
文を実行すると、カーソルは、アクティブ・セットの最初の行の前から最初の行に移動します。この行がカレント行になります。その後FETCH
文を実行するたびに、カーソルはアクティブ・セット内の次の行に進み、現在の行が変わっていきます。カーソルはアクティブ・セット内を順方向にしか進みません。すでにフェッチした行に戻るには、カーソルを再オープンし、アクティブ・セットの最初の行からやり直す必要があります。
アクティブ・セットを変更する場合、カーソルに関連付けられた問合せの入力ホスト変数に新しい値を割り当て、カーソルを再オープンしてください。MODE={ANSI | ANSI14 | ANSI13}
に設定されている場合、カーソルを一度クローズしてから、再オープンする必要があります。
次の例に示すように、異なる出力ホスト変数セットを使用して同じカーソルからフェッチできます。ただし、各FETCH
文のINTO
句の対応するホスト変数は、同じデータ型であることが必要です。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 20; ... EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND DO ... LOOP EXEC SQL FETCH emp_cursor INTO :emp_name1, :salary1; EXEC SQL FETCH emp_cursor INTO :emp_name2, :salary2; EXEC SQL FETCH emp_cursor INTO :emp_name3, :salary3; ... ENDLOOP;
アクティブ・セットが空か、それ以上の列がない場合、FETCH
は、データが見つからないことを示すOracle警告コードをSQLCAのSQLCODE
(または、MODE=ANSIの場合は状態変数SQLSTATE
)に戻します。出力ホスト変数のステータスは不確定です。(通常のプログラムでは、WHENEVER NOT FOUND
文がこのエラーを検出します。)カーソルを再利用するには、再オープンする必要があります。
4.4.4 カーソルのクローズ
アクティブ・セットからの行のフェッチが終了したら、カーソルをクローズし、カーソルのオープンによって確保していたリソース(記憶域など)を解放します。カーソルがクローズされると、解析ロックが解放されます。どのリソースが解放されるかは、オプションHOLD_CURSOR
およびRELEASE_CURSOR
の指定によって異なります。次の例では、emp_cursor
という名前のカーソルをクローズします。
EXEC SQL CLOSE emp_cursor;
クローズしたカーソルのアクティブ・セットは未定義になるため、クローズしたカーソルからフェッチすることはできません。必要であれば、(たとえば、入力ホスト変数に新しい値を指定して)カーソルを再オープンできます。
MODE={ANSI13|ORACLE}
の場合、コミットまたはロールバックを発行すると、CURRENT OF
句で参照されるカーソルがクローズされます。他のカーソルには、コミットまたはロールバックによる影響はなく、オープンの場合は、オープンのままです。ただし、MODE={ANSI|ANSI14}
の場合は、コミットまたはロールバックを発行すると、すべての明示カーソルがクローズされます。
4.4.5 CURRENT OF句の使用方法
指定したカーソルからフェッチした最後の行を参照するには、DELETE
またはUPDATE
文でCURRENT OF
cursor_name句を使用します。カーソルをオープンし、行に位置付けておく必要があります。フェッチが行われていない場合や、カーソルがオープンされていない場合には、CURRENT OF
句を使用するとエラーが発生し、1行も処理されません。
UPDATE
またはDELETE
文のCURRENT OF
句で参照されるカーソルを宣言するとき、FOR UPDATE OF
句をオプションとして指定できます。CURRENT OF
句は、必要に応じてFOR UPDATE
句を追加するようにプリコンパイラに指示します。詳細は、FOR UPDATE OF句の使用についてを参照してください。
次の例では、CURRENT OF
句を使用して、emp_cursorという名前のカーソルから最後にフェッチされた行を参照します。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK' FOR UPDATE OF SAL; ... EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND DO ... LOOP EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; ... EXEC SQL UPDATE EMP SET SAL = :new_salary WHERE CURRENT OF emp_cursor; ENDLOOP;
4.4.6 制限事項
明示的なFOR UPDATE OF
または暗黙的なFOR UPDATE
によって行の排他ロックが取得されます。いずれの行も、フェッチされるときではなくオープン時にロックされ、コミットまたはロールバックを行うとロックは解除されます。コミット後にFOR UPDATE
カーソルからフェッチしようとすると、次のエラーが発生します。
ORA-01002: fetch out of sequence
CURRENT OF
句ではホスト配列を使用できません。他の方法は、CURRENT OF句の擬似実行についてを参照してください。また、関連付けられた1つのFOR UPDATE OF
句で複数の表を参照することもできず、つまり、CURRENT OF
句との結合ができないということです。さらに、動的SQLではCURRENT OF
句を使用できません。
4.4.7 一般的な文の順序
次の例は、アプリケーション・プログラムでのカーソル制御文の一般的な順序を示しています。
-- Define a cursor. EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, JOB FROM EMP WHERE EMPNO = :emp_number FOR UPDATE OF JOB; -- Open the cursor and identify the active set. EXEC SQL OPEN emp_cursor; -- Exit if the last row was already fetched. EXEC SQL WHENEVER NOT FOUND DO no_more; -- Fetch and process data in a loop. LOOP EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title; -- host-language statements that operate on the fetched data EXEC SQL UPDATE EMP SET JOB = :new_job_title WHERE CURRENT OF emp_cursor; ENDLOOP; ... ROUTINE no_more BEGIN -- Disable the cursor. EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; exit program; END no_more;
4.4.8 完全な例
次のプログラムは、カーソルとFETCH
文の使用方法を説明するためのものです。プログラムでは、部門番号の入力を要求してから、その部門の全従業員の名前を表示します。
最後の1つのフェッチを除くすべてのフェッチで1行ずつ戻され、フェッチ中にエラーが検出されなければ、成功のステータス・コードが戻されます。最後のフェッチは失敗し、「データが見つかりません」というOracle警告コードがSQLCAのSQLCODE
に戻されます。実際にフェッチされた行の累積数は、SQLCAのSQLERRD(3)
に示されています。
-- declare host variables EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); emp_name CHARACTER(10); dept_number INTEGER; EXEC SQL END DECLARE SECTION; -- copy in the SQL Communications Area EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; -- handle processing errors EXEC SQL WHENEVER SQLERROR DO sql_error; -- log on to Oracle EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; -- declare a cursor EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME FROM EMP WHERE DEPTNO = :dept_number; display 'Department number? '; read dept_number; -- open the cursor and identify the active set EXEC SQL OPEN emp_cursor; -- exit if the last row was already fetched EXEC SQL WHENEVER NOT FOUND DO no_more; display 'Employee Name'; display '-------------'; -- fetch and process data in a loop LOOP EXEC SQL FETCH emp_cursor INTO :emp_name; display emp_name; ENDLOOP; ROUTINE no_more BEGIN EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; display 'End of program'; exit program; END no_more; ROUTINE sql_error BEGIN EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error; END sql_error;
4.5 カーソル変数
この項では、カーソル変数の概要を簡単に説明します。詳細は、ホスト言語の補足資料および「カーソル変数」を参照してください。
Pro*COBOLおよびPro*FORTRANプリコンパイラで静的埋込みSQLを使用すると、カーソル変数を宣言できます。カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セットの中のカレント行を指します。カーソルとカーソル変数との違いは、定数と変数との違いと同じです。カーソルは静的で、カーソル変数は特定の問合せに結び付けられていないため、動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。
また、カーソル変数に新しい値を割り当て、サブプログラム(Oracleデータベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中化できます。
まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の4つの文を使用してカーソル変数を制御します。
-
ALLOCATE
-
OPEN ... FOR
-
FETCH
-
CLOSE
カーソル変数を宣言して、メモリーを割り当てたら、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。サーバー側でOPEN、FORを使用して複数行の問合せのためにカーソル変数をオープンし、クライアント側ではその変数からFETCH
を行い、サーバー側かクライアント側のいずれかでCLOSE
します。
4.5.2 カーソル変数の割当て
カーソル変数にメモリーを割り当てるには、ALLOCATE
文を使用します。構文は次のとおりです。
EXEC SQL ALLOCATE <cursor_variable>;
4.5.3 カーソル変数のオープン
OPEN ... FOR
文を使用して、カーソル変数を複数行問合せに関連付け、問合せを実行し、アクティブ・セットを特定します。構文は次のとおりです。
EXEC SQL OPEN <cursor_variable> FOR <select_statement>;
SELECT
文では、入力ホスト変数およびPL/SQL変数、パラメータ、ファンクションを参照できますが、FOR UPDATE
には使用できません。次の例では、emp_cvという名前のカーソル変数をオープンします。
EXEC SQL OPEN emp_cv FOR SELECT * FROM EMP;
カーソル変数はサーバー側でオープンする必要があります。カーソル変数を入力ホスト変数として無名のPL/SQLブロックに渡すことでオープンします。実行時に、そのブロックはOracleサーバーに送られ、実行されます。次の例では、カーソル変数を宣言し、初期化してから、PL/SQLブロックに渡し、そこでカーソル変数がオープンされます。
EXEC SQL BEGIN DECLARE SECTION; ... -- declare cursor variable emp_cur SQL_CURSOR; EXEC SQL END DECLARE SECTION; -- initialize cursor variable EXEC SQL ALLOCATE :emp_cur; EXEC SQL EXECUTE -- pass cursor variable to PL/SQL block BEGIN -- open cursor variable OPEN :emp_cur FOR SELECT * FROM EMP; END; END-EXEC;
一般に、カーソル変数を仮パラメータの1つとして宣言するストアド・プロシージャをコールすることで、カーソル変数をPL/SQLに渡します。たとえば、次のパッケージ・プロシージャは、emp_cvという名前のカーソル変数をオープンします。
CREATE PACKAGE emp_data AS -- define REF CURSOR type TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- declare formal paramter of that type PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS BEGIN -- open cursor variable OPEN emp_cv FOR SELECT * FROM emp; END open_emp_cv; END emp_data;
このパッケージ・プロシージャは、次のように、どのアプリケーションからでもコールできます。
EXEC SQL EXECUTE BEGIN emp_data.open_emp_cv(:emp_cur); END; END-EXEC;
4.5.4 カーソル変数からのフェッチ
複数行問合せのためにカーソル変数をオープンした後、FETCH
文を使用して、アクティブ・セットから行を1行ずつ取得します。構文は次のとおりです。
EXEC SQL FETCH cursor_variable_name INTO {record_name | variable_name[, variable_name, ...]};
カーソル変数によって戻される各列値は、データ型に互換性がある場合には、INTO
句で対応するフィールドまたは変数に割り当てられます。
FETCH
文は、クライアント側で実行する必要があります。次の例では、行をフェッチして、emp_recという名前のホスト・レコードに入れます。
-- exit loop when done fetching EXEC SQL WHENEVER NOT FOUND DO no_more; LOOP -- fetch row into record EXEC SQL FETCH :emp_cur INTO :emp_rec; -- process the data ENDLOOP;