ヘッダーをスキップ

Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス
10g リリース2(10.2)

B19257-01
目次
目次
索引
索引

戻る 次へ

6 PL/SQLでのSQL操作の実行

この章では、Oracleデータを操作するためのSQLコマンド、ファンクションおよび演算子がPL/SQLでどのようにサポートされているかを示します。

この章の項目は、次のとおりです。

PL/SQLにおけるSQLサポートの概要

SQLを拡張したPL/SQLは、優れた性能に加えて、使いやすさも実現しています。PL/SQLは、EXPLAIN PLAN以外のすべてのSQLデータ操作文、トランザクション制御文、ファンクション、疑似列および演算子を完全にサポートしているため、Oracleデータを柔軟かつ安全に操作できます。PL/SQLは、現行のANSI/ISO SQL規格にも準拠しています。

PL/SQLでは、この章で説明する静的SQLだけでなく動的SQLもサポートしているため、SQLデータ定義文、データ制御文、セッション制御文を動的に実行できます。第7章「システム固有の動的SQLを使用したSQL操作の実行」を参照してください。

データ操作

例6-1に示すとおり、Oracleデータを操作する場合は、INSERT文、UPDATE文およびDELETE文などのDML操作をPL/SQLプログラム内に直接記述できます。特別な表記法は必要ありません。SQLのCOMMIT文をPL/SQLプログラム内に直接記述することもできます。「PL/SQLにおけるトランザクション処理の概要」を参照してください。また、『Oracle Database SQLリファレンス』の「COMMIT」も参照してください。

例6-1    PL/SQLを使用したデータ操作

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name 
       FROM employees;
DECLARE
  emp_id          employees_temp.employee_id%TYPE;
  emp_first_name  employees_temp.first_name%TYPE;
  emp_last_name   employees_temp.last_name%TYPE;
BEGIN
   INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry');
   UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299;
   DELETE FROM employees_temp WHERE employee_id = 299 
     RETURNING first_name, last_name INTO emp_first_name, emp_last_name;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE( emp_first_name  || ' ' || emp_last_name);
END;
/

DML文が影響を与える行の数を確認するには、例6-2に示すように、SQL%ROWCOUNTの値をチェックします。

例6-2    UPDATE後のSQL%ROWCOUNTのチェック

CREATE TABLE employees_temp AS SELECT * FROM employees;
BEGIN
  UPDATE employees_temp SET salary = salary * 1.05 WHERE salary < 5000;
  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
END;
/

例6-3に示すように、リテラル値またはその他のプログラミング言語のバインド変数を使用するすべての箇所で、PL/SQL変数を直接置き換えることができます。

例6-3    PL/SQL変数の置換

CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees;
DECLARE
   x VARCHAR2(20) := 'my_first_name';
   y VARCHAR2(25) := 'my_last_name';
BEGIN
   INSERT INTO employees_temp VALUES(x, y);
   UPDATE employees_temp SET last_name = x WHERE first_name = y;
   DELETE FROM employees_temp WHERE first_name = x;
   COMMIT;
END;
/

この表記法では、WHERE句の値のかわりに変数を使用できます。表名や列名などのかわりに変数を使用するには、「PL/SQLでのEXECUTE IMMEDIATE文の使用」で説明されているEXECUTE IMMEDIATE文を使用する必要があります。

PL/SQLレコードとSQLを併用してデータを更新および挿入する手順については、「PL/SQLレコードのデータベースへの挿入」および「PL/SQLレコード値を使用したデータベースの更新」を参照してくたさい。

PL/SQL変数への値の代入の詳細は、「SQL問合せ結果のPL/SQL変数への代入」を参照してください。


注意:

PL/SQLでデータ操作(DML)文を発行すると、場合によっては、文の実行後に変数値が未定義になることがあります。このようになるのは、次の場合です。

  • FETCH文またはSELECT文で例外が呼び出された場合、その文の後の定義変数値は未定義になります。

  • DML文によって行が処理されない場合、DML実行後のOUTバインドの値は未定義になります。これは、BULKまたは複数行にわたる操作には適用されません。

 

トランザクション制御

Oracleでは、トランザクションに基づいて作業します。つまり、トランザクションを使用してデータの整合性を確保します。トランザクションとは、論理作業単位を実行する一連のSQLのDML文です。たとえば、2つのUPDATE文を使用して、ある銀行口座に入金し、別の口座から出金します。一方の操作は成功するが他方の操作は失敗するという状態を許可しないことが重要です。

Oracleは、データベースを変更するトランザクションの終わりに、すべての変更内容を確定または取消しします。トランザクション中にプログラムに障害が発生すると、Oracleがエラーを検出してトランザクションをロールバックし、データベースを元の状態にリストアします。

COMMITコマンド、ROLLBACKコマンド、SAVEPOINTコマンドおよびSET TRANSACTIONコマンドを使用して、トランザクションを制御します。COMMITは、カレント・トランザクション中にデータベースに加えられた変更内容を確定します。ROLLBACKは、カレント・トランザクションを終了させ、トランザクションの開始以降に加えられた変更をすべて取り消します。SAVEPOINTは、トランザクション処理内の現在位置にマークを付けます。ROLLBACKSAVEPOINTを併用すると、トランザクションの一部のみを取り消すことができます。SET TRANSACTIONは、読取り/書込みアクセスや分離レベルなど、トランザクションのプロパティを設定します。「PL/SQLにおけるトランザクション処理の概要」を参照してください。

SQLファンクション

例6-4に、SQLファンクションをコールする問合せの一部を示します。

例6-4    PL/SQL内でのSQL COUNTファンクションのコール

DECLARE
  job_count NUMBER;
  emp_count NUMBER;
BEGIN
  SELECT COUNT(DISTINCT job_id) INTO job_count FROM employees;
  SELECT COUNT(*) INTO emp_count FROM employees;
END;
/

SQL疑似列

PL/SQLでは、SQL擬似列CURRVALLEVELNEXTVALROWIDおよびROWNUMが認識されます。ただし、疑似列の使用には、代入または条件テストで一部の疑似列を使用できないなどの制限があります。制限など、SQL疑似列の使用の詳細は、『Oracle Database SQLリファレンス』を参照してください。

CURRVALとNEXTVAL

順序とは、連続的な数値を生成するスキーマ・オブジェクトのことです。順序を作成する場合は、その初期値と増分を指定できます。CURRVALは指定された順序の中での現在の値を戻します。セッションの中でCURRVALを参照する前に、NEXTVALを使用して数値を生成する必要があります。NEXTVALを参照すると、現在の順序番号がCURRVALに格納されます。NEXTVALは順序に増分を加えて、次の値を戻します。順序の現在の値または次の値を得るには、ドット表記法を使用します。

sequence_name.CURRVAL
sequence_name.NEXTVAL

順序のNEXTVAL値を参照するたびに、トランザクションをコミットするかロールバックするかにかかわらず、順序はすぐに増分され、その変化は永続的になります。

順序を作成すると、トランザクション処理の目的のために独自の順序番号を生成させることができます。CURRVALおよびNEXTVALは、SELECTリスト、VALUES句およびSET句の中でしか使用できません。例6-5に、新規のシーケンス番号を生成して、2つ以上の文で同じ番号を参照する方法を示します。

例6-5    CURRVALとNEXTVALの使用

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name 
  FROM employees;
CREATE TABLE employees_temp2 AS SELECT employee_id, first_name, last_name 
  FROM employees;
 
DECLARE
   seq_value   NUMBER;
BEGIN
-- Display initial value of NEXTVAL
-- This is invalid: seq_value := employees_seq.NEXTVAL;
   SELECT employees_seq.NEXTVAL INTO seq_value FROM dual;
   DBMS_OUTPUT.PUT_LINE ('Initial sequence value: ' || TO_CHAR(seq_value));
-- The NEXTVAL value is the same no matter what table you select from
-- You usually use NEXTVAL to create unique numbers when inserting data.
   INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');
-- If you need to store the same value somewhere else, you use CURRVAL
   INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'Morgan', 'Smith');
-- Because NEXTVAL values might be referenced by different users and
-- applications, and some NEXTVAL values might not be stored in the
-- database, there might be gaps in the sequence
-- The following uses the stored value of the CURRVAL in seq_value to specify 
-- the record to delete because CURRVAL (or NEXTVAL) cannot used in a WHERE clause
-- This is invalid: WHERE employee_id = employees_seq.CURRVAL;
   SELECT employees_seq.CURRVAL INTO seq_value FROM dual;
   DELETE FROM employees_temp2 WHERE employee_id = seq_value;
-- The following udpates the employee_id with NEXTVAL for the specified record
   UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL 
     WHERE first_name = 'Lynette' AND last_name = 'Smith';
-- Display end value of CURRVAL
   SELECT employees_seq.CURRVAL INTO seq_value FROM dual;
   DBMS_OUTPUT.PUT_LINE ('Ending sequence value: ' || TO_CHAR(seq_value));
END;
/

LEVEL

SELECT CONNECT BY文でLEVELを使用すると、データベース表の行をツリー構造に整理できます。順序番号を使用して各行に一意の識別子を指定し、他の行からこれらの識別子を参照して親子関係を構築できます。LEVELはツリー構造の中のノードのレベル番号を戻します。ルートはレベル1、ルートの子はレベル2、孫はレベル3、のように続きます。

ツリーのルートを識別する条件は、START WITH句で指定します。PRIOR演算子を使用して、問合せがツリーの中を移動するときの向き(ルートから下へ、または枝から上へ)を指定します。

ROWID

ROWIDはデータベース表の行のROWID(バイナリ・アドレス)を戻します。UROWID型の変数を使用すると、ROWIDを読取り可能な書式で格納できます。

物理ROWIDを選択またはフェッチしてUROWID変数に入れる場合は、バイナリ値を文字列に変換するファンクションROWIDTOCHARを使用します。UPDATE文またはDELETE文のWHERE句の中でUROWID変数とROWID疑似列を比較すると、カーソルからフェッチされた最新の行を識別できます。例は、「コミットにまたがるフェッチ」を参照してください。

ROWNUM

ROWNUMは、行が表から取り出された順番を示す番号を戻します。最初に取り出された行のROWNUMは1、2番目に取り出された行のROWNUMは2、のように続きます。SELECT文にORDER BY句が含まれている場合、取り出された行がソートされる前に、行にROWNUMが割り当てられます。ソートされた最初のn行を取得するには、副問合せを使用します。ROWNUMの値が増えるのは、行が取り出されたときのみです。つまり、WHERE句でROWNUMを使用する場合は、次のようにする必要があります。

... WHERE ROWNUM <定数
... WHERE ROWNUM <=定数

例6-6に示すとおり、UPDATE文でROWNUMを使用して、表の中の個々の行に一意の値を代入できます。また、SELECT文のWHERE句でROWNUMを使用して、取り出される行の数を制限できます

例6-6    ROWNUMの使用

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees_temp
      WHERE salary > 2000 AND ROWNUM <= 10;  -- 10 arbitrary rows
   CURSOR c2 IS SELECT * FROM
     (SELECT employee_id, salary FROM employees_temp
       WHERE salary > 2000 ORDER BY salary DESC)
     WHERE ROWNUM < 5;  -- first 5 rows, in sorted order
BEGIN
-- Each row gets assigned a different number
  UPDATE employees_temp SET employee_id = ROWNUM;
END;
/

SQL演算子

PL/SQLでは、SQL文の中で、SQLの比較演算子、集合演算子および行演算子を使用できます。この項では、これらの演算子について簡単に説明します。詳細は、『Oracle Database SQLリファレンス』を参照してください。

比較演算子

比較演算子は、通常、DML文のWHERE句で述語を形成するために使用します。述語は、2つの式を比較して、TRUEFALSENULLのいずれかに評価します。次の比較演算子は、述語の形成に使用できます。述語は論理演算子ANDORおよびNOTを使用して結合できます。

演算子  説明 

ALL 

値をリストのすべての値、または副問合せが戻したすべての値と1つずつ比較して、結果がすべてTRUEであればTRUEに評価します。 

ANYSOME 

値をリストのすべての値、または副問合せが戻したすべての値と1つずつ比較して、結果のいずれかがTRUEであればTRUEに評価します。 

BETWEEN 

値が指定範囲内かどうかをテストします。 

EXISTS 

副問合せが行を1つでも戻すとTRUEを戻します。 

IN 

セット・メンバーシップをテストします。 

IS NULL 

NULLかどうかをテストします。 

LIKE 

文字列が、指定したパターンと一致するかどうかをテストします。指定パターンにはワイルドカードを使用できます。 

集合演算子

集合演算子は2つの問合せの結果を組み合せて1つの結果を戻します。INTERSECTによって、2つの問合せの両方で選択されたすべての行を、重複するものを除いて戻します。MINUSは、1番目の問合せによって選択されたが、2番目の問合せでは選択されなかったすべての行を、重複するものを除いて戻します。UNIONによって、2つの問合せのいずれかで選択されたすべての行を、重複するものを除いて戻します。UNION ALLは、重複した行も含めて、どちらかの問合せによって選択されたすべての行を戻します。

行演算子

行演算子は特定の行を戻すか、または参照します。ALLによって、問合せの結果や集合式に含まれる重複した行をそのまま残します。DISTINCTは、問合せの結果や集合式に含まれる重複した行を削除します。PRIORは、ツリー構造の問合せによって戻された現在行の親の行を参照します。

PL/SQLでのカーソルの管理

PL/SQLでは、暗黙カーソルおよび明示カーソルを使用します。PL/SQLでは、1行のみを戻す問合せを含むすべてのSQLデータ操作文に関して暗黙的にカーソルが宣言されます。問合せの処理を完全に制御する必要がある場合、PL/SQLブロック、サブプログラムまたはパッケージの宣言部の中でカーソルを明示的に宣言できます。複数の行を戻す問合せの場合は、明示カーソルを宣言する必要があります。

暗黙カーソル

暗黙カーソルはPL/SQLで自動的に管理されるため、これらのカーソルを処理するコードを記述する必要はありません。ただし、カーソル属性を使用して暗黙カーソルの実行情報を追跡できます。

暗黙カーソルの属性

暗黙カーソルの属性は、INSERT文、UPDATE文、DELETE文、SELECT INTO文、COMMITまたはROLLBACK文など、DMLおよびDDL文の実行に関する情報を戻します。カーソル属性は%FOUND%ISOPEN%NOTFOUNDおよび%ROWCOUNTです。カーソル属性の値は、常に直前に実行されたSQL文を参照しています。OracleがSQLカーソルをオープンするまでは、暗黙カーソルの属性の結果はNULLになります。

SQLカーソルには、FORALL文で使用するために設計された%BULK_ROWCOUNTという別の属性があります。詳細は、「%BULK_ROWCOUNT属性を持つFORALLによる影響を受ける行カウント」を参照してください。

%FOUNDカーソル属性: DML文で行が変更されたかどうか

SQLのDML文が実行されるまでは、%FOUNDの結果はNULLになります。その後、INSERT文、UPDATE文またはDELETE文が1行または複数の行に作用するか、またはSELECT INTO文が1行または複数の行を戻すと、%FOUNDの結果はTRUEになります。それ以外の場合、%FOUNDの結果はFALSEになります。例6-7では、%FOUNDを使用して、削除に成功した場合に行を挿入するようにしています。

例6-7    SQL%FOUNDの使用

CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
  DELETE FROM dept_temp WHERE department_id = dept_no;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
  END IF;
END;
/

%ISOPENカーソル属性: 暗黙カーソルの場合は常にFALSE

Oracleは、SQLカーソルに対応付けられたSQL文の実行を終了すると、このSQLカーソルを自動的にクローズします。その結果、%ISOPENの結果は常にFALSEになります。

%NOTFOUND属性: DML文で行の変更が失敗したかどうか

%NOTFOUNDは、論理的に%FOUNDの逆です。INSERT文、UPDATE文またはDELETE文がどの行にも作用しないか、またはSELECT INTO文がどの行も戻さない場合、%NOTFOUNDの結果はTRUEになります。それ以外の場合、%NOTFOUNDの結果はFALSEになります。

%ROWCOUNT属性: これまでに影響を受けた行数

%ROWCOUNTの結果は、INSERT文、UPDATE文またはDELETE文の影響を受けた行、またはSELECT INTO文に戻された行の数になります。INSERT文、UPDATE文またはDELETE文がどの行にも作用しないか、またはSELECT INTO文がどの行も戻さないと、%ROWCOUNTの結果は0になります。例6-8では、%ROWCOUNTは削除された行数を戻します。

例6-8    SQL%ROWCOUNTの使用

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;
/

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

SQL%ROWCOUNT属性の値は、PL/SQLから直前に実行されたSQL文を参照しています。属性の値を保存して後で使用する場合は、値をローカル変数に直接代入してください。

SQL%ROWCOUNT属性はトランザクションの状態には無関係です。セーブポイントへのロールバックが実行されても、SQL%ROWCOUNTの値が、セーブポイントに達する前の古い値に戻ることはありません。また、自律型トランザクションが終了しても、SQL%ROWCOUNTが親トランザクション内の元の値に戻ることはありません。

暗黙カーソルの属性を使用する場合のガイドライン

次に、暗黙カーソルの属性を使用する場合の考慮事項を示します。

明示カーソル

問合せの処理を完全に制御する必要がある場合、PL/SQLブロック、サブプログラムまたはパッケージの宣言部の中でカーソルを明示的に宣言できます。

カーソルの制御には、OPENFETCHおよびCLOSEの3つのコマンドを使用します。まず、結果セットを識別するOPEN文でカーソルを初期化します。次に、FETCHを繰り返し実行して、すべての行を取り出します。またはBULK COLLECT句を使用して、すべての行を一度にフェッチします。最後の行の処理が終わってから、CLOSE文でカーソルを解放します。

この方法では、暗黙カーソルのFORループなどの他の方法より多くのコードが必要になります。この方法のメリットは柔軟性があるということです。この方法では次の操作を実行できます。

カーソルの宣言

他の文でカーソルを参照するときは、事前に宣言する必要があります。カーソルに名前を付け、特定の問合せと関連付けます。オプションで、カーソルの戻り型(table_name%ROWTYPEなど)を宣言することもできます。また、オプションで、ローカル変数を参照するかわりにWHERE句で使用するパラメータを指定することもできます。これらのパラメータには、デフォルト値を設定できます。例6-9に、カーソルの宣言方法を示します。

例6-9    カーソルの宣言

DECLARE
  my_emp_id     NUMBER(6);      -- variable for employee_id
  my_job_id     VARCHAR2(10);   -- variable for job_id
  my_sal        NUMBER(8,2);    -- variable for salary
  CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees
      WHERE salary > 2000; 
  my_dept   departments%ROWTYPE;  -- variable for departments row
  CURSOR c2 RETURN departments%ROWTYPE IS 
      SELECT * FROM departments WHERE department_id = 110;

カーソルはPL/SQL変数ではありません。カーソルに値を代入したり、カーソルを式の中で使用することはできません。カーソルと変数は、同じ有効範囲規則に従います。データベース表に基づいてカーソルに名前を付けることができますが、お薦めしません。

カーソルはパラメータを取ることができます。カーソルのパラメータは、カーソルに結び付けられた問合せの中で、定数が使用可能な位置であればどこででも使用できます。カーソルの仮パラメータはINパラメータにする必要があります。このパラメータは問合せに値を提供しますが、問合せから値を戻しません。カーソル・パラメータにNOT NULL制約を課すことはできません。

次の例に示すように、カーソルのパラメータをデフォルト値に初期化できます。初期化すると、必要に応じてデフォルト値を受け入れたり上書きすることで、カーソルの実パラメータに様々な数値を渡すことができます。また、カーソルへの既存の参照を変更しなくても、新しい仮パラメータを追加できます。

DECLARE
   CURSOR c1 (low  NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS
              SELECT * FROM departments WHERE department_id > low
              AND department_id < high;

カーソルのパラメータは、カーソル宣言で指定されている問合せの範囲からしか参照できません。パラメータ値は、カーソルがオープンされているときに、カーソルに関連付けられた問合せから使用できます。

カーソルのオープン

カーソルをオープンすると、問合せが実行され、結果セットが識別されます(結果セットは、問合せの検索条件に一致するすべての行で構成されています)。FOR UPDATE句を使用して宣言されたカーソルの場合、OPEN文はこれらの行のロックもします。OPEN文の例を次に示します。


DECLARE
   CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM employees
      WHERE salary > 2000; 
BEGIN
  OPEN C1;

結果セット内の行は、OPEN文の実行時ではなく、FETCHによって取り出されます。

カーソルを使用したフェッチ

BULK COLLECT句(「カーソルを使用したフェッチ」で説明)を使用していない場合は、FETCH文によって結果セットの行が一度に1行ずつ取り出されます。各FETCH文は現在の行を取り出してから、カーソルを結果セットの次の行に進めます。各列を個別の変数に格納したり、行全体を適切なフィールドを持つレコード(通常は%ROWTYPEを使用して宣言する)に格納することができます。

カーソルと関連付けられた問合せが戻す列の値に対しては、INTOリストの中に、対応する、型互換の変数が存在している必要があります。例6-10に示すとおり、通常、FETCH文はLOOP文およびEXIT WHEN .. NOTFOUND文とともに使用します。問合せ内の組込み正規表現ファンクションの使用に注意してください。

例6-10    カーソルを使用したフェッチ

DECLARE
  v_jobid     employees.job_id%TYPE;     -- variable for job_id
  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  CURSOR c1 IS SELECT last_name, job_id FROM employees 
                 WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK');
  v_employees employees%ROWTYPE;         -- record variable for row
  CURSOR c2 is SELECT * FROM employees 
                 WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]');
BEGIN
  OPEN c1; -- open the cursor before fetching
  LOOP
    FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
    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
    FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
                               v_employees.job_id );
  END LOOP;
  CLOSE c2;
END;
/

問合せは、有効範囲内にあるPL/SQL変数を参照できます。問合せの中の変数は、カーソルがオープンされたときにのみ評価されます。例6-11では、factorは、FETCH文が実行されるたびに増加しますが、取り出された給与はそれぞれ2倍されます。

例6-11    有効範囲内のPL/SQL変数の参照

DECLARE
  my_sal employees.salary%TYPE;
  my_job employees.job_id%TYPE;
  factor INTEGER := 2;
  CURSOR c1 IS
    SELECT factor*salary FROM employees WHERE job_id = my_job;
BEGIN
   OPEN c1;  -- factor initially equals 2
   LOOP
      FETCH c1 INTO my_sal;
      EXIT WHEN c1%NOTFOUND;
      factor := factor + 1;  -- does not affect FETCH
   END LOOP;
   CLOSe c1;
END;
/

結果セットや問合せの中の変数の値を変更する場合は、カーソルをクローズし、入力変数を新しい値に設定して、再オープンする必要があります。ただし、同じカーソルを使用して、別々のFETCH文で、異なるINTOリストを使用できます。例6-12に示すとおり、個々のFETCH文で別の行を取り出し、ターゲット変数に値を代入します

例6-12    同じカーソルの異なる変数へのフェッチ

DECLARE
   CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
   name1 employees.last_name%TYPE;
   name2 employees.last_name%TYPE;
   name3 employees.last_name%TYPE;
BEGIN
   OPEN c1;
   FETCH c1 INTO name1;  -- this fetches first row
   FETCH c1 INTO name2;  -- this fetches second row
   FETCH c1 INTO name3;  -- this fetches third row
   CLOSE c1;
END;
/

FETCH文を実行した時点で結果セットに行が残っていなかった場合、ターゲット変数の値は未定義になります。結果として、FETCH文は行を戻すことに失敗します。この状況が発生しても、例外は呼び出されません。失敗を検出するには、カーソル属性%FOUNDまたは%NOTFOUNDを使用します。詳細は、「カーソル式の使用」を参照してください。

カーソルを使用したバルク・データのフェッチ

BULK COLLECT句を使用すると、結果セットのすべての行を一度にフェッチできます。「BULK COLLECT句を使用した、問合せ結果のコレクションへの取出し」を参照してください。例6-13では、1つのカーソルから2つのコレクションにバルク・フェッチを実行します。

例6-13    カーソルを使用したバルク・データのフェッチ

DECLARE
  TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
  ids  IdsTab;
  names NameTab;
  CURSOR c1 IS
    SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK';
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO ids, names;
  CLOsE c1;
-- Here is where you process the elements in the collections
  FOR i IN ids.FIRST .. ids.LAST
    LOOP
      IF ids(i) > 140 THEN
          DBMS_OUTPUT.PUT_LINE( ids(i) );
       END IF;
    END LOOP;
  FOR i IN names.FIRST .. names.LAST
    LOOP
      IF names(i) LIKE '%Ma%' THEN
          DBMS_OUTPUT.PUT_LINE( names(i) );
       END IF;
    END LOOP;
END;
/

カーソルのクローズ

CLOSE文によってカーソルは使用禁止になり、結果セットは未定義になります。クローズされたカーソルは、再オープンできます。これによって、WHERE句で参照されたカーソル・パラメータおよび変数の最新の値を使用して、問合せが再度実行されます。クローズされたカーソルに対してこれ以外の操作を実行すると、事前定義の例外INVALID_CURSORが呼び出されます。

明示カーソルの属性

どの明示カーソルおよびカーソル変数にも%FOUND%ISOPEN%NOTFOUNDおよび%ROWCOUNTの4つの属性があります。これらの属性をカーソルまたはカーソル変数名に付加すると、SQL文の実行について役立つ情報が戻されます。カーソル属性は、プロシージャ文では使用できますが、SQL文では使用できません。

明示カーソルの属性は、複数行の問合せの実行に関する情報を戻します。明示カーソルまたはカーソル変数をオープンすると、対応する問合せを満たす行が識別され、結果セットが形成されます。行は、結果セットから取り出されます。

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

カーソルまたはカーソル変数のオープン後、最初のフェッチが実行されるまでは、%FOUNDNULLを戻します。フェッチが実行された後、直前のフェッチが行を戻した場合はTRUEを、直前のフェッチが行を戻さなかった場合はFALSEを戻します。例6-14では、%FOUNDを使用してアクションを選択します。

例6-14    %FOUNDの使用

DECLARE
   CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
   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, so exit loop
      EXIT;
    END IF;
  END LOOP;
END;
/

カーソルまたはカーソル変数をオープンしていない場合、%FOUNDでカーソルまたはカーソル変数を参照すると、事前定義の例外INVALID_CURSORが呼び出されます。

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

%ISOPENは、カーソルまたはカーソル変数をオープンしている場合はTRUEを、他の場合はFALSEを戻します。例6-15では、%ISOPENを使用してアクションを選択します。

例6-15    %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 c1%ISOPEN = FALSE THEN  -- cursor was not already open
      OPEN c1;
   END IF;
   FETCH c1 INTO the_name, the_salary;
   CLOSE c1;
END;
/
%NOTFOUNDカーソル属性: フェッチが失敗したかどうか

%NOTFOUNDは、論理的に%FOUNDの逆です。%NOTFOUNDは、直前の取出しが行を戻した場合はFALSEに、直前の取出しが行を戻さなかった場合はTRUEになります。例6-16では、%NOTFOUNDを使用して、FETCHが行を戻さなくなった場合に、ループが終了するようにしています。

例6-16    %NOTFOUNDの使用

DECLARE
   CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
   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, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
       EXIT;
    ELSE   -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
    END IF;
   END LOOP;
END;
/

最初のフェッチの前は、%NOTFOUNDNULLを戻します。FETCHが正常に実行されない場合、EXIT WHEN文はWHEN条件がTRUEの場合にのみ実行されるため、ループは終了しません。安全のために、次のEXIT文をかわりに使用できます。

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

カーソルまたはカーソル変数をオープンしていない場合、%NOTFOUNDでカーソルまたはカーソル変数を参照すると、例外INVALID_CURSORが呼び出されます。

%ROWCOUNTカーソル属性: これまでにフェッチされた行数

カーソルまたはカーソル変数をオープンしている場合、%ROWCOUNTは0(ゼロ)になります。最初のフェッチの前は、%ROWCOUNTの評価結果は0です。その後は、これまでにフェッチした行の数になります。この値は、直前のフェッチが行を戻した場合に増分されます。例6-17では、%ROWCOUNTを使用して、フェッチされた行が10行を超えたかどうかをテストしています。

例6-17    %ROWCOUNTの使用

DECLARE
   CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
   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 record ---');
      END IF;
   END LOOP;
   CLOSE c1;
END;
/

カーソルまたはカーソル変数をオープンしていない場合、%ROWCOUNTでカーソルまたはカーソル変数を参照すると、INVALID_CURSORが呼び出されます。

表6-1に、OPEN文、FETCH文またはCLOSE文を実行する前後での、各カーソル属性が戻す値を示します。

表6-1    カーソル属性値 
    %FOUND  %ISOPEN  %NOTFOUND  %ROWCOUNT 

OPEN 

前 

例外 

FALSE 

例外 

例外 

 

後 

NULL 

TRUE 

NULL 

最初のFETCH 

前 

NULL 

TRUE 

NULL 

 

後 

TRUE 

TRUE 

FALSE 

以降のFETCH 

前 

TRUE 

TRUE 

FALSE 

 

後 

TRUE 

TRUE 

FALSE 

データに依存 

最後のFETCH 

前 

TRUE 

TRUE 

FALSE 

データに依存 

 

後 

FALSE 

TRUE 

TRUE 

データに依存 

CLOSE 

前 

FALSE 

TRUE 

TRUE 

データに依存 

 

後 

例外 

FALSE 

例外 

例外 

次に示す内容は、表6-1の情報に適用されます。

PL/SQLを使用したデータの問合せ

PL/SQLを使用すると、問合せ(SQLのSELECT文)を実行し、結果セットの個別のフィールドまたはすべての行にアクセスできます。従来のデータベース・プログラミングでは、カーソルと呼ばれる内部データ構造を使用して問合せの結果を処理していました。PL/SQLでは、ほとんどの場合にカーソルを管理できるため、問合せの結果を処理するコードが単純で小型になります。この項では、PL/SQLによってすべてが管理される単純な問合せと、自分でカーソルを使用する複雑な問合せを処理する方法について説明します。

1行のみの選択: SELECT INTO文

問合せが1行のみを戻すと予測している場合、通常のSQLのSELECT文に、結果を保持するPL/SQL変数を指定するINTO句を追加して記述できます。

問合せが複数の行を戻す可能性があり、そのうち最初の値以外は必要ない場合、ROWNUMの値と比較することで、結果セットを1行に制限できます。問合せが行を戻さない可能性がある場合、例外ハンドラを使用して、データが見つからない場合のアクションを指定します。

使用するデータに条件が存在するかどうかのチェックのみを行う場合、COUNT(*)演算子を使用して問合せを記述できます。この演算子は常に数値を戻すため、NO_DATA_FOUND例外は発生しません。

複数行の選択: BULK COLLECT句

1回に1行ずつ結果セットをループするのではなく、ローカルのPL/SQL変数に大量のデータを代入する必要がある場合、BULK COLLECT句を使用できます。特定の列のみを問い合せる場合、各列に対するすべての結果を個別のコレクション変数に格納できます。表のすべての列を問い合せる場合は、レコードのコレクションに結果セット全体を格納できます。これによって、結果のループ処理および別の列への参照が簡単になります。例6-13「カーソルを使用したバルク・データのフェッチ」を参照してください。

この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合は、より多くの処理をSQLで実行すると、コードのパフォーマンスを向上できます。

複数行のループ: カーソルFORループ

最も一般的な問合せは、SELECT文を発行し、結果セットの行に対してただちにループを1回実行することす。PL/SQLでは、単純なFORループを使用してこのような種類の問合せを実行できます。

FORループのイテレータ変数は、事前に宣言する必要はありません。イテレータ変数は、フィールド名が問合せの列名に一致する%ROWTYPEレコードです。この変数は、ループ中にのみ存在します。明示的な列名ではなく式を使用する場合、列の別名を使用すると、ループ内の対応する値を参照できます。

複雑な問合せの処理の実行: 明示カーソル

問合せの処理を完全に制御するには、OPEN文、FETCH文およびCLOSE文と明示カーソルを組み合せて使用します。

ある場所で問合せを指定し、他のサブプログラム内などの別の場所に行を取り出す必要がある場合があります。また、状況に応じて、ORDER BY句やGROUP BY句など、別の問合せパラメータを選択する必要がある場合もあります。さらに、一部の行に対して他の行とは異なる処理を行うために、より複雑なループが必要な場合もあります。

明示カーソルは非常に柔軟性があるため、必要に応じて様々な表記法から選択できます。次の項では、明示カーソルのすべての問合せ処理機能について説明します。

PL/SQLを使用したデータの問合せ: 暗黙カーソルのFORループ

PL/SQLを使用すると、問合せの発行、結果の各行の%ROWTYPEレコードへの取出し、およびループ内の各行の処理を簡単に実行できます。

SQL*Plusでの実行例を次に示します。この例では、120よりも大きなマネージャIDを持つ従業員の名前およびジョブIDを取得する問合せを実行します。

BEGIN
  FOR item IN 
  ( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%'
       AND manager_id > 120 )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||
                          item.job_id);
  END LOOP;
END;
/

FORループを反復する前に、PL/SQLは暗黙的に宣言したレコードに取り出した値を格納します。ループの中の一連の文は、問合せを満たす1つの行について1回実行されます。ループを終了させると、カーソルは自動的にクローズされます。EXIT文またはGOTO文を使用して、すべての行がフェッチされる前にループを終了させた場合やループの内側から例外が呼び出された場合も、カーソルはクローズされます。「LOOP文」を参照してください。

PL/SQLを使用したデータの問合せ: 明示カーソルのFORループ

同じプロシージャの別の部分から同じ問合せを参照する必要がある場合、その問合せを指定するカーソルを宣言し、FORループを使用して結果を処理できます。

DECLARE 
 CURSOR c1 IS SELECT last_name, job_id FROM employees 
                WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||
                          item.job_id);
  END LOOP;
END;
/

「LOOP文」を参照してください。

カーソルFORループ内の式の別名の定義

カーソルFORループでは、PL/SQLによって、結果セットの列に対応するフィールドを持つ%ROWTYPEレコードが作成されます。フィールドは、SELECTリストの中の対応する列と同じ名前を持ちます。

選択リストには、列と定数、連結された2つの列などの式が含まれる場合があります。この場合、列の別名を使用して、適切な列に一意の名前を付けます。

例6-18では、full_nameおよびdream_salaryが問合せの中で式の別名として使用されています。

例6-18    問合せ内での式の別名の使用

BEGIN
  FOR item IN
  ( SELECT first_name || ' ' || last_name AS full_name,
      salary * 10 AS dream_salary FROM employees WHERE ROWNUM <= 5 )
  LOOP
    DBMS_OUTPUT.PUT_LINE(item.full_name || ' dreams of making ' ||
                         item.dream_salary);
  END LOOP;
END;
/

副問合せの使用

副問合せは、別のSQL DML文内に出現する問合せであり、多くの場合カッコで囲まれています。文は、副問合せで戻された1つの値または複数の値の集合に対して実行されます。次に例を示します。

例6-19に、カーソル宣言で使用される2つの副問合せを示します。

例6-19    カーソルでの副問合せの使用

DECLARE
  CURSOR c1 IS
-- main query returns only rows where the salary is greater than the average
    SELECT employee_id, last_name FROM employees 
      WHERE salary > (SELECT AVG(salary) FROM employees);
  CURSOR c2 IS
-- subquery returns all the rows in descending order of salary
-- main query returns just the top 10 highest-paid employees
   SELECT * FROM
     (SELECT last_name, salary FROM employees ORDER BY salary DESC, last_name)
     WHERE ROWNUM < 11;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Above-average salary: ' || person.last_name);
  END LOOP;
  FOR person IN c2
  LOOP
    DBMS_OUTPUT.PUT_LINE('Highest paid: ' || person.last_name || 
                         ' $' || person.salary);
  END LOOP;
-- subquery identifies a set of rows to use with CREATE TABLE or INSERT
END;
/

FROM句の中で副問合せを使用した例6-20の問合せでは、従業員が5人以上の部門の番号と名称を戻します。

例6-20    FROM句での副問合せの使用

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, department_name, staff
      FROM departments t1,
      ( SELECT department_id, COUNT(*) as staff
          FROM employees GROUP BY department_id) t2
    WHERE
      t1.department_id = t2.department_id
      AND staff >= 5;
BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE('Department = ' || dept.department_name ||
                          ', staff = ' || dept.staff);
   END LOOP;
END;
/

相関副問合せの使用

副問合せが各表につき1回しか評価されないのに対し、相関副問合せは各行につき1回評価されます。例6-21では、給与が部門平均を上回っている従業員の名前と給与を戻しています。相関副問合せでは、表の各行について、対応する部門の平均給与を計算します。

例6-21    相関副問合せの使用

DECLARE
-- For each department, find the average salary. Then find all the employees in
--  that department making more than that average salary.
  CURSOR c1 IS
    SELECT department_id, last_name, salary FROM employees t
    WHERE salary >
      ( SELECT AVG(salary) FROM employees WHERE t.department_id = department_id )
    ORDER BY department_id;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
  END LOOP;
END;
/

メンテナンス可能なPL/SQL問合せの作成

ローカル変数を参照するかわりに、パラメータを受け入れるカーソルを宣言し、カーソルをオープンしたときにそれらのパラメータの値を渡すことができます。問合せが通常、特定の値を使用して発行される場合、それらの値をデフォルトとして指定できます。パラメータの値を渡すには、位置表記法および名前表記法のいずれも使用できます。

例6-22に、指定された部門内で、給与が指定額を超える従業員の給与を示します。

例6-22    カーソルFORループへのパラメータの引渡し

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees WHERE job_id = job AND salary > max_wage;
BEGIN
  FOR person IN c1('CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
                         person.salary || ', Job Id = ' || person.job_id );
  END LOOP;
END;
/

例6-23に、カーソルをオープンする方法の例を示します。

例6-23    明示カーソルへのパラメータの引渡し

DECLARE
  emp_job      employees.job_id%TYPE := 'ST_CLERK';
  emp_salary   employees.salary%TYPE := 3000;
  my_record employees%ROWTYPE;
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees WHERE job_id = job and salary > max_wage;
BEGIN
-- Any of the following statements opens the cursor:
-- OPEN c1('ST_CLERK', 3000); OPEN c1('ST_CLERK', emp_salary);
-- OPEN c1(emp_job, 3000); OPEN c1(emp_job, emp_salary);
  OPEN c1(emp_job, emp_salary);
  LOOP
     FETCH c1 INTO my_record;
     EXIT WHEN c1%NOTFOUND;
     -- process data record
     DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.last_name || ', salary = ' ||
                      my_record.salary || ', Job Id = ' || my_record.job_id );
  END LOOP;
END;
/

混同を避けるために、カーソルのパラメータとそれらのパラメータに渡すPL/SQL変数には異なる名前を使用します。

デフォルト値で宣言された仮パラメータには、対応する実パラメータがなくてもかまいません。実パラメータを省略した場合、仮パラメータは、OPEN文の実行時にデフォルト値を取ります。

カーソル変数(REF CURSOR)の使用

カーソルと同じように、カーソル変数は複数行の問合せの結果セットの中の現在行を指します。カーソル変数は、特定の問合せと結合されないため、より柔軟性があります。カーソル変数は、正しい列セットを戻す任意の問合せに対してオープンできます。

カーソル変数は、パラメータとしてローカル・サブプログラムおよびストアド・サブプログラムに渡します。1つのサブプログラム内でカーソル変数をオープンし、別のサブプログラム内でこの変数を処理することで、データ検索を集中的に実行できます。この方法は、PL/SQLサブプログラムが別の言語(JavaやVisual Basicなど)で記述されたサブプログラムへ結果セットを戻す可能性がある、複数言語のアプリケーションでも役立ちます。

カーソル変数は、すべてのPL/SQLクライアントで使用します。たとえば、OCIやPro*CプログラムなどのPL/SQLホスト環境の中でカーソル変数を宣言し、それを入力ホスト変数(バインド変数)としてPL/SQLに渡すことができます。PL/SQLエンジンを備えたOracle Formsなどのアプリケーション開発ツールでは、クライアント側でカーソル変数を完全に使用できます。また、クライアントとデータベース・サーバーの間で、リモート・プロシージャ・コールを介してカーソル変数をやり取りできます。

カーソル変数(REF CURSOR)

カーソル変数は、結果セットへのポインタに類似しています。1つのサブプログラム内で問合せを実行する際にカーソル変数を使用して、問合せ結果を別のサブプログラム(別の言語で記述されている場合もある)で処理します。カーソル変数のデータ型はREF CURSORであり、通称でREF CURSORと呼ばれます。

常に同じ問合せ作業領域を参照する明示カーソルとは異なり、カーソル変数は異なる作業領域を参照できます。カーソルを使用する予定の場所では、カーソル変数を使用できません。逆の場合も同じです。

変数を使用する理由

カーソル変数は、PL/SQLのストアド・サブプログラムと様々なクライアントとの間で問合せの結果セットを渡すために使用します。PL/SQLとそのクライアントは、結果セットが格納されている問合せ作業領域を指すポインタを共有します。たとえば、OCIクライアント、Oracle FormsアプリケーションおよびOracleデータベース・サーバーがすべて同じ作業領域を参照する可能性があります。

カーソル変数の値は、1つの有効範囲から別の有効範囲に渡すことができるため、問合せ作業領域は、それを指すカーソル変数が存在するかぎりアクセス可能になります。たとえば、Pro*Cプログラムに組み込まれたPL/SQLブロックにホスト・カーソル変数を渡す場合、カーソル変数が指す作業領域は、そのブロックの終了後もアクセス可能な状態のままです。

クライアント側にPL/SQLエンジンがあれば、クライアントからサーバーへのコールに課される制限はありません。たとえば、クライアント側でカーソル変数を宣言し、それをサーバー側でオープンしてフェッチした後で、クライアント側で引き続きフェッチすることができます。また、PL/SQLブロックを使用して複数のホスト・カーソル変数を1回の往復でオープンまたはクローズすることで、ネットワークの通信量を削減できます。

REF CURSOR型およびカーソル変数の宣言

カーソル変数を作成するには、REF CURSOR型を定義してから、その型のカーソル変数を宣言します。REF CURSOR型は、任意のPL/SQLブロック、サブプログラムまたはパッケージの中で定義できます。次の例では、DEPARTMENTS表から取り出した結果セットを表すREF CURSOR型を宣言しています。

DECLARE
  TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

REF CURSOR型には、強い(戻り型を持つ)ものと弱い(戻り型を持たない)ものがあります。強いREF CURSOR型の方が、エラー発生の可能性は少なくなります。これは、PL/SQLコンパイラの場合、強い型指定のカーソル変数は正しい列セットを戻す問合せにのみ関連付けることができるためです。弱いREF CURSOR型は、より柔軟です。弱い型指定のカーソル変数は、どの問合せにも関連付けることができます。弱いREF CURSORに対しては、型のチェックが行われないため、すべての弱い型は互換性があります。新しい型を作成するかわりに、事前定義のSYS_REFCURSOR型を使用することもできます。

REF CURSOR型を宣言すると、PL/SQLブロックまたはサブプログラムで、その型のカーソル変数を宣言できます。

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
   TYPE genericcurtyp IS REF CURSOR;  -- weak
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- didn't need to declare a new type
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp;  -- declare cursor variable

REF CURSOR型を使用する各サブプログラムでその同じ型を宣言することを回避するために、パッケージ仕様部でREF CURSORを宣言できます。その型のカーソル変数は、対応するパッケージ本文、または独自のファンクションやプロシージャ内で宣言できます。

例6-24に示すとおり、REF CURSOR型定義のRETURN句では、%ROWTYPEを使用して、強い型指定のカーソル変数を参照できます。

例6-24    %ROWTYPE変数を戻すカーソル変数

DECLARE
   TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
   tmp_cv TmpCurTyp;  -- declare cursor variable
   TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
   emp_cv EmpCurTyp;  -- declare cursor variable

例6-25に示すとおり、%ROWTYPEを使用して、レコード変数のデータ型を与えることもできます。

例6-25    %ROWTYPE属性を使用したデータ型の指定

DECLARE
   dept_rec departments%ROWTYPE;  -- declare record variable
   TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
   dept_cv DeptCurTyp;  -- declare cursor variable

例6-26では、RETURN句の中でユーザー定義のRECORD型を指定しています。

例6-26    レコード型を戻すカーソル変数

DECLARE
   TYPE EmpRecTyp IS RECORD (
      employee_id NUMBER,
      last_name VARCHAR2(25),
      salary   NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp;  -- declare cursor variable

パラメータとしてのカーソル変数の引渡し

カーソル変数はファンクションまたはプロシージャの仮パラメータとして宣言できます。例6-27では、REF CURSOR型を定義し、その型のカーソル変数を仮パラメータとして宣言しています。

例6-27    REF CURSORのパラメータとしての引渡し

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp empcurtyp;
-- after result set is built, process all the rows inside a single procedure
--  rather than calling a procedure for each row
   PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
      person employees%ROWTYPE;
   BEGIN
      DBMS_OUTPUT.PUT_LINE('-----');
      DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
      LOOP
         FETCH emp_cv INTO person;
         EXIT WHEN emp_cv%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||
                              ' ' || person.last_name);
      END LOOP;
   END;
BEGIN
-- First find 10 arbitrary employees.
  OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
  process_emp_cv(emp);
  CLOSE emp;
-- find employees matching a condition.
  OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
  process_emp_cv(emp);
  CLOSE emp;
END;
/

すべてのポインタと同様に、カーソル変数によってパラメータのエイリアシング機能を拡張できます。「サブプログラム名のオーバーロード」を参照してください。

カーソル変数の制御: OPEN-FOR、FETCHおよびCLOSE

カーソル変数の制御には、OPEN-FORFETCHおよびCLOSEの3つの文を使用します。まず、OPEN-FOR文でカーソル変数を複数行問合せ用にオープンします。次に、FETCH文で結果セットから行を取り出します。すべての行が処理された後に、CLOSE文でカーソル変数をクローズします。

カーソル変数のオープン

OPEN-FOR文を使用すると、カーソル変数を複数行の問合せに結び付けたり、問合せを実行したり、結果セットを識別することができます。カーソル変数は、PL/SQL内、またはOCIプログラムなどのPL/SQLホスト環境で直接宣言できます。OPEN-FOR文の構文は、「OPEN-FOR文」を参照してください。

問合せのSELECT文は、文中に直接コーディングすることも、文字列変数または文字列リテラルにすることもできます。文字列を問合せとして使用する場合、文字列にバインド変数用のプレースホルダを含め、USING句を使用して対応する値を指定できます。

この項では静的SQLの場合について説明します。ここではselect_statementを使用します。動的SQLの場合はdynamic_stringが使用されます。「OPEN-FOR文」を参照してください。

カーソルとは異なり、カーソル変数はパラメータを取りません。かわりに、カーソル変数にはパラメータのみでなく問合せ全体を渡すことができます。問合せでは、ホスト変数、PL/SQL変数、パラメータおよびファンクションを参照できます。

例6-28では、カーソル変数をオープンします。カーソルの属性(%FOUND%NOTFOUND%ISOPEN%ROWCOUNT)をカーソル変数に適用できることに注意してください。

例6-28    カーソル変数がオープンしているかどうかのチェック

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp_cv empcurtyp;
BEGIN
   IF NOT emp_cv%ISOPEN THEN  -- open cursor variable
      OPEN emp_cv FOR SELECT * FROM employees;
   END IF;
   CLOSE emp_cv;
END;
/

その他のOPEN-FOR文は、異なる複数の問合せ用に同じカーソル変数をオープンできます。カーソル変数を再オープンする場合、その前にクローズする必要はありません。静的カーソルをOPEN文で連続してオープンすると、事前定義の例外CURSOR_ALREADY_OPENが呼び出されます。別の問合せ用にカーソル変数を再オープンすると、前の問合せは失われます。

一般に、カーソル変数をオープンするときは、カーソル変数であるIN OUTパラメータを宣言するストアド・プロシージャにそのカーソル変数を渡します。例6-29では、カーソル変数をオープンしています。

例6-29    REF CURSORをオープンするストアド・プロシージャ

CREATE PACKAGE emp_data AS
  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
  PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
  PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
  BEGIN
    OPEN emp_cv FOR SELECT * FROM employees;
  END open_emp_cv;
END emp_data;
/

スタンドアロン・ストアド・プロシージャを使用してカーソル変数をオープンする方法もあります。パッケージの中でREF CURSOR型を定義して、ストアド・プロシージャのパラメータ宣言でその型を参照します。

データ検索を集中的に実行するために、ストアド・プロシージャの中で型互換性のある問合せをグループにまとめることができます。例6-30では、パッケージ・プロシージャは仮パラメータの1つとして選択子を宣言しています。コールされた場合、プロシージャは選択された問合せに対してカーソル変数emp_cvをオープンします。

例6-30    別の問合せでREF CURSORをオープンするストアド・プロシージャ

CREATE PACKAGE emp_data AS
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS
   BEGIN
      IF choice = 1 THEN
         OPEN emp_cv FOR SELECT * FROM employees WHERE commission_pct IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT * FROM employees WHERE salary > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT * FROM employees WHERE department_id = 100;
      END IF;
   END;
END emp_data;
/

例6-31に示すとおり、柔軟性を高めるために、異なる戻り型を指定した問合せをストアド・プロシージャで実行できます。

例6-31    異なる戻り型を持つカーソル変数

CREATE PACKAGE admin_data AS
   TYPE gencurtyp IS REF CURSOR;
   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
/
CREATE PACKAGE BODY admin_data AS
   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
   BEGIN
      IF choice = 1 THEN
         OPEN generic_cv FOR SELECT * FROM employees;
      ELSIF choice = 2 THEN
         OPEN generic_cv FOR SELECT * FROM departments;
      ELSIF choice = 3 THEN
         OPEN generic_cv FOR SELECT * FROM jobs;
      END IF;
   END;
END admin_data;
/

ホスト変数としてのカーソル変数の使用

OCIやPro*CプログラムなどのPL/SQLホスト環境で、カーソル変数を宣言できます。カーソル変数を使用する場合は、ホスト変数としてPL/SQLに渡す必要があります。次のPro*Cの例では、ホスト・カーソル変数と選択子をPL/SQLブロックに渡すことで、選択した問合せ用のカーソル変数をオープンしています。

EXEC SQL BEGIN DECLARE SECTION;
  ...
  /* Declare host cursor variable. */
  SQL_CURSOR generic_cv;
  int choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
  IF :choice = 1 THEN
    OPEN :generic_cv FOR SELECT * FROM employees;
  ELSIF :choice = 2 THEN
    OPEN :generic_cv FOR SELECT * FROM departments;
  ELSIF :choice = 3 THEN
    OPEN :generic_cv FOR SELECT * FROM jobs;
  END IF;
END;
END-EXEC;

ホスト・カーソル変数はすべての問合せの戻り型と互換性があります。ホスト・カーソル変数は、弱い型指定のPL/SQLカーソル変数と同じように動作します。

カーソル変数からのフェッチ

FETCH文は、複数行の問合せの結果セットから、行を取り出します。この文は、カーソル変数でも明示カーソルの場合と同様に機能します。例6-32では、カーソル変数からレコードへ一度に1行ずつ行をフェッチしています。

例6-32    カーソル変数からレコードへのフェッチ

DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
   emp_cv empcurtyp;
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cv FOR SELECT * FROM employees WHERE employee_id < 120;
   LOOP 
      FETCH emp_cv INTO emp_rec; -- fetch from cursor variable
      EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
      -- process data record
      DBMS_OUTPUT.PUT_LINE('Name = ' || emp_rec.first_name || ' ' ||
                            emp_rec.last_name);
   END LOOP;
   CLOSE emp_cv;
END;
/

例6-33に示すとおり、BULK COLLECT句を使用して、1つのカーソル変数から1つ以上のコレクションに行のバルク・フェッチを行います。

例6-33    カーソル変数からコレクションへのフェッチ

DECLARE
   TYPE empcurtyp IS REF CURSOR;
   TYPE namelist IS TABLE OF employees.last_name%TYPE;
   TYPE sallist IS TABLE OF employees.salary%TYPE;
   emp_cv empcurtyp;
   names  namelist;
   sals   sallist;
BEGIN
   OPEN emp_cv FOR SELECT last_name, salary FROM employees 
        WHERE job_id = 'SA_REP';
   FETCH emp_cv BULK COLLECT INTO names, sals;
   CLOSE emp_cv;
-- loop through the names and sals collections
   FOR i IN names.FIRST .. names.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
   END LOOP;
END;
/

カーソル変数がオープンしている場合のみ、対応付けられた問合せの中の変数が評価されます。結果セットや問合せの中の変数の値を変更する場合は、カーソル変数を新しい値に設定して再オープンします。同じカーソル変数を使用して、別々のFETCH文で、異なるINTO句を使用できます。各FETCH文で同じ結果セットから別の行をフェッチします。

PL/SQLでは、カーソル変数の戻り型が、必ずFETCH文のINTO句と互換性を持ちます。互換性がない場合、カーソル変数が強い型指定の場合はコンパイル時に、弱い型指定の場合は実行時にエラーが発生します。実行時に、PL/SQLは最初の取出しの前に、事前定義の例外ROWTYPE_MISMATCHを呼び出します。エラーをトラップし、異なる(互換性のある)INTO句を使用してFETCH文を実行すると、行は失われません。

カーソル変数を、そのカーソル変数からフェッチするサブプログラムの仮パラメータとして宣言する場合は、INまたはIN OUTモードを指定する必要があります。サブプログラムがカーソル変数もオープンする場合は、IN OUTモードを指定する必要があります。

クローズしている、または一度もオープンされていないカーソル変数からフェッチを実行すると、PL/SQLによって事前定義の例外INVALID_CURSORが呼び出されます。

カーソル変数のクローズ

CLOSE文によってカーソル変数は使用禁止になり、関連する結果セットは未定義になります。最後の行が処理された後でカーソル変数をクローズします。

カーソル変数を、そのカーソル変数をクローズするサブプログラムの仮パラメータとして宣言する場合は、INまたはIN OUTモードを指定する必要があります。すでにクローズされているか、1度もオープンされたことのないカーソル変数をクローズすると、PL/SQLによって事前定義の例外INVALID_CURSORが呼び出されます。

ホスト・カーソル変数をPL/SQLに渡すときのネットワークの通信量の削減

ホスト・カーソル変数をPL/SQLに渡す場合、OPEN-FOR文をグループ化することでネットワークの通信量を削減できます。たとえば、次のPL/SQLブロックでは、1回の往復で複数のカーソル変数をオープンします。

/* anonymous PL/SQL block in host environment */
BEGIN
  OPEN :emp_cv FOR SELECT * FROM employees;
  OPEN :dept_cv FOR SELECT * FROM departments;
  OPEN :loc_cv FOR SELECT * FROM locations;
END;
/

この方法はOracle Formsで便利です(たとえば、マルチブロック・フォームにデータを入れる場合)。ホスト・カーソル変数をPL/SQLブロックに渡してオープンする場合、ホスト・カーソル変数が指す問合せ作業領域は、ブロックの終了後もアクセス可能な状態のままです。そのため、OCIやPro*Cプログラムで、通常のカーソル操作用にその作業領域を使用できます。例では、1回の往復でこのような作業領域をいくつかオープンします。

BEGIN
  OPEN :c1 FOR SELECT 1 FROM dual;
  OPEN :c2 FOR SELECT 1 FROM dual;
  OPEN :c3 FOR SELECT 1 FROM dual;
END;
/

c1c2およびc3に代入されたカーソルは通常どおり動作し、あらゆる用途に使用できます。終了すると、次のようにカーソルを解放します。

BEGIN
  CLOSE :c1; CLOSE :c2; CLOSE :c3;
END;
/

カーソル変数でのエラーの回避

代入に関係する両方のカーソル変数が強い型指定である場合は、両方が同じデータ型であることが必要です(同じ戻り型であるのみでは不十分です)。一方または両方のカーソル変数が弱い型指定である場合は、データ型が異なってもかまいません。

問合せ作業領域を指していないカーソル変数に対して取出しまたはクローズを実行するか、カーソルの属性を参照すると、PL/SQLによって例外INVALID_CURSORが呼び出されます。カーソル変数(またはパラメータ)が問合せ作業領域を指すようにするには、次の2通りの方法があります。

オープンされていないカーソル変数を別のカーソル変数に代入すると、1番目のカーソル変数をオープンした後も、2番目のカーソル変数は無効のままです。

カーソル変数をパラメータとして渡す場合は注意が必要です。実パラメータと仮パラメータの戻り型に互換性がないと、実行時にPL/SQLによってROWTYPE_MISMATCHが呼び出されます。

カーソル変数の制限

現在、カーソル変数には次の制限があります。

例6-34    パッケージでのカーソル変数の宣言

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
-- emp_cv EmpCurTyp; -- not allowed
  PROCEDURE open_emp_cv;
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
-- emp_cv EmpCurTyp; -- not allowed
PROCEDURE open_emp_cv IS
  emp_cv EmpCurTyp; -- this is legal
  BEGIN
    OPEN emp_cv FOR SELECT * FROM employees;
  END open_emp_cv;
END emp_data;
/


注意:

  • サーバー間RPCでREF CURSOR変数を使用すると、エラーが発生します。ただし、リモート・アドレスがプロシージャ・ゲートウェイを介してアクセスされる、Oracle以外のデータベースである場合は、サーバー間RPCでREF CURSOR変数を使用できます。

  • サーバー間RPCではLOBパラメータを使用できません。

 

カーソル式の使用

カーソル式はネストしたカーソルを戻します。結果セットの各行には、通常の値の他に、行内の他の値に関係する副問合せで生成されるカーソルも含まれることがあります。1つの問合せで、複数の表から取り出された関連値の大きな集合を戻すことができます。結果セットは、最初にその行から、次に各行でネストしたカーソルからフェッチするネステッド・ループで処理できます。

PL/SQLでは、カーソルの宣言、REF CURSORの宣言およびREF CURSOR変数の一部として、カーソル式を持つ問合せがサポートされます。また、カーソル式は動的SQL問合せにも使用できます。次に構文を示します。

CURSOR(subquery)

ネストしたカーソルは、それを含んでいる行が親カーソルからフェッチされるときに暗黙的にオープンされます。ネストしたカーソルがクローズされるのは、次の場合のみです。

カーソル式の制限

次に、カーソル式に関する制限を示します。

カーソル式の例

例6-35では、指定した所在地IDと、その所在地にある全部門をフェッチできるカーソルを検索します。各部門の名前をフェッチすると、別の表から関連する従業員詳細をフェッチできる、もう1つのカーソルも取得します。

例6-35    カーソル式の使用

DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
   emp_cur emp_cur_typ;
   dept_name departments.department_name%TYPE;
   emp_name employees.last_name%TYPE;
   CURSOR c1 IS SELECT
      department_name,
-- second item in the result set is another result set,
-- which is represented as a ref cursor and labelled "employees".
      CURSOR
      ( SELECT e.last_name FROM employees e
         WHERE e.department_id = d.department_id) employees
      FROM departments d WHERE department_name like 'A%';
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO dept_name, emp_cur;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
-- for each row in the result set, the result set from a subquery is processed
-- the set could be passed to a procedure for processing rather than the loop
      LOOP
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
      END LOOP;
   END LOOP;
   CLOSE c1;
END;
/

カーソル副問合せを使用したREF CURSORの構成

カーソル副問合せはカーソル式とも呼ばれ、ファンクションにパラメータとして行セットを渡すことができます。たとえば、次の文では、StockPivotファンクションにパラメータを渡しており、このファンクションはカーソル副問合せから戻される行を表すREF CURSORで構成されています。

SELECT * FROM TABLE(StockPivot(
              CURSOR(SELECT * FROM StockTable)));

カーソル副問合せは、通常はテーブル・ファンクションと併用されます。「パイプライン・ファンクションによる変換の設定」を参照してください。

PL/SQLにおけるトランザクション処理の概要

この項では、データベースの整合性を確保する、SQLのCOMMITSAVEPOINTおよびROLLBACK文を使用した、PL/SQLによるトランザクション処理について説明します。SQL文は、PL/SQLプログラム内に直接記述できます。トランザクション処理はOracleの機能で、すべてのプログラミング言語で使用できます。これらの機能によって、複数のユーザーがデータベース上で同時に作業できます。また、各ユーザーが参照するデータのバージョンに一貫性があり、すべての変更が正しい順序で適用されることが保証されます。

通常、複数のユーザーがデータに同時にアクセスすることによって発生する問題を防ぐために、追加のコードを記述する必要はありません。Oracleでは、ロックを使用してデータへの同時アクセスを制御します。Oracleは、必要最小限のデータのみを、できるかぎり短い時間ロックします。表または行のロックは、どうしてもこのレベルの制御が必要な場合に要求できます。行の共有および排他のような数種類のロッキングのモードから選択できます。

トランザクションの詳細は、『Oracle Database概要』を参照してください。SQLのCOMMITSAVEPOINTおよびROLLBACK文の詳細は、『Oracle Database SQLリファレンス』を参照してください。

PL/SQLでのCOMMITの使用

COMMIT文は、カレント・トランザクションを終了し、トランザクション中に加えられた変更を永続的なものにして、他のユーザーから参照できるようにします。トランザクションは、PL/SQLのBEGIN-ENDブロックとは結び付けられません。ブロックは複数のトランザクションを含むことができます。また、トランザクションは複数のブロックにまたがることができます。

例6-36に、銀行口座の間で振替えを実行するトランザクションを示します。一方の口座から現金が出金され、同時に他方の口座に現金が入金されることが重要です。そうでない場合、処理の途中で問題が発生すると、両方の口座からその金額の現金が消失したり、両方の口座でその金額の現金が重複する可能性があります。

例6-36    WRITE句とCOMMITの併用

CREATE TABLE accounts (account_id NUMBER(6), balance NUMBER (10,2));
INSERT INTO accounts VALUES (7715, 6350.00); 
INSERT INTO accounts VALUES (7720, 5100.50); 
DECLARE
  transfer NUMBER(8,2) := 250;
BEGIN
  UPDATE accounts SET balance = balance - transfer WHERE account_id = 7715;
  UPDATE accounts SET balance = balance + transfer WHERE account_id = 7720;
  COMMIT COMMENT 'Transfer From 7715 to 7720' WRITE IMMEDIATE NOWAIT;
END;
/

オプションのCOMMENT句を使用すると、分散トランザクションに対応付けるコメントを指定できます。コミット中にネットワークやマシンに障害が発生した場合、分散トランザクションが認識されないかまたはインダウトの状態になる可能性があります。この場合、Oracleは、COMMENTで指定されたテキストをトランザクションIDとともにデータ・ディクショナリに格納します。

非同期コミットでは、WRITE句を使用してユーザーをより詳細に制御できます。このオプションは、コミット操作によって生成されたREDO情報をREDOログに書き込む場合の優先順位を指定します。

COMMITの使用の詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』のトランザクションのコミットに関する項を参照してください。分散トランザクションの詳細は、『Oracle Database概要』を参照してください。「COMMIT文」を参照してください。

PL/SQLでのROLLBACKの使用

ROLLBACK文は、カレント・トランザクションを終了し、トランザクション中に加えられたすべての変更を取り消します。表からの間違った行の削除などの誤操作を行った場合に、ロールバックは元のデータをリストアできます。例外が呼び出されたりSQL文が失敗したためにトランザクションを終了できない場合は、ロールバックを使用すると、対処措置を行い、実行しなおすことができます。

例6-37では、3つの異なるデータベース表に従業員に関する情報を挿入しています。INSERT文で重複する従業員番号を格納すると、事前定義の例外DUP_VAL_ON_INDEXが呼び出されます。すべての3つの表に対する変更が取り消されたことを確認するために、例外ハンドラがROLLBACKを実行します。

例6-37    ROLLBACKの使用

CREATE TABLE emp_name AS SELECT employee_id, last_name FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);
CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees;
CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id);
CREATE TABLE emp_job AS SELECT employee_id, job_id FROM employees;
CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id);

DECLARE
   emp_id       NUMBER(6);
   emp_lastname VARCHAR2(25);
   emp_salary   NUMBER(8,2);
   emp_jobid    VARCHAR2(10);
BEGIN
   SELECT employee_id, last_name, salary, job_id INTO emp_id, emp_lastname,
     emp_salary, emp_jobid FROM employees WHERE employee_id = 120;
   INSERT INTO emp_name VALUES (emp_id, emp_lastname);
   INSERT INTO emp_sal VALUES (emp_id, emp_salary);
   INSERT INTO emp_job VALUES (emp_id, emp_jobid);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Inserts have been rolled back');
END;
/

「ROLLBACK文」を参照してください。

PL/SQLでのSAVEPOINTの使用

SAVEPOINTは、トランザクション処理内の現在位置に名前とマークを付けます。セーブポイントを使用すると、トランザクション全体をロールバックするのではなく、トランザクションの一部をロールバックできます。セッションごとのアクティブなセーブポイントの数には、制限がありません。

例6-38では、挿入する前にセーブポイントをマークしています。INSERT文でemployee_id列に重複した値を格納しようとすると、事前定義の例外DUP_VAL_ON_INDEXが呼び出されます。この場合は、セーブポイントまでロールバックして、その挿入のみを取り消すことができます。

例6-38    ROLLBACKとSAVEPOINTの併用

CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);

DECLARE
   emp_id        employees.employee_id%TYPE;
   emp_lastname  employees.last_name%TYPE;
   emp_salary    employees.salary%TYPE;
BEGIN
   SELECT employee_id, last_name, salary INTO emp_id, emp_lastname,
     emp_salary FROM employees WHERE employee_id = 120;
   UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id;
   DELETE FROM emp_name WHERE employee_id = 130;
   SAVEPOINT do_insert;
   INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO do_insert;
      DBMS_OUTPUT.PUT_LINE('Insert has been rolled back');
END;
/

あるセーブポイントまでロールバックすると、そのセーブポイント以降にマークされたセーブポイントはすべて消去されます。ロールバック先のセーブポイントは消去されません。単純なロールバックまたはコミットではすべてのセーブポイントが消去されます。

再帰的サブプログラムの中でセーブポイントをマークすると、再帰しながら進む過程で、各レベルでSAVEPOINT文の新しいインスタンスが実行されます。ただし、ロールバックできるのは直前にマークされたセーブポイントまでです。

セーブポイント名は未宣言の識別子です。セーブポイント名を再利用すると、セーブポイントはトランザクションの中の古い位置から現在の位置に移動します。つまり、例6-39に示すとおり、セーブポイントへのロールバックは、トランザクションの現在の部分のみに影響を与えます。

例6-39    ROLLBACKによるSAVEPOINTの再使用

CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);

DECLARE
   emp_id        employees.employee_id%TYPE;
   emp_lastname  employees.last_name%TYPE;
   emp_salary    employees.salary%TYPE;
BEGIN
   SELECT employee_id, last_name, salary INTO emp_id, emp_lastname,
     emp_salary FROM employees WHERE employee_id = 120;
   SAVEPOINT my_savepoint;
   UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id;
   DELETE FROM emp_name WHERE employee_id = 130;
   SAVEPOINT my_savepoint;  -- move my_savepoint to current poin
   INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO my_savepoint;
      DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
END;
/

「SAVEPOINT文」を参照してください。

Oracleによる暗黙的なロールバックの実行方法

INSERT文、UPDATE文またはDELETE文を実行する前に、Oracleは(ユーザーが利用できない)暗黙的なセーブポイントをマークします。文の実行に失敗すると、Oracleはこのセーブポイントまでロールバックします。通常は、トランザクション全体ではなく、失敗したSQL文のみがロールバックされます。その文が原因で未処理例外が呼び出された場合は、ホスト環境によってロールバックの対象が決まります。

Oracleでは、デッドロックを解消するためにSQL文を1文のみロールバックすることもできます。Oracleは関係しているトランザクションの1つにエラーを戻し、そのトランザクション中の現在の文をロールバックします。

SQL文を実行する前に、Oracleはその文を解析する必要があります。すなわち、その文が構文規則に従っているかどうかや、有効なスキーマ・オブジェクトを参照しているかどうかを確認する必要があります。SQL文の実行時に検出されたエラーはロールバックを引き起こしますが、文の解析の際に検出されたエラーはロールバックを引き起こしません。

ストアド・サブプログラムを未処理例外で終了すると、PL/SQLは値をOUTパラメータに代入せず、ロールバックを実行しません。

トランザクションの終了

すべてのトランザクションは、明示的にコミットまたはロールバックする必要があります。コミットを発行するか、またはPL/SQLプログラムまたはクライアント・プログラムでロールバックするかは、アプリケーション・ロジックによって決まります。トランザクションを明示的にコミットまたはロールバックしなかった場合は、クライアント環境によって最終的な状態が決定されます。

たとえば、SQL*Plus環境で、PL/SQLブロックにCOMMIT文またはROLLBACK文がない場合、トランザクションの最終状態はそのブロックの実行後に行うことによって決まります。ユーザーがデータ定義文、データ制御文またはCOMMIT文を実行するか、EXITコマンド、DISCONNECTコマンドまたはQUITコマンドを発行すると、Oracleはトランザクションをコミットします。ROLLBACK文を実行するかSQL*Plusセッションを中断すると、Oracleはトランザクションをロールバックします。

SET TRANSACTIONを使用したトランザクション・プロパティの設定

SET TRANSACTION文を使用すると、読取り専用または読取り/書込みトランザクションの開始、分離レベルの確立、指定したロールバック・セグメントへのカレント・トランザクションの割当てができます。読取り専用トランザクションは、他のユーザーが更新している表に対して複数の問合せを実行する場合に便利です。

読取り専用トランザクションでは、複数の表と複数の問合せで構成された読取り一貫性のあるビューが作成され、すべての問合せがデータベースの同一のスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。コミットまたはロールバックするとトランザクションが終了します。例6-40では、スーパーマーケットの店長が、読取り専用トランザクションを使用して、当日、先週および先月の注文総数を調べています。トランザクションの途中で他のユーザーがデータベースを更新しても、注文総数には影響がありません。

例6-40    SET TRANSACTIONを使用した読取り専用トランザクションの開始

DECLARE
   daily_order_total   NUMBER(12,2);
   weekly_order_total  NUMBER(12,2); 
   monthly_order_total NUMBER(12,2);
BEGIN
   COMMIT; -- ends previous transaction
   SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
   SELECT SUM (order_total) INTO daily_order_total FROM orders
     WHERE order_date = SYSDATE;
   SELECT SUM (order_total) INTO weekly_order_total FROM orders
     WHERE order_date = SYSDATE - 7;
   SELECT SUM (order_total) INTO monthly_order_total FROM orders
     WHERE order_date = SYSDATE - 30;
   COMMIT; -- ends read-only transaction
END;
/

SET TRANSACTION文は、読取り専用トランザクションの最初のSQL文である必要があり、1つのトランザクションで1回しか使用できません。トランザクションをREAD ONLYに設定すると、それ以降の問合せからはトランザクションの開始前にコミットされた変更内容しか見えません。READ ONLYを使用しても、他のユーザーや他のトランザクションには影響がありません。

SET TRANSACTIONの制限

読取り専用トランザクションに使用できるのは、SELECT INTOOPENFETCHCLOSELOCK TABLECOMMITおよびROLLBACK文のみです。問合せはFOR UPDATEにはできません。

デフォルトのロックの上書き

デフォルトで、Oracleはデータ構造を自動的にロックします。これはOracleデータベースの最も重要なメリットです。様々なアプリケーションでは、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータの読取りおよび書込みを実行できます。

デフォルトのロックを上書きする必要がある場合は、特定の行や表全体のデータ・ロックを要求できます。明示的なロックによって、トランザクション中の表に対するアクセスを拒否できます。

FOR UPDATEの使用

UPDATE文またはDELETE文のCURRENT OF句で参照されるカーソルを宣言する場合は、FOR UPDATE句を使用して排他的な行ロックを取得する必要があります。次に例を示します。

DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees
      WHERE job_id = 'SA_REP' AND commission_pct > .10
      FOR UPDATE NOWAIT;

SELECT ... FOR UPDATE文は、更新または削除する行を識別し、結果セット内の各行をロックします。これは、行の中の既存の値に基づいて更新する場合に便利です。この場合、更新の前に他のユーザーが行を変更しないようにする必要があります。

オプションのキーワードNOWAITを指定すると、Oracleは他のユーザーが要求された行をロックしていても待機しません。制御はただちにプログラムに戻されるため、他の処理を行ってから、改めてロックを試みてください。キーワードNOWAITを省略すると、Oracleは行が利用できるようになるまで待ちます。

カーソルをオープンしたときにすべての行がロックされるのであり、行がフェッチされるときにロックされるのではありません。また、トランザクションをコミットまたはロールバックすると、行のロックは解除されます。行がロックされていないため、コミットの後でFOR UPDATEカーソルからフェッチすることはできません。回避策の詳細は、「コミットにまたがるフェッチ」を参照してください。

複数の表に対して問合せを実行する場合は、FOR UPDATE句を使用して、ロックを特定の表に制限できます。表の行は、FOR UPDATE OF句でその表の列を参照する場合にのみロックされます。たとえば、次の問合せでは表employeesの行はロックされますが、表departmentsの行はロックされません。

DECLARE
  CURSOR c1 IS SELECT last_name, department_name FROM employees, departments
    WHERE employees.department_id = departments.department_id 
          AND job_id = 'SA_MAN'
      FOR UPDATE OF salary;

カーソルからフェッチされた最新の行を参照するには、例6-41に示すように、UPDATE文またはDELETE文でCURRENT OF句を使用します。

例6-41    CURRENT OFを使用した、カーソルからフェッチされた最新行の更新

DECLARE
   my_emp_id NUMBER(6);
   my_job_id VARCHAR2(10);
   my_sal    NUMBER(8,2);
   CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_emp_id, my_job_id, my_sal;
      IF my_job_id = 'SA_REP' THEN
        UPDATE employees SET salary = salary * 1.02 WHERE CURRENT OF c1;
      END IF;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;
END;
/

LOCK TABLEの使用

LOCK TABLE文を使用して、指定されたロック・モードでデータベース表全体をロックすると、表へのアクセスを共有または拒否できます。行共有ロックでは表に対する同時アクセスができます。つまり、他のユーザーが排他的使用のために表全体をロックしないようにします。表ロックは、トランザクションがコミットまたはロールバックを発行したときに解除されます。

LOCK TABLE employees IN ROW SHARE MODE NOWAIT;

ロック・モードによって、表に対して他にどのようなロックを使用できるかが決まります。たとえば、1つの表に対して多くのユーザーが同時に行共用ロックを取得できますが、排他ロックを取得できるのは一度に1人のユーザーのみです。あるユーザーが表に対して排他ロックをかけていると、他のユーザーはその表に対して行の挿入、更新、削除を実行できません。ロック・モードの詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。

表がロックされていても、他のユーザーは表に対して問合せできますが、問合せを実行しても表のロックを取得できません。2つの異なるトランザクションが同じ行を変更した場合のみ、一方のトランザクションがもう一方のトランザクションの終了を待ちます。

コミットにまたがるフェッチ

コミットの後でFOR UPDATEカーソルからのフェッチを試行すると、PL/SQLによって例外が呼び出されます。FOR UPDATE句によって、カーソルをオープンすると行がロックされ、コミットすると行のロックが解除されます。

DECLARE
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
   CURSOR c1 IS SELECT * FROM employees;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 105;
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

複数のコミットにまたがってフェッチする場合は、ROWID擬似列を使用してCURRENT OF句と同じ処理を実行します。各行のROWIDを選択して、UROWID変数に入れます。その後、更新や削除のときに、ROWIDを使用して現在行を識別します。

例6-42    ROWIDを使用したCOMMIT間のフェッチ

DECLARE
   CURSOR c1 IS SELECT last_name, job_id, rowid FROM employees;
   my_lastname   employees.last_name%TYPE;
   my_jobid      employees.job_id%TYPE;
   my_rowid      UROWID;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_lastname, my_jobid, my_rowid;
      EXIT WHEN c1%NOTFOUND;
      UPDATE employees SET salary = salary * 1.02 WHERE rowid = my_rowid;
      -- this mimics WHERE CURRENT OF c1
      COMMIT;
   END LOOP;
   CLOSE c1;
END;
/

フェッチされた行は、FOR UPDATE句によってロックされていないため、他のユーザーによって意識せずに変更内容が上書きされる可能性があります。読取り一貫性のために必要な追加領域は、カーソルがクローズされるまで解放されません。そのため、大規模な更新では処理速度が低下する場合があります。

ROWID疑似列を参照するカーソルで%ROWTYPE属性を使用する例を次に示します。

DECLARE
   CURSOR c1 IS SELECT employee_id, last_name, salary, rowid FROM employees;
   emp_rec c1%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO emp_rec;
      EXIT WHEN c1%NOTFOUND;
      IF emp_rec.salary = 0 THEN
         DELETE FROM employees WHERE rowid = emp_rec.rowid;
      END IF;
   END LOOP;
   CLOSE c1;
END;
/

自律型トランザクションによる独立した作業単位の実行

自律型トランザクションは、別の、メイン・トランザクションによって開始される独立したトランザクションです。自律型トランザクションは、メイン・トランザクションをコミットまたはロールバックせずに、SQL操作を実行してコミットまたはロールバックします。たとえば、監査データをログ表に書き込む場合、監査操作が後で失敗しても、監査データをコミットする必要がある場合があります。すなわち、監査データの記録で問題が発生しても、メイン操作がロールバックされないようにする必要があります。

図6-1に、メイン・トランザクション(MT)から自律型トランザクション(AT)へ制御がどのように流れ、また戻るかを示します。

図6-1    トランザクション制御の流れ


画像の説明

自律型トランザクションのメリット

自律型トランザクションは、開始すると完全に独立します。ロック、リソースまたはコミット依存関係をメイン・トランザクションと共有することはありません。メイン・トランザクションがロールバックする場合でも、イベントや増分再試行カウンタなどのログを取ることができます。

さらに重要な点は、自律型トランザクションは再利用可能なソフトウェア・コンポーネントであるモジュール構造の作成に役立つということです。自律型トランザクションは、ストアド・プロシージャ内部でカプセル化できます。コール側のアプリケーションは、そのストアド・プロシージャによって実行された操作が成功したか失敗したかを知る必要はありません。

自律型トランザクションの定義

自律型トランザクションを定義するには、AUTONOMOUS_TRANSACTIONプラグマ(コンパイラ・ディレクティブ)を使用します。プラグマはルーチンを自律型(独立型)としてマークするようにPL/SQLコンパイラに指示します。このコンテキストでは、ルーチンには次のものが含まれます。

プラグマは、ルーチンの宣言部の任意の場所でコーディングできます。ただし、見やすくするために、セクションの先頭にプラグマをコーディングしてください。構文は、PRAGMA AUTONOMOUS_TRANSACTIONです。

例6-43では、パッケージ・ファンクションを自律型としてマークします。パッケージのすべてのサブプログラム(またはオブジェクト型のすべてのメソッド)を自律型としてマークするためにプラグマを使用することはできません。自律型としてマークできるのは、個々のルーチンのみです。

例6-43    パッケージでの自律型ファンクションの宣言

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
-- code for function raise_salary
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     new_sal NUMBER(8,2);
   BEGIN
     UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
     COMMIT;
     SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
     RETURN new_sal;
   END raise_salary;
END emp_actions;
/

例6-44では、スタンドアロン・プロシージャを自律型としてマークします。

例6-44    自立型スタンドアロン・プロシージャの宣言

CREATE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees SET salary = salary - amount WHERE employee_id = emp_id;
  COMMIT;
END lower_salary;
/

例6-45では、PL/SQLブロックを自律型としてマークします。ただし、ネストしたPL/SQLブロックは自律型としてマークできません。

例6-45    自律型PL/SQLブロックの宣言

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  emp_id NUMBER(6);
  amount NUMBER(6,2);
BEGIN
  emp_id := 200;
  amount := 200;
  UPDATE employees SET salary = salary - amount WHERE employee_id = emp_id;
  COMMIT;
END;
/

例6-46では、データベース・トリガーを自律型としてマークします。通常のトリガーとは異なり、自律型トリガーには、COMMITROLLBACKなどのトランザクション制御文を含めることができます。

例6-46    自律型トリガーの宣言

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, 
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
   INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                 :new.salary, :old.salary );
  COMMIT;
END;
/

自律型トランザクションとネストしたトランザクションの比較

自律型トランザクションは別のトランザクションによって開始されますが、これはネストしたトランザクションではありません。その理由は次のとおりです。

トランザクション・コンテキスト

メイン・トランザクションはそのコンテキストをネストしたルーチンと共有しますが、自律型トランザクションとは共有しません。ある自律型ルーチンが別の自律型ルーチンを(または自身を再帰的に)コールする場合、ルーチンはトランザクション・コンテキストを共有しません。ある自律型ルーチンが自律型ではないルーチンをコールする場合、ルーチンは同じトランザクション・コンテキストを共有します。

トランザクションの可視性

自律型トランザクションによって行われた変更は、自律型トランザクションがコミットすると、他のトランザクションから参照できるようになります。分離レベルがREAD COMMITTED(デフォルト)に設定されている場合、メイン・トランザクションが再開すると、これらの変更をメイン・トランザクションから参照できるようになります。

メイン・トランザクションの分離レベルをSERIALIZABLEに設定すると、その自律型トランザクションによって行われた変更は、再開してもメイン・トランザクションからは参照できません。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

自律型トランザクションの制御

自律型ルーチンの最初のSQL文でトランザクションが開始されます。1つのトランザクションが終了すると、次のSQL文で別のトランザクションが開始されます。カレント・トランザクションは、最後のコミットまたはロールバックよりも後に実行されたすべてのSQL文で構成されます。自律型トランザクションを制御するには、次の文を使用します。これは現在の(アクティブな)トランザクションのみに適用されます。

開始と終了

自律型ルーチンの実行部に入ると、メイン・トランザクションは停止します。ルーチンを終了すると、メイン・トランザクションは再開します。

正常に終了するには、すべての自律型トランザクションを明示的にコミットまたはロールバックする必要があります。ルーチン(またはそれによってコールされたルーチン)に保留中のトランザクションがある場合は、例外が呼び出され、保留中のトランザクションはロールバックされます。

コミットとロールバック

COMMITROLLBACKはアクティブな自律型トランザクションを終了しますが、自律型ルーチンから抜けるわけではありません。1つのトランザクションが終了すると、次のSQL文で別のトランザクションが開始されます。1つの自律型ルーチンは、複数のCOMMIT文を発行する場合、複数の自律型トランザクションを含むことができます。

セーブポイントの使用

セーブポイントの有効範囲は、それが定義されたトランザクションです。メイン・トランザクション内で定義されたセーブポイントは、その自律型トランザクション内で定義されたセーブポイントとは無関係です。実際、メイン・トランザクションと自律型トランザクションのセーブポイントには、同じ名前を使用できます。

ロールバックできるのは、カレント・トランザクション内でマークされたセーブポイントまでです。自律型トランザクション内では、メイン・トランザクション内でマークされたセーブポイントまではロールバックできません。メイン・トランザクションのセーブポイントまでロールバックするには、自律型ルーチンを抜けてメイン・トランザクションを再開する必要があります。

メイン・トランザクション内では、自律型トランザクションを開始する前にマークされたセーブポイントまでロールバックしても、自律型トランザクションはロールバックされません。自律型トランザクションは、メイン・トランザクションからは完全に独立していることに注意してください。

自律型トランザクションでのエラーの回避

一般的なエラーを回避するには、次の事項を考慮してください。

自律型トリガーの使用

データベース・トリガーを使用してイベントのログを透過的に取ることができます。ある表に対するすべての挿入を、ロールバックするものも含めて追跡するとします。例6-47では、トリガーを使用して、重複する行をシャドウ表に挿入します。トリガーは自律型であるため、メインの表への変更をコミットするかどうかに関係なく、シャドウ表への変更をコミットできます。

例6-47    自律型トリガーの使用

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

-- create an autonomous trigger that inserts into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
   BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                :new.salary, :old.salary );
  COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 115;
COMMIT;

-- update another salary, then roll back the update
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 116;
ROLLBACK;

-- show that both committed and rolled-back updates add rows to audit table
SELECT * FROM emp_audit WHERE emp_audit_id = 115 OR emp_audit_id = 116;

通常のトリガーとは異なり、自律型トリガーはシステム固有の動的SQLを使用して、DDL文を実行できます(第7章「システム固有の動的SQLを使用したSQL操作の実行」を参照)。次の例では、表emp_auditに行が挿入された後に、トリガーdrop_temp_tableが一時データベース表を削除します。

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE TABLE temp_audit ( emp_audit_id NUMBER(6), up_date DATE);

CREATE OR REPLACE TRIGGER drop_temp_table
   AFTER INSERT ON emp_audit
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
   COMMIT;
END;
/

データベース・トリガーの詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。

SQLからの自律型ファンクションのコール

SQL文からコールされるファンクションは、副作用を制御するための特定の規則に従う必要があります。「PL/SQLサブプログラムの副作用の制御」を参照してください。この規則に違反していないかどうかを確認するには、RESTRICT_REFERENCESプラグマを使用できます。プラグマは、ファンクションがデータベース表またはパッケージ変数に対する読込みまたは書込みを行っていないことを示します。詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。

ただし、自律型ルーチンの動作に関係なく、データベースに読込み禁止状態(RNDS)およびデータベースへの書込み禁止状態(WNDS)の規則に違反しないように定義できます。例6-48に示すように、これは便利な機能です。問合せからパッケージ・ファンクションlog_msgをコールすると、データベースへの書込み禁止状態の規則に違反することなく、データベース表debug_outputにメッセージが挿入されます。

例6-48    自律型ファンクションのコール

-- create the debug table
CREATE TABLE debug_output (msg VARCHAR2(200));

-- create the package spec
CREATE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;
/
-- create the package body
CREATE PACKAGE BODY debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      -- the following insert does not violate the constraint
      -- WNDS because this is an autonomous routine
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;
END debugging;
/
-- call the packaged function from a query
DECLARE
   my_emp_id    NUMBER(6);
   my_last_name VARCHAR2(25);
   my_count     NUMBER;
BEGIN
   my_emp_id := 120;
   SELECT debugging.log_msg(last_name) INTO my_last_name FROM employees
      WHERE employee_id = my_emp_id;
-- even if you roll back in this scope, the insert into 'debug_output' remains
-- committed because it is part of an autonomous transaction
   ROLLBACK;
END;
/


戻る 次へ
Oracle
Copyright © 2005 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引