Oracle PL/SQL カーソル

カーソルとは、Oracle PL/SQLにおいてSQLのDML文(SELECT、INSERT、UPDATE、DELETE及びMERGE)の処理に関する情報を保管した領域である。カーソルには「暗黙カーソル」と「明示カーソル」の2種類がある。

Table of Contents

  1. 1 暗黙カーソル
    1. 1.1 暗黙カーソルの属性
      1. 1.1.1 SQL%FOUND
      2. 1.1.2 SQL%ISOPEN
      3. 1.1.3 SQL%NOTFOUND
      4. 1.1.4 SQL%ROWCOUNT
  3. 2 明示カーソル
    1. 2.1 カーソルの宣言
    2. 2.2 OPEN
    3. 2.3 CLOSE
    4. 2.4 FETCH
    5. 2.5 FOR
    6. 2.6 明示カーソルの属性
      1. 2.6.1 cursor%ISOPEN
      2. 2.6.2 cursor%FOUND
      3. 2.6.3 cursor%NOTFOUND
      4. 2.6.4 cursor%ROWCOUNT

1 暗黙カーソル

暗黙カーソルとは、明示的にカーソルの宣言、オープン及びクローズをすることなく使えるカーソルである。暗黙カーソルは最後に実行したDML文の状態を保持する。

1.1 属性

カーソルには属性があり、DML文の実行に関する情報を保持している。

1.1.1 SQL%FOUND

SQL 文の INSERT、UPDATE 又は DELETE でひとつ以上の行に影響を与えた場合又はSELECT INTO文がひとつ以上の行を戻した場合、SQL%FOUND 属性が TRUE になる。それ以外の場合は SQL%FOUND 属性が FALSE になる。

CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
  DELETE FROM dept_temp WHERE department_id = dept_no;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
  END IF;
END;
/

1.1.2 SQL%ISOPEN

本来はカーソルがオープンされているかどうかを表す属性であるが、暗黙カーソルは DML 文を実行する際に自動的にオープンされ、DML 文の実行が終了すると自動的にクローズされるため、SQL%ISOPEN 属性は常に FALSE である。

1.1.3 SQL%NOTFOUND

SQL 文の INSERT、UPDATE 又は DELETE がどの行にも影響を与えなかった場合、又は SELECT INTO 文がどの行戻さなかった場合に SQL%NOTFOUND 属性が TRUE になる。それ以外の場合は SQL%NOTFOUND 属性が FALSE になる。

1.1.4 SQL%ROWCOUNT

SQL 文の INSERT、UPDATE 又は DELETE 文が影響を与えた行、又は SELECT INTO 文に戻された行の数を表す。

2 明示カーソル

明示カーソルとは、明示的にカーソルを宣言してから使用するカーソルである。暗黙カーソルは最後に実行したDML文の状態を保持するのに対して、明示カーソルはそれぞれのDML文ごとに異なるカーソルを作成できる。

2.1 カーソルの宣言

明示カーソルを使用するには、まずカーソルを宣言する必要がある。カーソルを宣言する構文を次に示す。

CURSOR cursor[(paramlist)] [RETURN return_type] IS select_query
cursor
カーソルの名前
paramlist
カーソルのパラメータを次の構文で指定する。 
parameter_name [IN] datatype [{ := | DEFAULT } expr]
parameter_name
パラメータの名前
datatype
パラメータのデータ型
expr
パラメータの初期値
return_type
戻り値のデータ型
select_query
SELECT文

カーソルを宣言する例を示す。

CURSOR csr1 IS SELECT empno, ename FROM employee WHERE salary > 3000;
CURSOR csr2 RETURN employee%ROWTYPE IS SELECT * FROM employee WHERE deptno = 4;
CURSOR csr3 (p_sal NUMBER DEFAULT 0) IS SELECT * FROM employee WHERE salary > p_sal;

2.2 OPEN

OPEN cursor[(paramlist)]
cursor

オープンするカーソルの名前を指定する。

paramlist

カーソルの引数をカンマで区切ったリストを指定する。

2.3 CLOSE

CLOSE cursor
cursor

クローズするカーソルの名前を指定する。

2.4 FETCH

カーソルを利用したフェッチを行なうには FETCH 文を使用する。

FETCH カーソル名 INTO 変数名 ; 
LOOP
FETCH csr1 INTO my_record;
EXIT WHEN csr1%NOTFOUND;
-- データレコード処理
END LOOP;

結果セットや問い合わせの中の変数の値を変更する場合は、カーソルをクローズし、入力変数を新しい値にして、もう一度オープンする必要がある。

2.5 FOR

カーソルを使った繰り返し処理としてカーソルFORループがある。カーソルFORループを使えば、カーソルのオープン、カーソルのクローズ、繰り返し処理の終了条件は省略することができる。

FOR record IN cursor LOOP
  -- 繰り返し処理
END LOOP;
FOR record IN cursor(param [,param...]) LOOP
  -- 繰り返し処理
END LOOP;
record
レコード名。暗黙的に宣言されているので、あらかじめ宣言部で明示的に宣言する必要はない。
cursor
カーソル名
param

カーソルに渡す引数

カーソル FOR ループ内で record_name . column_name で列の値を参照することができる。

2.6 属性

以下に明示カーソルの属性を示す。cursorはカーソル名を表している。

2.6.1 cursor%ISOPEN

カーソルがオープンされている(TRUE)か、カーソルがオープンされていない(FALSE)か。

2.6.2 cursor%FOUND

直前のフェッチが行を戻した(TRUE)か、直前のフェッチが行を戻さなかった(FALSE)か、カーソルがオープンされてからまだフェッチされていない(NULL)か。

2.6.3 cursor%NOTFOUND

直前のフェッチが行を戻さなかった(TRUE)か、直前のフェッチが行を戻した(FALSE)か、カーソルがオープンされてからまだフェッチされていない(NULL)か。

2.6.4 cursor%ROWCOUNT

これまでにフェッチした行数。カーソルをオープンした状態では0であり、フェッチで行が戻されるたびに数値が増加する。