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

» 2018年12月20日 05時00分 公開
[小笠原宏幸@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句を併用する必要があるため、処理終了後、適時トランザクションを終了して、ロックを解放する必要がある点に注意してください。

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。