| Oracle Database PL/SQL言語リファレンス 11g リリース1（11.1） E05670-03 |
|
 戻る |
 次へ |
静的SQLは、PL/SQL言語に属するSQLです。この章では、静的SQLおよびPL/SQLプログラムでのその使用方法について説明します。
ここでのトピック:
静的SQLは、PL/SQL言語に属するSQLです。静的SQLには、次のものがあります。
データ操作言語（DML）文（EXPLAIN PLANを除く）
静的SQLは、現行のANSI/ISO SQL規格に準拠しています。
例6-1に示すとおり、データベースのデータを操作する場合は、INSERT文、UPDATE文およびDELETE文などのDML操作をPL/SQLプログラム内に直接記述できます。特別な表記法は必要ありません。 SQLのCOMMIT文をPL/SQLプログラム内に直接記述することもできます。「PL/SQLにおけるトランザクション処理の概要」を参照してください。
例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;
/
|
参照: COMMIT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
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句の値のかわりに変数を使用できます。 表名や列名などのかわりに変数を使用するには、「システム固有の動的SQLの使用」で説明されているEXECUTE IMMEDIATE文を使用する必要があります。
PL/SQLレコードとSQLを併用してデータを更新および挿入する手順については、「データベースへのレコードの挿入」および「レコード値を使用したデータベースの更新」を参照してください。
PL/SQL変数への値の代入の詳細は、「SQL問合せ結果のPL/SQL変数への代入」を参照してください。
データベースでは、トランザクションに基づいて作業します。つまり、トランザクションを使用してデータの整合性を確保します。トランザクションとは、論理作業単位を実行する一連のSQLのDML文です。たとえば、2つのUPDATE文を使用して、ある銀行口座に入金し、別の口座から出金します。一方の操作は成功するが他方の操作は失敗するという状態を許可しないことが重要です。
データベースを変更するトランザクションの終わりに、データベースによってすべての変更内容の確定または取消しが行われます。 トランザクション中にプログラムに障害が発生すると、データベースによってエラーが検出され、トランザクションがロールバックされて、データベースが元の状態にリストアされます。
COMMIT、ROLLBACK、SAVEPOINTおよびSET TRANSACTION文を使用してトランザクションを制御します。COMMITは、カレント・トランザクション中にデータベースに加えられた変更内容を確定します。ROLLBACKは、カレント・トランザクションを終了させ、トランザクションの開始以降に加えられた変更をすべて取り消します。SAVEPOINTは、トランザクション処理内の現在位置にマークを付けます。ROLLBACKとSAVEPOINTを併用すると、トランザクションの一部のみを取り消すことができます。SET TRANSACTIONは、読取り/書込みアクセスや分離レベルなど、トランザクションのプロパティを設定します。 「PL/SQLにおけるトランザクション処理の概要」を参照してください。
例6-4の問合せは、SQLファンクション（COUNT）を起動します。
PL/SQLでは、SQL擬似列CURRVAL、LEVEL、NEXTVAL、ROWIDおよびROWNUMが認識されます。ただし、擬似列の使用には、代入または条件テストで一部の擬似列を使用できないなどの制限があります。制限などのSQL擬似列の使用の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
ここでのトピック:
順序とは、連続的な数値を生成するスキーマ・オブジェクトのことです。順序を作成する場合は、その初期値と増分を指定できます。CURRVALは指定された順序の中での現在の値を戻します。セッションの中でCURRVALを参照する前に、NEXTVALを使用して数値を生成する必要があります。NEXTVALを参照すると、現在の順序番号がCURRVALに格納されます。NEXTVALは順序に増分を加えて、次の値を戻します。順序の現在の値または次の値を得るには、ドット表記法を使用します。
sequence_name.CURRVAL sequence_name.NEXTVAL
sequence_nameは、ローカルにもリモートにもできます。
順序のNEXTVAL値を参照するたびに、トランザクションをコミットするかロールバックするかにかかわらず、順序はすぐに増分され、その変化は永続的になります。
順序を作成すると、トランザクション処理の目的のために独自の順序番号を生成させることができます。
例6-5では、新しい順序番号を生成し、複数の文でその番号を参照しています。（順序はすでに存在している必要があります。順序を作成するには、SQLのCREATE SEQUENCE文を使用します。）
例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
-- Generate initial sequence number
seq_value := employees_seq.NEXTVAL;
-- Print initial sequence number:
DBMS_OUTPUT.PUT_LINE
('Initial sequence value: ' || TO_CHAR(seq_value));
-- Use NEXTVAL to create unique number when inserting data:
INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL,
'Lynette', 'Smith');
-- Use CURRVAL to store same value somewhere else:
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.
-- Use CURRVAL to specify the record to delete:
seq_value := employees_seq.CURRVAL;
DELETE FROM employees_temp2 WHERE employee_id = seq_value;
-- Udpate employee_id with NEXTVAL for specified record:
UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL
WHERE first_name = 'Lynette' AND last_name = 'Smith';
-- Display final value of CURRVAL:
seq_value := employees_seq.CURRVAL;
DBMS_OUTPUT.PUT_LINE
('Ending sequence value: ' || TO_CHAR(seq_value));
END;
/
使用上の注意
sequence_name.CURRVALおよびsequence_name.NEXTVALは、NUMBER式を使用できるすべての場所で使用できます。
sequence_name.CURRVALまたはsequence_name.NEXTVALを使用してオブジェクト型メソッド・パラメータのデフォルト値を指定すると、コンパイラ・エラーが発生します。
PL/SQLは、出現するすべてのsequence_name.CURRVALおよびsequence_name.NEXTVALを評価します（これらが出現するすべての行に対して一回のみ一連の式を評価するSQLとは異なります）。
SELECT CONNECT BY文でLEVELを使用すると、データベース表の行をツリー構造に整理できます。順序番号を使用して各行に一意の識別子を指定し、他の行からこれらの識別子を参照して親子関係を構築できます。LEVELはツリー構造の中のノードのレベル番号を戻します。ルートはレベル1、ルートの子はレベル2、孫はレベル3、のように続きます。
ツリーのルートを識別する条件は、START WITH句で指定します。PRIOR演算子を使用して、問合せがツリーの中を移動するときの向き（ルートから下へ、または枝から上へ）を指定します。
ROWIDはデータベース表の行のROWID（バイナリ・アドレス）を戻します。UROWID型の変数を使用すると、ROWIDを読取り可能な書式で格納できます。
物理ROWIDを選択またはフェッチしてUROWID変数に入れる場合は、バイナリ値を文字列に変換するファンクションROWIDTOCHARを使用します。UPDATE文またはDELETE文のWHERE句の中でUROWID変数とROWID擬似列を比較すると、カーソルからフェッチされた最新の行を識別できます。 例は、「コミットにまたがるフェッチ」を参照してください。
ROWNUMは、行が表から取り出された順番を示す番号を戻します。最初に取り出された行のROWNUMは1、2番目に取り出された行のROWNUMは2、のように続きます。SELECT文にORDER BY句が含まれている場合、取り出された行がソートされる前に、行にROWNUMが代入されます。ソートされた最初のn行を取得するには、副問合せを使用します。ROWNUMの値が増えるのは、行が取り出されたときのみです。つまり、WHERE句でROWNUMを使用する場合は、次のようにする必要があります。
... WHERE ROWNUM < constant; ... WHERE ROWNUM <= constant;
例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;
/
PL/SQLでは、SQL文の中で、SQLの比較演算子、集合演算子および行演算子を使用できます。この項では、これらの演算子について簡単に説明します。 詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
ここでのトピック:
比較演算子は、通常、DML文のWHERE句で述語を形成するために使用します。述語は、2つの式を比較して、TRUE、FALSEまたはNULLのいずれかに評価します。次の比較演算子は、述語の形成に使用できます。述語は論理演算子AND、ORおよびNOTを使用して結合できます。
| 演算子 | 説明 |
|---|---|
ALL |
TRUEであればTRUEに評価します。 |
ANY、SOME |
TRUEであればTRUEに評価します。 |
BETWEEN |
値が指定範囲内かどうかをテストします。 |
EXISTS |
TRUEを戻します。 |
IN |
セット・メンバーシップをテストします。 |
IS NULL |
NULLかどうかをテストします。 |
LIKE |
文字列が、指定したパターンと一致するかどうかをテストします。指定パターンにはワイルドカードを使用できます。 |
集合演算子は2つの問合せの結果を組み合せて1つの結果を戻します。INTERSECTによって、2つの問合せの両方で選択されたすべての行を、重複するものを除いて戻します。MINUSは、1番目の問合せによって選択されたが、2番目の問合せでは選択されなかったすべての行を、重複するものを除いて戻します。UNIONによって、2つの問合せのいずれかで選択されたすべての行を、重複するものを除いて戻します。UNION ALLは、重複した行も含めて、どちらかの問合せによって選択されたすべての行を戻します。
PL/SQLでは、暗黙カーソルおよび明示カーソルを使用します。PL/SQLでは、1行のみを戻す問合せを含むすべてのSQLデータ操作文に関して暗黙的にカーソルが宣言されます。 暗黙カーソルはSQLカーソルと呼ばれます。問合せの処理を完全に制御する必要がある場合、PL/SQLブロック、サブプログラムまたはパッケージの宣言部の中でカーソルを明示的に宣言できます。 複数の行を戻す問合せの場合は、明示カーソルを宣言する必要があります。
ここでのトピック:
SQLカーソルはPL/SQLで自動的に管理されます。 これらのカーソルを処理するコードを記述する必要はありません。 ただし、カーソル属性を使用してSQLカーソルの実行情報を追跡できます。
ここでのトピック:
SQLカーソルの属性は、INSERT、UPDATE、DELETE、SELECT INTO、COMMIT、ROLLBACK文など、DMLおよびDDL文の実行に関する情報を戻します。カーソル属性は%FOUND、%ISOPEN、%NOTFOUNDおよび%ROWCOUNTです。カーソル属性の値は、常に直前に実行されたSQL文を参照しています。 データベースによってSQLカーソルがオープンされるまで、SQLカーソルの属性の結果はNULLになります。
SQLカーソルには、FORALL文で使用するために設計された%BULK_ROWCOUNTという別の属性があります。 詳細は、「FORALLによる影響を受ける行カウント（%BULK_ROWCOUNT属性）」を参照してください。
ここでのトピック:
SQLのDML文が実行されるまでは、%FOUNDの結果はNULLになります。その後、INSERT文、UPDATE文またはDELETE文が1行または複数の行に作用するか、またはSELECT INTO文が1行または複数の行を戻すと、%FOUNDの結果はTRUEになります。それ以外の場合、%FOUNDの結果はFALSEになります。 例6-7では、%FOUNDを使用して、削除に成功した場合に行を挿入するようにしています。
データベースによって、SQLカーソルに関連付けられたSQL文の実行が終了されると、このSQLカーソルは自動的にクローズされます。その結果、%ISOPENの結果は常にFALSEになります。
%NOTFOUNDは、論理的に%FOUNDの逆です。INSERT文、UPDATE文またはDELETE文がどの行にも作用しないか、またはSELECT INTO文がどの行も戻さない場合、%NOTFOUNDの結果はTRUEになります。それ以外の場合、%NOTFOUNDの結果はFALSEになります。
%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が親トランザクション内の元の値に戻ることはありません。
SQLカーソルの属性を使用する場合は、次のことを考慮してください。
カーソル属性の値は、常に直前に実行されたSQL文を参照します（その文の場所とは無関係です）。文が別の有効範囲に存在する場合もあります（サブブロックなど）。属性の値を保存して後で使用する場合は、値をローカル変数に直接代入してください。サブプログラム・コールなどの他の操作を実行すると、変数値がテスト前に変更される可能性があります。
%NOTFOUND属性は、SELECT INTO文と組み合せて使用しても効果がありません。
SELECT INTO文が行を戻せなかった場合は、PL/SQLによって事前定義済の例外NO_DATA_FOUNDがただちに呼び出され、%NOTFOUNDをチェックする前に制御フローが中断されます。
SQL集計関数を起動するSELECT INTO文は、常に値またはNULLを戻します。このような文の後では、%NOTFOUND属性の値は常にFALSEになるため、属性をチェックする必要はありません。
問合せの処理を完全に制御する必要がある場合、PL/SQLブロック、サブプログラムまたはパッケージの宣言部の中でカーソルを明示的に宣言できます。
カーソルの制御には、OPEN、FETCHおよびCLOSEの3つの文を使用します。まず、結果セットを識別するOPEN文でカーソルを初期化します。次に、FETCHを繰り返し実行して、すべての行を取り出します。またはBULK COLLECT句を使用して、すべての行を一度にフェッチします。最後の行の処理が終わってから、CLOSE文でカーソルを解放します。
この方法では、SQLカーソルのFORループなどの他の方法より多くのコードが必要になります。この方法のメリットは柔軟性があるということです。この方法では次の操作を実行できます。
複数のカーソルを宣言してオープンすることで、複数の問合せをパラレルで処理できます。
1回のループで複数の行を処理したり、行をスキップしたり、処理を複数のループに分割することができます。
ここでのトピック:
他の文でカーソルを参照するときは、事前に宣言する必要があります。カーソルに名前を付け、特定の問合せと関連付けます。オプションで、カーソルの戻り型（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
-- 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;
/
問合せは、有効範囲内にある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はNULLを戻します。フェッチの実行後、直前のフェッチが行を戻した場合は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は、カーソルまたはカーソル変数をオープンしている場合はTRUEを、そうでない場合はFALSEを戻します。 例6-15では、%ISOPENを使用してアクションを選択します。
%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;
/
最初のフェッチの前は、%NOTFOUNDはNULLを戻します。FETCHが正常に実行されない場合、EXIT WHEN文はWHEN条件がTRUEの場合にのみ実行されるため、ループは終了しません。安全のために、次のEXIT文をかわりに使用できます。
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
カーソルまたはカーソル変数をオープンしていない場合、%NOTFOUNDでカーソルまたはカーソル変数を参照すると、例外INVALID_CURSORが呼び出されます。
カーソルまたはカーソル変数をオープンしている場合、%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値 |
|---|---|---|---|---|
|
|
例外 |
|
例外 |
例外 |
|
|
|
|
|
0 |
|
|
|
|
|
0 |
|
|
|
|
|
1 |
|
|
|
|
|
1 |
|
|
|
|
|
データに依存 |
|
|
|
|
|
データに依存 |
|
|
|
|
|
データに依存 |
|
|
|
|
|
データに依存 |
|
|
例外 |
|
例外 |
例外 |
表6-1の情報には、次の内容が適用されます。
カーソルをオープンする前またはカーソルをクローズした後で、%FOUND、%NOTFOUNDまたは%ROWCOUNTを参照すると、INVALID_CURSORが呼び出されます。
最初のFETCHの後、結果セットが空の場合、%FOUNDはFALSE、%NOTFOUNDはTRUE、%ROWCOUNTは0になります。
PL/SQLを使用すると、問合せを実行し、結果セットの個別のフィールドまたはすべての行にアクセスできます。従来のデータベース・プログラミングでは、カーソルと呼ばれる内部データ構造を使用して問合せの結果を処理していました。PL/SQLでは、ほとんどの場合にカーソルを管理できるため、問合せの結果を処理するコードが単純で小型になります。この項では、PL/SQLによってすべてが管理される単純な問合せと、自分でカーソルを使用する複雑な問合せを処理する方法について説明します。
ここでのトピック:
問合せが1行のみを戻すと予測している場合、通常のSQLのSELECT文に、結果を保持するPL/SQL変数を指定するINTO句を追加して記述できます。
問合せが複数の行を戻す可能性があり、そのうち最初の値以外は必要ない場合、ROWNUMの値と比較することで、結果セットを1行に制限できます。問合せが行を戻さない可能性がある場合、例外ハンドラを使用して、データが見つからない場合のアクションを指定します。
使用するデータに条件が存在するかどうかのチェックのみを行う場合、COUNT(*)演算子を使用して問合せを記述できます。この演算子は常に数値を戻すため、NO_DATA_FOUND例外は発生しません。
1回に1行ずつ結果セットをループするのではなく、ローカルのPL/SQL変数に大量のデータを代入する必要がある場合、BULK COLLECT句を使用できます。特定の列のみを問い合せる場合、各列に対するすべての結果を個別のコレクション変数に格納できます。表のすべての列を問い合せる場合は、レコードのコレクションに結果セット全体を格納できます。これによって、結果のループ処理および別の列への参照が簡単になります。例6-13「カーソルを使用したバルク・データのフェッチ」を参照してください。
この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合は、より多くの処理をSQLで実行すると、コードのパフォーマンスを向上できます。
結果セットを1回のみループする必要がある場合は、FORループを使用します（後続の項を参照）。 この方法を使用すると、結果セットのコピーを格納する際のメモリーのオーバーヘッドを回避できます。
結果セットをループして特定の値をスキャンしたり、結果をフィルタリングしてより小さい結果セットにする場合、かわりに元の問合せでスキャンまたはフィルタリングを行います。簡単な場合であればWHERE句を追加できます。また、複数の結果セットを比較する場合は、INTERSECTやMINUSなどの集合演算子を使用できます。
結果セットをループして、各結果行に対して別の問合せまたはDML文を実行する場合は、より効率的な方法があります。問合せの場合、元の問合せに副問合せまたはEXISTS句やNOT EXISTS句を含めることを検討してください。DML文の場合、通常のループ内部でこれらの文をコーディングするより高速なFORALL文を使用することを検討してください。
最も一般的な問合せは、SELECT文を発行し、結果セットの行に対してただちにループを1回実行することす。 PL/SQLでは、単純なFORループを使用してこのような種類の問合せを実行できます。
FORループのイテレータ変数は、事前に宣言する必要はありません。イテレータ変数は、フィールド名が問合せの列名に一致する%ROWTYPEレコードです。この変数は、ループ中にのみ存在します。明示的な列名ではなく式を使用する場合、列の別名を使用すると、ループ内の対応する値を参照できます。
問合せの処理を完全に制御するには、OPEN、FETCHおよびCLOSE文と明示カーソルを組み合せて使用します。
ある場所で問合せを指定し、他のサブプログラム内などの別の場所に行を取り出す必要がある場合があります。また、状況に応じて、ORDER BY句やGROUP BY句など、別の問合せパラメータを選択する必要がある場合もあります。さらに、一部の行に対して他の行とは異なる処理を行うために、より複雑なループが必要な場合もあります。
明示カーソルは非常に柔軟性があるため、必要に応じて様々な表記法から選択できます。次の項では、明示カーソルのすべての問合せ処理機能について説明します。
ここでのトピック:
PL/SQLを使用すると、問合せの発行、結果の各行の%ROWTYPEレコードへの取出し、およびループ内の各行の処理を簡単に実行できます。
FORループ内に、問合せのテキストを直接含めます。
PL/SQLによって、結果セットの列に対応するフィールドを持つレコード変数が作成されます。
ループ内でこのレコード変数のフィールドを参照します。テストや計算、出力の表示または他の場所への結果の格納を実行できます。
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文」を参照してください。
同じサブプログラムの別の部分から同じ問合せを参照する必要がある場合、その問合せを指定するカーソルを宣言し、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;
/
カーソルFORループでは、PL/SQLによって、結果セットの列に対応するフィールドを持つ%ROWTYPEレコードが作成されます。フィールドは、SELECTリストの中の対応する列と同じ名前を持ちます。
選択リストには、列と定数、連結された2つの列などの式が含まれる場合があります。この場合、列の別名を使用して、適切な列に一意の名前を付けます。
例6-18では、full_nameおよびdream_salaryが問合せの中で式の別名として使用されています。
副問合せは、別のSQL DML文内に出現する問合せであり、多くの場合カッコで囲まれています。文は、副問合せで戻された1つの値または複数の値の集合に対して実行されます。次に例を示します。
副問合せを使用して列のMAX、MINまたはAVGの値を検索し、この単一の値を使用してWHERE句の中で比較を行うことができます。
副問合せを使用して値の集合を検索し、その値を使用してWHERE句の中でINまたはNOT INの比較を行うことができます。この方法を使用すると、結合を回避できます。
副問合せを使用して値の集合をフィルタリングし、外部の問合せでORDER BYやGROUP BYなどの別の操作を適用できます。
問合せのFROM句で、表名のかわりに副問合せを使用できます。この方法を使用すると、表全体を結合するかわりに、表と別の表から取り出した小さい行セットを結合できます。
副問合せで定義した行セットを使用して、表を作成したり、表へ挿入することができます。
例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)
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;
/
ローカル変数を参照するかわりに、パラメータを受け入れるカーソルを宣言し、カーソルをオープンしたときにそれらのパラメータの値を渡すことができます。問合せが通常、特定の値を使用して発行される場合、それらの値をデフォルトとして指定できます。パラメータの値を渡すには、位置表記法および名前表記法のいずれも使用できます。
例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文の実行時にそのデフォルト値を取ります。仮パラメータのデフォルト値が式の場合に、対応する実パラメータをOPEN文に指定すると、その式は評価されません。
カーソルと同じように、カーソル変数は複数行の問合せの結果セットの中の現在行を指します。カーソル変数は、特定の問合せと結合されないため、より柔軟性があります。カーソル変数は、正しい列セットを戻す任意の問合せに対してオープンできます。
カーソル変数は、パラメータとしてローカル・サブプログラムおよびストアド・サブプログラムに渡します。1つのサブプログラム内でカーソル変数をオープンし、別のサブプログラム内でこの変数を処理することで、データ検索を集中的に実行できます。この方法は、PL/SQLサブプログラムが別の言語（JavaやVisual Basicなど）で記述されたサブプログラムへ結果セットを戻す可能性がある、複数言語のアプリケーションでも役立ちます。
カーソル変数は、すべてのPL/SQLクライアントで使用します。たとえば、OCIやPro*CプログラムなどのPL/SQLホスト環境の中でカーソル変数を宣言し、それを入力ホスト変数（バインド変数）としてPL/SQLに渡すことができます。PL/SQLエンジンを備えたOracle Formsなどのアプリケーション開発ツールでは、クライアント側でカーソル変数を完全に使用できます。また、クライアントとデータベース・サーバーの間で、リモート・サブプログラム・コールを介してカーソル変数をやり取りできます。
ここでのトピック:
カーソル変数は、結果セットへのポインタに類似しています。1つのサブプログラム内で問合せを実行する際にカーソル変数を使用して、問合せ結果を別のサブプログラム（別の言語で記述されている場合もある）で処理します。 カーソル変数のデータ型はREF CURSORであり、通称でREF CURSORと呼ばれます。
常に同じ問合せ作業領域を参照する明示カーソルとは異なり、カーソル変数は異なる作業領域を参照できます。カーソルを使用する予定の場所では、カーソル変数を使用できません。逆の場合も同じです。
カーソル変数は、PL/SQLのストアド・サブプログラムと様々なクライアントとの間で問合せの結果セットを渡すために使用します。PL/SQLとそのクライアントは、結果セットが格納されている問合せ作業領域を指すポインタを共有します。 たとえば、OCIクライアント、Oracle Formsアプリケーションおよびデータベースがすべて同じ作業領域を参照する場合があります。
カーソル変数の値は、1つの有効範囲から別の有効範囲に渡すことができるため、問合せ作業領域は、それを指すカーソル変数が存在するかぎりアクセス可能になります。たとえば、Pro*Cプログラムに組み込まれたPL/SQLブロックにホスト・カーソル変数を渡す場合、カーソル変数が指す作業領域は、そのブロックの終了後もアクセス可能な状態のままです。
クライアント側にPL/SQLエンジンがあれば、クライアントからサーバーへのコールに課される制限はありません。たとえば、クライアント側でカーソル変数を宣言し、それをサーバー側でオープンしてフェッチした後で、クライアント側で引き続きフェッチすることができます。また、PL/SQLブロックを使用して複数のホスト・カーソル変数を1回の往復でオープンまたはクローズすることで、ネットワークの通信量を削減できます。
カーソル変数を作成するには、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 -- Strong: TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- Weak: TYPE genericcurtyp IS REF CURSOR; cursor1 empcurtyp; cursor2 genericcurtyp; my_cursor SYS_REFCURSOR; -- no new type needed 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-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 invoking 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;
/
すべてのポインタと同様に、カーソル変数によってパラメータのエイリアシング機能を拡張できます。 「PL/SQLサブプログラム名のオーバーロード」を参照してください。
カーソル変数の制御には、OPEN-FOR、FETCHおよび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が呼び出されます。
ホスト・カーソル変数を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; /
c1、c2およびc3に代入されたカーソルは通常どおり動作し、あらゆる用途に使用できます。終了すると、次のようにカーソルを解放します。
BEGIN CLOSE :c1; CLOSE :c2; CLOSE :c3; END; /
代入に関係する両方のカーソル変数が強い型指定である場合は、両方が同じデータ型であることが必要です（同じ戻り型であるのみでは不十分です）。 一方または両方のカーソル変数が弱い型指定である場合は、データ型が異なってもかまいません。
問合せ作業領域を指していないカーソル変数に対して取出しまたはクローズを実行するか、カーソルの属性を参照すると、PL/SQLによって例外INVALID_CURSORが呼び出されます。カーソル変数（またはパラメータ）が問合せ作業領域を指すようにするには、次の2通りの方法があります。
OPEN-FOR文でカーソル変数を問合せ用にオープンします。
すでにオープンされたホスト・カーソル変数またはPL/SQLカーソル変数の値を、カーソル変数に代入します。
オープンされていないカーソル変数を別のカーソル変数に代入すると、1番目のカーソル変数をオープンした後も、2番目のカーソル変数は無効のままです。
カーソル変数をパラメータとして渡す場合は注意が必要です。実パラメータと仮パラメータの戻り型に互換性がないと、実行時にPL/SQLによってROWTYPE_MISMATCHが呼び出されます。
パッケージ仕様部ではカーソル変数を宣言できません（例6-34を参照）。
ホスト・カーソル変数をOCIクライアントからPL/SQLにバインドする場合は、同じサーバー・コールで変数をオープンしないかぎり、サーバー側で変数からフェッチできません。
比較演算子を使用して、カーソル変数が等しいかどうか、またはNULLかどうかをテストできません。
データベースの列にカーソル変数の値を格納できません。CREATE TABLE文の中で使用するための等価の型は存在しません。
カーソル変数を、結合配列、ネストした表またはVARRAYに格納できません。
カーソルとカーソル変数には相互操作性がありません。つまり、一方の値が期待されている場所で、もう一方が使用できません。たとえば、カーソルFORループの中ではカーソル変数を参照できません。
例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; /
|
注意:
|
カーソル式はネストしたカーソルを戻します。結果セットの各行には、通常の値の他に、行内の他の値に関係する副問合せで生成されるカーソルも含まれることがあります。1つの問合せで、複数の表から取り出された関連値の大きな集合を戻すことができます。結果セットは、最初にその行から、次に各行でネストしたカーソルからフェッチするネステッド・ループで処理できます。
PL/SQLでは、カーソルの宣言、REF CURSORの宣言およびREF CURSOR変数の一部として、カーソル式を持つ問合せがサポートされます。（また、カーソル式は動的SQL問合せにも使用できます。）
次にカーソル式の構文を示します。
CURSOR(subquery)
ネストしたカーソルは、それを含んでいる行が親カーソルからフェッチされるときに暗黙的にオープンされます。ネストしたカーソルがクローズされるのは、次の場合のみです。
ネストしたカーソルをユーザーが明示的にクローズしたとき。
親カーソルが再実行されるとき。
親カーソルがクローズされるとき。
親カーソルが取り消されるとき。
親カーソルの1つのフェッチ中にエラーが呼び出されるとき。ネストしたカーソルはクリーンアップの一部としてクローズされます。
例6-35では、カーソルc1はカーソル式を含む問合せと関連付けられています。departments表の部門ごとに、ネストしたカーソルはその部門の各従業員の名前を戻します（employees表から取得します）。
例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_id,
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 -- Process each row of query's result set
FETCH c1 INTO dept_name, emp_cur;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
LOOP -- Process each row of subquery's result set
-- (this could be done in a procedure instead)
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型の仮パラメータがある場合、対応する実際のパラメータにカーソル式を使用できます。実際のパラメータとしてカーソル式を使用すると、ファンクションに行セットをパラメータとして渡すことができます。
カーソル式は、通常はパイプライン・テーブル・ファンクションと併用されます。「パイプライン・テーブル・ファンクションによる複数変換の実行」を参照してください。
カーソル式は、SQLカーソルとは併用できません。
カーソル式を使用できるのは、次の場合のみです。
カーソル式自体が副問合せである場合を除き、他の問合せの式の中でネストされていないSELECT文中。
SELECT文のFROM句で、テーブル・ファンクションの引数として。
カーソル式を使用できるのは、問合せ仕様部の最も外側のSELECTリスト内のみです。
カーソル式はビュー宣言には使用できません。
カーソル式のBINDおよびEXECUTE操作は実行できません。
この項では、データベースの整合性を確保する、SQLのCOMMIT、SAVEPOINTおよびROLLBACK文を使用した、PL/SQLによるトランザクション処理について説明します。SQL文は、PL/SQLプログラム内に直接記述できます。 トランザクション処理はデータベースの機能で、すべてのプログラミング言語で使用できます。これらの機能によって、複数のユーザーがデータベース上で同時に作業できます。また、各ユーザーが参照するデータのバージョンに一貫性があり、すべての変更が正しい順序で適用されることが保証されます。
通常、複数のユーザーがデータに同時にアクセスすることによって発生する問題を防ぐために、追加のコードを記述する必要はありません。 データベースでは、ロックを使用してデータへの同時アクセスを制御します。データベースは、必要最小限のデータのみを、できるかぎり短い時間ロックします。表または行のロックは、どうしてもこのレベルの制御が必要な場合に要求できます。行の共有および排他のような数種類のロッキングのモードから選択できます。
ここでのトピック:
|
参照:
|
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句を使用すると、分散トランザクションに関連付けるコメントを指定できます。コミット中にネットワークやコンピュータに障害が発生した場合、分散トランザクションが認識されないか、またはインダウトの状態になる可能性があります。 この場合は、データベースによって、COMMENTで指定されたテキストがトランザクションIDとともにデータ・ディクショナリに格納されます。
非同期コミットでは、WRITE句を使用してユーザーをより詳細に制御できます。このオプションは、コミット操作によって生成されたREDO情報をREDOログに書き込む場合の優先順位を指定します。
|
参照:
|
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 were rolled back');
END;
/
|
参照: ROLLBACK文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
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 was 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;
-- Move my_savepoint to current point
SAVEPOINT my_savepoint;
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;
/
|
参照: SQLのSET TRANSACTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
INSERT文、UPDATE文またはDELETE文の実行前に、データベースによって（ユーザーが利用できない）暗黙的なセーブポイントがマークされます。 文が正常に実行されなかった場合は、データベースによってこのセーブポイントまでロールバックが行われます。通常は、トランザクション全体ではなく、失敗したSQL文のみがロールバックされます。その文が原因で未処理例外が呼び出された場合は、ホスト環境によってロールバックの対象が決まります。
データベースでは、デッドロックを解消するためにSQL文を1文のみロールバックすることもできます。 データベースによって、関係しているトランザクションの1つにエラーが戻され、そのトランザクション中の現在の文がロールバックされます。
SQL文を実行する前に、データベースはその文を解析する必要があります。すなわち、その文が構文規則に従っているかどうかや、有効なスキーマ・オブジェクトを参照しているかどうかを確認する必要があります。SQL文の実行時に検出されたエラーはロールバックを引き起こしますが、文の解析の際に検出されたエラーはロールバックを引き起こしません。
ストアド・サブプログラムを未処理例外で終了すると、PL/SQLは値をOUTパラメータに代入せず、ロールバックを実行しません。
すべてのトランザクションは、明示的にコミットまたはロールバックする必要があります。コミットを発行するか、またはPL/SQLプログラムまたはクライアント・プログラムでロールバックするかは、アプリケーション・ロジックによって決まります。トランザクションを明示的にコミットまたはロールバックしなかった場合は、クライアント環境によって最終的な状態が決定されます。
たとえば、SQL*Plus環境で、PL/SQLブロックにCOMMIT文またはROLLBACK文がない場合、トランザクションの最終状態はそのブロックの実行後に行うことによって決まります。 ユーザーがデータ定義文、データ制御文またはCOMMIT文を実行するか、あるいはEXIT文、DISCONNECT文またはQUIT文を発行すると、データベースによってトランザクションがコミットされます。 ROLLBACK文を実行するか、またはSQL*Plusセッションを中断すると、データベースによってトランザクションがロールバックされます。
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 INTO、OPEN、FETCH、CLOSE、LOCK TABLE、COMMITおよびROLLBACK文のみです。問合せはFOR UPDATEにはできません。
|
参照: SQLのSET TRANSACTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
デフォルトでは、データベースによってデータ構造が自動的にロックされます。これはデータベースの最も重要なメリットです。様々なアプリケーションでは、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータの読取りおよび書込みを実行できます。
デフォルトのロックを上書きする必要がある場合は、特定の行や表全体のデータ・ロックを要求できます。 明示的なロックによって、トランザクション中の表に対するアクセスを拒否できます。
LOCK TABLE文を使用すると、明示的に表全体をロックできます。
SELECT FOR UPDATE文を使用すると、表の特定の行を明示的にロックし、行を読み取った後に行が変更されないようにできます。この方法によって、文を発行する前に、UPDATE文またはDELETE文が影響を与える行およびその数を確認できます。この間、他のアプリケーションは行を変更できません。
ここでのトピック:
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を指定すると、別のユーザーが要求された行をロックしていてもデータベースは待機しません。制御はただちにプログラムに戻されるため、他の処理を行ってから、改めてロックを試みてください。 キーワードNOWAITを省略すると、行が利用できるようになるまでデータベースは待機します。
カーソルをオープンしたときにすべての行がロックされるのであり、行がフェッチされるときにロックされるのではありません。また、トランザクションをコミットまたはロールバックすると、行のロックは解除されます。行がロックされていないため、コミットの後で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 employees IN ROW SHARE MODE NOWAIT;
ロック・モードによって、表に対して他にどのようなロックを使用できるかが決まります。たとえば、1つの表に対して多くのユーザーが同時に行共用ロックを取得できますが、排他ロックを取得できるのは一度に1人のユーザーのみです。あるユーザーが表に対して排他ロックをかけていると、他のユーザーはその表に対して行の挿入、更新、削除を実行できません。
表がロックされていても、他のユーザーは表に対して問合せできますが、問合せを実行しても表のロックを取得できません。2つの異なるトランザクションが同じ行を変更した場合のみ、一方のトランザクションがもう一方のトランザクションの終了を待ちます。
|
参照:
|
コミットの後でFOR UPDATEカーソルからのフェッチを試行すると、PL/SQLによって例外が呼び出されます。FOR UPDATE句によって、カーソルをオープンすると行がロックされ、コミットすると行のロックが解除されます。
DECLARE
-- if "FOR UPDATE OF salary" is included on following line,
-- an exception is raised
CURSOR c1 IS SELECT * FROM employees;
emp_rec employees%ROWTYPE;
BEGIN
OPEN c1;
LOOP
-- FETCH fails on the second iteration with FOR UPDATE
FETCH c1 INTO emp_rec;
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）へ制御がどのように流れ、また戻るかを示します。
ここでのトピック:
自律型トランザクションは、開始すると完全に独立します。ロック、リソースまたはコミット依存関係をメイン・トランザクションと共有することはありません。メイン・トランザクションがロールバックする場合でも、イベントや増分再試行カウンタなどのログを取ることができます。
さらに重要な点は、自律型トランザクションは再利用可能なソフトウェア・コンポーネントであるモジュール構造の作成に役立つということです。自律型トランザクションは、ストアド・サブプログラム内部でカプセル化できます。 コール元のアプリケーションは、そのストアド・サブプログラムによって実行された操作が成功したか失敗したかを知る必要はありません。
自律型トランザクションを定義するには、AUTONOMOUS_TRANSACTIONプラグマ（コンパイラ・ディレクティブ）を使用します。プラグマはルーチンを自律型（独立型）としてマークするようにPL/SQLコンパイラに指示します。 このコンテキストでは、ルーチンには次のものが含まれます。
トップレベル（ネストしていない）の無名PL/SQLブロック
ローカル、スタンドアロンおよびパッケージ・サブプログラム
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では、データベース・トリガーを自律型としてマークします。通常のトリガーとは異なり、自律型トリガーには、COMMITやROLLBACKなどのトランザクション制御文を含めることができます。
例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文で構成されます。自律型トランザクションを制御するには、次の文を使用します。これは現在の（アクティブな）トランザクションのみに適用されます。
COMMIT
ROLLBACK [TO savepoint_name]
SAVEPOINT savepoint_name
SET TRANSACTION
|
注意:
|
ここでのトピック:
自律型ルーチンの実行部に入ると、メイン・トランザクションは停止します。ルーチンを終了すると、メイン・トランザクションは再開します。
正常に終了するには、すべての自律型トランザクションを明示的にコミットまたはロールバックする必要があります。ルーチン（またはそれによって起動されたルーチン）に保留中のトランザクションがある場合は、例外が呼び出され、保留中のトランザクションはロールバックされます。
COMMITとROLLBACKはアクティブな自律型トランザクションを終了しますが、自律型ルーチンから抜けるわけではありません。1つのトランザクションが終了すると、次のSQL文で別のトランザクションが開始されます。1つの自律型ルーチンは、複数のCOMMIT文を発行する場合、複数の自律型トランザクションを含むことができます。
セーブポイントの有効範囲は、それが定義されたトランザクションです。メイン・トランザクション内で定義されたセーブポイントは、その自律型トランザクション内で定義されたセーブポイントとは無関係です。実際、メイン・トランザクションと自律型トランザクションのセーブポイントには、同じ名前を使用できます。
ロールバックできるのは、カレント・トランザクション内でマークされたセーブポイントまでです。自律型トランザクション内では、メイン・トランザクション内でマークされたセーブポイントまではロールバックできません。メイン・トランザクションのセーブポイントまでロールバックするには、自律型ルーチンを抜けてメイン・トランザクションを再開する必要があります。
メイン・トランザクション内では、自律型トランザクションを開始する前にマークされたセーブポイントまでロールバックしても、自律型トランザクションはロールバックされません。自律型トランザクションは、メイン・トランザクションからは完全に独立していることに注意してください。
メイン・トランザクションが保持するリソースに、自律型トランザクションがアクセスしようとすると、デッドロックが発生します。この場合は、データベースによって自律型トランザクションで例外が呼び出されます。例外が未処理になった場合、自律型トランザクションはロールバックされます。
データベース初期化パラメータTRANSACTIONSは、同時トランザクションの最大数を指定します。自律型トランザクションはメイン・トランザクションと同時に実行されるため、この最大数を超える場合があります。
コミットまたはロールバックせずにアクティブな自律型トランザクションを終了しようとすると、データベースによって例外が呼び出されます。例外が未処理になった場合、トランザクションはロールバックされます。
データベース・トリガーを使用してイベントのログを透過的に取ることができます。ある表に対するすべての挿入を、ロールバックするものも含めて追跡するとします。例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の使用」を参照）。次の例では、表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;
/
データベース・トリガー詳細は、第9章「トリガーの使用」を参照してください。
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;
/
-- invoke 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;
/
