Hitachi

ノンストップデータベース HiRDB Version 10 SQLリファレンス


4.9.1 DECLARE CURSOR 形式1の形式と規則

〈この項の構成〉

(1) 機能

問合せ指定の検索結果をFETCH文で1行ずつ取り出すために,カーソルを宣言します。

形式1では,直接指定したカーソル指定に対するカーソルの宣言をします。

(2) 使用権限

なし。

(3) 形式1<直接指定したカーソル指定に対するカーソルの宣言>

 DECLARE カーソル名 CURSOR
〔WITH HOLD〕〔{WITH RETURN | WITHOUT RETURN}〕 FOR
 〈カーソル指定 形式1〉
  〈問合せ式〉
   〈問合せ指定〉
   {SELECT 〔{ALL|DISTINCT}〕{選択式〔,選択式〕…|*}
    〈表式〉
    FROM 表参照〔,表参照〕…
       〔WHERE  探索条件〕
       〔GROUP  BY  値式〔,値式〕…〕
       〔HAVING  探索条件〕
    | 問合せ式 }
    〔ORDER BY {列指定|ソート項目指定番号}〔{ASC|DESC}〕
       〔,{列指定|ソート項目指定番号}〔{ASC|DESC}〕〕… 〕
    〔LIMIT {〔オフセット行数,〕{リミット行数|ALL}
        | {リミット行数|ALL} 〔OFFSET オフセット行数〕}〕
 〈排他オプション〉
  〔〔{WITH {SHARE|EXCLUSIVE}LOCK
    |WITHOUT LOCK〔{WAIT|NOWAIT}〕}〕
  〔{WITH ROLLBACK|NO WAIT}〕〕
    〔FOR{UPDATE 〔OF列名〔,列名〕…〕〔NOWAIT〕|READ ONLY}〕
    〔UNTIL DISCONNECT〕

(4) オペランド

(a) カーソル名

カーソルの名称を指定します。

UAP中に指定する場合は,SQLの予約語と同じ名称でも,引用符(”)で囲まないでください。ただし,SQLの予約語と同じ名称を手続き中に指定する場合は,引用符(”)で囲んでください。

カーソル名については,「名前の指定」を参照してください。

(b) 〔WITH HOLD〕

ホールダブルカーソルを使用する場合に指定します。

WITH HOLDは,UNTIL DISCONNECTを指定した場合の機能と同じなので,説明はUNTIL DISCONNECTを参照してください。また,この指定がUNTIL DISCONNECTと重複してもしなくても,機能差はありません。

(c) 〔{WITH RETURN | WITHOUT RETURN}〕

SQL手続き中のカーソル宣言で,そのカーソルの結果集合の返却可能性を指定します。

WITH RETURNを指定して宣言したカーソルを結果集合カーソルといいます。

SQL手続き中で宣言された結果集合カーソルを,開いた状態のまま手続きを終了すると,カーソルの結果集合を手続きの呼出し元に返却できます。

結果集合カーソルを宣言するSQL手続き定義のDYNAMIC RESULT SETS句には1以上の値を指定してください。

返却した結果集合の使用方法については,「結果集合返却機能」を参照してください。

(d) カーソル指定 形式1

問合せの内容を表すカーソルを指定します。

カーソル指定については「カーソル指定 形式1」を,問合せ式については「問合せ式」を,問合せ指定については「問合せ指定」を,表式については「表式」を,探索条件については「探索条件」を参照してください。

(e) 排他オプション

問合せをする場合の排他制御モードと,ほかのユーザが資源を占有していた場合の処置を指定します。

排他オプションについては,「排他オプション」を参照してください。

(f) 〔FOR{UPDATE 〔OF列名〔,列名〕…〕〔NOWAIT〕|READ ONLY}〕

FOR UPDATE 〔OF列名〔,列名〕…〕をFOR UPDATE句といいます。

FOR UPDATE

カーソルを使用して検索中の表に対して,そのカーソルを使用した行の更新,又は削除をして,更にカーソルを使用しない行の更新,削除,又は追加をする場合に指定します。

指定したカーソルを使用して行を更新するUPDATE文,又は削除するDELETE文がモジュール中に含まれている場合に,FOR UPDATE OFを省略するとFOR UPDATEが仮定され,すべての列を更新,追加,又は削除できます。

カーソルを使用して検索中の表に対して,そのカーソル,及びほかのカーソルを使用した行の更新,削除がなく,カーソルを使用しない行の更新,削除,及び追加もしない場合は,オペランドを省略してください。SQL中の排他オプションを省略した場合,排他オプションはpd_isolation_levelの指定値,PDISLLVLの指定値,又はSQLコンパイルオプションで指定するデータ保証レベルの指定値によって決まりますが,PDFORUPDATEEXLOCKにYES,又はSQLコンパイルオプションのデータ保証レベルの後にFOR UPDATE EXCLUSIVEを指定することで,この指定のあるカーソルに対する排他オプションはWITH EXCLUSIVE LOCKに仮定されます。詳細については,マニュアル「HiRDB UAP開発ガイド」を参照してください。

OF列名〔,列名〕…

カーソルを使用して検索中の表に対して,そのカーソルを使用した検索行の更新だけをする場合に,更新する列を指定します。

SELECT文の選択式で指定していない列でも指定できます。ただし,同じ列を2回以上指定できません。また,予備列は指定できません。

カーソルを使用して検索中の表に対して,そのカーソル及びほかのカーソルを使用した行の更新,削除がなく,カーソルを使用しない行の更新,削除,及び追加もしない場合には,オペランドを省略してください。

指定する列名は,AS列名に指定した列名ではなく,最も外側の問合せ指定のFROM句に指定した表の列を指定します。

〔NOWAIT〕

FOR UPDATE句を指定し,かつ排他オプションにWITH EXCLUSIVE LOCK NO WAITを指定した場合と同じ動作をします。ただし,排他オプションを指定した場合,NOWAITは指定できません。

排他オプションにWITH EXCLUSIVE LOCK NO WAITを指定した場合の動作については,「排他オプション」を参照してください。

FOR READ ONLY

カーソルを使用して検索中にほかのカーソル,又は直接探索条件を指定して更新する場合に指定します。この指定は,検索中の更新が検索結果に影響を与えないようにするためです。

(g) 〔UNTIL DISCONNECT〕

ホールダブルカーソルを使用する場合に指定します。

この指定をしたときの機能は,WITH HOLDを指定した場合と全く同じです。また,この指定がWITH HOLDと重複してもしなくても,機能差は生じません。

なお,ホールダブルカーソルについては,マニュアル「HiRDB UAP開発ガイド」を参照してください。

ホールダブルカーソルについての規則を次に示します。

  1. 次の場合,ホールダブルカーソルは使用できません。

    • ホールダブルカーソルに未対応のプラグインを使用した抽象データ型の列を指定した場合

    • ホールダブルカーソルに未対応のプラグインを使用した関数呼出しを指定した場合

    • ホールダブルカーソルに未対応のプラグインを使用した関数呼出しを指定して導出した,名前付きの導出表に対する問合せを指定した場合

    • ON COMMIT DELETE ROWSを指定して定義した一時表に対する問合せ

  2. ホールダブルカーソルが開いている場合,定義系SQLは実行できません。

  3. ホールダブルカーソルを使用したSELECT文に対してOPEN文を実行後,そのSELECT文中で使用している表に対してPURGE TABLE文を実行すると,カーソルは閉じた状態になります。

  4. ホールダブルカーソルを使用したSELECT文に対してOPEN文を実行してからDISCONNECTするまでの間に,そのSELECT文中で使用している表に対して,ほかのユーザが定義系SQL文を発行すると,定義系SQLは排他待ちの状態になります。また,ホールダブルカーソルを使用したSELECT文に対する前処理が有効な間に,そのSELECT文中で使用している表に対して,ほかのユーザが定義系SQL文を発行すると,定義系SQLは排他待ちの状態になります。

注※

プラグインのホールダブルカーソルへの対応状況については,ディクショナリ表SQL_PLUGINSのPLUGIN_HOLDABLE列で確認できます。ディクショナリ表SQL_PLUGINSについては,マニュアル「HiRDB UAP開発ガイド」を参照してください。

(5) 共通規則

  1. 宣言したカーソルは,閉じた状態になります。

  2. DECLARE CURSOR中のSELECT文で指定した埋込み変数,SQL変数,又はSQLパラメタの値は,カーソルを開いてからカーソルを閉じるまでこのカーソルのOPEN文実行時の値が有効です。値を変更したい場合は,いったんカーソルを閉じてから,再度開いてください。

  3. 1個のUAPでは,最大1,023個のカーソルを宣言できます。

  4. カーソル指定又は排他オプション中に次のどれかの指定を含む場合は,そのカーソルを使用した更新及び削除はできません。また,FOR UPDATE句の指定もできません。

    (5-a) UNION 〔ALL〕,又はEXCEPT〔ALL〕

    (5-b) 最も外側の問合せ指定のFROM句に指定した表を,副問合せのFROM句に指定

    (5-c) 最も外側の問合せ指定での表の結合

    (5-d) 最も外側の問合せ指定でのFROM句の導出表

    (5-e) 最も外側の問合せ指定でのSELECT DISTINCT

    (5-f) 最も外側の問合せ指定でのGROUP BY句

    (5-g) 最も外側の問合せ指定でのHAVING句

    (5-h) 最も外側の問合せ指定に対する集合関数

    (5-i) 最も外側の問合せ指定に対するウィンドウ関数

    (5-j) 最も外側の問合せ指定のFROM句中で,次に示すどれかのビュー表を指定

    • ビュー定義文で上記(5-a)〜(5-i)を指定して定義したビュー表

    • ビュー定義文で最も外側の問合せ指定のSELECT句に,列指定以外の値式を指定して定義したビュー表

    • ビュー定義文でREAD ONLYを指定したビュー表

    (5-k) WITHOUT LOCK NOWAIT

    (5-l) WITH句を指定した問合せ式本体の最も外側の問合せ指定のFROM句に問合せ名を指定

  5. そのカーソルを指定した行の更新,又は削除がある場合FOR READ ONLY句は指定できません。

  6. FOR READ ONLY句を指定した場合,次に示す制限があります。

    (5-a) 選択式中に,結果が次のデータ型になるスカラ演算,関数呼出し,及びコンポネント指定は指定できません。

    • BLOB

    • 最大長が32,001バイト以上のBINARY

    • BOOLEAN

    • 抽象データ型

    (5-b) 選択式中のWRITE指定の出力BLOB値には,列指定だけ指定できます。

    (5-c) GET_JAVA_STORED_ROUTINE_SOURCE指定は指定できません。

(6) 参照制約に関する規則

  1. ホールダブルカーソルを使用して,外部キーの定義されている表を検索している場合,検索中の表が検査保留状態となったときは,カーソルは閉じた状態になります。

(7) 留意事項

  1. カーソル名は,埋込み変数名と同様に,コンパイル単位のモジュール内で有効な名前であり,同じカーソルに対する複数のSQLを,複数のモジュールにわたって使用できません。

  2. カーソル宣言は,この宣言で使用したカーソル名を参照するどのSQL文よりも先行して記述する必要があります。

  3. DECLARE CURSORは実行文ではないため,SQLCODEにリターンコードは返されません。したがって,リターンコードの判定はしないでください。

  4. SQL最適化オプションの更新SQLの作業表作成抑止を適用して,更にインデクスキー値無排他機能を使用すると,FOR UPDATE又はFOR UPDATE OFを指定しないカーソルの使用中に,行の更新,追加,又は削除ができます。

  5. FOR READ ONLYを指定している場合,HiRDBが作業表を作成することがあります。このとき,作業表の行長によっては,FOR READ ONLYの処理が制限されることがあります。作業表の行長については,マニュアル「HiRDB システム導入・設計ガイド」を参照してください。

(8) 使用例

  1. 在庫表(ZAIKO)から,行を1行ずつ取り出すためにカーソル(CR1)を宣言します。

    DECLARE CR1 CURSOR FOR
        SELECT SCODE,SNAME,COL,TANKA,ZSURYO
            FROM ZAIKO
  2. 在庫表(ZAIKO)から,単価(TANKA)が5,000円以上の行を1行ずつ取り出すためにカーソル(CR1)を宣言します。

    DECLARE CR1 CURSOR FOR
        SELECT * FROM ZAIKO
            WHERE TANKA >= 5000
  3. 在庫表(ZAIKO)から,カーソル(CR1)を使用してすべての行を検索しながら単価(TANKA)を1割引にし,更に行を挿入します。

    DECLARE CR1 CURSOR FOR
        SELECT * FROM ZAIKO
            FOR UPDATE
    OPEN CR1
      FETCH CR1 INTO <各列を取り出す変数名>
    UPDATE ZAIKO
        SET TANKA = <単価の1割引の値>
            WHERE CURRENT OF CR1
    INSERT INTO ZAIKO VALUES(<各列の挿入値>)
    CLOSE CR1
  4. 在庫表(ZAIKO)から,カーソル(CR1)を使用してすべての行を検索しながら単価(TANKA)を1割引にします。

    DECLARE CR1 CURSOR FOR
        SELECT * FROM ZAIKO
            FOR UPDATE OF TANKA
    OPEN CR1
    FETCH CR1 INTO <各列を取り出す変数名>
    UPDATE ZAIKO
        SET TANKA = <単価の1割引の値>
            WHERE CURRENT OF CR1
    CLOSE CR1
  5. 在庫表(ZAIKO)からカーソル(CR1)を使用してすべての行を検索中に,カーソルを使用しないで,直接商品名(SNAME)がセーターの行を削除します。

    DECLARE CR1 CURSOR FOR
        SELECT * FROM ZAIKO
            FOR READ ONLY
    OPEN CR1
    FETCH CR1 INTO <各列を取り出す変数名>
    DELETE FROM ZAIKO
        WHERE SNAME=N’セーター’
    CLOSE CR1