5 埋込みPL/SQLの使用方法
この章の構成は、次のとおりです。
PL/SQLトランザクション処理ブロックをプログラム内に埋め込むことにより、パフォーマンスを改善する方法を説明します。
5.1 PL/SQLの利点
この項では、PL/SQLによって提供される次のような機能および利点について説明します。
5.1.1 パフォーマンスの向上
PL/SQLによって、オーバーヘッドの削減、パフォーマンスの改善および生産性の向上が図れます。たとえば、PL/SQLを使用しないと、Oracleは一度に1つずつSQL文を処理する必要があります。SQL文ごとにサーバーに対する別のコールが発生し、その結果、オーバーヘッドが増加します。しかし、PL/SQLを使用すると、SQL文のブロック全体をサーバーに送信できます。これにより、アプリケーションとOracle間の通信は最小限に抑えられます。
5.1.3 カーソルFORループ
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;
レコード中のフィールドの参照にはドット表記法を使用することに注意してください。
5.1.4 サブプログラム
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を使用して)データベースに格納できます。こうすることで、サブプログラムをその都度再コンパイルせずに、複数のアプリケーションからコールできます。
5.1.5 パラメータ・モード
仮パラメータの動作を定義するには、パラメータ・モードを使用します。パラメータ・モードにはIN (デフォルト)、OUTおよびIN OUTの3つがあります。7INパラメータを使用すると、コールされるプログラムに値を渡せます。OUTパラメータを使用すると、サブプログラムのコール元に値を戻せます。IN OUTパラメータを使用すると、コールされるサブプログラムに初期値を渡し、コール元には更新された値を戻せます。
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。表3-6は、データ型間の有効な変換を示しています。
5.1.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;
パッケージ仕様部内の宣言のみ参照可能で、アプリケーションからアクセスできます。パッケージ本体中の詳細な実装内容は非表示のためアクセスできません。
5.1.7 PL/SQL表
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) ...
5.1.8 ユーザー定義のレコード
%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 ...
5.2 PL/SQLブロックの埋込みについて
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オプションも指定する必要があります。詳細は、プリコンパイラ・オプションの使用についてを参照してください。
5.3 ホスト変数の使用方法について
ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。
たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLではデータベースにアクセスし、ホスト変数を使用して結果をホスト・プログラムに戻すことができます。
PL/SQLブロック内では、ホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる場所であればそのブロックのどこにでも使用できます。ただし、文字ホスト変数は、長さが255文字以内です。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。
5.3.1 例
次の例では、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がブロック内で設定されていることに注意してください。
5.3.2 複雑な例
次の例では、ユーザーに銀行口座番号、取引の種類、取引金額の入力を要求し、その後、口座の借方または貸方に記入します。口座が存在しない場合、例外が発生します。取引が完了すると、そのステータスを表示します。
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;
5.3.3 VARCHAR擬似型
プログラム要件への対応で、可変長文字列の宣言に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;
5.4 インジケータ変数の使用について
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では、ブロックに入るときに標識変数の値がチェックされ、ブロックから出るときにその値が正しく設定されます。
5.4.1 NULLの処理
ブロックに入るとき、インジケータ変数の値が-1であれば、PL/SQLでは自動的にNULLがホスト変数に割り当てられます。ブロックから出るとき、ホスト変数がNULLであれば、PL/SQLにより自動的に-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;
5.4.2 切り捨てられた値の処理
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;
5.5 ホスト配列の使用について
入力ホスト配列およびインジケータ配列は、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表 | CHAR | DATE | LONG | LONG RAW | NUMBER | RAW | ROWID | VARCHAR2 |
|---|---|---|---|---|---|---|---|---|
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
|||||||
|
|
_/ |
_/ |
||||||
|
|
_/ |
_/ |
_/ |
_/ |
||||
|
|
_/ |
_/ |
||||||
|
|
_/ |
|||||||
|
|
_/ |
_/ |
||||||
|
|
_/ |
|||||||
|
|
_/ |
_/ |
_/ |
_/ |
||||
|
|
_/ |
|||||||
|
|
_/ |
_/ |
_/ |
_/ |
||||
|
|
_/ |
_/ |
_/ |
_/ |
||||
|
|
_/ |
|||||||
|
|
_/ |
_/ |
5.5.1 ARRAYLEN文
入力ホスト配列を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に送信されるとき、一緒に送られるホスト配列はずっと小さくなります。これにより、時間を節約し、ネットワーク化された環境でネットワークの通信量を削減できます。
5.6 カーソルの使用方法について
すべての埋込みSQL文は、DECLARE CURSOR文で明示的に、またはプリコンパイラによって暗黙的に、カーソルを割り当てられます。内部的には、Oracle Precompilersはカーソル・キャッシュと呼ばれるキャッシュを維持して、埋込み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
5.7 ストアド・サブプログラム
無名ブロックとは異なり、PL/SQLサブプログラム(プロシージャおよびファンクション)は、別々にコンパイルしてOracleデータベースに格納し、起動できます。SQL*PlusなどのOracleツールを使用して明示的に作成したサブプログラムを、ストアド・サブプログラムと呼びます。コンパイルされ、データ・ディクショナリに格納されたストアド・サブプログラムは、データベース・オブジェクトとなり、再コンパイルせずに再実行できます。
PL/SQLブロック内のサブプログラムまたはストアド・サブプログラムがアプリケーションによってOracleに送られると、それはインライン・サブプログラムと呼ばれます。Oracleでは、インライン・サブプログラムをコンパイルし、システム・グローバル領域(SGA)にキャッシュしますが、ソースまたはオブジェクト・コードをデータ・ディクショナリに格納することはありません。
パッケージ内で定義されているサブプログラムは、そのパッケージの一部とみなされ、パッケージ・サブプログラムと呼ばれます。パッケージ内で定義されていないストアド・サブプログラムは、スタンドアロン・プログラムと呼ばれます。
5.7.1 ストアド・サブプログラムの作成
次の例に示すように、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ではエラーではなく警告が出ます。
5.7.2 ストアド・サブプログラムのコール
ホスト・プログラムからストアド・サブプログラムを起動(コール)するには、無名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仮パラメータは割り当てられた値であることが必要です。そうしないと、対応する実パラメータの値が未確定になります。
5.7.3 リモート・アクセス
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;
5.7.4 ストアド・サブプログラムに関する情報の取得
プログラム要件への対応では、ホスト・プログラムにOCIコールを埋め込む方法を学習しました。ライブラリ・ルーチンSQLLDAをコールして、LDAを設定すると、OCIコールODESSPを使用して、ストアド・サブプログラムに関する有益な情報を取得できます。ODESSPをコールするときには、有効なLDAとサブプログラムの名前を渡す必要があります。パッケージ・プログラムの場合は、パッケージ名も渡す必要があります。ODESSPからは、各サブプログラム・パラメータについて、そのデータ型、サイズ、位置などの情報が戻されます。
Oracle付属のDBMS_DESCRIBEパッケージでは、describe_procedureプロシージャも使用できます。
5.8 動的PL/SQLの使用について
Oracleプリコンパイラでは、PL/SQLブロック全体が1つのSQL文のように扱われます。つまり、PL/SQLブロックをホスト変数の文字列に格納できることを意味します。その場合、ブロックにホスト変数が含まれていなければ、動的SQL方法1を使用してPL/SQL文字列を実行できます。ブロックにホスト変数が含まれていて、その数がわかっている場合は、動的SQL方法2を使用してPL/SQL文字列を準備し、実行します。ブロックに含まれるホスト変数の数がわからない場合は、動的SQL方法4を使用する必要があります。詳細は、動的SQLの使用方法を参照してください。