| Pro*C/C++プログラマーズ・ガイド 11gリリース2（11.2） B61343-01 |
|
 戻る |
 次へ |
この章では、PL/SQLトランザクション処理ブロックをプログラム内に埋め込むことによりパフォーマンスを改善する方法について説明します。この章の項目は、次のとおりです。
この項では、PL/SQLによって提供される次のような機能および利点について説明します。
PL/SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLにより、オーバーヘッドの削減、パフォーマンスの改善、生産性の向上が図れます。たとえば、PL/SQLを使用しないと、OracleはSQL文を一度に1つずつしか処理できません。その結果、SQL文ごとにサーバーへ別々のコールが発生し、オーバーヘッドが増加します。しかし、PL/SQLを使用すると、サーバーにSQL文のブロック全体を送ることができます。これにより、アプリケーションとOracleとの間の通信は最小限になります。
PL/SQLは、Oracleサーバーと密接に統合されています。たとえば、PL/SQLデータ型の大部分は、Oracleデータ・ディクショナリにとっても固有なデータ型です。さらに、次の例に示すとおり、データ・ディクショナリ内に格納された列定義に基づいて変数を宣言するための%TYPE属性を指定できます。
job_title emp.job%TYPE;
したがって、列の厳密なデータ型を知る必要はありません。しかも、列定義を変更すれば、変数宣言もそれに応じて自動的に変更されます。これにより、データ独立性を提供し、メンテナンス・コストを削減し、データベース変更時にプログラムが順応できます。
PL/SQLを使用すれば、カーソルを定義して操作するためにDECLARE文、OPEN文、FETCH文およびCLOSE文を指定する必要はありません。かわりに、カーソルFORループを指定できます。カーソル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表の中に挿入されます。次に、新しい番号がコール側に戻されます。
仮パラメータの動作を定義するには、パラメータ・モードを使用します。パラメータ・モードには、IN（デフォルト）、OUTおよびIN OUTの3種類があります。INパラメータを使用すると、コールされるサブプログラムに値を渡すことができます。OUTパラメータを使用すると、サブプログラムのコール側に値を戻すことができます。IN OUTパラメータを使用すると、コールされるサブプログラムに初期値を渡し、更新された値をコール側に戻すことができます。
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能である必要があります。表7-1は、データ型間の正当な変換を示しています。
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型、PLS_INTEGER型またはVARCHAR2型のいずれかに属している必要があります。
ブロック、プロシージャ、ファンクションまたはパッケージのいずれかの宣言部で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, -- default is NULL allowed
dname CHAR(9),
loc CHAR(14));
コンポーネント宣言は変数宣言に似ているため注意してください。各コンポーネントには、固有の名前と特定のデータ型があります。どのコンポーネント宣言にもNOT NULLオプションを追加できます。これにより、コンポーネントへのNULLの割当てを防止します。
次の例に示すように、一度DeptRecTypを定義すれば、その型のレコードを宣言できます。
dept_rec DeptRecTyp;
識別子dept_recはレコード全体を表しています。
ドット表記法を使用すると、レコード内の個別コンポーネントを参照できます。たとえば、次のようにdept_recレコード内でdnameコンポーネントを参照します。
dept_rec.dname ...
Pro*C/C++プリコンパイラでは、PL/SQLブロックが1つの埋込みSQL文と同様に扱われます。したがって、PL/SQLブロックは、プログラム内のSQL文を記述できる位置であればどこにでも記述できます。
PL/SQLブロックをPro*C/C++プログラム内に埋め込むには、次のように、EXEC SQL EXECUTEおよびEND-EXECキーワードでPL/SQLブロックを囲むのみです。
EXEC SQL EXECUTE DECLARE ... BEGIN ... END; END-EXEC;
END-EXECキーワードの後には、セミコロンを付ける必要があります。
プログラムを作成した後に、通常の方法でソース・ファイルをプリコンパイルします。
プログラムに埋込みPL/SQLが含まれている場合、PL/SQLはOracleサーバーによって解析される必要があるため、必ずSQLCHECK=SEMANTICSコマンドライン・オプションを指定してください。サーバーに接続する場合には、SQLCHECK=SEMANTICSのみでなくUSERIDオプションも指定してください。
ホスト変数はホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数とPL/SQLは共有できます。これにより、PL/SQLでのホスト変数の設定および参照が可能になります。
たとえば、ユーザーに対して情報を提供し、ホスト変数を使用してその情報をPL/SQLブロックに渡すことができます。これにより、PL/SQLを使用してデータベースにアクセスし、ホスト変数を介してその結果をホスト・プログラムに戻すことができます。
PL/SQLブロック内ではホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる位置であればどこにでも使用できます。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。
|
注意: PL/SQLブロックでVARCHAR、CHARZまたはSTRING型を出力ホスト変数として使用するには、ブロックを入力する前に長さを初期化する必要があります。次の例のように、長さはVARCHAR、CHARZまたはSTRINGの宣言された（最大の）長さに設定してください。 |
次の例は、PL/SQLでホスト変数を使用する方法を示しています。プログラムでは、ユーザーに従業員番号の入力を求めるプロンプトが表示され、その番号に応じて、従業員の役職名、雇用日、給与が表示されます。
char username[100], password[20];
char job_title[20], hire_date[9], temp[32];
int emp_number;
float salary;
#include <sqlca.h>
printf("Username? \n");
gets(username);
printf("Password? \n");
gets(password);
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oracle\n");
for (;;)
{
printf("Employee Number (0 to end)? ");
gets(temp);
emp_number = atoi(temp);
if (emp_number == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting program\n");
break;
}
/*-------------- 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 -----------------*/
printf("Number Job Title Hire Date Salary\n");
printf("------------------------------------\n");
printf("%6d %8.8s %9.9s %6.2f\n",
emp_number, job_title, hire_date, salary);
}
...
exit(0);
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
ホスト変数emp_numberはPL/SQLブロックが入力される前に設定され、ホスト変数job_title、hire_dateおよびsalaryはブロック内で設定されることに注意してください。
次の例では、ユーザーに銀行口座番号、取引の種類、取引金額の入力を求めるプロンプトが表示されてから、口座に記帳されます。口座が存在しない場合は、例外が発生します。取引が完了すると、そのステータスが表示されます。
#include <stdio.h>
#include <sqlca.h>
char username[20];
char password[20];
char status[80];
char temp[32];
int acct_num;
double trans_amt;
void sql_error();
main()
{
char trans_type;
strcpy(password, "TIGER");
strcpy(username, "SCOTT");
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oracle\n");
for (;;)
{
printf("Account Number (0 to end)? ");
gets(temp);
acct_num = atoi(temp);
if(acct_num == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting program\n");
break;
}
printf("Transaction Type - D)ebit or C)redit? ");
gets(temp);
trans_type = temp[0];
printf("Transaction Amount? ");
gets(temp);
trans_amt = atof(temp);
/*----------------- 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 ----------------------- */
printf("\nStatus: %s\n", status);
}
exit(0);
}
void
sql_error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
}
VARCHARデータ型は、可変長文字列の宣言に使用できます。VARCHARが入力ホスト変数の場合は、どのくらいの長さを前提にすればよいかをOracleに通知する必要があります。したがって、文字列コンポーネントに格納される値の実際の長さに、長さコンポーネントを設定してください。
VARCHARが出力ホスト変数の場合は、Oracleで自動的に長さコンポーネントが設定されます。ただし、PL/SQLブロックでVARCHAR（CHARZおよびSTRING）出力ホスト変数を使用するには、ブロックを入力する前に長さコンポーネントを初期化する必要があります。したがって、次の例に示すとおり、長さコンポーネントはVARCHARの宣言された（最大の）長さに設定してください。
int emp_number;
varchar emp_name[10];
float salary;
...
emp_name.len = 10; /* initialize length component */
EXEC SQL EXECUTE
BEGIN
SELECT ename, sal INTO :emp_name, :salary
FROM emp
WHERE empno = :emp_number;
...
END;
END-EXEC;
...
PL/SQLブロックでは、Cポインタまたは配列構文を使用しないでください。PL/SQLコンパイラはCホスト変数の式を認識できないため、解析できません。たとえば、次のSQL文は無効です。
EXEC SQL EXECUTE
BEGIN
:x[5].name := 'SCOTT';
...
END;
END-EXEC;
構文エラーを回避するには、プレースホルダ（一時変数）を使用して、構造体の移入を行う構造体フィールドのアドレスを保持します。たとえば、次の例は有効です。
name = x[5].name ;
EXEC SQL EXECUTE
BEGIN
:name := ...;
...
END;
END-EXEC;
PL/SQLではNULL値を操作できるため、標識変数を必要としません。たとえば、PL/SQL内でIS NULL演算子を使用すると、次のようにNULL値をテストできます。
IF variable IS NULL THEN ...
その後、次のように代入演算子（:=）を使用して、NULL値を指定できます。
variable := NULL;
ただし、C言語のようなホスト言語はNULL値を操作できないため、標識変数を必要とします。埋込みPL/SQLで、次の用途でのこの要件を満たすには、標識変数を使用します。
ホスト・プログラムからNULL入力値を受け取る。
NULL値または切り捨てられた値をホスト・プログラムに出力する。
PL/SQLブロックで標識変数を使用する場合は、次の規則に従ってください。
標識変数は単独では参照できません。対応するホスト変数に追加する必要があります。
標識変数付きでホスト変数を参照する場合、同一ブロック中では常に同じ方法で参照する必要があります。
次の例では、標識変数ind_commがSELECT文でそのホスト変数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であれば、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;
...
入力ホスト配列およびインジケータ配列は、PL/SQLブロックに渡せます。入力ホスト配列およびインジケータ配列は、BINARY_INTEGER型またはPLS_INTEGER型のPL/SQL変数を使用して索引付けができます。VARCHAR2型のキーは使用できません。通常は、ホスト配列全体がPL/SQLに渡されます。ただし、より小さい配列サイズを指定するには、ARRAYLEN文（後述）を使用できます。
さらに、ホスト配列のすべての値をPL/SQL表の複数の行に割り当てるために、プロシージャ・コールを使用できます。配列の添字範囲がm .. nの場合、対応するPL/SQL表の索引範囲は常に1 .. n - m + 1になります。たとえば、配列の添字範囲が5 .. 10の場合、対応するPL/SQL表の索引範囲は、1 .. (10 - 5 + 1)または1 .. 6です。
次の例では、salaryという名前の配列をPL/SQLブロックに渡し、そのブロックのファンクション・コール内でその配列を使用しています。この関数は一連の数値の中央値を検出するため、medianという名前が付けられています。この関数の仮パラメータには、num_tabというPL/SQL表が含まれています。この関数コールは、実パラメータsalary内のすべての値を仮パラメータnum_tab内の行に割り当てます。
...
float salary[100];
/* populate the host array */
EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
median_salary REAL;
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表のすべての行の値をホスト配列の対応する要素に割り当てる場合にも、プロシージャ・コールを使用できます。
表7-1に、PL/SQL表の行の値とホスト配列のエレメント間での有効な変換を示します。たとえば、LONG VARCHAR型のホスト配列は、VARCHAR2型、LONG型、RAW型またはLONG RAW型のPL/SQL表と互換性があります。ただし、CHAR型のPL/SQL表とは互換性がないため注意してください。
表7-1 正当なデータ型変換
| PL/SQL表→ホスト配列 | CHAR | DATE | LONG | LONG RAW | NUMBER | RAW | ROWID | VARCHAR2 |
|---|---|---|---|---|---|---|---|---|
|
CHARF |
可 |
- |
- |
- |
- |
- |
- |
- |
|
CHARZ |
可 |
- |
- |
- |
- |
- |
- |
- |
|
DATE |
- |
可 |
- |
- |
- |
- |
- |
- |
|
DECIMAL |
- |
- |
- |
- |
可 |
- |
- |
- |
|
DISPLAY |
- |
- |
- |
- |
可 |
- |
- |
- |
|
FLOAT |
- |
- |
- |
- |
可 |
- |
- |
- |
|
INTEGER |
- |
- |
- |
- |
可 |
- |
- |
- |
|
LONG |
可 |
- |
可 |
- |
- |
- |
- |
- |
|
LONG VARCHAR |
- |
- |
可 |
可 |
- |
可 |
- |
可 |
|
LONG VARRAW |
- |
- |
- |
可 |
- |
可 |
- |
- |
|
NUMBER |
- |
- |
- |
- |
可 |
- |
- |
- |
|
RAW |
- |
- |
- |
可 |
- |
可 |
- |
- |
|
ROWID |
- |
- |
- |
- |
- |
- |
可 |
- |
|
STRING |
- |
- |
可 |
可 |
- |
可 |
- |
可 |
|
UNSIGNED |
- |
- |
- |
- |
可 |
- |
- |
- |
|
VARCHAR |
- |
- |
可 |
可 |
- |
可 |
- |
可 |
|
VARCHAR2 |
- |
- |
可 |
可 |
- |
可 |
- |
可 |
|
VARNUM |
- |
- |
- |
- |
可 |
- |
- |
- |
|
VARRAW |
- |
- |
- |
可 |
- |
可 |
- |
- |
|
注意: Pro*C/C++プリコンパイラでは、ホスト配列の使用方法はチェックされません。たとえば、索引範囲チェックは実行されません。 |
入力ホスト配列をPL/SQLブロックに渡して処理するとします。デフォルトでは、入力ホスト配列をバインドすると、Pro*C/C++プリコンパイラは、その宣言されたサイズを使用します。ただし、その配列のすべては処理しない場合もあります。この場合には、ARRAYLEN文を使用して、より小さい配列サイズを指定できます。ARRAYLEN文では、ホスト配列をホスト変数と対応付け、そのホスト変数がより小さいサイズを格納します。文の構文は次のとおりです。
EXEC SQL ARRAYLEN host_array (dimension) [EXECUTE];
dimensionは4バイト整数型のホスト変数です。リテラルや式ではありません。
EXECUTEはオプションのキーワードです。
ARRAYLEN文はhost_arrayおよびdimensionの宣言とともに（ただし、それらの宣言よりも後に）表示する必要があります。ホスト配列の中にはオフセットを指定できません。しかし、この目的にC言語の機能が使用できる場合もあります。次の例では、ARRAYLENを使用して、bonusという名前のCホスト配列のデフォルトのサイズを上書きしています。
float bonus[100];
int dimension;
EXEC SQL ARRAYLEN bonus (dimension);
/* populate the host array */
...
dimension = 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, :dimension);
...
END;
END-EXEC;
ARRAYLENでホスト配列のサイズが100要素から25要素に減少するため、25の配列要素のみがPL/SQLブロックに渡されます。結果として、PL/SQLブロックが実行のためにOracleに送られるとき、より小さくなったホスト配列が一緒に送られます。これにより、時間の節約になり、ネットワーク環境での通信量が低減します。
動的SQL方法2のEXEC SQL EXECUTE文で使用されるホスト配列には、オプション・キーワードEXECUTEの有無によって、2つの異なる解釈があります。
デフォルト（ARRAYLEN文にEXECUTEキーワードがないとき）:
ホスト配列はPL/SQLブロックが実行される回数が決定されるときに考慮されます。（最小値の配列サイズが使用されます。）
ホスト配列はPL/SQL索引表に結合されません。
EXECUTEキーワードが存在しているとき:
ホスト配列は索引表に結合されます。
PL/SQLブロックは1回のみ実行されます。
EXEC SQL EXECUTE文で指定されているすべてのホスト変数は、次のいずれかです。
ARRAYLEN ... EXECUTE文で指定
スカラー
たとえば、次のPL/SQLプロシージャを仮定します。
CREATE OR REPLACE PACKAGE pkg AS
TYPE tab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
PROCEDURE proc1 (parm1 tab, parm2 NUMBER, parm3 tab);
END;
次のPro*C/C++ファンクションは、特定のPL/SQLブロックを実行する回数を決定するためにホスト配列を使用する方法を示しています。この場合、PL/SQLブロックはemp表に新しい行が3行あるために3回実行されます。
func1()
{
int empno_arr[5] = {1111, 2222, 3333, 4444, 5555};
char *ename_arr[3] = {"MICKEY", "MINNIE", "GOOFY"};
char *stmt1 = "BEGIN INSERT INTO emp(empno, ename) VALUES :b1, :b2; END;";
EXEC SQL PREPARE s1 FROM :stmt1;
EXEC SQL EXECUTE s1 USING :empno_arr, :ename_arr;
}
次のPro*C/C++ファンクションは、動的方法2でホスト配列をPL/SQL索引表にバインドする方法を示しています。すべてのホスト配列のARRAYLEN...EXECUTE文の存在がEXEC SQL EXECUTE文内で指定されていることに注意してください。
func2()
{
int ii = 2;
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;
char *stmt2 = "begin pkg.proc1(:v1, :v2, :v3); end; ";
EXEC SQL PREPARE s2 FROM :stmt2;
EXEC SQL EXECUTE s2 USING :int_tab, :ii, :int_tab;
}
次のPro*C/C++ファンクションは、int_arrのARRAYLEN...EXECUTE文がないために、プリコンパイル時警告を生じます。
func3()
{
int int_arr[3];
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;
char *stmt3 = "begin pkg.proc1(:v1, :v2, :v3); end; ";
EXEC SQL PREPARE s3 FROM :stmt3;
EXEC SQL EXECUTE s3 USING :int_tab, :int_arr, :int_tab;
}
プログラムで同時に使用できるカーソルの最大数は、データベース初期化パラメータOPEN_CURSORSによって設定されます。埋込みPL/SQLブロックの実行中に、1つのカーソル、親カーソルがブロック全体に対応付けられ、1つのカーソル、子カーソルが埋込みPL/SQLブロックの各SQL文に対応付けられます。親カーソルと子カーソルは、両方とも、OPEN_CURSORS制限にカウントされます。
次の計算は、使用するカーソルの最大数を決定する方法を示します。カーソル数の合計がOPEN_CURSORSを超えないようにします。
SQL statement cursors PL/SQL parent cursors PL/SQL child cursors + 6 cursors for overhead -------------------------- Sum of cursors in use
プログラムで、OPEN_CURSORSによって設定された制限を超える数のカーソルが使用された場合、エラーが発生します。
無名ブロックとは異なり、PL/SQLサブプログラム（プロシージャおよびファンクション）およびJavaメソッドは別個にコンパイルされ、Oracleデータベースに格納されて起動されます。
Javaメソッドの作成方法の詳細は、『Oracle Database Java開発者ガイド』を参照してください。
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ではない）で始まります。ただし、PL/SQL終了記号のEND-EXECで終了する点は、他のCREATE埋込み文とは異なります。
次の例では、EMP表から1組の行を取り出す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ブロックまたはCALL埋込みSQL文のいずれかを使用できます。
次の例では、raise_salaryという名前のスタンドアロン・プロシージャをコールします。
EXEC SQL EXECUTE
BEGIN
raise_salary(:emp_id, :increase);
END;
END-EXEC;
ストアド・サブプログラムにはパラメータを組み込めることに注意してください。この例では、実パラメータemp_idおよびincreaseはC言語のホスト変数です。
次の例では、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表、PL/SQLユーザー定義レコード、プロシージャ・コールまたは式を使用できます。しかし、OUT実パラメータは、リテラル、プロシージャ・コールまたは式にしないでください。
埋込みPL/SQLブロックでSQLCHECK=SEMANTICSプリコンパイラ・オプションを使用する必要があります。
次の例では、仮パラメータのうち3つがPL/SQL表であり、対応する実パラメータはホスト配列です。プログラムでは、get_employeesストアド・プロシージャがコールされ、従業員のデータが一群ずつ表示され、データがなくなるまで繰り返されます。このプログラムは、demoディレクトリのsample9.pcファイルに入っており、オンラインで利用できます。CALLDEMOストアド・パッケージ作成用のSQLスクリプトは、calldemo.sqlファイルに入っています。
/*************************************************************
Sample Program 9: Calling a stored procedure
This program connects to ORACLE using the SCOTT/TIGER
account. The program declares several host arrays, then
calls a PL/SQL stored procedure (GET_EMPLOYEES in the
CALLDEMO package) that fills the table OUT parameters. The
PL/SQL procedure returns up to ASIZE values.
Sample9 keeps calling GET_EMPLOYEES, getting ASIZE arrays
each time, and printing the values, until all rows have been
retrieved. GET_EMPLOYEES sets the done_flag to indicate "no
more data."
*************************************************************/
#include <stdio.h>
#include <string.h>
EXEC SQL INCLUDE sqlca.h;
typedef char asciz[20];
typedef char vc2_arr[11];
EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;
/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;
asciz username;
asciz password;
int dept_no; /* which department to query? */
vc2_arr emp_name[10]; /* array of returned names */
vc2_arr job[10];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
EXEC SQL END DECLARE SECTION;
long SQLCODE;
void print_rows(); /* produces program output */
void sql_error(); /* handles unrecoverable errors */
main()
{
int i;
char temp_buf[32];
/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
strcpy(username, "scott");
strcpy(password, "tiger");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n\n", username);
printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);/* Print column headers. */
printf("\n\n");
printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%s\n", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop.
* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back.
*/
for (;;)
{
EXEC SQL EXECUTE
BEGIN calldemo.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);
}
void
print_rows(n)
int n;
{
int i;
if (n == 0)
{
printf("No rows retrieved.\n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2f\n",
emp_name[i], job[i], salary[i]);
}
/* Handle errors. Exit on any error. */
void
sql_error()
{
char msg[512];
int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);
printf("\nORACLE error detected:");
printf("\n%.*s \n", msg_len, msg);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能である必要があります。また、ストアド・プロシージャの終了前には、すべてのOUT仮パラメータは割り当てられた値である必要があります。そうでなければ、対応する実パラメータの値は未確定です。
無名PL/SQLブロックを使用する場合、SQLCHECK=SEMANTICSは必須です。
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;
前述の埋込みPL/SQLブロックの概念はCALL文にも当てはまります。CALL埋込みSQL文の書式は次のとおりです。
EXEC SQL CALL [schema.] [package.]stored_proc[@db_link](arg1, ...) [INTO :ret_var [[INDICATOR]:ret_ind]] ;
パラメータは、次のとおりです。
schema
プロシージャを含むスキーマ
package
プロシージャを含むパッケージ
stored_proc
コールするJavaまたはPL/SQLストアド・プロシージャ
db_link
オプション・リモート・データベース・リンク
arg1...
渡された引数（変数、リテラル、式）
ret_var
結果を受け取るオプション・ホスト変数
ind_var
ret_varのオプション・標識変数
CALL文には、SQLCHECK=SYNTAXまたはSEMANTICSのいずれかを使用できます。
入力として整数値をとるPL/SQL関数fact（mathpkgパッケージに格納されています）を作成し、その階乗の整数値を戻します。
EXEC SQL CREATE OR REPLACE PACKAGE BODY mathpkg as
function fact(n IN INTEGER) RETURN INTEGER AS
BEGIN
IF (n <= 0) then return 1;
ELSE return n * fact(n - 1);
END IF;
END fact;
END mathpkge;
END-EXEC.
次に、そのCALL文を使用しているPro*C/C++アプリケーションで、factを使用します。
... int num, fact; ... EXEC SQL CALL mathpkge.fact(:num) INTO :fact ; ...
|
関連項目
|
|
注意: ログイン・データ領域（LDA）は、Oracleではサポートされなくなりました。Oracleの次のバージョンでは、Pro*C/C++プログラムにOCIリリース7コールを埋め込む機能は廃止になります。 |
第4章「データ型とホスト変数」では、ホスト・プログラムにOCIコールを埋め込む方法を説明しています。ライブラリ・ルーチンSQLLDAをコールしてLDAを設定した後に、OCIコールodesspを使用して、ストアド・サブプログラムに関する情報を取得します。odesspをコールするときには、有効なLDAとサブプログラム名を渡す必要があります。パッケージ・サブプログラムの場合は、パッケージ名も渡す必要があります。odesspは、各サブプログラム・パラメータに関する情報（データ型、サイズ、位置など）を戻します。詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
DBMS_DESCRIBEパッケージでは、DESCRIBE_PROCEDUREストアド・プロシージャを使用できます。このプロシージャの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
PL/SQLでは、外部プロシージャのC言語関数をコールできます。外部プロシージャは、Dynamic Link Library（DLL）またはSolarisの.soライブラリなどに格納されています。
外部プロシージャをサーバーで実行する場合、同一トランザクション中でSQLおよびPL/SQLが実行されるようにサーバーにコールバックできます。サーバーで外部プロシージャを実行すると、クライアントで実行した場合よりも処理速度が速く、外部システムおよびデータ・ソースと、データベース・サーバーとのインタフェースとして使用できます。
サーバー側の外部C言語関数を実行する場合は、関数内でREGISTER CONNECT埋込みSQL文を使用する必要があります。文の構文は次のとおりです。
EXEC SQL REGISTER CONNECT USING :epctx [RETURNING :host_context] ;
epctxは、OCIExtProcContextへのタイプ・ポインタの外部プロシージャ・コンテキストです。epctxはPL/SQLによってプロシージャに渡されます。
host_contextは、外部プロシージャによって戻されるランタイム・コンテキストです。現在の設定は、デフォルト（グローバル）・コンテキストです。
REGISTER CONNECT文により、カレントのOracle接続およびトランザクションに対応付けられたOCIハンドル・セット（OCIEnv、OCISvcCtxおよびOCIError）が戻されます。これらのハンドルは、グローバルSQLLIBランタイム・コンテキストのPro*C/C++デフォルトである名前なし接続の定義に使用されます。このため、REGISTER CONNECTが、CONNECT文のかわりに使用されます。
後続の埋込みSQL文では、このOCIハンドル・セットを使用します。後続の埋込みSQL文は、グローバルSQLLIBランタイム・コンテキスト、名前なし接続に対して実行されます。別々にプリコンパイルされたプログラム・ユニットにある埋込みSQL文も同様です。コミットされていない変更は無効です。今後のバージョンでは、（非デフォルトの）ランタイム・コンテキストはオプションのRETURNING句で返されるようになります。
グローバル・ランタイム・コンテキストのアクティブなデフォルト接続はまだありません。すでに接続が確立しているときにREGISTER CONNECTを使用すると、ランタイム・エラーが戻されます。
|
関連項目 OCI関数の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。 |
実際の業務では、外部プロシージャは複数の異なるアプリケーションから再利用できるようにすることをお薦めします。
外部プロシージャには次の規則があります。
外部プロシージャを使用できるのはCのみです。C++外部プロシージャはサポートされていません。
外部プロシージャ・コンテキストに接続した場合、接続を追加できません。ランタイム・エラーが発生します。
マルチスレッドの外部プロシージャはサポートされていません。EXEC SQL ENABLE THREADS文は使用できません。ランタイム・エラーが発生します。Pro*C/C++では、ここで説明している外部プロシージャを使用しない場合は、アプリケーションでのマルチスレッドをサポートしています。
DDL文を使用できません。ランタイム・エラーが発生します。
EXEC SQL COMMITおよびEXEC SQL ROLLBACKなどのトランザクション制御文を使用できません。
EXEC SQL OBJECTなどのオブジェクト・ナビゲーション文を使用できません。 .
EXEC SQL LOB文のポーリングを行うことはできません。
EXEC TOOLS文を使用できません。ランタイム・エラーが発生します。
外部プロシージャextp1を作成する場合の簡単な例を示します。
外部Cプロシージャを格納するには、コードのコンパイルおよびリンクを行い、DLLなどのライブラリに格納します。
次のSQLコマンドを1回実行して、外部プロシージャextp1を登録します。
CREATE OR REPLACE PROCEDURE extp1 AS EXTERNAL NAME "extp1" LIBRARY mylib WITH CONTEXT PARAMETERS(CONTEXT) ;
mylibは、プロシージャextp1が格納されるライブラリです。WITH CONTEXTが指定されているため、このプロシージャは、引数型OCIExtProcContext*で暗黙的にコールされます。このコールでは、コンテキストが省略されていますが、プロシージャには渡されます。ただし、CREATE文のCONTEXTキーワードは、プレース・マーカーとして指定されています。
このコンテキスト・パラメータは、extp1の内側のEXEC SQL REGISTER CONNECT文で参照されます。
外部プロシージャのコール方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
外部プロシージャは、SQL*Plusから次のようにコールされます。
SQL> BEGIN INSERT INTO emp VALUES(9999,'JOHNSON','SALESMAN',7782, sysdate, 1200,150,10); extp1; END;
void extp1 (epctx)
OCIExtProcContext *epctx;
{
char name[15];
EXEC SQL REGISTER CONNECT USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE empno = 9999;
return;
err: SQLExtProcError(SQL_SINGLE_RCTX,sqlca.sqlerrm.sqlerrmc,sqlca.sqlerrm.sqlerrml);
return;
}
SQLLIB関数SQLExtProcError()を使用すると、外部Cプロシージャでエラーが発生した場合にPL/SQLに制御を戻すことができます。関数とその引数は次のとおりです。
SQLExtProcError (ctx, msg, msglen)
パラメータは、次のとおりです。
ctx (IN) sql_context *
この関数は、REGISTER CONNECT文のターゲットSQLLIBランタイム・コンテキストです。REGISTER CONNECT文は、この関数が起動される前に実行する必要があります。現在、グローバル・ランタイム・コンテキストのみがサポートされています。
msg (OUT) char *
エラー・メッセージのテキスト
msglen (OUT) size_t
メッセージのバイト単位の長さ
この関数が実行されると、SQLLIBによりOCIサービス関数OCIExtProcRaiseExcpWithMsgがコールされます。
メッセージは、SQLCAの構造体sqlerrmから出力されます。SQLCAの構造およびsqlerrmの説明は、「SQLCAの構造体」を参照してください。
SQLExtProcError()の使用方法の例です。
void extp1 (epctx)
OCIExtProcContext *epctx;
{
char name[15];
EXEC SQL REGISTER CONNECT USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE smpno = 9999;
return;
err:
SQLExtProcError (SQL_SINGLE_RCTX, sqlca.sqlerrm.sqlerrmc,
sqlca.sqlerrm.sqlerrml);
printf("\n%*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
return;
}
プリコンパイラでは、PL/SQLブロック全体が1つのSQL文として処理されます。つまり、PL/SQLブロックをホスト変数の文字列に格納できます。この場合、ブロックにホスト変数が含まれない場合は、動的SQL方法1を使用して、PL/SQL文字列をEXECUTEできます。ブロックにホスト変数が含まれる場合、変数の数がわかっているときは、動的SQL方法2を使用してPL/SQL文字列をPREPAREおよびEXECUTEできます。ブロックにホスト変数が含まれる場合、変数の数がわからないときは、動的SQL方法4を使用する必要があります。
