カーソルの概要

カーソルとは、特定のSELECT文またはDML文の処理に関する情報を格納しておく、SQLのプライベート領域を指すポインタです。

ノート:

このトピックで説明するカーソルはセッション・カーソルです。セッション・カーソルはセッション・メモリーに存在し、セッションが終了すると消滅します。

PL/SQLで構築され管理されるカーソルは、暗黙カーソルです。ユーザーが構築および管理するカーソルは、明示カーソルです。

セッション・カーソルに関する情報は、その属性から取得できます(属性は、プロシージャ文では参照できますが、SQL文では参照できません)。

各ユーザー・セッションでオープンされて解析されているセッション・カーソルをリストするには、動的パフォーマンス・ビューV$OPEN_CURSORを問い合せます。

1つのセッションで同時にオープンできるカーソル数は、次のものにより決まります。

• セッションで使用可能なメモリーの量

• 初期化パラメータOPEN_CURSORSの値

ノート:

一般に、PL/SQLによる明示カーソルは、それをセッションが最初にオープンするときにのみ解析されます。また、SQL文の解析(およびそれによる暗黙カーソルの作成)は、その文が初めて実行されるときにのみ行われます。

解析されたSQL文は、すべてキャッシュに入れられます。SQL文は、新しいSQL文によってキャッシュからエージ・アウトされた場合にのみ再解析されます。したがって、明示カーソルを再オープンするには、まずクローズする必要がありますが、PL/SQLはカーソルに関連付けられた問合せを再解析する必要はありません。明示カーソルをクローズしてからすぐに再オープンした場合、PL/SQLは関連付けられた問合せを再解析しません。

ここでのトピック

• 暗黙カーソル

• 明示カーソル

関連項目:

• 動的パフォーマンス・ビューV$OPEN_CURSORの詳細は、『Oracle Databaseリファレンス』を参照してください

• 初期化パラメータOPEN_CURSORSの詳細は、『Oracle Databaseリファレンス』を参照してください

暗黙カーソル

暗黙カーソルは、PL/SQLで構築され管理されるセッション・カーソルです。PL/SQLでは、SELECT文またはDML文を実行するたびに、暗黙カーソルがオープンされます。暗黙カーソルを制御することはできませんが、その属性から情報を取得することはできます。

暗黙カーソルの属性値の構文は、SQLattributeです(このため、暗黙カーソルは、SQLカーソルとも呼ばれます)。SQLattributeは、直前に実行されたSELECT文またはDML文を常に参照します。そのような文が実行されていない場合は、SQLattributeの値はNULLです。

暗黙カーソルは、それが関連付けられている文の実行後、クローズされます。ただし、その属性値は、他のSELECT文またはDML文が実行されるまで、使用可能な状態に保たれます。

直前に実行されたSELECT文またはDML文が、別の有効範囲に存在する場合もあります。属性の値を保存して後で使用する場合は、値をローカル変数に直接代入してください。そうしないと、サブプログラム起動などの他の操作を実行した場合、属性値がテスト前に変更される可能性があります。

暗黙カーソルの属性には次のものがあります。

• SQL%ISOPEN属性: カーソルがオープンしているかどうか

• SQL%FOUND属性: 影響を受けた行があったかどうか

• SQL%NOTFOUND属性: どの行も影響を受けなかったかどうか

• SQL%ROWCOUNT属性: 影響を受けた行数

• SQL%BULK_ROWCOUNT(「FORALL文の影響を受ける行の数の取得」を参照)

• SQL%BULK_EXCEPTIONS(「FORALL文が完了した後のFORALL例外の処理」を参照

関連項目:

構文およびセマンティクスの詳細は、「暗黙カーソル属性」を参照してください

SQL%ISOPEN属性: カーソルがオープンしているかどうか

暗黙カーソルは、関連付けられている文の実行後、常にクローズされるため、SQL%ISOPENは常にFALSEを戻します。

SQL%FOUND属性: 影響を受けた行があったかどうか

SQL%FOUNDは次の値を戻します:

• SELECT文もDML文も実行されない場合は、NULL。

• SELECT文が1つ以上の行を戻した場合、またはDML文が1つ以上の行に影響を与えた場合は、TRUE。

• それ以外の場合は、FALSE

例7-3では、SQL%FOUNDを使用して、DELETE文によって影響を受けた行があるかどうかを判断しています。

例7-3 SQL%FOUND暗黙カーソル属性

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
  SELECT * FROM departments;
 
CREATE OR REPLACE PROCEDURE p (
  dept_no NUMBER
) AUTHID CURRENT_USER AS
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
 
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE (
      'Delete succeeded for department number ' || dept_no
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/
BEGIN
  p(270);
  p(400);
END;
/

結果:

Delete succeeded for department number 270
No department number 400

SQL%NOTFOUND属性: どの行も影響を受けなかったかどうか

SQL%NOTFOUND(論理的にSQL%FOUNDの逆)は次の値を戻します。

• SELECT文もDML文も実行されない場合は、NULL。

• SELECT文が1つ以上の行を戻した場合、またはDML文が1つ以上の行に影響を与えた場合は、FALSE。

• それ以外の場合は、TRUE

SQL%NOTFOUND属性は、PL/SQLのSELECT INTO文と組み合せて使用しても効果がありません。これは、次のような理由からです。

• SELECT INTO文によって行が戻されない場合、SQL%NOTFOUNDをチェックする前に、PL/SQLによって事前定義例外NO_DATA_FOUNDがただちに呼び出されます。

• SQL集計ファンクションを起動するSELECT INTO文は、常にある値(多くはNULL)を戻します。このような文の後では、SQL%NOTFOUND属性の値は常にFALSEになるため、属性をチェックする必要はありません。

SQL%ROWCOUNT属性: 影響を受けた行数

SQL%ROWCOUNTは次の値を戻します:

• SELECT文もDML文も実行されない場合は、NULL。

• それ以外の場合は、SELECT文によって戻された行の数、またはDML文によって影響を受けた行の数(INTEGER)。

ノート:

サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11gリリース2以前(またはその逆)の場合、SQL%ROWCOUNTが戻す最大値は4,294,967,295です。

例7-4では、SQL%ROWCOUNTを使用して、削除された行数を判断しています。

SELECT INTO文がBULK COLLECT句を使用せずに複数の行を戻した場合、PL/SQLによって事前定義の例外TOO_MANY_ROWSが呼び出され、SQL%ROWCOUNTは、問合せを満たす行の実数ではなく、1を戻します。

SQL%ROWCOUNT属性の値はトランザクションの状態には無関係です。したがって、次のようになります:

• トランザクションによってセーブポイントへロールバックされても、SQL%ROWCOUNTの値が、以前のセーブポイントの値に戻ることはありません。

• 自律型トランザクションが終了しても、SQL%ROWCOUNTが親トランザクション内の元の値に戻ることはありません。

例7-4 SQL%ROWCOUNT暗黙カーソル属性

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
  SELECT * FROM employees;

DECLARE
  mgr_no NUMBER(6) := 122;
BEGIN
  DELETE FROM employees_temp WHERE manager_id = mgr_no;
  DBMS_OUTPUT.PUT_LINE
    ('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

結果:

Number of employees deleted: 8

明示カーソル

明示カーソルは、ユーザーが構築および管理するセッション・カーソルです。明示カーソルを宣言および定義する際には、カーソルに名前を付け、問合せ(通常は複数の行を戻す問合せ)と関連付ける必要があります。その後、次のいずれかの方法で問合せ結果セットを処理できます。

• (OPEN文で)明示カーソルをオープンし、(FETCH文で)結果セットから行をフェッチし、(CLOSE文で)明示カーソルをクローズします。

• カーソルFOR LOOP文で明示カーソルを使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。

明示カーソルに値を代入したり、明示カーソルを式の中で使用したり、明示カーソルを仮サブプログラム・パラメータやホスト変数として使用することはできません。これらの操作は、カーソル変数を使用することで実行できます(「カーソル変数」を参照)。

明示カーソルまたはカーソル変数は、暗黙カーソルとは異なり、その名前で参照できます。そのため、明示カーソルまたはカーソル変数は、名前付きカーソルと呼ばれます。

ここでのトピック

• 明示カーソルの宣言および定義

• 明示カーソルのオープンおよびクローズ

• 明示カーソルによるデータのフェッチ

• 明示カーソル問合せの変数

• 明示カーソル問合せで列の別名が必要な場合

• パラメータを受け入れる明示カーソル

• 明示カーソル属性

明示カーソルの宣言および定義

最初に明示カーソルを宣言して、後で同じブロック、サブプログラムまたはパッケージ内で定義するか、または宣言と定義を同時に行うことができます。

カーソルの宣言のみを行う明示カーソル宣言の構文は、次のとおりです。

CURSOR cursor_name [ parameter_list ] RETURN return_type;

明示カーソル定義の構文は、次のとおりです。

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
  IS select_statement;

先にカーソルを宣言した場合、後で明示カーソル定義によってカーソルを定義します。それ以外の場合は、明示カーソルの宣言と定義を同時に行います。

例7-5では、3つの明示カーソルを宣言および定義しています。

関連項目:

• 明示カーソルの宣言と定義の構文およびセマンティクスの詳細は、「明示カーソルの宣言および定義」を参照してください

• 「パラメータを受け入れる明示カーソル」

例7-5 明示カーソルの宣言および定義

DECLARE
  CURSOR c1 RETURN departments%ROWTYPE;    -- Declare c1
 
  CURSOR c2 IS                             -- Declare and define c2
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > 2000; 
 
  CURSOR c1 RETURN departments%ROWTYPE IS  -- Define c1,
    SELECT * FROM departments              -- repeating return type
    WHERE department_id = 110;
 
  CURSOR c3 RETURN locations%ROWTYPE;      -- Declare c3
 
  CURSOR c3 IS                             -- Define c3,
    SELECT * FROM locations                -- omitting return type
    WHERE country_id = 'JP';
BEGIN
  NULL;
END;
/

明示カーソルのオープンおよびクローズ

明示カーソルを宣言および定義したら、OPEN文でそのカーソルをオープンし、次の操作を実行できます。

1. 問合せを処理するデータベース・リソースを割り当てます。

2. 次のように問合せを処理します。

   1. 結果セットを識別します。

      問合せが変数またはカーソル・パラメータを参照している場合、結果セットはそれらの値に影響を受けます。詳細は、「明示カーソル問合せの変数」および「パラメータを受け入れる明示カーソル」を参照してください。

   2. 問合せにFOR UPDATE句がある場合、結果セットの行をロックします。

      詳細は、「SELECT FOR UPDATEカーソルとFOR UPDATEカーソル」を参照してください。

3. カーソルを結果セットの最初の行の前に配置します。

オープンした明示カーソルは、CLOSE文を使用してクローズすることで、そのリソースを再利用できます。カーソルのクローズ後は、その結果セットからレコードをフェッチしたり、その属性を参照することはできません。これらの操作が試行されると、PL/SQLは事前定義の例外INVALID_CURSORを呼び出します。

クローズしたカーソルは、再オープンできます。明示カーソルは、再オープンする前にクローズする必要があります。それ以外の場合、PL/SQLは事前定義の例外CURSOR_ALREADY_OPENを呼び出します。

関連項目:

• 構文およびセマンティクスの詳細は、「OPEN文」を参照してください

• CLOSE文の構文およびセマンティクスの詳細は、「CLOSE文」を参照してください

明示カーソルによるデータのフェッチ

明示カーソルをオープンしたら、FETCH文を使用して、問合せ結果セットの行をフェッチできます。1つの行を戻すFETCH文の基本構文は、次のとおりです。

FETCH cursor_name INTO into_clause

into_clauseは、変数のリストまたはシングル・レコード変数です。問合せが戻す各列に対して、変数のリストまたはレコードの中に、対応する型互換の変数またはフィールドが存在している必要があります。%TYPEおよび%ROWTYPE属性は、FETCH文で使用する変数およびレコードを宣言する場合に便利です。

FETCH文は、結果セットの現在の行を取り出し、その行の列値を変数またはレコードに格納して、カーソルを次の行に進めます。

通常、FETCH文はLOOP文の内部で使用し、FETCH文で取り出す行がなくなったら、ループを終了します。この終了条件を検出するには、カーソル属性%NOTFOUNDを使用します(「%NOTFOUND属性: どの行もフェッチされていないかどうか」を参照)。PL/SQLは、FETCH文が行を戻さない場合でも、例外を呼び出しません。

例7-6では、LOOP文の内部でFETCHおよび%NOTFOUNDを使用して、2つの明示カーソルの結果セットから一度に1行ずつ行をフェッチします。最初のFETCH文では、列値を変数に取り出しています。2番目のFETCH文では、列値をレコードに取り出しています。変数とレコードは、それぞれ%TYPEと%ROWTYPEで宣言されています。

例7-7では、それぞれが異なるレコード変数にフェッチする5つのFETCH文を使用して、結果セットの最初の5行を5つのレコードにフェッチします。レコード変数は、%ROWTYPEで宣言されています。

関連項目:

• FETCH文の構文およびセマンティクスの詳細は、「FETCH文」を参照してください

• 一度に複数の行を戻すFETCH文の詳細は、「BULK COLLECT句を使用したFETCH文」を参照してください

例7-6 LOOP文内部のFETCH文

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
    ORDER BY last_name;

  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  v_jobid     employees.job_id%TYPE;     -- variable for job_id

  CURSOR c2 IS
    SELECT * FROM employees
    WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
    ORDER BY job_id;

  v_employees employees%ROWTYPE;  -- record variable for row of table

BEGIN
  OPEN c1;
  LOOP  -- Fetches 2 columns into variables
    FETCH c1 INTO v_lastname, v_jobid;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );

  OPEN c2;
  LOOP  -- Fetches entire row into the v_employees record
    FETCH c2 INTO v_employees;
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
                               v_employees.job_id );
  END LOOP;
  CLOSE c2;
END;
/

結果:

Atkinson                 ST_CLERK
Bell                     SH_CLERK
Bissot                   ST_CLERK
...
Walsh                    SH_CLERK
-------------------------------------
Higgins                  AC_MGR
Gruenberg                FI_MGR
Martinez                 MK_MAN
...
Errazuriz                SA_MAN

例7-7 異なる変数への同じ明示カーソルのフェッチ

DECLARE
  CURSOR c IS
    SELECT e.job_id, j.job_title
    FROM employees e, jobs j
    WHERE e.job_id = j.job_id AND e.manager_id = 100
    ORDER BY last_name;
 
  -- Record variables for rows of cursor result set:
 
  job1 c%ROWTYPE;
  job2 c%ROWTYPE;
  job3 c%ROWTYPE;
  job4 c%ROWTYPE;
  job5 c%ROWTYPE;
 
BEGIN
  OPEN c;
  FETCH c INTO job1;  -- fetches first row
  FETCH c INTO job2;  -- fetches second row
  FETCH c INTO job3;  -- fetches third row
  FETCH c INTO job4;  -- fetches fourth row
  FETCH c INTO job5;  -- fetches fifth row
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/

結果:

Sales Manager (SA_MAN)
Sales Manager (SA_MAN)
Stock Manager (ST_MAN)
Administration Vice President (AD_VP)
Stock Manager (ST_MAN)

PL/SQL procedure successfully completed.

明示カーソル問合せの変数

明示カーソル問合せでは、有効範囲内にある任意の変数を参照できます。明示カーソルをオープンすると、PL/SQLによって問合せ内のすべての変数が評価され、結果セットの識別時にそれらの値が使用されます。変数の値を後で変更しても、結果セットは変更されません。

例7-8では、明示カーソル問合せで変数factorを参照しています。カーソルがオープンしたとき、factorの値は2です。したがって、sal_multipleは、各フェッチ後にfactorが増分されても、常にsalの2倍になります。

結果セットを変更するには、例7-9に示すとおり、カーソルをクローズし、変数の値を変更してからカーソルを再度オープンする必要があります。

例7-8 明示カーソル問合せの変数(結果セットの変更なし)

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  CURSOR c1 IS
    SELECT salary, salary*factor FROM employees
    WHERE job_id LIKE 'AD_%';
 
BEGIN
  OPEN c1;  -- PL/SQL evaluates factor
 
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1;  -- Does not affect sal_multiple
  END LOOP;
 
  CLOSE c1;
END;
/

結果:

factor = 2
sal          = 4400
sal_multiple = 8800
factor = 3
sal          = 24000
sal_multiple = 48000
factor = 4
sal          = 17000
sal_multiple = 34000
factor = 5
sal          = 17000
sal_multiple = 34000

例7-9 明示カーソル問合せの変数(結果セットの変更あり)

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  CURSOR c1 IS
    SELECT salary, salary*factor FROM employees
    WHERE job_id LIKE 'AD_%';
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
  END LOOP;
  CLOSE c1;
 
  factor := factor + 1;
 
  DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
  END LOOP;
  CLOSE c1;
END;
/

結果:

factor = 2
sal          = 4400
sal_multiple = 8800
sal          = 24000
sal_multiple = 48000
sal          = 17000
sal_multiple = 34000
sal          = 17000
sal_multiple = 34000
factor = 3
sal          = 4400
sal_multiple = 13200
sal          = 24000
sal_multiple = 72000
sal          = 17000
sal_multiple = 51000
sal          = 17000
sal_multiple = 51000

明示カーソル問合せで列の別名が必要な場合

明示カーソル問合せに仮想列(式)が含まれる場合、次のいずれかに該当すると、その列には別名が必要です。

• カーソルを使用して、%ROWTYPEで宣言されたレコードにフェッチする場合。

• 仮想列をプログラム内で参照する場合。

例7-10では、前述の両方の理由のため、明示カーソル内の仮想列に別名が必要です。

関連項目:

例7-21

例7-10 仮想列に別名が必要な明示カーソル

DECLARE
  CURSOR c1 IS
    SELECT employee_id,
           (salary * .05) raise
    FROM employees
    WHERE job_id LIKE '%_MAN'
    ORDER BY employee_id;
  emp_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (
      'Raise for employee #' || emp_rec.employee_id ||
      ' is $' || emp_rec.raise
    ); 
  END LOOP;
  CLOSE c1;
END;
/

結果:

Raise for employee #114 is $550
Raise for employee #120 is $400
Raise for employee #121 is $410
Raise for employee #122 is $395
Raise for employee #123 is $325
Raise for employee #124 is $368.445
Raise for employee #145 is $700
Raise for employee #146 is $675
Raise for employee #147 is $600
Raise for employee #148 is $550
Raise for employee #149 is $525
Raise for employee #201 is $650

パラメータを受け入れる明示カーソル

仮パラメータを含む明示カーソルを作成し、オープンするたびにそのカーソルに異なる実パラメータを渡すことができます。カーソル問合せ内では、定数を使用できる場所であればどこでも仮カーソル・パラメータを使用できます。カーソル問合せの外部では、仮カーソル・パラメータは参照できません。

ヒント:

混同を避けるため、仮カーソル・パラメータと実カーソル・パラメータには異なる名前を使用してください。

例7-11では、2つの仮パラメータで職種とその最大給与を表す明示カーソルを作成しています。カーソル問合せは、職種と最大給与を指定してオープンされると、その職種で給与が余分に支払われている従業員を選択します(問合せは、そのような従業員ごとに氏名と超過支払額を選択します)。次に、この例では、カーソル問合せの結果セットを出力するプロシージャを作成しています(プロシージャの詳細は、「PL/SQLサブプログラム」を参照してください)。最後に、この例では、実パラメータの1つのセットを使用したカーソルのオープン、その結果セットの出力、カーソルのクローズ、および異なる実パラメータを使用したカーソルのオープン、その結果セットの出力、カーソルのクローズを行っています。

ここでのトピック

• デフォルト値を持つ仮カーソル・パラメータ

• デフォルト値を持つ仮カーソル・パラメータの追加

関連項目:

• 仮カーソル・パラメータの詳細は、「明示カーソルの宣言および定義」を参照してください

• 実カーソル・パラメータの詳細は、「OPEN文」を参照してください

例7-11 パラメータを受け入れる明示カーソル

DECLARE
  CURSOR c (job VARCHAR2, max_sal NUMBER) IS
    SELECT last_name, first_name, (salary - max_sal) overpayment
    FROM employees
    WHERE job_id = job
    AND salary > max_sal
    ORDER BY salary;
 
  PROCEDURE print_overpaid IS
    last_name_   employees.last_name%TYPE;
    first_name_  employees.first_name%TYPE;
    overpayment_      employees.salary%TYPE;
  BEGIN
    LOOP
      FETCH c INTO last_name_, first_name_, overpayment_;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
        ' (by ' || overpayment_ || ')');
    END LOOP;
  END print_overpaid;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('----------------------');
  DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:');
  DBMS_OUTPUT.PUT_LINE('----------------------');
  OPEN c('ST_CLERK', 5000);
  print_overpaid; 
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE('-------------------------------');
  DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
  DBMS_OUTPUT.PUT_LINE('-------------------------------');
  OPEN c('SA_REP', 10000);
  print_overpaid; 
  CLOSE c;
END;
/

結果:

----------------------
Overpaid Stock Clerks:
----------------------
-------------------------------
Overpaid Sales Representatives:
-------------------------------
Vishney, Clara (by 500)
Abel, Ellen (by 1000)
Ozer, Lisa (by 1500)
 
PL/SQL procedure successfully completed.

デフォルト値を持つ仮カーソル・パラメータ

仮パラメータを使用して明示カーソルを作成する場合、それらのカーソルにデフォルト値を指定できます。仮パラメータがデフォルト値を持つ場合、対応する実パラメータはオプションになります。実パラメータを指定せずにカーソルをオープンすると、仮パラメータにそのデフォルト値が割り当てられます。

例7-12では、場所IDを表す仮パラメータを持つ明示カーソルを作成しています。このパラメータのデフォルト値は、企業本社の場所IDです。

例7-12 デフォルト値を持つカーソル・パラメータ

DECLARE
  CURSOR c (location NUMBER DEFAULT 1700) IS
    SELECT d.department_name,
           e.last_name manager,
           l.city
    FROM departments d, employees e, locations l
    WHERE l.location_id = location
      AND l.location_id = d.location_id
      AND d.department_id = e.department_id
    ORDER BY d.department_id;
 
  PROCEDURE print_depts IS
    dept_name  departments.department_name%TYPE;
    mgr_name   employees.last_name%TYPE;
    city_name  locations.city%TYPE;
  BEGIN
    LOOP
      FETCH c INTO dept_name, mgr_name, city_name;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(dept_name || ' (Manager: ' || mgr_name || ')');
    END LOOP;
  END print_depts;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT HEADQUARTERS:');
  DBMS_OUTPUT.PUT_LINE('--------------------------------');
  OPEN c;
  print_depts; 
  DBMS_OUTPUT.PUT_LINE('--------------------------------');
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE('DEPARTMENTS IN CANADA:');
  DBMS_OUTPUT.PUT_LINE('--------------------------------');
  OPEN c(1800); -- Toronto
  print_depts; 
  CLOSE c;
  OPEN c(1900); -- Whitehorse
  print_depts; 
  CLOSE c;
END;
/
 

結果は次のようになります。

DEPARTMENTS AT HEADQUARTERS:
--------------------------------
Administration (Manager: Whalen)
Purchasing (Manager: Himuro)
Purchasing (Manager: Tobias)
Purchasing (Manager: Baida)
Purchasing (Manager: Li)
Purchasing (Manager: Colmenares)
Purchasing (Manager: Khoo)
Executive (Manager: Yang)
Executive (Manager: Garcia)
Executive (Manager: King)
Finance (Manager: Urman)
Finance (Manager: Sciarra)
Finance (Manager: Chen)
Finance (Manager: Faviet)
Finance (Manager: Gruenberg)
Finance (Manager: Popp)
Accounting (Manager: Higgins)
Accounting (Manager: Gietz)
--------------------------------
DEPARTMENTS IN CANADA:
--------------------------------
Marketing (Manager: Davis)
Marketing (Manager: Martinez)
 
PL/SQL procedure successfully completed.

デフォルト値を持つ仮カーソル・パラメータの追加

カーソルに仮パラメータを追加し、その追加したパラメータにデフォルト値を指定する場合、カーソルに対する既存の参照を変更する必要はありません。例7-13を例7-11と比較してください。

例7-13 既存のカーソルへの仮パラメータの追加

DECLARE
  CURSOR c (job VARCHAR2, max_sal NUMBER,
            hired DATE DEFAULT TO_DATE('31-DEC-1999', 'DD-MON-YYYY')) IS
    SELECT last_name, first_name, (salary - max_sal) overpayment
    FROM employees
    WHERE job_id = job
    AND salary > max_sal
    AND hire_date > hired
    ORDER BY salary;
 
  PROCEDURE print_overpaid IS
    last_name_   employees.last_name%TYPE;
    first_name_  employees.first_name%TYPE;
    overpayment_      employees.salary%TYPE;
  BEGIN
    LOOP
      FETCH c INTO last_name_, first_name_, overpayment_;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
        ' (by ' || overpayment_ || ')');
    END LOOP;
  END print_overpaid;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('-------------------------------');
  DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
  DBMS_OUTPUT.PUT_LINE('-------------------------------');
  OPEN c('SA_REP', 10000);  -- existing reference
  print_overpaid; 
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
  DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2014:');
  DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
  OPEN c('SA_REP', 10000, TO_DATE('31-DEC-2014', 'DD-MON-YYYY'));
                          -- new reference
  print_overpaid; 
  CLOSE c;
END;
/

結果:

-------------------------------
Overpaid Sales Representatives:
-------------------------------
Vishney, Clara (by 500)
Abel, Ellen (by 1000)
Ozer, Lisa (by 1500)
------------------------------------------------
Overpaid Sales Representatives Hired After 2014:
------------------------------------------------
Vishney, Clara (by 500)
Ozer, Lisa (by 1500)
 
PL/SQL procedure successfully completed.

明示カーソル属性

明示カーソル属性値の構文は、cursor_nameのすぐ後にattributeが続きます(たとえば、c1%ISOPEN)。

ノート:

明示カーソルとカーソル変数(名前付きカーソル)は、同じ属性を持ちます。このトピックは、特に説明がないかぎり、すべての名前付きカーソルに適用されます。

明示カーソルの属性には次のものがあります。

• %ISOPEN属性: カーソルがオープンしているかどうか

• %FOUND属性: 1行がフェッチされたかどうか

• %NOTFOUND属性: どの行もフェッチされていないかどうか

• %ROWCOUNT属性: フェッチされた行数

明示カーソルをオープンしていない場合、%ISOPEN以外の属性を参照すると、事前定義の例外INVALID_CURSORが呼び出されます。

関連項目:

名前付きカーソル(明示カーソルとカーソル変数)の属性の構文およびセマンティクスの詳細は、「名前付きカーソル属性」を参照してください

%ISOPEN属性: カーソルがオープンしているかどうか

%ISOPENは、明示カーソルをオープンしている場合はTRUEを、そうでない場合はFALSEを戻します。

%ISOPENは、次の場合に便利です。

• 明示カーソルをオープンする前にまだオープンしていないことを確認する場合。

  すでにオープンしている明示カーソルをオープンしようとすると、PL/SQLによって事前定義の例外CURSOR_ALREADY_OPENが呼び出されます。明示カーソルは、再オープンする前にクローズする必要があります。

  ノート:

  前述の内容は、カーソル変数には適用されません。

• 明示カーソルをクローズする前にオープンしていることを確認する場合。

例7-14では、明示カーソルc1がオープンしていない場合にのみオープンし、オープンしている場合にのみクローズします。

例7-14 %ISOPEN明示カーソル属性

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11;

  the_name employees.last_name%TYPE;
  the_salary employees.salary%TYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;

  FETCH c1 INTO the_name, the_salary;

  IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
END;
/

%FOUND属性: 1行がフェッチされたかどうか

%FOUNDは次の値を戻します:

• 明示カーソルのオープン後、最初のフェッチが実行されるまでは、NULL。

• 明示カーソルからの最新のフェッチで行が戻された場合は、TRUE。

• それ以外の場合は、FALSE

%FOUNDは、処理する必要のあるフェッチ行が存在するかどうかを確認する場合に便利です。

例7-15では、結果セットをループして各フェッチ行を出力し、フェッチする行がなくなると終了します。

例7-15 %FOUND明示カーソル属性

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;

  my_ename   employees.last_name%TYPE;
  my_salary  employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%FOUND THEN  -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
    ELSE  -- fetch failed
      EXIT;
    END IF;
  END LOOP;
END;
/

結果:

Name = Faviet, salary = 9000
Name = Garcia, salary = 17000
Name = Gruenberg, salary = 12008
Name = Jackson, salary = 4800
Name = James, salary = 9000
Name = King, salary = 24000
Name = Miller, salary = 6000
Name = Nguyen, salary = 4200
Name = Williams, salary = 4800
Name = Yang, salary = 17000

%NOTFOUND属性: どの行もフェッチされていないかどうか

%NOTFOUND(論理的に%FOUNDの逆)は次の値を戻します。

• 明示カーソルのオープン後、最初のフェッチが実行されるまでは、NULL。

• 明示カーソルからの最新のフェッチで行が戻された場合は、FALSE。

• それ以外の場合は、TRUE

例7-16に示すとおり、%NOTFOUNDは、FETCHが行を戻さなくなったときにループを終了する場合に便利です。

例7-16 %NOTFOUND明示カーソル属性

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;

   my_ename   employees.last_name%TYPE;
   my_salary  employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%NOTFOUND THEN -- fetch failed
      EXIT;
    ELSE  -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE
        ('Name = ' || my_ename || ', salary = ' || my_salary);
    END IF;
  END LOOP;
END;
/

結果:

Name = Faviet, salary = 9000
Name = Garcia, salary = 17000
Name = Gruenberg, salary = 12008
Name = Jackson, salary = 4800
Name = James, salary = 9000
Name = King, salary = 24000
Name = Miller, salary = 6000
Name = Nguyen, salary = 4200
Name = Williams, salary = 4800
Name = Yang, salary = 17000

%ROWCOUNT属性: フェッチされた行数

%ROWCOUNTは次の値を戻します:

• 明示カーソルのオープン後、最初のフェッチが実行されるまでは、0(ゼロ)。

• それ以外の場合は、フェッチされた行数(INTEGER)。

  ノート:

  サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11g2以前(またはその逆)の場合、SQL%ROWCOUNTが戻す最大値は4,294,967,295です。

例7-17では、フェッチした行の数をカウントして出力し、5行目のフェッチ後にメッセージを出力します。

例7-17 %ROWCOUNT明示カーソル属性

DECLARE
  CURSOR c1 IS
    SELECT last_name FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;

  name  employees.last_name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
    DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
    IF c1%ROWCOUNT = 5 THEN
       DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

結果:

1. Abel
2. Ande
3. Atkinson
4. Baida
5. Banda
--- Fetched 5th row ---
6. Bates
7. Bell
8. Bernstein
9. Bissot
10. Bloom