PL/SQLでSQLの処理件数(ROWCOUNT)を取得する方法。カーソルの件数取得方法も解説

PL/SQLとは

PL/SQLとは、SQLを拡張したプログラム言語で、Oracle社が提供するOracleデータベース用に開発されました。

SQLは、テーブルなどのデータ集合からレコードを取得したり更新するための言語ですが、PL/SQLでは、SQLで出来ることに加えて、条件分岐（IF）、繰り返し（FOR/WHILE）などの構文を使用して、業務ロジック（手続き）を実行できます。

PL/SQLを使えば、例えば、SELECT文などで取得した結果をもとに、条件分岐、繰り返し処理、さらにはCSVファイルなどのファイル出力などの処理も実装できます。

この記事では、PL/SQLで直前に実行したSQLの処理件数を取得する方法と、カーソル使い方などについて解説します。

直前に実行したSQLの処理件数を取得する

PL/SQLで直前に実行した、SQLの処理件数を取得する時は、SQL%ROWCOUNTを使用します。SQL%ROWCOUNTは、データ更新件数の判定や、デバッグ時の処理件数の確認などで重宝します。

では、SQL%ROWCOUNTを使ったサンプルコードを見てみましょう。

BEGIN
  UPDATE EMP SET SAL = 1000 WHERE DEPTNO = 10;  --データを3件更新
  DBMS_OUTPUT.PUT_LINE('更新件数=' || SQL%ROWCOUNT);
END;

実行結果

更新件数=3

INSERT文、DELETE文でもSQL%ROWCOUNTで処理件数を取得できます。

-- INSERT
BEGIN
  INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES(7369, 'SMITH CLERK', 7902, 800, 10);
  DBMS_OUTPUT.PUT_LINE('登録件数=' || SQL%ROWCOUNT);
END;

-- DELETE
BEGIN
  DELETE FROM EMP WHERE DEPTNO = 10;
  DBMS_OUTPUT.PUT_LINE('削除件数=' || SQL%ROWCOUNT);
END;

SELECT文ではSQL%ROWCOUNTは使えない

SQL%ROWCOUNTは、直前のSQLで影響を受けた行数を返す構文のため、データ更新が発生しないSELECT文では使用できません。SELECT文で件数を知りたい場合は、通常のCOUNT文を使用しましょう。

DECLARE
  v_cnt   NUMBER;
BEGIN                     
  SELECT COUNT(1)
  INTO v_cnt
  FROM EMP
  WHERE DEPTNO = 10;

  DBMS_OUTPUT.PUT_LINE('件数=' || v_cnt);
END;

カーソルの使い方とROWCOUNTを取得する方法

カーソル(CURSOR) とは、主にSELECT文などの問合せ結果を管理するための領域で、PL/SQLで複数行のデータを検索して処理する時に使用します。

ここからは、PL/SQLのカーソルの基本的な使い方や、カーソルの行数を取得する方法を解説していきます。

カーソルの宣言

カーソルはCURSOR <カーソル名>[(パラメータ,…)] IS <SELECT文>の構文で宣言します。

--パラメータなしのカーソル
CURSOR MY_CURSOR IS SELECT * FROM EMP WHERE DEPTNO = 10;
--パラメータが１つあるカーソル
CURSOR MY_CURSOR (DEPTNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO = DEPTNO;
--パラメータにデフォルト値を設定したカーソル
CURSOR MY_CURSOR (DEPTNO NUMBER DEFAULT 20) IS SELECT * FROM EMP WHERE DEPTNO = DEPTNO;

カーソル変数の定義

カーソルを使用する時は、<カーソル名>%ROWTYPEの型で、検索結果を１件ずつ格納する変数が必要になります。
以下は、MY_CURSORのカーソル変数を宣言する例です。

my_cursor_rec MY_CURSOR%ROWTYPE;

カーソルのオープン

カーソルを使用するには、まずカーソルをオープンして、カーソルに設定された問い合わせ文（SELECT）を実行します。カーソルのオープンはopen <カーソル名>の形式で記述します。

OPEN MY_CURSOR;

パラメータがあるカーソルの場合は、次のように記述します。

OPNE MY_CURSOR(20);

ポテパンダの一言メモ

open時に実行されたSELECT文の結果は、その時点のデータでカーソルの作業領域に保持されます。たとえ、オープン後に対象となるテーブルの値が書き換わっても、カーソルの検索結果は変わりません。

フェッチ（fetch）

カーソルをオープンしたら、検索結果を取り出します。LOOPなどの繰り返しを使って、カーソルから１件ずつレコードを取り出します。

LOOP
  FETCH MY_CURSOR INTO my_cursor_rec;  --カーソルから１件データを取り出す（フェッチ）
  EXIT WHEN MY_CURSOR%NOTFOUND;        --終了判定

  DBMS_OUTPUT.PUT_LINE('ENAME=' || my_cursor_rec.ENAME);
END LOOP;

別の方法で、カーソルFORループを使用して、レコードを１件ずつ取り出す方法もあります。カーソルFORループでは、レコードを取り出すFETCHや、終了判定のEXIT WHENが省略でき、シンプルに処理を記述できます。

FOR my_cursor_rec IN MY_CURSOR LOOP
  DBMS_OUTPUT.PUT_LINE('ENAME=' || my_cursor_rec.ENAME);
END LOOP;

カーソルのクローズ

使い終わったカーソルは、CLOSE <カーソル名>で閉じ、結果セットが格納されているメモリ領域を解放します。カーソルを閉じ忘れると、メモリを圧迫するばかりか、次に同じセッションでカーソルをオープンしようとした時に、２重オープンエラーになります。

CLOSE MY_CURSOR;

ポテパンダの一言メモ

カーソルFORループを使用した場合、カーソルのOPEN/CLOSEは自動的に行われるため、記述を省略できます。

カーソル属性でカーソルの状態を取得

カーソルには、次の属性を使って、取り出すレコードの有無を確認したり、現在のカーソルのオープン状態を確認したりできます。

%ROWCOUNTの使用には注意

カーソル属性の%ROWCOUNTの使用には注意が必要です。前述のように、%ROWCOUNTは「FETCHを使ってカーソルから取り出したレコード数」を取得するカーソル属性です。カーソルのレコード件数を取得する属性ではありません。

次のコードを見てください。繰り返しでレコードをFETCHする度に、%ROWCOUNTが変わっていっています。

OPNE MY_CURSOR;
LOOP
  FETCH MY_CURSOR INTO my_cursor_rec;
  EXIT WHEN MY_CURSOR%NOTFOUND;

  DBMS_OUTPUT.PUT_LINE('%ROWCOUNT=' || MY_CURSOR%ROWCOUNT);  --%ROWCOUNTを出力
END LOOP;

実行結果

%ROWCOUNT=1
%ROWCOUNT=2
%ROWCOUNT=3
%ROWCOUNT=4
%ROWCOUNT=5
・・・

ポテパンダの一言メモ

OracleのPL/SQLには、レコードのフェッチ前に、カーソルのレコード数を取得する方法はありません。レコード数を知りたい場合は、繰り替えし処理で、一旦最後のレコードまでFETCHしてから%ROWCOUNT属性を参照します。

さいごに

PL/SQLで、直前に実行したSQLの処理件数（SQL%ROWCOUNT）を取得する方法と、カーソルの使い方や、カーソルの行数を取得する方法を解説してきました。

処理件数に応じた処理の分岐は、よく使う実装パターンのため、是非覚えておきましょう。

【関連記事】
▶SQL Developerを使ってみよう！資格取得の学習にも使える便利なツール