| Oracle Database PL/SQL言語リファレンス 12c リリース1 (12.1) B71296-06 |
|
 前 |
 次 |
静的SQLは、SQL構文をPL/SQL文中で直接使用できるようにするPL/SQLの機能です。
この章では、静的SQLおよびその使用方法について説明します。
ここでのトピック
関連項目:
PL/SQLの静的SQL文を次に示します。特に説明がないかぎり、これらの文の構文は、対応するSQL文と同じです。
SELECT(この文は問合せとも呼ばれます)
PL/SQL構文の詳細は、「SELECT INTO文」を参照してください。
データ操作言語(DML)文:
INSERT
PL/SQL構文の詳細は、「INSERT文の拡張機能」を参照してください。
UPDATE
PL/SQL構文の詳細は、「UPDATE文の拡張機能」を参照してください。
DELETE
PL/SQL構文の詳細は、「DELETE文の拡張機能」を参照してください。
MERGE(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
注意:
『Oracle Database SQL言語リファレンス』におけるDMLの定義とは異なります。
トランザクション制御言語(TCL)文:
COMMIT(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
ROLLBACK(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
SAVEPOINT(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
SET TRANSACTION(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
LOCK TABLE(構文の詳細は、『Oracle Database SQL言語リファレンス』を参照)
PL/SQLの静的SQL文では、対応するSQLでバインド変数のプレースホルダを使用できる場合は、PL/SQLの識別子を使用できます。PL/SQLの識別子は、変数または仮パラメータを示している必要があります。
表名や列名などに対してPL/SQL識別子を使用するには、「システム固有の動的SQL」で説明されているEXECUTE IMMEDIATE文を使用します
注意:
PL/SQLコードによってDML文が実行された後、変数の値が未定義になるものがあります。次に例を示します。
FETCH文またはSELECT文で例外が呼び出された場合、その文の後の定義変数値は未定義になります。
行が処理されないDML文の後、そのDML文がBULKまたは複数行にわたる操作でなければ、OUTバインド変数の値は未定義になります。
例6-1 静的SQL文
この例では、PL/SQLの無名ブロックで3つのPL/SQL変数を宣言し、静的SQL文のINSERT、UPDATE、DELETEでそれらを使用しています。このブロックでは、静的SQL文COMMITも使用しています。
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE := 299; emp_first_name employees_temp.first_name%TYPE := 'Bob'; emp_last_name employees_temp.last_name%TYPE := 'Henry'; BEGIN INSERT INTO employees_temp (employee_id, first_name, last_name) VALUES (emp_id, emp_first_name, emp_last_name); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = emp_id; DELETE FROM employees_temp WHERE employee_id = emp_id RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name); END; /
結果:
Robert Henry
疑似列は、表の列のように動作しますが、表には格納されません。
制限などの擬似列の一般情報は、『Oracle Database SQL言語リファレンス』を参照してください。
静的SQLには、次のSQL疑似列が含まれます。
CURRVALおよびNEXTVAL (詳細は、「PL/SQLのCURRVALおよびNEXTVAL」を参照)。
LEVEL(詳細は、『Oracle Database SQL言語リファレンス』を参照)
OBJECT_VALUE(詳細は、『Oracle Database SQL言語リファレンス』を参照)
関連項目:
トリガーにおけるOBJECT_VALUEの使用方法の詳細は、「OBJECT_VALUE疑似列」を参照してください
ROWID(詳細は、『Oracle Database SQL言語リファレンス』を参照)
ROWNUM(詳細は、『Oracle Database SQL言語リファレンス』を参照)
順序の作成後、SQL文でその値にアクセスするには、順序の現在の値を戻すCURRVAL疑似列を使用するか、順序を増分して新しい値を戻すNEXTVAL疑似列を使用します。
これらの疑似列を参照するには、ドット表記法(sequence_name.CURRVALなど)を使用します。
注意:
sequence_name.NEXTVALを参照するたびに、トランザクションをコミットするかロールバックするかにかかわらず、順序はすぐに増分され、その変化は永続的になります。
NUMBER式が使用できる場所であればどこにでも、PL/SQL式でsequence_name.CURRVALおよびsequence_name.NEXTVALを使用できます。ただし、次のことに注意してください。
sequence_name.CURRVALまたはsequence_name.NEXTVALを使用してADTメソッド・パラメータのデフォルト値を指定すると、コンパイル・エラーが発生します。
PL/SQLは、出現するすべてのsequence_name.CURRVALおよびsequence_name.NEXTVALを評価します(これらが出現するすべての行に対して順序式を評価するSQLとは異なります)。
関連項目:
順序の概要は、『Oracle Database SQL言語リファレンス』を参照してください
CURRVALおよびNEXTVALの完全な構文については、『Oracle Database SQL言語リファレンス』を参照してください
例6-2 擬似列CURRVALおよびNEXTVAL
この例では、順序HR.EMPLOYEES_SEQの順序番号を生成し、複数の文でその番号を参照しています。
DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DROP TABLE employees_temp2; 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 (employee_id, first_name, last_name) 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 database, there might be gaps in sequence. */ -- Use CURRVAL to specify record to delete: seq_value := employees_seq.CURRVAL; DELETE FROM employees_temp2 WHERE employee_id = seq_value; -- Update 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; /
カーソルとは、特定のSELECT文またはDML文の処理に関する情報を格納しておく、SQLのプライベート領域を指すポインタです。
注意:
このトピックで説明するカーソルはセッション・カーソルです。セッション・カーソルはセッション・メモリーに存在し、セッションが終了すると消滅します。
PL/SQLで構築され管理されるカーソルは、暗黙カーソルです。ユーザーが構築および管理するカーソルは、明示カーソルです。
セッション・カーソルに関する情報は、その属性から取得できます(属性は、プロシージャ文では参照できますが、SQL文では参照できません)。
各ユーザー・セッションでオープンされて解析されているセッション・カーソルをリストするには、動的パフォーマンス・ビューV$OPEN_CURSORを問い合せます。
1つのセッションで同時にオープンできるカーソル数は、次のものにより決まります。
セッションで使用可能なメモリーの量
初期化パラメータOPEN_CURSORSの値
注意:
一般に、PL/SQLによる明示カーソルの解析は、それをセッションが最初にオープンするときにのみ行われます。また、SQL文の解析(およびそれによる暗黙カーソルの作成)は、その文が初めて実行されるときにのみ行われます。
解析されたSQL文は、すべてキャッシュに入れられます。SQL文は、新しいSQL文によってキャッシュからエージ・アウトされた場合にのみ再解析されます。したがって、明示カーソルを再オープンするには、まずクローズする必要がありますが、PL/SQLはカーソルに関連付けられた問合せを再解析する必要はありません。明示カーソルをクローズしてからすぐに再オープンした場合、PL/SQLは関連付けられた問合せを再解析しません。
ここでのトピック
関連項目:
動的パフォーマンス・ビューV$OPEN_CURSORの詳細は、『Oracle Databaseリファレンス』を参照してください
初期化パラメータOPEN_CURSORSについては、『Oracle Databaseリファレンス』を参照してください
暗黙カーソルは、PL/SQLで構築され管理されるセッション・カーソルです。PL/SQLでは、SELECT文またはDML文を実行するたびに、暗黙カーソルがオープンされます。暗黙カーソルを制御することはできませんが、その属性から情報を取得することはできます。
暗黙カーソルの属性値の構文は、SQLattributeです(このため、暗黙カーソルは、SQLカーソルとも呼ばれます)。SQLattributeは、直前に実行されたSELECT文またはDML文を常に参照します。そのような文が実行されていない場合は、SQLattributeの値はNULLです。
暗黙カーソルは、それが関連付けられている文の実行後、クローズされます。ただし、その属性値は、他のSELECT文またはDML文が実行されるまで、使用可能な状態に保たれます。
直前に実行されたSELECT文またはDML文が、別の有効範囲に存在する場合もあります。属性の値を保存して後で使用する場合は、値をローカル変数に直接代入してください。そうしないと、サブプログラム起動などの他の操作を実行した場合、属性値がテスト前に変更される可能性があります。
暗黙カーソルの属性には次のものがあります。
SQL%BULK_ROWCOUNT(「FORALL文の影響を受ける行の数の取得」を参照)
SQL%BULK_EXCEPTIONS(「FORALL文が完了した後のFORALL例外の処理」を参照
関連項目:
構文およびセマンティクスの詳細は、「暗黙カーソル属性」を参照してください
SQL%FOUNDは次の値を戻します。
SELECT文もDML文も実行されない場合は、NULL。
SELECT文が1つ以上の行を戻した場合、またはDML文が1つ以上の行に影響を与えた場合は、TRUE。
それ以外の場合は、FALSE
例6-3では、SQL%FOUNDを使用して、DELETE文によって影響を受けた行があるかどうかを判断しています。
例6-3 SQL%FOUND暗黙カーソル属性
DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
SELECT * FROM departments;
CREATE OR REPLACE PROCEDURE p (
dept_no NUMBER
) AUTHID CURRENT_USER AS
BEGIN
DELETE FROM dept_temp
WHERE department_id = dept_no;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE (
'Delete succeeded for department number ' || dept_no
);
ELSE
DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
END IF;
END;
/
BEGIN
p(270);
p(400);
END;
/
結果:
Delete succeeded for department number 270 No department number 400
SQL%NOTFOUND(論理的にSQL%FOUNDの逆)は次の値を戻します。
SELECT文もDML文も実行されない場合は、NULL。
SELECT文が1つ以上の行を戻した場合、またはDML文が1つ以上の行に影響を与えた場合は、FALSE。
それ以外の場合は、TRUE
SQL%NOTFOUND属性は、PL/SQLのSELECT INTO文と組み合せて使用しても効果がありません。これは、次のような理由からです。
SELECT INTO文によって行が戻されない場合、SQL%NOTFOUNDをチェックする前に、PL/SQLによって事前定義例外NO_DATA_FOUNDがただちに呼び出されます。
SQL集計関数を起動するSELECT INTO文は、常にある値(多くはNULL)を戻します。このような文の後では、SQL%NOTFOUND属性の値は常にFALSEになるため、属性をチェックする必要はありません。
SQL%ROWCOUNTは次の値を戻します。
SELECT文もDML文も実行されない場合は、NULL。
それ以外の場合は、SELECT文によって戻された行の数、またはDML文によって影響を受けた行の数(INTEGER)。
注意:
サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11g2以前(またはその逆)の場合、SQL%ROWCOUNTが戻す最大値は4,294,967,295です。
例6-4では、SQL%ROWCOUNTを使用して、削除された行数を判断しています。
SELECT INTO文がBULK COLLECT句を使用せずに複数の行を戻した場合、PL/SQLによって事前定義の例外TOO_MANY_ROWSが呼び出され、SQL%ROWCOUNTは、問合せを満たす行の実数ではなく、1を戻します。
SQL%ROWCOUNT属性の値はトランザクションの状態には無関係です。したがって、次のようになります。
トランザクションによってセーブポイントへロールバックされても、SQL%ROWCOUNTの値が、以前のセーブポイントの値に戻ることはありません。
自律型トランザクションが終了しても、SQL%ROWCOUNTが親トランザクション内の元の値に戻ることはありません。
例6-4 SQL%ROWCOUNT暗黙カーソル属性
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
SELECT * FROM employees;
DECLARE
mgr_no NUMBER(6) := 122;
BEGIN
DELETE FROM employees_temp WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE
('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/
結果:
Number of employees deleted: 8
明示カーソルは、ユーザーが構築および管理するセッション・カーソルです。明示カーソルを宣言および定義する際には、カーソルに名前を付け、問合せ(通常は複数の行を戻す問合せ)と関連付ける必要があります。その後、次のいずれかの方法で問合せ結果セットを処理できます。
(OPEN文で)明示カーソルをオープンし、(FETCH文で)結果セットから行をフェッチし、(CLOSE文で)明示カーソルをクローズします。
カーソルFOR LOOP文で明示カーソルを使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。
明示カーソルに値を代入したり、明示カーソルを式の中で使用したり、明示カーソルを仮サブプログラム・パラメータやホスト変数として使用することはできません。これらの操作は、カーソル変数を使用することで実行できます(「カーソル変数」を参照)。
明示カーソルまたはカーソル変数は、暗黙カーソルとは異なり、その名前で参照できます。そのため、明示カーソルまたはカーソル変数は、名前付きカーソルと呼ばれます。
ここでのトピック
最初に明示カーソルを宣言して、後で同じブロック、サブプログラムまたはパッケージ内で定義するか、または宣言と定義を同時に行うことができます。
カーソルの宣言のみを行う明示カーソル宣言の構文は、次のとおりです。
CURSOR cursor_name [ parameter_list ] RETURN return_type;
明示カーソル定義の構文は、次のとおりです。
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement;
先にカーソルを宣言した場合、後で明示カーソル定義によってカーソルを定義します。それ以外の場合は、明示カーソルの宣言と定義を同時に行います。
例6-5では、3つの明示カーソルを宣言および定義しています。
関連項目:
明示カーソルの宣言と定義の構文およびセマンティクスの詳細は、「明示カーソルの宣言および定義」を参照してください
例6-5 明示カーソルの宣言および定義
DECLARE
CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2 IS -- Declare and define c2
SELECT employee_id, job_id, salary FROM employees
WHERE salary > 2000;
CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
SELECT * FROM departments -- repeating return type
WHERE department_id = 110;
CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3
CURSOR c3 IS -- Define c3,
SELECT * FROM locations -- omitting return type
WHERE country_id = 'JP';
BEGIN
NULL;
END;
/
明示カーソルを宣言および定義したら、OPEN文でそのカーソルをオープンし、次の操作を実行できます。
問合せを処理するデータベース・リソースを割り当てます。
次のように問合せを処理します。
結果セットを識別します。
問合せが変数またはカーソル・パラメータを参照している場合、結果セットはそれらの値に影響を受けます。詳細は、「明示カーソル問合せの変数」および「パラメータを受け入れる明示カーソル」を参照してください。
問合せにFOR UPDATE句がある場合、結果セットの行をロックします。
詳細は、「SELECT FOR UPDATEカーソルとFOR UPDATEカーソル」を参照してください。
カーソルを結果セットの最初の行の前に配置します。
オープンした明示カーソルは、CLOSE文を使用してクローズすることで、そのリソースを再利用できます。カーソルのクローズ後は、その結果セットからレコードをフェッチしたり、その属性を参照することはできません。これらの操作が試行されると、PL/SQLは事前定義の例外INVALID_CURSORを呼び出します。
クローズしたカーソルは、再オープンできます。明示カーソルは、再オープンする前にクローズする必要があります。それ以外の場合、PL/SQLは事前定義の例外CURSOR_ALREADY_OPENを呼び出します。
明示カーソルをオープンしたら、FETCH文を使用して、問合せ結果セットの行をフェッチできます。1つの行を戻すFETCH文の基本構文は、次のとおりです。
FETCH cursor_name INTO into_clause
into_clauseは、変数のリストまたはシングル・レコード変数です。問合せが戻す各列に対して、変数のリストまたはレコードの中に、対応する型互換の変数またはフィールドが存在している必要があります。%TYPEおよび%ROWTYPE属性は、FETCH文で使用する変数およびレコードを宣言する場合に便利です。
FETCH文は、結果セットの現在の行を取り出し、その行の列値を変数またはレコードに格納して、カーソルを次の行に進めます。
通常、FETCH文はLOOP文の内部で使用し、FETCH文で取り出す行がなくなったら、ループを終了します。この終了条件を検出するには、カーソル属性%NOTFOUNDを使用します(「%NOTFOUND属性: どの行もフェッチされていないかどうか」を参照)。PL/SQLは、FETCH文が行を戻さない場合でも、例外を呼び出しません。
例6-6では、LOOP文の内部でFETCHおよび%NOTFOUNDを使用して、2つの明示カーソルの結果セットから一度に1行ずつ行をフェッチします。最初のFETCH文では、列値を変数に取り出しています。2番目のFETCH文では、列値をレコードに取り出しています。変数とレコードは、それぞれ%TYPEと%ROWTYPEで宣言されています。
例6-7では、それぞれが異なるレコード変数にフェッチする5つのFETCH文を使用して、結果セットの最初の5行を5つのレコードにフェッチします。レコード変数は、%ROWTYPEで宣言されています。
関連項目:
FETCH文の構文およびセマンティクスの詳細は、「FETCH文」を参照してください
一度に複数の行を戻すFETCH文の詳細は、「BULK COLLECT句を使用したFETCH文」を参照してください
例6-6 LOOP文内部のFETCH文
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
CURSOR c2 IS
SELECT * FROM employees
WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
ORDER BY job_id;
v_employees employees%ROWTYPE; -- record variable for row of table
BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1 INTO v_lastname, v_jobid;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
OPEN c2;
LOOP -- Fetches entire row into the v_employees record
FETCH c2 INTO v_employees;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id );
END LOOP;
CLOSE c2;
END;
/
結果:
Atkinson ST_CLERK Bell SH_CLERK Bissot ST_CLERK ... Walsh SH_CLERK ------------------------------------- Higgins AC_MGR Greenberg FI_MGR Hartstein MK_MAN ... Zlotkey SA_MAN
例6-7 異なる変数への同じ明示カーソルのフェッチ
DECLARE
CURSOR c IS
SELECT e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id AND e.manager_id = 100
ORDER BY last_name;
-- Record variables for rows of cursor result set:
job1 c%ROWTYPE;
job2 c%ROWTYPE;
job3 c%ROWTYPE;
job4 c%ROWTYPE;
job5 c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO job1; -- fetches first row
FETCH c INTO job2; -- fetches second row
FETCH c INTO job3; -- fetches third row
FETCH c INTO job4; -- fetches fourth row
FETCH c INTO job5; -- fetches fifth row
CLOSE c;
DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/
結果:
Sales Manager (SA_MAN) Administration Vice President (AD_VP) Sales Manager (SA_MAN) Stock Manager (ST_MAN) Marketing Manager (MK_MAN) PL/SQL procedure successfully completed.
明示カーソル問合せでは、有効範囲内にある任意の変数を参照できます。明示カーソルをオープンすると、PL/SQLによって問合せ内のすべての変数が評価され、結果セットの識別時にそれらの値が使用されます。変数の値を後で変更しても、結果セットは変更されません。
例6-8では、明示カーソル問合せで変数factorを参照しています。カーソルがオープンしたとき、factorの値は2です。したがって、sal_multipleは、各フェッチ後にfactorが増分されても、常にsalの2倍になります。
結果セットを変更するには、例6-9に示すとおり、カーソルをクローズし、変数の値を変更してからカーソルを再度オープンする必要があります。
例6-8 明示カーソル問合せの変数(結果セットの変更なし)
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; BEGIN OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE c1; END; /
結果:
factor = 2 sal = 4400 sal_multiple = 8800 factor = 3 sal = 24000 sal_multiple = 48000 factor = 4 sal = 17000 sal_multiple = 34000 factor = 5 sal = 17000 sal_multiple = 34000
例6-9 明示カーソル問合せの変数(結果セットの変更あり)
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT salary, salary*factor FROM employees
WHERE job_id LIKE 'AD_%';
BEGIN
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1 INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1 INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
END;
/
結果:
factor = 2 sal = 4400 sal_multiple = 8800 sal = 24000 sal_multiple = 48000 sal = 17000 sal_multiple = 34000 sal = 17000 sal_multiple = 34000 factor = 3 sal = 4400 sal_multiple = 13200 sal = 24000 sal_multiple = 72000 sal = 17000 sal_multiple = 51000 sal = 17000 sal_multiple = 51000
明示カーソル問合せに仮想列(式)が含まれる場合、次のいずれかに該当すると、その列には別名が必要です。
カーソルを使用して、%ROWTYPEで宣言されたレコードにフェッチする場合。
仮想列をプログラム内で参照する場合。
例6-10では、前述の両方の理由のため、明示カーソル内の仮想列に別名が必要です。
関連項目:
例6-10 仮想列に別名が必要な明示カーソル
DECLARE
CURSOR c1 IS
SELECT employee_id,
(salary * .05) raise
FROM employees
WHERE job_id LIKE '%_MAN'
ORDER BY employee_id;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
'Raise for employee #' || emp_rec.employee_id ||
' is $' || emp_rec.raise
);
END LOOP;
CLOSE c1;
END;
/
結果:
Raise for employee #114 is $550 Raise for employee #120 is $400 Raise for employee #121 is $410 Raise for employee #122 is $395 Raise for employee #123 is $325 Raise for employee #124 is $368.445 Raise for employee #145 is $700 Raise for employee #146 is $675 Raise for employee #147 is $600 Raise for employee #148 is $550 Raise for employee #149 is $525 Raise for employee #201 is $650
仮パラメータを含む明示カーソルを作成し、オープンするたびにそのカーソルに異なる実パラメータを渡すことができます。カーソル問合せ内では、定数を使用できる場所であればどこでも仮カーソル・パラメータを使用できます。カーソル問合せの外部では、仮カーソル・パラメータは参照できません。
ヒント:
混同を避けるため、仮カーソル・パラメータと実カーソル・パラメータには異なる名前を使用してください。
例6-11では、2つの仮パラメータで職種とその最大給与を表す明示カーソルを作成しています。カーソル問合せは、職種と最大給与を指定してオープンされると、その職種で給与が余分に支払われている従業員を選択します(問合せは、そのような従業員ごとに氏名と超過支払額を選択します)。次に、この例では、カーソル問合せの結果セットを出力するプロシージャを作成しています(プロシージャの詳細は、「PL/SQLサブプログラム」を参照してください)。最後に、この例では、実パラメータの1つのセットを使用したカーソルのオープン、その結果セットの出力、カーソルのクローズ、および異なる実パラメータを使用したカーソルのオープン、その結果セットの出力、カーソルのクローズを行っています。
ここでのトピック
関連項目:
仮カーソル・パラメータの詳細は、「明示カーソルの宣言および定義」を参照してください
実カーソル・パラメータの詳細は、「OPEN文」を参照してください
例6-11 パラメータを受け入れる明示カーソル
DECLARE CURSOR c (job VARCHAR2, max_sal NUMBER) IS SELECT last_name, first_name, (salary - max_sal) overpayment FROM employees WHERE job_id = job AND salary > max_sal ORDER BY salary; PROCEDURE print_overpaid IS last_name_ employees.last_name%TYPE; first_name_ employees.first_name%TYPE; overpayment_ employees.salary%TYPE; BEGIN LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ || ' (by ' || overpayment_ || ')'); END LOOP; END print_overpaid; BEGIN DBMS_OUTPUT.PUT_LINE('----------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:'); DBMS_OUTPUT.PUT_LINE('----------------------'); OPEN c('ST_CLERK', 5000); print_overpaid; CLOSE c; DBMS_OUTPUT.PUT_LINE('-------------------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:'); DBMS_OUTPUT.PUT_LINE('-------------------------------'); OPEN c('SA_REP', 10000); print_overpaid; CLOSE c; END; /
結果:
---------------------- Overpaid Stock Clerks: ---------------------- ------------------------------- Overpaid Sales Representatives: ------------------------------- Vishney, Clara (by 500) Abel, Ellen (by 1000) Ozer, Lisa (by 1500) PL/SQL procedure successfully completed.
仮パラメータを使用して明示カーソルを作成する場合、それらのカーソルにデフォルト値を指定できます。仮パラメータがデフォルト値を持つ場合、対応する実パラメータはオプションになります。実パラメータを指定せずにカーソルをオープンすると、仮パラメータにそのデフォルト値が割り当てられます。
例6-12では、場所IDを表す仮パラメータを持つ明示カーソルを作成しています。このパラメータのデフォルト値は、企業本社の場所IDです。
例6-12 デフォルト値を持つカーソル・パラメータ
DECLARE CURSOR c (location NUMBER DEFAULT 1700) IS SELECT d.department_name, e.last_name manager, l.city FROM departments d, employees e, locations l WHERE l.location_id = location AND l.location_id = d.location_id AND d.department_id = e.department_id ORDER BY d.department_id; PROCEDURE print_depts IS dept_name departments.department_name%TYPE; mgr_name employees.last_name%TYPE; city_name locations.city%TYPE; BEGIN LOOP FETCH c INTO dept_name, mgr_name, city_name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name || ' (Manager: ' || mgr_name || ')'); END LOOP; END print_depts; BEGIN DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT HEADQUARTERS:'); DBMS_OUTPUT.PUT_LINE('--------------------------------'); OPEN c; print_depts; DBMS_OUTPUT.PUT_LINE('--------------------------------'); CLOSE c; DBMS_OUTPUT.PUT_LINE('DEPARTMENTS IN CANADA:'); DBMS_OUTPUT.PUT_LINE('--------------------------------'); OPEN c(1800); -- Toronto print_depts; CLOSE c; OPEN c(1900); -- Whitehorse print_depts; CLOSE c; END; /
結果は次のようになります。
DEPARTMENTS AT HEADQUARTERS: -------------------------------- Administration (Manager: Whalen) Purchasing (Manager: Colmenares) Purchasing (Manager: Baida) Purchasing (Manager: Himuro) Purchasing (Manager: Raphaely) Purchasing (Manager: Khoo) Purchasing (Manager: Tobias) Executive (Manager: Kochhar) Executive (Manager: De Haan) Executive (Manager: King) Finance (Manager: Popp) Finance (Manager: Greenberg) Finance (Manager: Faviet) Finance (Manager: Chen) Finance (Manager: Urman) Finance (Manager: Sciarra) Accounting (Manager: Gietz) Accounting (Manager: Higgins) -------------------------------- DEPARTMENTS IN CANADA: -------------------------------- Marketing (Manager: Hartstein) Marketing (Manager: Fay) PL/SQL procedure successfully completed.
例6-13 既存のカーソルへの仮パラメータの追加
DECLARE
CURSOR c (job VARCHAR2, max_sal NUMBER,
hired DATE DEFAULT TO_DATE('31-DEC-1999', 'DD-MON-YYYY')) IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job
AND salary > max_sal
AND hire_date > hired
ORDER BY salary;
PROCEDURE print_overpaid IS
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
LOOP
FETCH c INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
' (by ' || overpayment_ || ')');
END LOOP;
END print_overpaid;
BEGIN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
DBMS_OUTPUT.PUT_LINE('-------------------------------');
OPEN c('SA_REP', 10000); -- existing reference
print_overpaid;
CLOSE c;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2004:');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
OPEN c('SA_REP', 10000, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
-- new reference
print_overpaid;
CLOSE c;
END;
/
結果:
------------------------------- Overpaid Sales Representatives: ------------------------------- Vishney, Clara (by 500) Abel, Ellen (by 1000) Ozer, Lisa (by 1500) ------------------------------------------------ Overpaid Sales Representatives Hired After 2004: ------------------------------------------------ Vishney, Clara (by 500) Ozer, Lisa (by 1500) PL/SQL procedure successfully completed.
明示カーソル属性値の構文は、cursor_nameのすぐ後にattributeが続きます(たとえば、c1%ISOPEN)。
注意:
明示カーソルとカーソル変数(名前付きカーソル)は、同じ属性を持ちます。このトピックは、特に説明がないかぎり、すべての名前付きカーソルに適用されます。
明示カーソルの属性には次のものがあります。
%ISOPEN属性: カーソルがオープンしているかどうか
%FOUND属性: 1行がフェッチされたかどうか
%NOTFOUND属性: どの行もフェッチされていないかどうか
%ROWCOUNT属性: フェッチされた行数
明示カーソルをオープンしていない場合、%ISOPEN以外の属性を参照すると、事前定義の例外INVALID_CURSORが呼び出されます。
関連項目:
名前付きカーソル(明示カーソルとカーソル変数)の属性の構文およびセマンティクスの詳細は、「名前付きカーソル属性」を参照してください
%ISOPENは、明示カーソルをオープンしている場合はTRUEを、そうでない場合はFALSEを戻します。
%ISOPENは、次の場合に便利です。
明示カーソルをオープンする前にまだオープンしていないことを確認する場合。
すでにオープンしている明示カーソルをオープンしようとすると、PL/SQLによって事前定義の例外CURSOR_ALREADY_OPENが呼び出されます。明示カーソルは、再オープンする前にクローズする必要があります。
注意:
前述の内容は、カーソル変数には適用されません。
明示カーソルをクローズする前にオープンしていることを確認する場合。
例6-14では、明示カーソルc1がオープンしていない場合にのみオープンし、オープンしている場合にのみクローズします。
例6-14 %ISOPEN明示カーソル属性
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
END;
/
%FOUNDは次の値を戻します。
明示カーソルのオープン後、最初のフェッチが実行されるまでは、NULL。
明示カーソルからの最新のフェッチで行が戻された場合は、TRUE。
それ以外の場合は、FALSE
%FOUNDは、処理する必要のあるフェッチ行が存在するかどうかを確認する場合に便利です。
例6-15では、結果セットをループして各フェッチ行を出力し、フェッチする行がなくなると終了します。
例6-15 %FOUND明示カーソル属性
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed
EXIT;
END IF;
END LOOP;
END;
/
結果:
Name = Austin, salary = 4800 Name = De Haan, salary = 17000 Name = Ernst, salary = 6000 Name = Faviet, salary = 9000 Name = Greenberg, salary = 12008 Name = Hunold, salary = 9000 Name = King, salary = 24000 Name = Kochhar, salary = 17000 Name = Lorentz, salary = 4200 Name = Pataballa, salary = 4800
%NOTFOUND(論理的に%FOUNDの逆)は次の値を戻します。
明示カーソルのオープン後、最初のフェッチが実行されるまでは、NULL。
明示カーソルからの最新のフェッチで行が戻された場合は、FALSE。
それ以外の場合は、TRUE
例6-16に示すとおり、%NOTFOUNDは、FETCHが行を戻さなくなったときにループを終了する場合に便利です。
例6-16 %NOTFOUND明示カーソル属性
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE
('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/
結果:
Name = Austin, salary = 4800 Name = De Haan, salary = 17000 Name = Ernst, salary = 6000 Name = Faviet, salary = 9000 Name = Greenberg, salary = 12008 Name = Hunold, salary = 9000 Name = King, salary = 24000 Name = Kochhar, salary = 17000 Name = Lorentz, salary = 4200 Name = Pataballa, salary = 4800
%ROWCOUNTは次の値を戻します。
明示カーソルのオープン後、最初のフェッチが実行されるまでは、0(ゼロ)。
それ以外の場合は、フェッチされた行数(INTEGER)。
注意:
サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11g2以前(またはその逆)の場合、SQL%ROWCOUNTが戻す最大値は4,294,967,295です。
例6-17では、フェッチした行の数をカウントして出力し、5行目のフェッチ後にメッセージを出力します。
例6-17 %ROWCOUNT明示カーソル属性
DECLARE
CURSOR c1 IS
SELECT last_name FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
END IF;
END LOOP;
CLOSE c1;
END;
/
結果:
1. Abel 2. Ande 3. Atkinson 4. Austin 5. Baer --- Fetched 5th row --- 6. Baida 7. Banda 8. Bates 9. Bell 10. Bernstein
PL/SQLでは、従来のデータベース・プログラミングと同様に、カーソルを使用して問合せ結果セットを処理できます。ただし、PL/SQLでは、暗黙カーソルまたは明示カーソルを使用できます。
前者ではコードが削減されますが、後者ではより柔軟な処理が可能です。たとえば、明示カーソルはパラメータを受け入れることができます。
次のPL/SQL文では、PL/SQLによって自動で定義および管理される暗黙カーソルを使用します。
SELECT INTO
暗黙カーソルFOR LOOP
次のPL/SQL文では、明示カーソルを使用します。
明示カーソルFOR LOOP
ユーザーが明示カーソルを定義しますが、文の実行中はPL/SQLが管理します。
OPEN、FETCHおよびCLOSE
ユーザーが明示カーソルを定義および管理します。
注意:
問合せによって行が戻されない場合、PL/SQLによって例外NO_DATA_FOUNDが呼び出されます。
ここでのトピック
関連項目:
クライアントへの結果セットの戻しの詳細は、『Oracle Database開発ガイド』を参照してください。
例外処理の詳細は、「例外ハンドラ」を参照してください
暗黙カーソルを使用して、SELECT INTO文は、1つ以上のデータベース表から値を取り出し(この処理はSQLのSELECT文と同様です)、それらの値を変数に格納します(この処理はSQLのSELECT文では実行されません)。
ここでのトピック
関連項目:
SELECT INTO文の構文およびセマンティクスの詳細は、「SELECT INTO文」を参照してください
問合せで1行のみが戻されると予想される場合、SELECT INTO文を使用して、その行の値を1つ以上のスカラー変数(「SELECT INTO文を使用した変数への値の代入」を参照)または1つのレコード変数(「SELECT INTO文によるレコード変数への行の代入」を参照)に格納します。
問合せで複数の行が戻される可能性があるが、n番目の行のみを処理すればよい場合、WHERE ROWNUM=n句を使用して結果セットを目的の行に制限します。ROWNUM疑似列の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
大量の表データを変数に代入する必要がある場合、BULK COLLECT句付きでSELECT INTO文を使用することをお薦めします。この文は、結果セット全体を1つ以上のコレクション変数に取り出します。詳細は、「BULK COLLECT句を使用したSELECT INTO文」を参照してください。
カーソルFOR LOOP文では、SELECT文を実行してから、すぐに結果セットの行をループできます。
この文では、暗黙カーソルまたは明示カーソルを使用できます(ただしカーソル変数は使用できません)。
SELECT文をカーソルFOR LOOP文でのみ使用する場合、例6-18に示すとおり、カーソルFOR LOOP文の内側にSELECT文を指定します。この形式のカーソルFOR LOOP文は、暗黙カーソルを使用するため、暗黙カーソルFOR LOOP文と呼ばれます。暗黙カーソルは文に対して内部的であるため、SQLという名前で参照することはできません。
同じPL/SQLユニットでSELECT文を複数回使用する場合、例6-19に示すとおり、そのユニットで明示カーソルを定義し、カーソルFOR LOOP文にそのカーソルを指定します。この形式のカーソルFOR LOOP文は、明示カーソルFOR LOOP文と呼ばれます。同じPL/SQLユニット内の別の場所でも同じ明示カーソルを使用できます。
カーソルFOR LOOP文は、そのカーソルが戻す型の%ROWTYPEレコード変数として暗黙的にループ索引を宣言します。このレコードは、ループに対してローカルで、ループの実行中のみ存在します。ループの内側の文は、レコードとそのフィールドを参照できます。例6-21に示すように、仮想列は別名でのみ参照できます。
ループ索引のレコード変数の宣言後、FOR LOOP文によって指定したカーソルがオープンされます。ループが反復されるたびに、FOR LOOP文によって結果セットから行がフェッチされ、レコードに格納されます。フェッチする行がなくなると、カーソルFOR LOOP文によってカーソルがクローズされます。また、ループの内側の文によって制御がループの外側に移された場合や、PL/SQLによって例外が呼び出された場合も、カーソルがクローズされます。
関連項目:
カーソルFOR LOOP文の構文およびセマンティクスの詳細は、「カーソルFOR LOOP文」を参照してください
注意:
カーソルFOR LOOP文の内側で例外が呼び出されると、カーソルは、例外ハンドラが実行される前にクローズされます。したがって、ハンドラでは明示カーソルの属性の値を参照できません。
例6-18 暗黙カーソルFOR LOOP文
この例では、暗黙カーソルFOR LOOP文で、担当マネージャのIDが120より大きいすべての事務員の名前および職種IDを出力します。
BEGIN
FOR item IN (
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%'
AND manager_id > 120
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
結果:
Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK
例6-19 明示カーソルFOR LOOP文
この例は、例6-18と類似していますが、明示カーソルFOR LOOP文を使用しています。
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
結果:
Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK
例6-20 明示カーソルFOR LOOP文へのパラメータの引渡し
この例では、2つのパラメータを受け入れる明示カーソルを宣言および定義し、明示カーソルFOR LOOP文でそのカーソルを使用して、指定された部門内で給与が指定額を超える従業員の給与を示します。
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('ST_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; /
結果:
Name = Nayer, salary = 3200, Job Id = ST_CLERK Name = Bissot, salary = 3300, Job Id = ST_CLERK Name = Mallin, salary = 3300, Job Id = ST_CLERK Name = Ladwig, salary = 3600, Job Id = ST_CLERK Name = Stiles, salary = 3200, Job Id = ST_CLERK Name = Rajs, salary = 3500, Job Id = ST_CLERK Name = Davies, salary = 3100, Job Id = ST_CLERK
例6-21 カーソルFOR LOOPによる仮想列の参照
この例では、暗黙カーソルFOR LOOPで、仮想列をその別名full_nameおよびdream_salaryで参照しています。
BEGIN
FOR item IN (
SELECT first_name || ' ' || last_name AS full_name,
salary * 10 AS dream_salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY dream_salary DESC, last_name ASC
) LOOP
DBMS_OUTPUT.PUT_LINE
(item.full_name || ' dreams of making ' || item.dream_salary);
END LOOP;
END;
/
結果:
Stephen King dreams of making 240000 Lex De Haan dreams of making 170000 Neena Kochhar dreams of making 170000 Alexander Hunold dreams of making 90000 Bruce Ernst dreams of making 60000
問合せ結果セットの処理を完全に制御するには、明示カーソルを宣言し、それらのカーソルをOPEN文、FETCH文およびCLOSE文を使用して管理します。
この結果セットの処理方法は、他の方法より複雑ですが、より柔軟な処理が可能です。たとえば、次のことが可能です。
複数のカーソルを使用して、複数の結果セットをパラレルに処理できます。
1回のループで複数の行を処理したり、行をスキップしたり、処理を複数のループに分割することができます。
あるPL/SQLユニットで問合せを指定し、別のユニットで行を取り出すことができます。
詳細および例は、「明示カーソル」を参照してください。
問合せ結果セットを処理する際に、その結果セットをループして行ごとに別の問合せを実行する場合、ループの内側から2番目の問合せを削除し、その問合せを最初の問合せの副問合せにすると、パフォーマンスを向上できます。
通常の副問合せが表ごとに評価されるのに対し、相関副問合せは行ごとに評価されます。
副問合せの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例6-22 親問合せのFROM句内の副問合せ
この例では、FROM句に副問合せを含む問合せを使用して明示カーソルc1を定義しています。
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
ORDER BY staff;
BEGIN
FOR dept IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Department = '
|| dept.department_name || ', staff = ' || dept.staff);
END LOOP;
END;
/
結果:
Department = IT, staff = 5 Department = Finance, staff = 6 Department = Purchasing, staff = 6 Department = Sales, staff = 34 Department = Shipping, staff = 45
例6-23 相関副問合せ
この例では、給与が部門平均を上回っている従業員の名前と給与を戻しています。相関副問合せでは、表の各行について、対応する部門の平均給与を計算します。
DECLARE
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, last_name;
BEGIN
FOR person IN c1
LOOP
DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
END LOOP;
END;
/
結果:
Making above-average salary = Hartstein Making above-average salary = Raphaely Making above-average salary = Bell ... Making above-average salary = Higgins
カーソル変数は、明示カーソルと似ていますが、次の点が異なります。
1つの問合せに限定されません。
問合せのカーソル変数をオープンし、結果セットを処理し、そのカーソル変数を別の問合せに使用できます。
値を代入できます。
式で使用できます。
サブプログラム・パラメータとして使用できます。
カーソル変数は、サブプログラム間で問合せ結果セットを渡すために使用できます。
ホスト変数として使用できます。
カーソル変数は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果セットを渡すために使用できます。
パラメータを受け入れることはできません。
カーソル変数にパラメータを渡すことはできませんが、問合せ全体を渡すことができます。問合せには変数を含めることができます。
カーソル変数がこのような柔軟性を持つ理由は、それがポインタであるため(その値が項目自体ではなく項目のアドレスを示すため)です。
カーソル変数を参照する前に、カーソル変数をオープンするか、オープンされたPL/SQLカーソル変数またはホスト・カーソル変数の値をカーソル変数に代入することで、SQL作業領域を指すようにする必要があります。
注意:
カーソル変数と明示カーソルには互換性がありません(一方の値が期待されている場所で、もう一方は使用できません)。たとえば、カーソルFOR LOOP文の中ではカーソル変数を参照できません。
ここでのトピック
関連項目:
明示カーソルの詳細は、「明示カーソル」を参照してください
カーソル変数のその他のメリットの詳細は、『Oracle Database開発ガイド』を参照してください
カーソル変数のデメリットの詳細は、『Oracle Database開発ガイド』を参照してください。
カーソル変数を作成するには、事前定義のSYS_REFCURSOR型の変数を宣言するか、REF CURSOR型を定義してからその型の変数を宣言します。
注意:
カーソル変数は、通称でREF CURSORと呼ばれることがあります。
REF CURSOR型の定義の基本構文は、次のとおりです。
TYPE type_name IS REF CURSOR [ RETURN return_type ]
(構文およびセマンティクスの詳細は、「カーソル変数の宣言」を参照してください。)
return_typeを指定する場合、REF CURSOR型とその型のカーソル変数は、強い型になりますが、指定しない場合、それらは弱い型になります。SYS_REFCURSORとその型のカーソル変数は、弱い型です。
強いカーソル変数には、指定した型を戻す問合せのみを関連付けることができます。弱いカーソル変数には、任意の問合せを関連付けることができます。
弱いカーソル変数は、強いカーソル変数よりエラーの発生する可能性が高まりますが、より柔軟な処理が可能です。弱いREF CURSOR型は、相互に互換性があり、事前定義のSYS_REFCURSOR型とも互換性があります。弱いカーソル変数の値は、他の弱いカーソル変数に代入できます。
強いカーソル変数の値を別の強いカーソル変数に代入できるのは、(戻り型が同じである以外に)両方のカーソル変数の型が同じである場合のみです。
注意:
テーブル・ファンクションの弱いカーソル変数引数をパーティション化するには、PARTITION BY RANGEやPARTITION BY HASHではなく、PARTITION BY ANY句のみを使用します。構文およびセマンティクスについては、「parallel_enable_clause ::=」および「parallel_enable_clause」を参照してください。
例6-24では、強い型と弱い型のREF CURSOR、それらの型の変数、および事前定義のSYS_REFCURSOR型の変数を定義しています。
例6-25では、return_typeは、ユーザー定義のRECORD型です。
例6-24 カーソル変数の宣言
DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong type TYPE genericcurtyp IS REF CURSOR; -- weak type cursor1 empcurtyp; -- strong cursor variable cursor2 genericcurtyp; -- weak cursor variable my_cursor SYS_REFCURSOR; -- weak cursor variable TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; -- strong type dept_cv deptcurtyp; -- strong cursor variable BEGIN NULL; END; /
例6-25 ユーザー定義の戻り型を持つカーソル変数
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; BEGIN NULL; END; /
カーソル変数を宣言したら、OPEN FOR文でその変数をオープンし、次の操作を実行できます。
カーソル変数を問合せに関連付けます(通常、この問合せで複数の行が戻されます)。
問合せには、バインド変数用のプレースホルダを含め、その値をOPEN FOR文のUSING句で指定できます。
問合せを処理するデータベース・リソースを割り当てます。
次のように問合せを処理します。
結果セットを識別します。
問合せが変数を参照している場合、結果セットはそれらの値に影響を受けます。詳細は、「カーソル変数問合せの変数」を参照してください。
問合せにFOR UPDATE句がある場合、結果セットの行をロックします。
詳細は、「SELECT FOR UPDATEカーソルとFOR UPDATEカーソル」を参照してください。
カーソルを結果セットの最初の行の前に配置します。
カーソル変数は、再オープンする(別のOPEN FOR文で使用する)前にクローズする必要はありません。カーソル変数を再オープンすると、前にその変数に関連付けられていた問合せは失われます。
カーソル変数が必要なくなったら、CLOSE文を使用してクローズすることで、そのリソースを再利用できます。カーソル変数のクローズ後は、その結果セットからレコードをフェッチしたり、その属性を参照することはできません。これらの操作が試行されると、PL/SQLは事前定義の例外INVALID_CURSORを呼び出します。
クローズしたカーソル変数は、再オープンできます。
関連項目:
「OPEN FOR文の構文およびセマンティクス」の詳細は、「OPEN FOR文」を参照してください
CLOSE文の構文およびセマンティクスの詳細は、「CLOSE文」を参照してください
カーソル変数をオープンしたら、FETCH文を使用して、問合せ結果セットの行をフェッチできます。
カーソル変数の戻り型は、FETCH文のinto_clauseと互換性を持つ必要があります。カーソル変数が強い型の場合、PL/SQLはコンパイル時に互換性がないことを捕捉します。カーソル変数が弱い型の場合、PL/SQLは実行時に互換性がないことを捕捉し、最初のフェッチの前に事前定義の例外ROWTYPE_MISMATCHを呼び出します。
関連項目:
FETCH文の構文およびセマンティクスの詳細は、「FETCH文」を参照してください
一度に複数の行を戻すFETCH文の詳細は、「BULK COLLECT句を使用したFETCH文」を参照してください
例6-26 カーソル変数によるデータのフェッチ
この例では、例6-6で2つの明示カーソルを使用して処理していたことを、1つのカーソル変数を使用して処理しています。最初のOPEN FOR文には、問合せ自体が含まれます。2番目のOPEN FOR文は、その値が問合せである変数を参照します。
DECLARE cv SYS_REFCURSOR; -- cursor variable v_lastname employees.last_name%TYPE; -- variable for last_name v_jobid employees.job_id%TYPE; -- variable for job_id query_2 VARCHAR2(200) := 'SELECT * FROM employees WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'') ORDER BY job_id'; v_employees employees%ROWTYPE; -- record variable row of table BEGIN OPEN cv FOR SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK') ORDER BY last_name; LOOP -- Fetches 2 columns into variables FETCH cv INTO v_lastname, v_jobid; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid ); END LOOP; DBMS_OUTPUT.PUT_LINE( '-------------------------------------' ); OPEN cv FOR query_2; LOOP -- Fetches entire row into the v_employees record FETCH cv INTO v_employees; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id ); END LOOP; CLOSE cv; END; /
結果:
Atkinson ST_CLERK Bell SH_CLERK Bissot ST_CLERK ... Walsh SH_CLERK ------------------------------------- Higgins AC_MGR Greenberg FI_MGR Hartstein MK_MAN ... Zlotkey SA_MAN
例6-27 カーソル変数からコレクションへのフェッチ
この例では、FETCH文のBULK COLLECT句を使用して、カーソル変数から2つのコレクション(ネストした表)にフェッチします。
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'
ORDER BY salary DESC;
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;
/
結果:
Name = Ozer, salary = 11500 Name = Abel, salary = 11000 Name = Vishney, salary = 10500 ... Name = Kumar, salary = 6100
PL/SQLカーソル変数に、別のPL/SQLカーソル変数またはホスト・カーソル変数の値を代入できます。
次に構文を示します。
target_cursor_variable := source_cursor_variable;
source_cursor_variableがオープンしている場合、代入後にtarget_cursor_variableもオープンします。2つのカーソル変数は、同じSQL作業領域を指します。
source_cursor_variableがオープンしていない場合、代入後にtarget_cursor_variableがオープンしても、source_cursor_variableはオープンしません。
カーソル変数に関連付けられた問合せでは、有効範囲内にある任意の変数を参照できます。
OPEN FOR文を使用してカーソル変数をオープンすると、PL/SQLによって問合せ内のすべての変数が評価され、結果セットの識別時にそれらの値が使用されます。変数の値を後で変更しても、結果セットは変更されません。
結果セットを変更するには、例6-29に示すとおり、変数の値を変更してから同じ問合せでカーソル変数を再度オープンする必要があります。
例6-28 カーソル変数問合せの変数(結果セットの変更なし)
この例では、値2を持つ変数factorを参照する問合せのカーソル変数をオープンしています。したがって、sal_multipleは、各フェッチ後にfactorが増分されても、常にsalの2倍になります。
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE cv; END; /
結果:
factor = 2 sal = 4400 sal_multiple = 8800 factor = 3 sal = 24000 sal_multiple = 48000 factor = 4 sal = 17000 sal_multiple = 34000 factor = 5 sal = 17000 sal_multiple = 34000
例6-29 カーソル変数問合せの変数(結果セットの変更あり)
DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; factor := factor + 1; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN cv FOR SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; CLOSE cv; END; /
結果:
factor = 2 sal = 4400 sal_multiple = 8800 sal = 24000 sal_multiple = 48000 sal = 17000 sal_multiple = 34000 sal = 17000 sal_multiple = 34000 factor = 3 sal = 4400 sal_multiple = 13200 sal = 24000 sal_multiple = 72000 sal = 17000 sal_multiple = 51000 sal = 17000 sal_multiple = 51000
次のすべてが該当する場合、コレクションを問い合せることができます。
コレクションのデータ型が、スキーマ・レベルで作成されているか、またはパッケージ仕様部で宣言されている。
コレクション要素のデータ型が、スカラー・データ型、ユーザー定義のデータ型またはレコード型のいずれかである。
問合せのFROM句では、コレクションはtable_collection_expressionにTABLE演算子の引数として指定されます。
注意:
SQLコンテキストでは、戻り型がパッケージ仕様部で宣言されているファンクションを使用できません。
関連項目:
table_collection_expressionの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
CREATE PACKAGE文の詳細は、「CREATE PACKAGE文」を参照してください
コレクション型およびコレクション変数の詳細は、「PL/SQLのコレクションおよびレコード」を参照してください
例7-9、システム固有の動的SQLを使用したコレクションの問合せ
例6-30 静的SQLを使用したコレクションの問合せ
この例では、カーソル変数がレコードの連想配列の問合せに関連付けられています。ネストした表型mytabは、パッケージ仕様部で宣言されています。
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS TYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30)); TYPE mytab IS TABLE OF rec INDEX BY pls_integer; END; DECLARE v1 pkg.mytab; -- collection of records v2 pkg.rec; c1 SYS_REFCURSOR; BEGIN v1(1).f1 := 1; v1(1).f2 := 'one'; OPEN c1 FOR SELECT * FROM TABLE(v1); FETCH c1 INTO v2; CLOSE c1; DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2); END; /
結果:
Values in record are 1 and one
カーソル変数は明示カーソルと同じ属性を持ちます(「明示カーソル属性」を参照)。カーソル変数の属性値の構文は、cursor_variable_nameのすぐ後にattributeが続きます(たとえば、cv%ISOPEN)。カーソル変数をオープンしていない場合、%ISOPEN以外の属性を参照すると、事前定義の例外INVALID_CURSORが呼び出されます。
カーソル変数は、サブプログラム・パラメータとして使用することができ、この使用方法は、サブプログラム間で問合せ結果を渡す場合に便利です。
次に例を示します。
カーソル変数をあるサブプログラムでオープンし、別のサブプログラムで処理できます。
多言語アプリケーションでは、PL/SQLサブプログラムは、カーソル変数を使用して、異なる言語で記述されたサブプログラムに結果セットを戻すことができます。
注意:
起動元と起動先のサブプログラムは、同じデータベース・インスタンス内に存在する必要があります。データベース・リンクを介して起動されたサブプログラムに、カーソル変数を渡したり、戻すことはできません。
注意:
カーソル変数はポインタであるため、カーソル変数をサブプログラム・パラメータとして使用すると、サブプログラム・パラメータのエイリアシングの可能性が高くなり、意図しない結果が発生することがあります。詳細は、「カーソル変数パラメータによるサブプログラムのパラメータのエイリアシング」を参照してください。
カーソル変数をサブプログラムの仮パラメータとして宣言する場合、次のことに注意してください。
サブプログラムがカーソル変数をオープンするか、カーソル変数に値を代入する場合、パラメータ・モードはIN OUTである必要があります。
サブプログラムがカーソル変数からフェッチするか、カーソル変数をクローズするのみである場合、パラメータ・モードはINまたはIN OUTのどちらでも可能です。
対応するカーソル変数の仮パラメータと実パラメータの戻り型には、互換性が必要です。それ以外の場合、PL/SQLは事前定義の例外ROWTYPE_MISMATCHを呼び出します。
異なるPL/SQLユニットのサブプログラム間でカーソル変数パラメータを渡すには、パッケージでパラメータのREF CURSOR型を定義します。型がパッケージに含まれる場合、複数のサブプログラムでその型を使用できます。あるサブプログラムでその型の仮パラメータを宣言し、別のサブプログラムでその型の変数を宣言して、それらの変数を最初のサブプログラムに渡すことができます。
関連項目:
サブプログラムのパラメータの詳細は、「サブプログラムのパラメータ」を参照してください
仮カーソル変数パラメータで実パラメータとして使用できるCURSOR式の詳細は、「CURSOR式」を参照してください
パッケージの詳細は、「PL/SQLパッケージ」を参照してください
例6-31 1つの問合せ用にカーソル変数をオープンするプロシージャ
この例では、REF CURSOR型と、その型のカーソル変数パラメータをオープンするプロシージャをパッケージで定義しています。
CREATE OR REPLACE PACKAGE emp_data AUTHID DEFINER AS TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp); END emp_data; / CREATE OR REPLACE 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; /
例6-32 選択された問合せ用のカーソル変数のオープン(同じ戻り型)
この例では、ストアド・プロシージャが選択された問合せ用にカーソル変数パラメータをオープンします。問合せには、同じ戻り型が含まれます。
CREATE OR REPLACE PACKAGE emp_data AUTHID DEFINER AS TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT); END emp_data; / CREATE OR REPLACE 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-33 選択された問合せ用のカーソル変数のオープン(異なる戻り型)
この例では、ストアド・プロシージャが選択された問合せ用にカーソル変数パラメータをオープンします。問合せには、異なる戻り型が含まれます。
CREATE OR REPLACE PACKAGE admin_data AUTHID DEFINER AS TYPE gencurtyp IS REF CURSOR; PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT); END admin_data; / CREATE OR REPLACE 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; /
カーソル変数は、ホスト変数として使用することができ、この使用方法は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果を渡す場合に便利です。
カーソル変数がホスト変数の場合、PL/SQLとクライアント(ホスト環境)は、結果セットを格納するSQL作業領域に対するポインタを共有します。
カーソル変数をホスト変数として使用するには、ホスト環境内でカーソル変数を宣言し、その変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。ホスト・カーソル変数は、(弱いPL/SQLカーソル変数と同様に)すべての問合せの戻り型と互換性があります。
カーソル変数の値を1つの有効範囲から別の有効範囲に渡したとしても、SQL作業領域は、それを指すカーソル変数が存在するかぎりアクセス可能です。たとえば、例6-34で、Pro*Cプログラムはホスト・カーソル変数を埋込みPL/SQL無名ブロックに渡しています。ブロックの実行後も、カーソル変数はSQL作業領域を指しています。
クライアント側にPL/SQLエンジンがあれば、クライアントからサーバーへのコールに課される制限はありません。たとえば、クライアント側でカーソル変数を宣言し、それをサーバー側でオープンしてフェッチした後で、クライアント側で引き続きフェッチすることができます。また、複数のホスト・カーソル変数を1回の往復でオープンまたはクローズするPL/SQL無名ブロックを使用することで、ネットワークの通信量を削減できます。次に例を示します。
/* PL/SQL anonymous 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; /
PL/SQL無名ブロックの実行後もカーソル変数はSQL作業領域を指しているため、クライアント・プログラムではそれらの作業領域を使用できます。クライアント・プログラムでカーソルが必要なくなった場合、PL/SQL無名ブロックを使用してそれらのカーソルをクローズできます。次に例を示します。
/* PL/SQL anonymous block in host environment */ BEGIN CLOSE :emp_cv; CLOSE :dept_cv; CLOSE :loc_cv; END; /
この方法は、Oracle Formsと同様に、マルチブロック・フォームにデータを移入する場合に便利です。たとえば、次のように1回の往復で複数のSQL作業領域をオープンできます。
/* PL/SQL anonymous block in host environment */ BEGIN OPEN :c1 FOR SELECT 1 FROM DUAL; OPEN :c2 FOR SELECT 1 FROM DUAL; OPEN :c3 FOR SELECT 1 FROM DUAL; END; /
注意:
ホスト・カーソル変数をOracle Call Interface(OCI)クライアントからPL/SQLにバインドすると、同じサーバー・コールで変数をオープンしないかぎり、サーバー側の変数からフェッチすることはできません。
例6-34 Pro*Cクライアント・プログラムにおけるホスト変数としてのカーソル変数
この例では、Pro*Cクライアント・プログラムでカーソル変数と選択子を宣言し、それらをホスト変数としてPL/SQL無名ブロックに渡すことで、選択した問合せ用にカーソル変数をオープンしています。
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR generic_cv; -- Declare host cursor variable.
int choice; -- Declare selector.
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE :generic_cv; -- Initialize host cursor variable.
-- 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;
CURSOR式は、ネストしたカーソルを戻します。
構文は次のとおりです。
CURSOR ( subquery )
CURSOR式は、副問合せではないSELECT文で使用するか(例6-35を参照)、カーソル変数パラメータを受け入れるファンクションに渡すことができます(「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」を参照)。カーソル式は、暗黙カーソルとは併用できません。
関連項目:
制限などのCURSOR式の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例6-35 CURSOR式
この例では、カーソル式を含む問合せで明示カーソルを宣言および定義しています。departments表の部門ごとに、ネストしたカーソルはその部門の各従業員の名前を戻します(employees表から取得します)。
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,
CURSOR ( SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id
ORDER BY e.last_name
) employees
FROM departments d
WHERE department_name LIKE 'A%'
ORDER BY department_name;
BEGIN
OPEN c1;
LOOP -- Process each row of query 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 result set
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;
/
結果:
Department: Accounting -- Employee: Gietz -- Employee: Higgins Department: Administration -- Employee: Whalen
トランザクション処理はOracle Databaseの機能で、複数のユーザーがデータベース上で同時に作業することができ、各ユーザーが参照するデータのバージョンに一貫性があり、すべての変更が正しい順序で適用されることが保証されます。
トランザクションは、Oracle Databaseが1つの単位として処理する連続した1つ以上のSQL文です(これらの文は、すべて実行されるか、1つも実行されないかのいずれかです)。
Oracle Databaseによってデータ構造が自動的にロックされるため、異なるユーザーが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。データの可用性を最大にするため、Oracle Databaseでは必要最小限のデータが最小限の時間のみロックされます。
複数のユーザーがデータに同時にアクセスすることによって発生する問題を防ぐために、追加のコードを記述する必要はほとんどありません。ただし、このレベルの制御が必要な場合は、Oracle Databaseのデフォルトのロック・メカニズムを手動で上書きできます。
ここでのトピック
関連項目:
トランザクションの詳細は、『Oracle Database概要』を参照してください。
トランザクション処理の詳細は、『Oracle Database概要』を参照してください
Oracle Databaseのロック・メカニズムの詳細は、『Oracle Database概要』を参照してください
手動のデータ・ロックの詳細は、『Oracle Database概要』を参照してください
COMMIT文は、カレント・トランザクションを終了し、その変更を永続的なものにして、他のユーザーから参照できるようにします。
注意:
トランザクションは複数のブロックにまたがることができ、ブロックは複数のトランザクションを含むことができます。
COMMIT文のWRITE句では、コミット操作によって生成される情報をOracle DatabaseがREDOログに書き込む場合の優先順位を指定します。
注意:
データベース初期化パラメータCOMMIT_LOGGINGおよびCOMMIT_WAITが設定されていない場合、非分散トランザクションに対するPL/SQLのデフォルトのコミット動作はBATCH NOWAITです。
関連項目:
トランザクションのコミットの詳細は、『Oracle Database概要』を参照してください。
分散トランザクションの詳細は、『Oracle Database概要』を参照してください。
COMMIT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
スタンバイ・データベースへのフェイルオーバー中にデータを消失させないようにする方法については、『Oracle Data Guard概要および管理』を参照してください。
例6-36 COMMENT句およびWRITE句を使用するCOMMIT文
この例では、トランザクションによって銀行口座の間で振替えを実行しています。一方の口座から現金が出金され、同時に他方の口座に現金が入金されることが重要であるため、COMMIT WRITE IMMEDIATE NOWAIT文を使用しています。
DROP TABLE accounts; CREATE TABLE accounts ( account_id NUMBER(6), balance NUMBER (10,2) ); INSERT INTO accounts (account_id, balance) VALUES (7715, 6350.00); INSERT INTO accounts (account_id, balance) VALUES (7720, 5100.50); CREATE OR REPLACE PROCEDURE transfer ( from_acct NUMBER, to_acct NUMBER, amount NUMBER ) AUTHID CURRENT_USER AS BEGIN UPDATE accounts SET balance = balance - amount WHERE account_id = from_acct; UPDATE accounts SET balance = balance + amount WHERE account_id = to_acct; COMMIT WRITE IMMEDIATE NOWAIT; END; /
振替え前の問合せ:
SELECT * FROM accounts;
結果:
ACCOUNT_ID BALANCE
---------- ----------
7715 6350
7720 5100.5
BEGIN
transfer(7715, 7720, 250);
END;
/
振替え後の問合せ:
SELECT * FROM accounts;
結果:
ACCOUNT_ID BALANCE
---------- ----------
7715 6100
7720 5350.5
ROLLBACK文は、カレント・トランザクションを終了し、トランザクション中に加えられたすべての変更を取り消します。
表からの間違った行の削除などの誤操作を行った場合に、ロールバックは元のデータをリストアできます。SQL文が失敗したりPL/SQLが例外を呼び出したためにトランザクションを終了できない場合は、ロールバックを使用すると、対処措置を行い、実行しなおすことができます。
関連項目:
ROLLBACK文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例6-37 ROLLBACK文
この例では、3つの異なる表に従業員に関する情報を挿入しています。INSERT文で重複する従業員番号を格納すると、PL/SQLによって事前定義の例外DUP_VAL_ON_INDEXが呼び出されます。3つの表すべてに対する変更が確実に取り消されるようにするため、例外ハンドラがROLLBACKを実行します。
DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name FROM employees; CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id); DROP TABLE emp_sal; CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees; CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id); DROP TABLE emp_job; 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 (employee_id, last_name) VALUES (emp_id, emp_lastname); INSERT INTO emp_sal (employee_id, salary) VALUES (emp_id, emp_salary); INSERT INTO emp_job (employee_id, job_id) VALUES (emp_id, emp_jobid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Inserts were rolled back'); END; /
SAVEPOINT文は、トランザクション処理内の現在位置に名前とマークを付けます。
セーブポイントを使用すると、トランザクション全体をロールバックするのではなく、トランザクションの一部をロールバックできます。セッションごとのアクティブなセーブポイントの数には、制限がありません。
あるセーブポイントまでロールバックすると、そのセーブポイント以降にマークされたセーブポイントはすべて消去されます。ロールバック先のセーブポイントは消去されません。単純なロールバックまたはコミットではすべてのセーブポイントが消去されます。
再帰的サブプログラムの中でセーブポイントをマークすると、再帰しながら進む過程で、各レベルでSAVEPOINT文の新しいインスタンスが実行されます。ただし、ロールバックできるのは直前にマークされたセーブポイントまでです。
セーブポイント名は未宣言の識別子です。トランザクションでセーブポイント名を再利用すると、セーブポイントはトランザクションの中の古い位置から現在の位置に移動します(つまり、セーブポイントへのロールバックは、トランザクションの現在の部分のみに影響を与えます)。
関連項目:
SQLのSET TRANSACTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例6-38 SAVEPOINT文およびROLLBACK文
この例では、挿入する前にセーブポイントをマークしています。INSERT文でemployee_id列に重複した値を格納しようとすると、PL/SQLによって事前定義の例外DUP_VAL_ON_INDEXが呼び出され、トランザクションがセーブポイントまでロールバックして、INSERT文のみを取り消します。
DROP TABLE emp_name; 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 (employee_id, last_name, salary) 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; /
例6-39 ROLLBACKでのSAVEPOINTの再使用
DROP TABLE emp_name; 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; INSERT INTO emp_name (employee_id, last_name, salary) 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; /
INSERT文、UPDATE文、DELETE文またはMERGE文の実行前に、データベースによって(ユーザーが利用できない)暗黙的なセーブポイントがマークされます。文が正常に実行されなかった場合は、データベースによってこのセーブポイントまでロールバックが行われます。
通常は、トランザクション全体ではなく、失敗したSQL文のみがロールバックされます。その文が原因で未処理例外が呼び出された場合は、ホスト環境によってロールバックの対象が決まります。
データベースでは、デッドロックを解消するためにSQL文を1文のみロールバックすることもできます。データベースによって、関係しているトランザクションにエラーが戻され、そのトランザクション中の現在の文がロールバックされます。
SQL文を実行する前に、データベースはその文を解析する必要があります(すなわち、その文が構文規則に従っているかどうかや、有効なスキーマ・オブジェクトを参照しているかどうかを確認する必要があります)。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。
ストアド・サブプログラムを未処理例外で終了すると、PL/SQLは値をOUTパラメータに代入せず、ロールバックを実行しません。
例外処理の詳細は、「PL/SQLのエラー処理」を参照してください
SET TRANSACTION文を使用すると、読取り専用または読取り/書込みトランザクションの開始、分離レベルの確立、指定したロールバック・セグメントへのカレント・トランザクションの代入を実行できます。
読取り専用トランザクションは、他のユーザーが更新している表に対して複数の問合せを実行する場合に便利です。
読取り専用トランザクションでは、複数の表と複数の問合せで構成された読取り一貫性のあるビューが作成され、すべての問合せがデータベースの同一のスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。コミットまたはロールバックするとトランザクションが終了します。
SET TRANSACTION文は、読取り専用トランザクションの最初のSQL文である必要があり、1つのトランザクションで1回しか使用できません。トランザクションをREAD ONLYに設定すると、それ以降の問合せからはトランザクションの開始前にコミットされた変更内容しか見えません。READ ONLYを使用しても、他のユーザーや他のトランザクションには影響がありません。
読取り専用トランザクションに使用できるのは、SELECT、OPEN、FETCH、CLOSE、LOCK TABLE、COMMITおよびROLLBACK文のみです。問合せはFOR UPDATEにはできません。
関連項目:
SQLのSET TRANSACTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例6-40 読取り専用トランザクションでのSET TRANSACTION文
この例では、読取り専用トランザクションによって、当日、先週および先月の注文総数を収集しています。トランザクションの途中で他のユーザーがデータベースを更新しても、注文総数には影響がありません。orders表は、サンプル・スキーマOEにあります。
DECLARE daily_order_total NUMBER(12,2); weekly_order_total NUMBER(12,2); monthly_order_total NUMBER(12,2); BEGIN COMMIT; -- end 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; /
デフォルトでは、Oracle Databaseによってデータ構造が自動的にロックされるため、異なるアプリケーションが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。
トランザクションの間にデータへの排他アクセスが必要な場合、次のSQL文を使用してデフォルトのロックを上書きできます。
LOCK TABLEは、表全体を明示的にロックします。
SELECTをFOR UPDATE句とともに使用すると(SELECT FOR UPDATE)、表の特定の行を明示的にロックできます。
ここでのトピック
LOCK TABLE文は、1つ以上の表を指定されたロック・モードで明示的にロックするため、それらの表へのアクセスを共有または拒否できます。
ロック・モードによって、表に対して他にどのようなロックを使用できるかが決まります。たとえば、1つの表に対して多くのユーザーが同時に行共用ロックを取得できますが、排他ロックを取得できるのは一度に1人のユーザーのみです。あるユーザーが表に対して排他ロックをかけていると、他のユーザーはその表に対して行の挿入、更新、削除を実行できません。
表がロックされていても、他のユーザーは表に対して問合せできますが、問合せを実行しても表のロックを取得できません。2つの異なるトランザクションが同じ行を変更した場合のみ、一方のトランザクションがもう一方のトランザクションの終了を待ちます。LOCK TABLE文を使用すると、別のトランザクションが完了するまでの待機時間を指定できます。
表ロックは、それらを取得したトランザクションがコミットまたはロールバックされる際に解除されます。
関連項目:
表の明示的なロックの詳細は、『Oracle Database開発ガイド』を参照してください。
LOCK TABLE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
FOR UPDATE句を伴うSELECT文(SELECT FOR UPDATE文)では、結果セットの行が選択され、それらがロックされます。SELECT FOR UPDATEを使用すると、行の中の既存の値に基づいて更新できます(これは、更新前に、他のユーザーがそれらの値を変更できないことが保証されるためです)。また、SELECT FOR UPDATEを使用すると、例9-6のように、更新する必要のない行をロックできます。
注意:
ハイブリッド列圧縮(HCC)で圧縮された表では、DML文によって行ではなく圧縮単位がロックされます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。
デフォルトでは、SELECT FOR UPDATE文は、要求された行ロックが取得されるまで待機します。この動作を変更するには、SELECT FOR UPDATE文のNOWAIT、WAITまたはSKIP LOCKED句を使用します。これらの句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT FOR UPDATEが明示カーソルと関連付けられる場合、そのカーソルはFOR UPDATEカーソルと呼ばれます。UPDATE文またはDELETE文のCURRENT OF句には、FOR UPDATEカーソルのみを使用できます。(CURRENT OF句は、SQL文UPDATEおよびDELETEのWHERE句に対するPL/SQLの拡張機能であり、文をカーソルの現在の行に制限します。)
SELECT FOR UPDATEで複数の表の問合せを実行する場合、列がFOR UPDATE句に現れる行のみがロックされます。
結果セットの行は、FOR UPDATEカーソルをオープンするときにロックされるのであり、フェッチされるときにロックされるのではありません。また、トランザクションをコミットまたはロールバックすると、行のロックは解除されます。例6-41に示すように、行のロックが解除された後は、FOR UPDATEカーソルからはフェッチできません(ROLLBACKとCOMMITを入れ替えても結果は同じです)。
回避策は、ROWID擬似列を使用してCURRENT OF句をシミュレートすることです(『Oracle Database SQL言語リファレンス』を参照)。各行のROWIDを選択して、UROWID変数に入れ、更新や削除のときに、ROWIDを使用して現在行を識別します(例6-42を参照)。(UROWID変数の値を出力するには、『Oracle Database SQL言語リファレンス』で説明されているROWIDTOCHARファンクションを使用してその値をVARCHAR2に変換します。)
注意:
ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWIDが変更されます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。
注意:
フェッチされた行は、FOR UPDATE句によってロックされないため、他のユーザーによって意識せずに変更内容が上書きされる可能性があります。
注意:
読取り一貫性のために必要な追加領域は、カーソルがクローズされるまで解放されません。そのため、大規模な更新では処理速度が低下する場合があります。
例6-41 COMMIT文の後でのFOR UPDATEカーソルによるフェッチ
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
CURSOR c1 IS
SELECT * FROM emp
FOR UPDATE OF salary
ORDER BY employee_id;
emp_rec emp%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec; -- fails on second iteration
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
'emp_rec.employee_id = ' ||
TO_CHAR(emp_rec.employee_id)
);
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 105;
COMMIT; -- releases locks
END LOOP;
END;
/
結果:
emp_rec.employee_id = 100 DECLARE * ERROR at line 1: ORA-01002: fetch out of sequence ORA-06512: at line 11
例6-42 ROWID擬似列を使用したCURRENT OF句のシミュレート
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
CURSOR c1 IS
SELECT last_name, job_id, rowid
FROM emp; -- no FOR UPDATE clause
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 emp
SET salary = salary * 1.02
WHERE rowid = my_rowid; -- simulates WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
/
自律型トランザクションは、別の、メイン・トランザクションによって開始される独立したトランザクションです。
自律型トランザクションは、メイン・トランザクションをコミットまたはロールバックせずに、SQL操作を実行してコミットまたはロールバックします。
図6-1に、メイン・トランザクション(MT)から自律型ルーチン(proc2)へ制御がどのように流れ、また戻るかを示します。自律型ルーチンは、2つの自立型トランザクション(AT1およびAT2)をコミットします。
注意:
自律型トランザクションは別のトランザクションによって開始されますが、これはネストしたトランザクションではありません。その理由は次のとおりです。
ロックなどのトランザクション・リソースをメイン・トランザクションと共有しません。
メイン・トランザクションに依存しません。
たとえば、メイン・トランザクションがロールバックする場合は、ネストしたトランザクションがロールバックするのに対し、自律型トランザクションはロールバックしません。
コミットされた変更を、他のトランザクションからすぐに参照できます。
ネストしたトランザクションのコミットされた変更は、メイン・トランザクションがコミットするまで他のトランザクションからは参照できません。
自律型トランザクションで例外が呼び出されると、文レベルのロールバックではなくトランザクション・レベルのロールバックが発生します。
ここでのトピック
関連項目:
自律型トランザクションの詳細は、『Oracle Database開発ガイド』を参照してください。
自律型トランザクションは、開始すると完全に独立します。ロック、リソースまたはコミット依存関係をメイン・トランザクションと共有することはありません。メイン・トランザクションがロールバックする場合でも、イベントや増分再試行カウンタなどのログを取ることができます。
自律型トランザクションは再利用可能なソフトウェア・コンポーネントであるモジュール構造の作成に役立ちます。自律型トランザクションは、ストアド・サブプログラム内部でカプセル化できます。起動元のアプリケーションは、そのストアド・サブプログラムによって実行された操作が成功したか失敗したかを知る必要はありません。
メイン・トランザクションはそのコンテキストをネストしたルーチンと共有しますが、自律型トランザクションとは共有しません。ある自律型ルーチンが別の自律型ルーチンを(または自身を再帰的に)起動する場合、ルーチンはトランザクション・コンテキストを共有しません。ある自律型ルーチンが自律型ではないルーチンを起動する場合、ルーチンは同じトランザクション・コンテキストを共有します。
自律型トランザクションによって行われた変更は、自律型トランザクションがコミットすると、他のトランザクションから参照できるようになります。分離レベルがREAD COMMITTED(デフォルト)に設定されている場合、メイン・トランザクションが再開すると、これらの変更をメイン・トランザクションから参照できるようになります。
メイン・トランザクションの分離レベルをSERIALIZABLEに設定すると、その自律型トランザクションによって行われた変更は、再開してもメイン・トランザクションからは参照できません。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
注意:
トランザクションのプロパティは、それらが設定されたトランザクションにのみ適用されます。
カーソル属性は自律型トランザクションの影響を受けません。
自律型ルーチンを宣言するには、AUTONOMOUS_TRANSACTIONプラグマを使用します。
このプラグマの詳細は、「AUTONOMOUS_TRANSACTIONプラグマ」を参照してください。
ヒント:
読みやすくするために、AUTONOMOUS_TRANSACTIONプラグマは宣言部の先頭に置きます。(プラグマは宣言部の任意の場所に置くことができます。)
AUTONOMOUS_TRANSACTIONプラグマはパッケージ全体またはADT全体に適用することはできませんが、パッケージ内の各サブプログラムまたはADTの各メソッドに適用できます。
例6-43 パッケージでの自律型ファンクションの宣言
この例では、パッケージ・ファンクションを自律型としてマークします。
CREATE OR REPLACE PACKAGE emp_actions AUTHID DEFINER 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 自律型スタンドアロン・プロシージャの宣言
この例では、スタンドアロン・サブプログラムを自律型としてマークします。
CREATE OR REPLACE PROCEDURE lower_salary
(emp_id NUMBER, amount NUMBER)
AUTHID DEFINER 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ブロックを自律型としてマークします。(ネストしたPL/SQLブロックは自律型にはできません。)
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER(6) := 200;
amount NUMBER(6,2) := 200;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END;
/
自律型ルーチンの最初のSQL文でトランザクションが開始されます。1つのトランザクションが終了すると、次のSQL文で別のトランザクションが開始されます。カレント・トランザクションは、最後のコミットまたはロールバックよりも後に実行されたすべてのSQL文で構成されます。自律型トランザクションを制御するには、次の文を使用します(これは現在の(アクティブな)トランザクションのみに適用されます)。
COMMIT
ROLLBACK [TO savepoint_name]
SAVEPOINT savepoint_name
SET TRANSACTION
ここでのトピック
自律型ルーチンの実行部に入ると、メイン・トランザクションは停止します。ルーチンを終了すると、メイン・トランザクションは再開します。
コミットまたはロールバックせずにアクティブな自律型トランザクションを終了しようとすると、データベースによって例外が呼び出されます。例外が未処理になった場合、またはその他の未処理例外が発生してトランザクションが終了した場合、トランザクションはロールバックします。
正常に終了するには、ルーチンはすべての自律型トランザクションを明示的にコミットまたはロールバックする必要があります。ルーチン(またはそれによって起動されたルーチン)に保留中のトランザクションがある場合は、PL/SQLによって例外が呼び出され、保留中のトランザクションはロールバックします。
COMMITとROLLBACKはアクティブな自律型トランザクションを終了しますが、自律型ルーチンから抜けるわけではありません。1つのトランザクションが終了すると、次のSQL文で別のトランザクションが開始されます。1つの自律型ルーチンは、複数のCOMMIT文を発行する場合、複数の自律型トランザクションを含むことができます。
セーブポイントの有効範囲は、それが定義されたトランザクションです。メイン・トランザクション内で定義されたセーブポイントは、その自律型トランザクション内で定義されたセーブポイントとは無関係です。実際、メイン・トランザクションと自律型トランザクションのセーブポイントには、同じ名前を使用できます。
ロールバックできるのは、カレント・トランザクション内でマークされたセーブポイントまでです。自律型トランザクション内では、メイン・トランザクション内でマークされたセーブポイントまではロールバックできません。メイン・トランザクションのセーブポイントまでロールバックするには、自律型ルーチンを抜けてメイン・トランザクションを再開する必要があります。
メイン・トランザクション内では、自律型トランザクションを開始する前にマークされたセーブポイントまでロールバックしても、自律型トランザクションはロールバックされません。自律型トランザクションは、メイン・トランザクションからは完全に独立していることに注意してください。
一般的なエラーを回避するには、次のことに注意してください。
メイン・トランザクションが保持するリソースに、自律型トランザクションがアクセスしようとすると、デッドロックが発生することがあります。この場合は、データベースによって自律型トランザクションで例外が呼び出され、例外が未処理になった場合、自律型トランザクションはロールバックします。
データベース初期化パラメータTRANSACTIONSは、同時トランザクションの最大数を指定します。自律型トランザクションはメイン・トランザクションと同時に実行されるため、この最大数を超える場合があります。
コミットまたはロールバックせずにアクティブな自律型トランザクションを終了しようとすると、データベースによって例外が呼び出されます。例外が未処理になった場合、トランザクションはロールバックします。
自律型トランザクションがオープンされている間は、自律型ルーチン内でPIPE ROW文を実行することはできません。PIPE ROW文を実行する前に、自律型トランザクションをクローズする必要があります。この処理は通常、PIPE ROW文を実行する前に自律型トランザクションをコミットまたはロールバックすることによって行います。
TCL文またはDDL文を実行する場合、トリガーは自律型である必要があります。
DDL文を実行する場合、トリガーは、システム固有の動的SQLを使用する必要があります。
関連項目:
トリガーの一般情報は、「PL/SQLトリガー」を参照してください
TCL文の一般情報は、「静的SQLの説明」を参照してください
DDL文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
システム固有の動的SQLの詳細は、「システム固有の動的SQL」を参照してください
トリガーの使用例の1つは、イベントのログを透過的に取ることです(たとえば、ある表に対するすべての挿入を、ロールバックしたものも含めてログに記録できます)。
例6-46 自律型トリガーによるINSERT文のログへの記録
この例では、EMPLOYEES表に行が挿入されるたびに、トリガーによって同じ行がログ表に挿入されます。トリガーは自律型であるため、メイン表への変更をコミットするかどうかに関係なく、ログ表への変更をコミットできます。
DROP TABLE emp; CREATE TABLE emp AS SELECT * FROM employees; -- Log table: DROP TABLE log; CREATE TABLE log ( log_id NUMBER(6), up_date DATE, new_sal NUMBER(8,2), old_sal NUMBER(8,2) ); -- Autonomous trigger on emp table: CREATE OR REPLACE TRIGGER log_sal BEFORE UPDATE OF salary ON emp FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log ( log_id, up_date, new_sal, old_sal ) VALUES ( :old.employee_id, SYSDATE, :new.salary, :old.salary ); COMMIT; END; / UPDATE emp SET salary = salary * 1.05 WHERE employee_id = 115; COMMIT; UPDATE emp SET salary = salary * 1.05 WHERE employee_id = 116; ROLLBACK; -- Show that both committed and rolled-back updates -- add rows to log table SELECT * FROM log WHERE log_id = 115 OR log_id = 116;
結果:
LOG_ID UP_DATE NEW_SAL OLD_SAL
---------- --------- ---------- ----------
115 02-OCT-12 3255 3100
116 02-OCT-12 3045 2900
2 rows selected.
例6-47 自律型トリガーによるDDLに対するシステム固有の動的SQLの使用
この例では、自律型トリガーでシステム固有の動的SQL(EXECUTE IMMEDIATE文)を使用し、行が表logに挿入された後で一時表を削除しています。
DROP TABLE temp; CREATE TABLE temp ( temp_id NUMBER(6), up_date DATE ); CREATE OR REPLACE TRIGGER drop_temp_table AFTER INSERT ON log DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE 'DROP TABLE temp'; COMMIT; END; / -- Show how trigger works SELECT * FROM temp;
結果:
no rows selected INSERT INTO log (log_id, up_date, new_sal, old_sal) VALUES (999, SYSDATE, 5000, 4500); 1 row created. SELECT * FROM temp;
結果:
SELECT * FROM temp * ERROR at line 1: ORA-00942: table or view does not exist
SQL文から呼び出されたファンクションは、副作用を制御する規則に従う必要があります。
定義上は、自立型ルーチンによってデータベース状態の読取りおよび書込みは行われません(つまり、データベース表に対する問合せや変更が行われません)。
関連項目:
詳細は、「サブプログラムの副作用」を参照してください
例6-48 自律型ファンクションの起動
パッケージ・ファンクションlog_msgは自律型です。ファンクションが起動されると、データベース状態の書込み(データベース表の変更)禁止の規則に違反することなく、そのファンクションによってデータベース表debug_outputにメッセージが挿入されます。
DROP TABLE debug_output;
CREATE TABLE debug_output (message VARCHAR2(200));
CREATE OR REPLACE PACKAGE debugging AUTHID DEFINER AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
END debugging;
/
CREATE OR REPLACE PACKAGE BODY debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO debug_output (message) VALUES (msg);
COMMIT;
RETURN msg;
END;
END debugging;
/
-- Invoke package function from 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;
/
