PL/SQLでカーソルを使う（基本編）：超入門「PL/SQL」（6）

本連載は、「PL/SQL（Procedure Language/Structured Query Language）」を理解し、活用していくための実践講座です。今回から「カーソル」について2回に分けて解説します。

[小笠原宏幸，＠IT]

印刷

通知 連載「超入門「PL/SQL」」の新着をメールで通知

見るPost

Shareシェア

はてなブックマーク

SharePocket Button

目次

• カーソル処理の概要
• カーソル処理の記述方法
  • （1）カーソルの定義
  • （2）変数の定義
  • （3）カーソルのオープン
  • （4）結果セットから変数へ代入
  • （5）LOOP処理の終了
  • （6）カーソルのクローズ
  • サンプルプログラムでカーソルを使う
• カーソル属性

連載バックナンバー

　本連載は、Oracle Database向けにデータベース言語 SQL（Structured Query Language）を拡張したプログラミング言語である「PL/SQL（Procedure Language/Structured Query Language）」を理解し、活用するための実践講座です。SQLは知っているけれど、OracleでのPL/SQLは初めてという人向けに、機能の概要と具体的な書き方を解説します。

　PL/SQLはOracleが開発したプログラミング言語であるため、Oracle Databaseに格納されているデータを効率的に処理できるという利点があります。SELECT文によって取得した行データをPL/SQLの変数に代入して処理したい場合、「カーソル」機能を使用します。カーソルを使用するには一連の指定が必要になるため、基礎編と応用編の2回に分けて解説します。カーソルはPL/SQLの中でも非常に重要な機能です。しっかり押さえておきましょう。

カーソル処理の概要

　カーソルを使用すると、Oracle Databaseの表から取り出した複数行を処理できます。取り出す行数が1行、または0行の場合でも問題ありません。

　では初めに、カーソル処理の内部的な流れを以下の図で解説します。まず、任意のデータを取り出すSELECT文をカーソルに対応付けます（図1）。

図1　カーソルの定義

　カーソルに対応付けたSELECT文をオープン（実行）すると、検索結果がメモリ上に保持されます（図2）。これを「結果セット」と言います。

図2　カーソルのオープン

　結果セットの1行目を取り出し、用意しておいた変数に代入します（図3）。

図3　変数への代入

　LOOP文を用い、2行目から最終行まで図3と同様に取り出して変数に代入する処理を繰り返します（図4）。

図4　LOOP文と変数への代入

　このように、カーソルに対応付けたSELECT文を実行して結果セットを識別した後、結果セットから1行を取り出し変数に代入します。その後、LOOP文を併用して変数に代入する処理を繰り返し、最終的に結果セットの全ての行を変数に代入する、という流れとなります。

　では、このような処理を行うための記述方法を次に解説します。

カーソル処理の記述方法

　先ほどの説明からも分かる通り、カーソル処理の記述はこれまでの連載内容と比べると複雑です。まずは、図5でカーソルを使う際に必要な記述の全体像を確認してください。

図5　カーソル処理の記述概要

　それでは、図5をベースに各項目の記述方法を解説します。

（1）カーソルの定義

　実行したいSELECT文に対してカーソル名を定義します。カーソル名は変数と同様の「命名規則」に従います。このとき「SQL」という名前は指定できません。

CURSOR <カーソル名> IS <問い合わせ文> ;

カーソルを定義する書式

（2）変数の定義

　実行部でカーソルの結果セットを1行ずつ変数に代入します。変数はカーソルの結果セットの列ごとに定義するのではなく、通常は「<カーソル名>%ROWTYPE」と定義します。こうすることで、結果セットと同じ行構造の変数を用意できます。

<変数名> <カーソル名>%ROWTYPE ;

変数を定義する書式

（3）カーソルのオープン

　カーソルに対応付けられたSELECT文を実行し、結果セットを識別します。結果セットはオープンした時点のデータを保持します。

OPEN <カーソル名> ;

カーソルをオープンする書式

（4）結果セットから変数へ代入

　カーソルの結果セットから1行を取り出し、（2）で定義した変数に代入します。

FETCH <カーソル名> INTO <変数名> ;

1行を取り出し、変数に代入する書式

　FETCH INTO文を使用する際は、次に挙げる挙動に注意してください。

• FETCH INTO文は1行のみを取り出すため、LOOP文を併用して複数行を取り出します。
• 結果セットは、オープン直後は1行目、次は2行目……というように上から順番に取り出されます。

（5）LOOP処理の終了

　LOOP処理によって結果セットから複数行を取り出した後、このLOOP処理を終了する条件が必要です。終了条件の指定方法は幾つかありますが、今回はEXIT WHEN文を使用した方法を解説します。

EXIT WHEN <終了条件> ;

LOOP処理を終了する書式

　終了条件にはWHERE句と同様の条件式を指定できる他、後ほど説明する「カーソル属性」を指定できます。カーソル属性を使用すると、例えば「結果セットの全行を取り出して、取り出す行がなくなったらLOOP処理を終了する」というような指定が可能です。

（6）カーソルのクローズ

　結果セットが確保していたメモリ領域を解放します。なお、クローズ処理の明示的な指定を省略できますが、その場合、PL/SQLブロックが終了するまでは結果セットのメモリ領域を確保し続けます。メモリを有効活用するためにも、一連の処理が終了したらクローズ処理を指定するようにしましょう。

CLOSE <カーソル名> ;

カーソルをクローズする書式

サンプルプログラムでカーソルを使う

　それでは、カーソルの記述方法をサンプルプログラムで確認しましょう。

SQL> DECLARE
 2    CURSOR dept_cur IS SELECT * FROM dept;
 3    dept_rec dept_cur%ROWTYPE;
 4  BEGIN
 5    OPEN dept_cur;
 6      LOOP
 7        FETCH dept_cur INTO dept_rec;
 8        EXIT WHEN dept_cur%NOTFOUND;
 9          DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||' : '||dept_rec.dname||' : '||dept_rec.loc);
10      END LOOP;
11    CLOSE dept_cur;
12  END;
13  /
10 : ACCOUNTING : NEW YORK
20 : RESEARCH : DALLAS
30 : SALES : CHICAGO
40 : OPERATIONS : BOSTON

カーソルを使うサンプルプログラム

　初めに宣言部で「SELECT * FROM dept;」というSELECT文に対して、DEPT_CURという名前でカーソル名を定義しています。併せて、カーソルの結果セットの行を代入するため、変数DEPT_RECを「dept_cur%ROWTYPE」で定義しています。

　次に実行部でOPEN文を使用し、カーソルDEPT_CURに対応付けたSELECT文を実行しています。これによって結果セットが識別されます。その後、FETCH INTO文を使用して、結果セットの1行目を変数DEPT_RECに代入しています。そして、変数DEPT_RECの各フィールドに代入されている値をDBMS_OUTPUT.PUT_LINEで出力しています。

　この「変数への代入→値の出力」という一連の処理を、結果セットの2行目以降でも繰り返し行うため、LOOP文を併用しています。また、LOOP～END LOOP間には、LOOP処理を終了するためにEXIT WHEN文を指定しています。「dept_cur%NOTFOUND」という条件を指定しているため、カーソルDEPT_CURの結果セットが全行取り出され、取り出す行がなくなったらLOOP処理を終了します（カーソル属性の%NOTFOUNDは次に説明します）。

　最後にCLOSE文を指定し、カーソルをクローズしています。これで結果セットが確保していたメモリ領域を解放します。

カーソル属性

　カーソル属性を使用すると、「取り出す行があるかどうか」「これまで何行取り出したのか」のような、現在のカーソルの状態を確認できます。そのため、カーソル属性で得られた結果に基づいて次の実行内容を決定できます。それでは、どのような属性があるのかを次の表で見てみましょう。

属性	意味
%NOTFOUND	直前のFETCH文が行を戻した場合はFALSE、行を戻さない場合はTRUE
%FOUND	直前のFETCH文が行を戻した場合はTRUE、行を戻さない場合はFALSE
%ROWCOUNT	これまでに取り出した行数
%ISOPEN	カーソルがオープンしている場合はTRUE、クローズしている場合はFALSE

　カーソル属性は次のように指定します。

<カーソル名><カーソル属性>

カーソル属性を指定する書式

　次のサンプルプログラムでは、%NOTFOUND属性、%ROWCOUNT属性を使用しています。

SQL> DECLARE
 2    CURSOR dept_cur IS SELECT * FROM dept;
 3    dept_rec dept_cur%ROWTYPE;
 4  BEGIN
 5    OPEN dept_cur;
 6      LOOP
 7        FETCH dept_cur INTO dept_rec;
 8        EXIT WHEN dept_cur%NOTFOUND;
 9          DBMS_OUTPUT.PUT_LINE(dept_cur%ROWCOUNT);
10      END LOOP;
11    CLOSE dept_cur;
12  END;
13  /
1
2
3
4

　%NOTFOUND属性を、EXIT WHEN文の条件として使用しています。カーソルDEPT_CURの結果セットは4行であるため、4回目のLOOP処理までは%NOTFOUND属性をFALSEと識別し、処理を続行します。その後、5回目のLOOP処理の際に結果セットから取り出す行がないため、%NOTFOUND属性がTRUEと識別し、LOOP処理を終了します。

　%ROWCOUNT属性は、カーソルの結果セットから取り出した行数をカウントし、その結果をDBMS_OUTPUT.PUT_LINEで表示するために使用しています。実行結果からは、カーソルの結果セットから4行を取り出したことが分かります（末尾の4行）。

　いかがでしたでしょうか。カーソル処理では複数のステップが必要になるため、難しく感じられた方もいるかもしれません。しかし、カーソルは多くの場面で使用しますので、この機会にしっかりと理解しておきましょう。

　次回は、カーソル処理をより効率的に行うための応用編になります。

この連載を「連載記事アラート」に登録する New

筆者紹介

小笠原宏幸（おがさわら ひろゆき）

株式会社アシスト データベース技術本部所属。普段はOracle、PostgreSQL、JP1などの分野で研修講師を担当。また、書籍「SQL逆引き大全363の極意」（株式会社秀和システム）をはじめ、「これならわかるOracle超入門教室」（株式会社翔泳社）、「プロとしてのPL/SQL入門」（SBクリエイティブ株式会社）の共著も担当。