この章の内容は次のとおりです。
この章では、PL/SQLトランザクション処理ブロックをプログラムに埋め込むことにより、パフォーマンスを改善する方法について説明します。
この項では、PL/SQLによって提供される次のような機能および利点について説明します。
PL/SQLにより、オーバーヘッドの削減、パフォーマンスの改善、生産性の向上が図れます。たとえば、PL/SQLを使用しないと、Oracleでは一度に1つずつしかSQL文を処理できません。SQL文ごとにサーバーに対する別のコールが発生し、その結果、オーバーヘッドが増加します。しかし、PL/SQLを使用すると、SQL文のブロック全体をサーバーに送信できます。これにより、アプリケーションとOracle間の通信は最小限に抑えられます。
PL/SQLは、Oracleサーバーと密接に統合されています。たとえば、PL/SQLデータ型の大部分は、Oracleデータ・ディクショナリにとっても固有のデータ型です。さらに、次の例に示すように、データ・ディクショナリに格納された列定義に基づいて変数を宣言するための%TYPE
属性を指定できます。
job_title emp.job%TYPE;
したがって、列の正確なデータ型を知る必要はありません。しかも、列定義を変更すれば、変数宣言もそれに応じて自動的に変更されます。これにより、データの独立性がもたらされ、メンテナンス・コストを削減し、データベース変更時にプログラムを適応させることができます。
PL/SQLを使用すれば、カーソルを定義して操作するために、DECLARE
、OPEN
、FETCH
およびCLOSE
文を使用する必要はありません。かわりに、カーソルFOR
ループを使用でき、ループ索引をレコードとして暗黙的に宣言し、指定された問合せに関連付けられているカーソルをオープンして、データを繰り返しカーソルからフェッチしてレコードに入れてから、カーソルをクローズします。次に例を示します。
DECLARE ... BEGIN FOR emprec IN (SELECT empno, sal, comm FROM emp) LOOP IF emprec.comm / emprec.sal > 0.25 THEN ... ... END LOOP; END;
レコード内のフィールドを参照するためにドット表記法を使用することに注意してください。
PL/SQLにはプロシージャとファンクションと呼ばれる2種類のサブプログラムがあり、これらを使用すると、各動作を分離できるため、アプリケーションの開発が容易になります。一般にアクションの実行にはプロシージャを使用し、値の計算にはファンクションを使用します。
プロシージャとファンクションには拡張性があります。つまり、プロシージャとファンクションを使用することで、必要に応じてPL/SQL言語を調整できます。たとえば、新しい部門を作成するプロシージャが必要な場合、次のように記述します。
PROCEDURE create_dept (new_dname IN CHAR(14), new_loc IN CHAR(13), new_deptno OUT NUMBER(2)) IS BEGIN SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO dept VALUES (new_deptno, new_dname, new_loc); END create_dept;
このプロシージャをコールすると、プロシージャでは新しい部門名と場所を受け取り、部門番号データベース順序の次の値を選択し、その新しい番号、名前および場所をdept表に挿入してから、新しい番号をコール元に戻します。
サブプログラムは(CREATE
FUNCTION
およびCREATE
PROCEDURE
を使用して)データベースに格納できます。こうすることで、サブプログラムをその都度再コンパイルせずに、複数のアプリケーションからコールできます。
仮パラメータの動作を定義するには、パラメータ・モードを使用します。パラメータ・モードにはIN
(デフォルト)、OUT
およびIN OUT
の3つがあります。7IN
パラメータを使用すると、コールされるプログラムに値を渡せます。OUT
パラメータを使用すると、サブプログラムのコール元に値を戻せます。IN OUT
パラメータを使用すると、コールされるサブプログラムに初期値を渡し、コール元には更新された値を戻せます。
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。表3-6は、データ型間の有効な変換を示しています。
PL/SQLでは、論理的に関連する型、プログラム・オブジェクトおよびサブプログラムを1つのパッケージにまとめることができます。パッケージは、コンパイルしてOracleデータベースに格納でき、そこでその内容を複数のアプリケーションで共有できるようになります。
パッケージには通常、仕様部と本体の2つの部分があります。仕様部とは、アプリケーションへのインタフェースで、使用可能な型、定数、変数、例外、カーソルおよびサブプログラムが宣言されます。本体は、カーソルおよびサブプログラムを定義して、仕様を実行します。次の例では、2つの雇用プロシージャをパッケージ化しています。
PACKAGE emp_actions IS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; PACKAGE BODY emp_actions IS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;
パッケージの仕様部内の宣言のみが表示され、アプリケーションからアクセスできます。パッケージ本体の実装の詳細は非表示であるため、アクセスできません。
PL/SQLには、TABLE
という名前の複合データ型が用意されています。TABLE
型のオブジェクトは、PL/SQL表と呼ばれ、データベース表をモデルとしています(ただし、同じではありません)。PL/SQL表は1列のみで、主キーを使用して、配列と同じように行にアクセスします。列は任意のスカラー型(CHAR
、DATE
またはNUMBER
など)にできますが、主キーはBINARY_INTEGER
型にする必要があります。
ブロック、プロシージャ、ファンクションまたはパッケージのいずれかの宣言部で、PL/SQL表の型を宣言できます。次の例では、NumTabTypと呼ばれるTABLE
型を宣言しています。
DECLARE TYPE NumTabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; ... BEGIN ... END;
次の例に示すように、NumTabTyp型を定義すると、その型のPL/SQL表を宣言できます。
num_tab NumTabTyp;
識別子num_tabは、PL/SQL表全体を表しています。
配列に似た構文を使用してPL/SQL表内の行を参照し、主キーの値を指定します。たとえば、num_tabという名前のPL/SQL表内の9番目の行を参照するには、次のように指定します。
num_tab(9) ...
%ROWTYPE
属性を使用して、データベース表内の行を表すレコード、またはカーソルによってフェッチされる行を表すレコードを宣言できます。しかし、レコード内のフィールドのデータ型は指定できず、ユーザー独自のフィールドも定義できません。複合データ型RECORD
を使用すると、これらの制限を取り除くことができます。
RECORD
型のオブジェクトはレコードと呼ばれます。PL/SQL表とは異なり、レコードには一意の名前のフィールドがあり、フィールドのデータ型は異なっていてもかまいません。たとえば、ある従業員について異なる種類のデータ(名前、給与、雇用日など)があるとします。このデータは、型は異なりますが、論理的に関連しています。従業員の名前、給与および雇用日などのフィールドを持つレコードによって、1つの論理単位としてデータを処理できます。
ブロック、プロシージャ、ファンクションまたはパッケージのいずれかの宣言部で、レコード型およびレコード・オブジェクトを宣言できます。次の例では、DeptRecTypというRECORD
型を宣言しています。
DECLARE TYPE DeptRecTyp IS RECORD (deptno NUMBER(4) NOT NULL := 10, -- must initialize dname CHAR(9), loc CHAR(14));
フィールド宣言は変数宣言と似ていることに注意してください。各フィールドには、一意の名前と特定のデータ型があります。どのフィールド宣言にもNOT
NULL
オプションを追加でき、そのフィールドへのNULLの割当てを防止します。ただし、NOT
NULL
フィールドは初期化する必要があります。
次の例に示すように、DeptRecTyp型を定義すると、その型のレコードを宣言できます。
dept_rec DeptRecTyp;
識別子dept_recは、レコード全体を表しています。
レコード内の個々のフィールドを参照するには、ドット表記法を使用します。たとえば、dept_recレコードのdnameフィールドは、次のように記述して参照します。
dept_rec.dname ...
Oracleプリコンパイラでは、PL/SQLブロックが1つの埋込みSQL文と同様に扱われます。したがって、PL/SQLブロックは、ホスト・プログラム内でSQL文を記述できる場所であれば、どこにでも記述できます。
PL/SQLブロックをホスト・プログラムに埋め込むには、次のように、PL/SQLブロックをキーワードのEXEC
SQL
EXECUTE
とEND-EXEC
で囲みます。
EXEC SQL EXECUTE DECLARE ... BEGIN ... END; END-EXEC;
END-EXEC
キーワードの後には、ホスト言語の文終了文字を付ける必要があります。
プログラムにPL/SQLブロックを埋め込む場合、PL/SQLはOracleで解析する必要があるため、プリコンパイラ・オプションSQLCHECK=SEMANTICS
を指定します。Oracleに接続するには、USERID
オプションも指定する必要があります。詳細は、「プリコンパイラ・オプションの使用方法」を参照してください。
ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。
たとえば、ユーザーに情報の提供を求め、ホスト変数を使用して、この情報をPL/SQLブロックに渡すことができます。これにより、PL/SQLではデータベースにアクセスし、ホスト変数を使用して結果をホスト・プログラムに戻すことができます。
PL/SQLブロック内では、ホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる場所であればそのブロックのどこにでも使用できます。ただし、文字ホスト変数は、長さが255文字以内です。SQL文のホスト変数と同様、PL/SQLブロックのホスト変数も先頭にコロンと付ける必要があります。コロンにより、ホスト変数をPL/SQL変数やデータベース・オブジェクトと区切ります。
次の例は、PL/SQLでのホスト変数の使用方法を示しています。プログラムでは、ユーザーに従業員番号の入力を要求し、その番号に応じて従業員の役職名、雇用日および給与を表示します。
EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); emp_number INTEGER; job_title CHARACTER(20); hire_date CHARACTER(9); salary REAL; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; EXEC SQL WHENEVER SQLERROR DO sql_error; EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; LOOP display 'Employee Number (0 to end)? '; read emp_number; IF emp_number = 0 THEN EXEC SQL COMMIT WORK RELEASE; display 'Exiting program'; exit program; ENDIF; ---------------- begin PL/SQL block ----------------- EXEC SQL EXECUTE BEGIN SELECT job, hiredate, sal INTO :job_title, :hire_date, :salary FROM emp WHERE empno = :emp_number; END; END-EXEC; ---------------- end PL/SQL block ----------------- display 'Number Job Title Hire Date Salary'; display '------------------------------------'; display emp_number, job_title, hire_date, salary; ENDLOOP; ... ROUTINE sql_error BEGIN EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error; END sql_error;
ホスト変数emp_numberがPL/SQLブロックが入力される前に設定され、ホスト変数job_title、hire_dateおよびsalaryがブロック内で設定されていることに注意してください。
次の例では、ユーザーに銀行口座番号、取引の種類、取引金額の入力を要求し、その後、口座の借方または貸方に記入します。口座が存在しない場合、例外が発生します。取引が完了すると、そのステータスを表示します。
EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); acct_num INTEGER; trans_type CHARACTER(1); trans_amt REAL; status CHARACTER(80); EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; EXEC SQL WHENEVER SQLERROR DO sql_error; EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; LOOP display 'Account Number (0 to end)? '; read acct_num; IF acct_num = 0 THEN EXEC SQL COMMIT WORK RELEASE; display 'Exiting program'; exit program; ENDIF; display 'Transaction Type - D)ebit or C)redit? ' read trans_type; display 'Transaction Amount? ' read trans_amt; --------------------- begin PL/SQL block ------------------- EXEC SQL EXECUTE DECLARE old_bal NUMBER(9,2); err_msg CHAR(70); nonexistent EXCEPTION; BEGIN :trans_type := UPPER(:trans_type); IF :trans_type = 'C' THEN -- credit the account UPDATE accts SET bal = bal + :trans_amt WHERE acctid = :acct_num; IF SQL%ROWCOUNT = 0 THEN -- no rows affected RAISE nonexistent; ELSE :status := 'Credit applied'; END IF; ELSIF :trans_type = 'D' THEN -- debit the account SELECT bal INTO old_bal FROM accts WHERE acctid = :acct_num; IF old_bal >= :trans_amt THEN -- enough funds UPDATE accts SET bal = bal - :trans_amt WHERE acctid = :acct_num; :status := 'Debit applied'; ELSE :status := 'Insufficient funds'; END IF; ELSE :status := 'Invalid type: ' || :trans_type; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND OR nonexistent THEN :status := 'Nonexistent account'; WHEN OTHERS THEN err_msg := SUBSTR(SQLERRM, 1, 70); :status := 'Error: ' || err_msg; END; END-EXEC; ------------------- end PL/SQL block ----------------------- display 'Status: ', status; ENDLOOP; ROUTINE sql_error BEGIN EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error; END sql_error;
第3章「プログラム要件への対応」で、可変長文字列の宣言にVARCHAR
擬似型が使用できると説明したことを思い出してください。VARCHAR
が入力ホスト変数の場合は、予想される長さをOracleに通知する必要があります。したがって、長さフィールドを文字列フィールドに格納される値の実際の長さに設定してください。
VARCHAR
が出力ホスト変数の場合、Oracleでは自動的に長さフィールドが設定されます。しかし、PL/SQLブロックでVARCHAR
出力ホスト変数を使用するには、ブロックに入る前に、長さフィールドを初期化する必要があります。したがって、次の例に示すように、長さフィールドを宣言されたVARCHAR
の(最大の)長さに設定してください。
EXEC SQL BEGIN DECLARE SECTION; emp_number INTEGER; emp_name VARCHAR(10); salary REAL; ... EXEC SQL END DECLARE SECTION; ... set emp_name.len = 10; -- initialize length field EXEC SQL EXECUTE BEGIN SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE empno = :emp_number; ... END; END-EXEC;
PL/SQLではNULLを操作できるため、インジケータ変数は必要ありません。たとえば、PL/SQL内では、次のようにIS NULL
演算子を使用してNULLがないか検査できます。
IF variable IS NULL THEN ...
次のように、代入演算子(:=
)を使用してNULLを割り当てることができます。
variable := NULL;
しかし、ホスト言語ではNULLを操作できないため、インジケータ変数が必要です。埋込みPL/SQLでは、次の目的でインジケータ変数を使用できるため、この要件を満たします。
ホスト・プログラムからのNULL入力の受入れ
NULLまたは切り捨てられた値のホスト・プログラムへの出力
PL/SQLブロックでインジケータ変数を使用するときは、次の規則に従ってください。
インジケータ変数は単独では参照できません。関連付けられたホスト変数に追加する必要があります。
インジケータ変数を指定してホスト変数を参照する場合、同じブロックでは常に同じ方法で参照する必要があります。
次の例では、SELECT
文でインジケータ変数ind_commがホスト変数commissionとともに指定されているため、IF
文でも同様に指定する必要があります。
EXEC SQL EXECUTE BEGIN SELECT ename, comm INTO :emp_name, :commission:ind_comm FROM emp WHERE empno = :emp_number; IF :commission:ind_comm IS NULL THEN ... ... END; END-EXEC;
PL/SQLでは、:commission:ind_commが他の単純な変数と同じように扱われることに注意してください。PL/SQLブロック内のインジケータ変数は直接参照できませんが、PL/SQLでは、ブロックに入るときにインジケータ変数の値がチェックされ、ブロックから出るときにその値が正しく設定されます。
ブロックに入るとき、インジケータ変数の値が-1
であれば、PL/SQLでは自動的にNULLがホスト変数に割り当てられます。ブロックから出るとき、ホスト変数がNULLであれば、自動的に-1の値がインジケータ変数に割り当てられます。次の例では、PL/SQLブロックに入る前にind_salの値が-1になっていると、salary_missing例外が発生します。例外とは、名前が指定されたエラー状態のことです。
EXEC SQL EXECUTE BEGIN IF :salary:ind_sal IS NULL THEN RAISE salary_missing; END IF; ... END; END-EXEC;
PL/SQLでは、切り捨てられた文字列値がホスト変数に割り当てられても、例外とはみなされません。しかし、インジケータ変数を使用している場合には、PL/SQLによってインジケータ変数が文字列の元の長さに設定されます。次の例では、ホスト・プログラムは、ind_nameの値をチェックして、切り捨てられた値がemp_nameに割り当てられたかどうかを判別できます。
EXEC SQL EXECUTE DECLARE ... new_name CHAR(10); BEGIN ... :emp_name:ind_name := new_name; ... END; END-EXEC;
入力ホスト配列およびインジケータ配列は、PL/SQLブロックに渡せます。これらには、BINARY_INTEGER
型のPL/SQL変数、またはその型と互換性のあるホスト変数によって索引付けができます。通常は、ホスト配列全体がPL/SQLに渡されますが、ARRAYLEN
文(後述)を使用すれば、より小さい配列サイズを指定できます。
さらに、サブプログラム・コールを使用して、ホスト配列のすべての値をPL/SQL表の複数の行に割り当てることができます。配列のサブスクリプトの範囲がm~nの場合、対応するPL/SQL表の索引範囲は常に1~(n - m + 1)になります。たとえば、配列サブスクリプト範囲が5~10の場合、対応するPL/SQL表の索引範囲は、1~(10 - 5 + 1)または1~6です。
注意: Oracleプリコンパイラでは、ホスト変数の使用方法はチェックされません。たとえば、索引の範囲チェックは行われません。 |
次の例では、salaryというホスト配列をPL/SQLブロックに渡し、ブロックではこのホスト配列がファンクション・コールで使用されます。このファンクションは、一連の数値の中央値を検出するため、medianという名前が付いています。その仮パラメータには、num_tabというPL/SQL表が含まれています。このファンクション・コールにより、実パラメータsalary内のすべての値を仮パラメータnum_tab内の行に割り当てます。
EXEC SQL BEGIN DECLARE SECTION; ... salary (100) REAL; median_salary REAL; EXEC SQL END DECLARE SECTION; -- populate the host array EXEC SQL EXECUTE DECLARE TYPE NumTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; n BINARY_INTEGER; ... FUNCTION median (num_tab NumTabTyp, n INTEGER) RETURN REAL IS BEGIN -- compute median END; BEGIN n := 100; :median_salary := median(:salary, n); ... END; END-EXEC;
また、サブプログラム・コールを使用して、PL/SQL表内のすべての行をホスト配列内の対応する要素に割り当てることもできます。
表5-1に、PL/SQL表の行の値とホスト配列の要素間での有効な変換を示しています。たとえば、LONG
型のホスト配列は、VARCHAR2
、LONG
、RAW
またはLONG RAW
型のPL/SQL表と互換性があります。ただし、CHAR
型のPL/SQL表とは互換性がありません。
表5-1 PL/SQL表の行値とホスト配列の要素の有効な変換
PL/SQL表 | ホスト配列 | |||||||
---|---|---|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
|||||||
|
_/ |
_/ |
||||||
|
_/ |
_/ |
_/ |
_/ |
||||
|
_/ |
_/ |
||||||
|
_/ |
|||||||
|
_/ |
_/ |
||||||
|
_/ |
|||||||
|
_/ |
_/ |
_/ |
_/ |
||||
|
_/ |
|||||||
|
_/ |
_/ |
_/ |
_/ |
||||
|
_/ |
_/ |
_/ |
_/ |
||||
|
_/ |
|||||||
|
_/ |
_/ |
入力ホスト配列をPL/SQLブロックに渡して処理する必要があるとします。デフォルトでは、このようなホスト配列をバインドする際、Oracleプリコンパイラでは、宣言されたサイズが使用されます。ただし、配列全体を処理する必要がない場合があります。この場合、ARRAYLEN
文を使用して、より小さい配列サイズを指定できます。ARRAYLEN
文は、ホスト配列を格納サイズがより小さいホスト変数と関連付けます。文の構文は次のとおりです。
EXEC SQL ARRAYLEN host_array (dimension);
dimensionは4バイトの整数型ホスト変数です。リテラルや式ではありません。
ARRAYLEN
文は、宣言部でhost_arrayおよびdimension宣言の後に指定する必要があります。ホスト配列にオフセットは指定できません。しかし、その目的にはホスト言語の機能を使用できる場合があります。
次の例では、ARRAYLEN
を使用して、bonusというホスト配列のデフォルトのサイズをオーバーライドします。
EXEC SQL BEGIN DECLARE SECTION; bonus (100) REAL; my_dim INTEGER; EXEC SQL ARRAYLEN bonus (my_dim); EXEC SQL END DECLARE SECTION; -- populate the host array ... set my_dim = 25; -- set smaller array dimension EXEC SQL EXECUTE DECLARE TYPE NumTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; median_bonus REAL; FUNCTION median (num_tab NumTabTyp, n INTEGER) RETURN REAL IS BEGIN -- compute median END; BEGIN median_bonus := median(:bonus, :my_dim); ... END; END-EXEC;
ARRAYLEN
によってホスト配列のサイズが100要素から25要素に減るため、PL/SQLブロックには25の配列要素のみが渡されます。その結果、PL/SQLブロックが実行のためOracleに送信されるとき、一緒に送られるホスト配列はずっと小さくなります。これにより時間が節約され、ネットワーク環境ではネットワークの通信量が減ります。
どの埋込みSQL文にも、カーソルが割り当てられています。DECLARE
CURSOR
文でユーザーによって明示的に割り当てらるか、プリコンパイラによって暗黙的に割り当てられるかのいずれかです。Oracleプリコンパイラの内部には、埋込みSQL文の実行を制御するためのカーソル・キャッシュと呼ばれるキャッシュが保持されいます。どのSQL文にも、実行されると、カーソル・キャッシュのエントリが割り当てられます。このエントリは、Oracle内のプログラム・グローバル領域(PGA)にあるプライベートSQL領域とリンクしています。
MAXOPENCURSORS
、HOLD_CURSOR
およびRELEASE_CURSOR
などの各種プリコンパイラ・オプションを使用して、カーソル・キャッシュを管理することで、パフォーマンスが改善されます。たとえば、RELEASE_CURSOR
では、カーソル・キャッシュとプライベートSQL領域間のリンクに起こることを制御します。RELEASE_CURSOR
=YES
を指定すると、OracleでSQL文が実行された後、リンクが削除されます。これにより、プライベートSQL領域に割り当てられたメモリーが解放され、解析ロックも解除されます。
カーソル・キャッシュ管理のために、埋込みPL/SQLブロックはSQL文と同様に扱われます。実行時に、親カーソルと呼ばれるカーソルが、PL/SQLブロック全体と関連付けられます。カーソル・キャッシュには対応するエントリが作成され、このエントリがPGA内のプライベートSQL領域にリンクします。
PL/SQLブロック内の各SQL文にも、PGAのプライベートSQL領域が必要です。したがって、これらのSQL文のために、PL/SQLでは子カーソル・キャッシュと呼ばれる個別のキャッシュが管理されます。このカーソルは、子カーソルと呼ばれます。子カーソル・キャッシュの管理はPL/SQLによって行われるため、ユーザーは子カーソルを直接制御できません。
プログラムで同時に使用できるカーソルの最大数は、Oracle初期化パラメータのOPEN_CURSORS
によって設定されます。図5-1は、使用されるカーソルの最大数を計算する方法を示しています。
OPEN_CURSORS
で設定された制限をプログラムが超えると、次のOracleエラーが発生します。
ORA-01000: maximum open cursors exceeded
このエラーは、RELEASE_CURSOR
=YES
オプションとHOLD_CURSOR
=NO
オプションを指定すれば回避できます。RELEASE_CURSOR
をYES
に設定してプログラム全体をプリコンパイルする必要ない場合は、次のように、単に各PL/SQLブロックの後でオプションをNO
にリセットします。
EXEC ORACLE OPTION (RELEASE_CURSOR=YES); -- first embedded PL/SQL block EXEC ORACLE OPTION (RELEASE_CURSOR=NO); -- embedded SQL statements EXEC ORACLE OPTION (RELEASE_CURSOR=YES); -- second embedded PL/SQL block EXEC ORACLE OPTION (RELEASE_CURSOR=NO); -- embedded SQL statements
無名ブロックとは異なり、PL/SQLサブプログラム(プロシージャおよびファンクション)は、別々にコンパイルしてOracleデータベースに格納し、起動できます。SQL*PlusなどのOracleツールを使用して明示的に作成したサブプログラムは、ストアド・サブプログラムと呼ばれます。コンパイルされ、データ・ディクショナリに格納されたストアド・サブプログラムは、データベース・オブジェクトとなり、再コンパイルせずに再実行できます。
PL/SQLブロック内のサブプログラムまたはストアド・サブプログラムがアプリケーションによってOracleに送られると、それはインライン・サブプログラムと呼ばれます。Oracleでは、インライン・サブプログラムをコンパイルし、システム・グローバル領域(SGA)にキャッシュしますが、ソースまたはオブジェクト・コードをデータ・ディクショナリに格納することはありません。
パッケージ内で定義されているサブプログラムは、そのパッケージの一部とみなされ、パッケージ・サブプログラムと呼ばれます。パッケージ内で定義されていないストアド・サブプログラムは、スタンドアロン・プログラムと呼ばれます。
次の例に示すように、SQL文CREATE
FUNCTION
、CREATE
PROCEDURE
およびCREATE
PACKAGE
をホスト・プログラムに埋め込むことができます。
EXEC SQL CREATE FUNCTION sal_ok (salary REAL, title CHAR) RETURN BOOLEAN AS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok; END-EXEC;
埋込みCREATE
{FUNCTION | PROCEDURE | PACKAGE}
文がハイブリッドであることに注意してください。他のすべての埋込みCREATE
文と同様に、キーワードEXEC SQL
(EXEC SQL EXECUTE
ではない)で始まります。ただし、他の埋込みCREATE
文と異なり、PL/SQLの終了文字END-EXEC
で終わります。
次の例では、emp表からひとまとまりの行をフェッチするget_employeesというプロシージャを含むパッケージを作成します。バッチ・サイズは、プロシージャのコール元(別のストアド・サブプログラムの場合もあれば、クライアント・アプリケーションの場合もある)によって決められます。
プロシージャでは、3つのPL/SQL表をOUT
仮パラメータとして宣言し、その後、従業員のバッチ・データをPL/SQL表にフェッチします。一致する実パラメータはホスト配列です。プロシージャの終了時には、PL/SQL表のすべての行の値が、ホスト配列の対応する要素に自動的に割り当てられます。
EXEC SQL CREATE OR REPLACE PACKAGE emp_actions AS TYPE CharArrayTyp IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; TYPE NumArrayTyp IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; PROCEDURE get_employees( dept_number IN INTEGER, batch_size IN INTEGER, found IN OUT INTEGER, done_fetch OUT INTEGER, emp_name OUT CharArrayTyp, job-title OUT CharArrayTyp, salary OUT NumArrayTyp); END emp_actions; END-EXEC; EXEC SQL CREATE OR REPLACE PACKAGE BODY emp_actions AS CURSOR get_emp (dept_number IN INTEGER) IS SELECT ename, job, sal FROM emp WHERE deptno = dept_number; PROCEDURE get_employees( dept_number IN INTEGER, batch_size IN INTEGER, found IN OUT INTEGER, done_fetch OUT INTEGER, emp_name OUT CharArrayTyp, job_title OUT CharArrayTyp, salary OUT NumArrayTyp) IS BEGIN IF NOT get_emp%ISOPEN THEN OPEN get_emp(dept_number); END IF; done_fetch := 0; found := 0; FOR i IN 1..batch_size LOOP FETCH get_emp INTO emp_name(i), job_title(i), salary(i); IF get_emp%NOTFOUND THEN CLOSE get_emp; done_fetch := 1; EXIT; ELSE found := found + 1; END IF; END LOOP; END get_employees; END emp_actions; END-EXEC;
CREATE
文でREPLACE
句を指定すると、パッケージの削除、再作成および権限の再付与を行わなくても、既存のパッケージを再定義できます。CREATE
文の完全な構文は、『Oracle Database SQL言語リファレンス』を参照してください。
埋込みCREATE {FUNCTION|PROCEDURE|PACKAGE}
文が失敗した場合、Oracleではエラーではなく警告が出ます。
ホスト・プログラムからストアド・サブプログラムを起動(コール)するには、無名PL/SQLブロックを使用する必要があります。次の例では、raise_salaryというスタンドアロン・プロシージャをコールします。
EXEC SQL EXECUTE BEGIN raise_salary(:emp_id, :increase); END; END-EXEC;
ストアド・サブプログラムにはパラメータを組み込めることに注意してください。この例では、実パラメータemp_idおよびincreaseはホスト変数です。
次の例では、raise_salaryプロシージャがemp_actionsというパッケージに格納されるため、プロシージャ・コールを完全に修飾するには、ドット表記法を使用する必要があります。
EXEC SQL EXECUTE BEGIN emp_actions.raise_salary(:emp_id, :increase); END; END-EXEC;
IN実パラメータには、リテラル、ホスト変数、ホスト配列、PL/SQL定数または変数、PL/SQL表、PL/SQLユーザー定義レコード、サブプログラム・コールまたは式を使用できます。しかし、実OUTパラメータには、リテラル、サブプログラム・コールまたは式を使用できません。
次のPro*Cの例では、3つの仮パラメータがPL/SQL表で、対応する実パラメータはホスト配列です。プログラムでは、ストアド・プロシージャget_employeesを繰り返しコールし、データがなくなるまで、従業員データの各バッチを表示します。
#include <stdio.h> #include <string.h> typedef char asciz; EXEC SQL BEGIN DECLARE SECTION; /* Define type for null-terminated strings */ EXEC SQL TYPE asciz IS STRING(20); asciz username[20]; asciz password[20]; int dept_no; /* which department to query */ char emp_name[10][21]; char job[10][21]; float salary[10]; int done_flag; int array_size; int num_ret; /* number of rows returned */ int SQLCODE; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE sqlca; int print_rows(); /* produces program output */ int sql_error(); /* handles NOLOGGING errors */ main() { int i; /* Connect to Oracle. */ strcpy(username, "SCOTT"); strcpy(password, "TIGER"); EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to Oracle as user: %s\n", username); printf("enter department number: "); scanf("%d", &dept_no); fflush(stdin); /* Set the array size. */ array_size = 10; done_flag = 0; num_ret = 0; /* Array fetch loop - ends when done_flag is true. */ for (;;) { EXEC SQL EXECUTE BEGIN emp_actions.get_employees (:dept_no, :array_size, :num_ret, :done_flag, :emp_name, :job, :salary); END; END-EXEC; print_rows(num_ret); if (done_flag) break; } /* Disconnect from the database. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } print_rows(n) int n; { int i; if (n == 0) { printf("No rows retrieved.\n"); return; } printf("\n\nGot %d row%c\n", n, n == 1 ? '\0' : 's'); printf("%-20.20s%-20.20s%s\n", "Ename", "Job", "Salary"); for (i = 0; i < n; i++) printf("%20.20s%20.20s%6.2f\n", emp_name[i], job[i], salary[i]); } sql_error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nOracle error detected:"); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。また、ストアド・サブプログラムを終了する前には、すべてのOUT仮パラメータは割り当てられた値であることが必要です。そうでなければ、対応する実パラメータの値は未確定です。
PL/SQLを使用すると、データベース・リンクを介してリモート・データベースにアクセスできます。一般的に、データベース・リンクは、データベース管理者(DBA)によって設定され、Oracleデータ・ディクショナリに格納されます。データベース・リンクは、リモート・データベースの位置、リモート・データベースへのパス、使用するOracleユーザー名およびパスワードをOracleに伝えます。次の例では、データベース・リンクdallasを使用して、raise_salaryプロシージャをコールします。
EXEC SQL EXECUTE BEGIN raise_salary@dallas(:emp_id, :increase); END; END-EXEC;
次の例に示すように、シノニムを作成して、リモート・サブプログラムに位置の透過性を提供できます。
CREATE PUBLIC SYNONYM raise_salary FOR raise_salary@dallas;
第3章「プログラム要件への対応」では、ホスト・プログラムにOCIコールを埋め込む方法を学習しました。ライブラリ・ルーチンSQLLDA
をコールして、LDAを設定すると、OCIコールODESSP
を使用して、ストアド・サブプログラムに関する有益な情報を取得できます。ODESSP
をコールするときには、有効なLDAとサブプログラムの名前を渡す必要があります。パッケージ・プログラムの場合は、パッケージ名も渡す必要があります。ODESSP
からは、各サブプログラム・パラメータについて、そのデータ型、サイズ、位置などの情報が戻されます。
Oracle付属のDBMS_DESCRIBEパッケージでは、describe_procedureプロシージャも使用できます。
Oracleプリコンパイラでは、PL/SQLブロック全体が1つのSQL文のように扱われます。したがって、PL/SQLブロックを文字列ホスト変数に格納できます。そのとき、そのブロックにホスト変数が含まれていない場合は、動的SQL方法1を使用して、PL/SQL文字列を実行できます。あるいは、ブロックにホスト変数が含まれていて、その数がわかっている場合は、動的SQL方法2を使用して、PL/SQL文字列を準備し、実行できます。ブロックに未知数のホスト変数が含まれている場合は、動的SQL方法4を使用する必要があります。詳細は、第10章「動的SQLの使用方法」を参照してください。