PL/SQLでカーソルを使う（応用編）：超入門「PL/SQL」（7）（2/3 ページ）

[小笠原宏幸，＠IT]

FOR UPDATE付きカーソル

　カーソルの問い合わせ文では、通常のSQLと同じようにFOR UPDATE句を指定できます。FOR UPDATE句とは、問い合わせ文を実行すると対象行を排他ロックする機能で、ロックをトランザクションが終了するまで保持します。これにより、カーソルオープン時に結果セットの行をロックでき、トランザクション終了まで他ユーザーによる結果セットの変更を防止できます。

カーソル処理でWHERE CURRENT OF句を使用した更新と削除

　続いて、パフォーマンスの向上が期待できる機能を解説します。

　結果セットから行データを取り出して変数に代入した後、その行データに対して更新や削除を行いたいケースがあります。例えば、次のサンプルプログラムのようなケースです。

SQL> DECLARE
  2    CURSOR emp_cur IS SELECT empno,sal FROM emp
  3                      WHERE deptno = 30;
  4  BEGIN
  5    FOR emp_rec IN emp_cur LOOP
  6      IF emp_rec.sal < 2000
  7        THEN UPDATE emp
  8             SET sal = sal + 100
  9             WHERE empno = emp_rec.empno;
 10      END IF;
 11    END LOOP;
 12  END;
 13  /

サンプルプログラム

　このサンプルプログラムでは、EMP表のDEPTNO列の値が30の行を結果セットとして識別します。1行ずつ変数に代入し、IF文でSALフィールドの値が2000よりも小さい場合（IF emp_rec.sal < 2000）、現在処理している行に対して（WHERE empno = emp_rec.empno）、sal列の値を100加算した値に更新します。

　このような処理の場合、カーソルの問い合わせ文にFOR UPDATE句を指定すると、カーソル処理で使用するUPDATE文、DELETE文のWHERE句の条件にCURRENT OF句を指定できます。まずは書式を確認しましょう。

DECLARE
  CURSOR …… SELECT …… FOR UPDATE ；
BEGIN
[ UPDATE | DELETE ] …… WHERE CURRENT OF <カーソル名> ;
END;

FOR UPDATE句、WHERE CURRENT OF句の書式

　それでは、先ほどのプログラムをWHERE CURRENT OF句に変更したものを見てみましょう。

SQL> DECLARE
  2    CURSOR emp_cur IS SELECT empno,sal FROM emp
  3                      WHERE deptno = 30
  4                      FOR UPDATE;
  5  BEGIN
  6    FOR emp_rec IN emp_cur LOOP
  7      IF emp_rec.sal < 2000
  8        THEN UPDATE emp
  9             SET sal = sal + 100
 10             WHERE CURRENT OF emp_cur;
 11      END IF;
 12    END LOOP;
 13  END;
 14  /

WHERE CURRENT OF句を使って書き換えたサンプルプログラム

　このようなWHERE CURRENT OF句を使用したカーソル処理では、パフォーマンスの向上が期待できます。

　WHERE CURRENT OF句を使用した更新や削除では、現在処理している行に対し、内部的にROWIDを使用したデータアクセスを行います。ROWIDとは各行に振られているアドレスのことで、他のデータアクセスと比べて最小限のデータアクセスで該当行にアクセスできることが特徴です。

　このように、WHERE CURRENT OF句を使用するメリットは大きいと言えます。ただし、FOR UPDATE句を併用する必要があるため、処理終了後、適時トランザクションを終了して、ロックを解放する必要がある点に注意してください。