| Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 11g リリース2(11.2) B61344-01 |
|
 前へ |
 次へ |
この章の項目は次のとおりです。
この章は、埋込みSQLプログラミングの基本技術を理解し、利用する上で役立ちます。ホスト変数、インジケータ変数、カーソル、カーソル変数、およびOracleデータの挿入、更新、選択、削除を行う基本的なSQLコマンドの使用方法について学習します。
Oracleでは、ホスト変数を使用してプログラムにデータやステータス情報を渡します。一方、プログラムでは、ホスト変数を使用してOracleにデータを渡します。
その使用方法によって、ホスト変数は出力変数または入力変数と呼ばれます。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
Oracleで入力ホスト変数を含むSQL文が実行される前に、プログラムではそれらの変数に値を割り当てる必要があります。次の例を考えてみましょう。
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句にある入力変数の前にコロンが付いていることに注意してください。
任意のホスト変数をオプションのインジケータ変数に関連付けることができます。そのホスト変数がSQL文で使用されるたびに、結果コードが関連付けられたインジケータ変数に格納されます。したがって、インジケータ変数により、ホスト変数を監視できます。
インジケータ変数は、VALUES句またはSET句ではNULLを入力ホスト変数に割り当てるために、INTO句では出力ホスト変数でNULLまたは切り捨てられた値を検出するために使用します。
出力ホスト変数の場合、Oracleでインジケータ変数に割り当てられる値には、次の意味があります。
-2: Oracleでは切り捨てられた列値をホスト変数に割り当てましたが、数値が大きすぎるため、元の長さの列値をインジケータ変数に割り当てることができませんでした。
-1: 列値はNULLであるため、ホスト変数の値は不確定です。
0: Oracleでは列値をそのままの形でホスト変数に割り当てました。
> 0: Oracleでは切り捨てられた列値をホスト変数に割り当て、元の列の長さ(マルチバイトのグローバリゼーション・サポート・ホスト変数の場合、バイト単位ではなく文字単位で表される)をインジケータ変数に割り当て、SQLCAのSQLCODEをゼロに設定しました。
インジケータ変数は、宣言部では2バイト整数として定義する必要があり、SQL文では(キーワードINDICATORを使用しない場合)前にコロンを付け、関連付けられたホスト変数に追加する必要があります。
インジケータ変数を使用して、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);
インジケータ変数を使用して、次の例のように、戻されたNULLを操作できます。
EXEC SQL SELECT ENAME, SAL, COMM INTO :emp_name, :salary, :commission:ind_comm FROM EMP WHERE EMPNO = :emp_number; IF ind_comm = -1 THEN set pay = salary; -- commission is null; ignore it ELSE set pay = salary + commission; ENDIF;
DBMS=V6の場合、次の例のように、インジケータ変数がないホスト変数に、NULLを選択またはフェッチにより入れることができます。
-- assume that commission is NULL EXEC SQL SELECT ENAME, SAL, COMM INTO :emp_name, :salary, :commission FROM EMP WHERE EMPNO = :emp_number;
SQLCAのSQLCODEはゼロに設定され、Oracleで文がエラーや例外を検出することなく実行されたことを示しています。
ただし、DBMS=V7(デフォルト)の場合、インジケータ変数のないホスト変数にNULLを選択またはフェッチにより入れると、次のエラー・メッセージが表示されます。
ORA-01405: fetched column value is 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));
実行可能な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行のみを戻す問合せ
複数の行を戻す問合せ
複数の行を戻す問合せには、明示的に宣言されたカーソルまたはカーソル変数(または、第9章「ホスト配列の使用方法」で説明されているホスト配列の使用)が必要です。次の埋込みSQL文により、明示カーソルを定義し、制御できます。
DECLARE: カーソルを指定し、問合せと関連付けます。
OPEN: 問合せを実行し、アクティブ・セットを識別します。
FETCH: カーソルを進め、アクティブ・セット内の行を1行ずつ取得します。
CLOSE: カーソルを無効にします(アクティブ・セットは未定義になります)。
後続の項では、まずINSERT、UPDATE、DELETEと1行のSELECT文のコードの方法を学習します。その後、複数行のSELECT文に進みます。
データベースに対する問合せは、一般的な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行が戻され、エラーは発生しません。
SELECT文では、次の標準的なSQL句をすべて使用できます。INTO、FROM、WHERE、CONNECT BY、START WITH、GROUP BY、HAVING、ORDER BYおよびFOR UPDATE OFです。
表またはビューに行を追加するには、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で定義した表の各列の値が定義どおりの順序で含まれている場合は、列リストを省略してもかまいません。
副問合せは、ネストされたSELECT文です。副問合せを使用すれば、マルチパート検索を実行できます。これらは、次の目的で使用できます。
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文で副問合せがどのように使用されているかに注意してください。
表またはビューの指定した列の値を変更するには、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;
表またはビューから行を削除するには、DELETE文を使用します。次の例では、EMP表から指定した部門の全従業員を削除します。
EXEC SQL DELETE FROM EMP WHERE DEPTNO = :dept_number;
1つの問合せで複数の行が戻される場合、カーソルを明示的に定義すれば、次のことができます。
問合せにより戻された最初の行の後の処理
現在どの行が処理されているかの追跡
カーソルは、問合せによって戻された行の集合内で現在の行はどれかを示します。これにより、プログラムでは行を1行ずつ処理できます。次の文を使用して、カーソルの定義および操作を行います。
DECLARE
OPEN
FETCH
CLOSE
まず、DECLARE文を使用して、カーソルに名前を付け、問合せに関連付けます。
OPEN文により問合せが実行され、問合せの検索条件を満たす行がすべて識別されます。これらの行が、カーソルのアクティブ・セットと呼ばれる集合を形成します。このカーソルをオープンした後、これを使用して、関連付けられた問合せによって戻された行を取得できます。
アクティブ・セットの行は、1行ずつ取得されます(ホスト配列を使用していない場合)。アクティブ・セット内の現在の行を取得するには、FETCH文を使用します。FETCH文は、すべての行が取得されるまで繰り返し実行できます。
アクティブ・セットからの行の取得が完了したら、CLOSE文でこのカーソルを無効にします。アクティブ・セットは未定義になります。
次の例のように、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」を参照してください。
問合せを実行して、アクティブ・セットを識別するには、OPEN文を使用します。次の例では、emp_cursorという名前のカーソルをオープンします。
EXEC SQL OPEN emp_cursor;
OPENにより、カーソルはアクティブ・セットの最初の行の直前に位置付けられます。また、SQLCA内のSQLERRD(3)に保存されている処理済の行数もゼロに設定されます。ただし、この時点で実際に取得される行はありません。それはFETCH文によって行われます。
カーソルをオープンすると、問合せの入力ホスト変数は、カーソルを再オープンするまで再検査されません。したがって、アクティブ・セットは変わりません。アクティブ・セットを変更するには、カーソルを再オープンする必要があります。
一般に、カーソルはいったんクローズしてから再オープンします。ただし、MODE=ORACLE(デフォルト)を指定すると、再オープンの前にカーソルをクローズする必要はありません。こうすれば、パフォーマンスが向上します。詳細は、付録C「パフォーマンスのチューニング」を参照してください。
OPENによって行われる作業量は、HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSの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文でこのエラーが検出されます)。カーソルを再利用するには、再オープンする必要があります。
アクティブ・セットからの行のフェッチが終了したら、カーソルをクローズし、カーソルのオープンによって確保していたリソース(記憶域など)を解放します。カーソルがクローズされると、解析ロックが解除されます。どのリソースが解放されるかは、オプションHOLD_CURSORおよびRELEASE_CURSORの指定によって異なります。次の例では、emp_cursorという名前のカーソルをクローズします。
EXEC SQL CLOSE emp_cursor;
クローズしたカーソルのアクティブ・セットは未定義になるため、クローズしたカーソルからはフェッチできません。必要であれば、(たとえば、入力ホスト変数に新しい値を指定して)カーソルを再オープンできます。
MODE={ANSI13|ORACLE}の場合、コミットまたはロールバックを発行すると、CURRENT OF句で参照されるカーソルがクローズされます。他のカーソルには、コミットまたはロールバックによる影響はなく、オープンの場合は、オープンのままです。ただし、MODE={ANSI|ANSI14}の場合は、コミットまたはロールバックを発行すると、すべての明示カーソルがクローズされます。
指定したカーソルからフェッチした最後の行を参照するには、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;
明示的な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句を使用できません。
次の例は、アプリケーション・プログラムでのカーソル制御文の一般的な順序を示しています。
-- 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;
次のプログラムは、カーソルと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;
この項では、カーソル変数の概要を簡単に説明します。詳細は、使用ホスト言語の補足資料および『Oracle Database PL/SQL言語リファレンス』を参照してください。
Pro*COBOLおよびPro*FORTRANプリコンパイラで静的埋込みSQLを使用すると、カーソル変数を宣言できます。カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セット内の現在の行を指します。カーソルとカーソル変数の違いは、定数と変数の違いと同じです。カーソルは静的ですが、カーソル変数は、特定の問合せに関連付けられないので動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。
また、カーソル変数に新しい値を割り当て、サブプログラム(Oracleデータベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中的に行うことができます。
まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の4つの文を使用してカーソル変数を制御します。
ALLOCATE
OPEN ... FOR
FETCH
CLOSE
カーソル変数を宣言して、メモリーを割り当てたら、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。サーバー側でOPEN、FORを使用して複数行の問合せのためにカーソル変数をオープンし、クライアント側ではその変数からFETCHを行い、サーバー側かクライアント側のいずれかでCLOSEします。
カーソル変数を複数行の問合せに関連付け、問合せを実行して、アクティブ・セットを識別するには、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;
複数行問合せのためにカーソル変数をオープンした後、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;
