DB/Oracle/PLSQL/カーソル

Top/DB/Oracle/PLSQL/カーソル

目次†

カーソル！

↑

カーソルの種類†

カーソルっていうのは、メモリ領域に対するポインタ。より具体的には、SQL 文を実行したときの結果セットに対する、現在の処理対象である行へのポインタのこと。明示カーソルと暗黙カーソルがある。表をごにょごにょするときに、今どの行を見ているかとか、そういう情報を内部的に持っているような感じ。

暗黙カーソルと明示カーソルがある。

  • カーソルの種類
    種類説明
    暗黙カーソルDML や SELECT 文の実行時に、PL/SQL さんが勝手に作るカーソル
    明示カーソルプログラムを書くときときにきちんと宣言してきちんと使うためのカーソル。主に SELECT 文の結果が複数行になるときに使う
↑

暗黙カーソル†

DML や SELECT 文の発行時、PL/SQL によって勝手に作られて使われて破棄されるカーソル。必要に応じて勝手に生成、操作されるので、動きは意識する必要はない。

ただ、直前の SQL 文の実行結果に関する情報を SQL カーソル属性としてもつので、これは活用できる。これを参照することで、直前の SQL 文がどのように処理されたかがわかる。

↑

カーソルの属性†

  • SQL カーソル属性の種類
    属性説明
    SQL%FOUNDブール型。直前の SQL 文で 1 行以上返ってきた場合に TRUE
    SQL%NOTFOUNDブール型。直前の SQL 文で 1 行も返ってこなかった場合に TRUE
    SQL%ROWCOUNT整数型。直前の SQL 文で影響を受けた行数
  • SQL カーソル属性 
    begin
    update set ...

    -- 直前の SQL 文（この場合 UPDATE）で何行更新されたか表示
    dbms_output.put_line(sql%rowcount);
...
↑

明示カーソル†

プログラマが明示的に生成して操作する、結果セットに対するポインタ。

通常、SQL では行単位でしか操作できないので、複数行を戻す問い合わせを処理する場合などには明示的に処理対象とする行を指定しないといけない。

ざっくりとした使い方は以下。

  1. カーソルの宣言
    • CORSOR hoge IS SELECT ...;
  2. カーソルのオープン
    • OPEN hoge;
      • カーソルに関連付けられた SQL 文を実行
      • カーソルを結果セットの一行目へセット
  3. フェッチ
    • FETCH hoge INTO fuga;
      • カーソルのある行のデータを変数へ格納
      • カーソルを次の行へ移動
  4. カーソルのクローズ
    • CLOSE hoge;
      • カーソルと結果セットの解放
↑

基本的な書き方†

カーソルの宣言で、任意の SELECT 文をカーソルと関連付け指定できる。ただし変数格納用の INTO は含めない。必要に応じて ORDER BY 句でソートする（結果セットのソートはできないうえ、カーソルは逐次移動しかできないため）。

  • 明示カーソル 
    declare
    -- カーソルの宣言に実行したい SQL 文を含める
    cursor c_hoge is
      select foo, bar, baz from fuga;

    -- 格納する用の変数を用意しておく
    v_foo fuga.foo%type;
    v_bar fuga.bar%type;
    v_baz fuga.baz%type;

    -- カーソルの結果に合わせたレコード変数の宣言も可能
    v_record c_hoge%rowtype;
begin
    -- カーソルのオープン
    open c_hoge;

    -- 検索結果すべてに対してのループ処理
    loop
        -- カーソルのある行のデータを変数に格納する
        fetch c_hoge into v_foo, v_bar, v_baz;

        -- 行がなければループを終了する
        exit when c_hoge%notfound;
        
        -- 行があった場合の処理を書く
        ...
    end loop;
end;
/
↑

パラメータの付加†

カーソルにパラメータを付加して、オープンするときに値を渡せる。オープン時に渡すパラメータを変えることで、実行する SQL 文中の値を動的に変更できる。

WHERE 句に与える値などをオープンするたびに動的に変更できるので、同じカーソルを再利用できて効率も良い。

  • パラメータの付加 
    declare
    -- カーソル宣言時にパラメータも記述する
    cursor c_hoge (p_hoge number, p_fuga varchar2) is
      select *
        from foo
        -- SQL 文中にパラメータを埋め込んでおく
        where bar = p_hoge
        and baz = p_fuga;
begin
    -- オープン時に引数っぽく値を渡す
    -- この例でいえば "where bar = 100 and baz = 'Hoge'" として実行される
    open c_hoge (100, 'Hoge');

    -- クローズして再オープンすればまた違う値を渡せる
    -- 今度は "where bar = 101 and baz = 'Fuga'" として実行される
    close c_hoge;
    open c_hoge (101, 'Fuga');
end;
/
↑

カーソルの属性†

暗黙カーソルにもあった属性に、ISOPEN が追加されている。

%NOTFOUND と %ROWCOUNT をよく使う。%ISOPEN はカーソルの再オープン前の状態チェックなどに使用する。こともあるかもしれない。

  • 明示カーソル属性の種類
    属性説明
    %ISOPENブール型。カーソルがオープンしていれば TRUE
    %FOUNDブール型。最新のフェッチで 1 行以上返ってきた場合に TRUE
    %NOTFOUNDブール型。最新のフェッチで 1 行も返ってこなかった場合に TRUE
    %ROWCOUNT整数型。これまでにフェッチで戻ってきた行の合計数
  • 明示カーソル属性 
    begin
    ...
    loop
        ...
        -- 上から 10 件だけ処理したいときの例
        -- OR 条件は結果が 10 件未満だったときのためのもの 
        exit c_hoge%rowcount > 10 or c_hoge%notfound;
        ...
    end loop;
    ...
end;
↑

カーソル FOR ループ†

検索結果に対する FOR EACH みたいなもの。便利。

オープン、フェッチ、終了、クローズは暗黙的に処理されるので記述不要。

  • カーソル FOR ループ 
    declare
    -- カーソルの宣言は必要
    cursor c_hoge is
      select ...;
begin
    -- レコード変数名を直接指定。宣言不要
    for r_hoge in c_hoge loop
        ...
    end loop;
end;
/

副問い合わせを利用すれば、カーソル宣言すら省略できる。コードの可読性が落ちるのと、カーソル属性が使えなくなる点は注意。

  • 副問い合わせを利用したカーソル FOR ループ 
    declare
    -- カーソルの宣言は不要
begin
    -- レコード変数名を直接指定。宣言不要
    for r_hoge in (select ...) loop
        ...
    end loop;
end;
/
Last-modified: 2011-11-08 (火) 16:59:58