PL/SQLでカーソルを使う(応用編):超入門「PL/SQL」(7)(2/3 ページ)
本連載は、「PL/SQL(Procedure Language/Structured Query Language)」を理解し、活用するための実践講座です。前回に引き続き、今回は「カーソル」の応用について解説します。
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;
それでは、先ほどのプログラムを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句を使用した更新や削除では、現在処理している行に対し、内部的にROWIDを使用したデータアクセスを行います。ROWIDとは各行に振られているアドレスのことで、他のデータアクセスと比べて最小限のデータアクセスで該当行にアクセスできることが特徴です。
このように、WHERE CURRENT OF句を使用するメリットは大きいと言えます。ただし、FOR UPDATE句を併用する必要があるため、処理終了後、適時トランザクションを終了して、ロックを解放する必要がある点に注意してください。
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
- DBエンジンを最大限に生かすバッチアプリの作り方
第1回では、バッチアプリケーションを設計するうえで考慮すべき4つのポイントについて解説しました。今回は、Oracleデータベースを最大限に利用したバッチアプリケーションの実装テクニックについて解説します。 - ODTを使ったVisual StudioからのPL/SQL開発
オラクル社が提供する3つの.NET環境用開発ツール「ODP.NET」「ODT」「ODE.NET」。Oracle 10gリリース2に対応した最新版を使って、Oracleデータベースを前提とした.NETアプリケーションの開発手法を解説する。(編集部) - 更新/挿入/削除のSQLを高速化する3つの技とは?
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。(編集局) - 【Oracle Database】忘れていませんか? 「アラートログ調査」に必要な、たった3つのキホン
データベース管理システムの運用でトラブルが発生したらどうするか。データベースサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は基本編として「アラートログの調査で押さえるべき3つのポイント」を解説します。【Oracle Database 12c対応版】