| Pro*C/C++プログラマーズ・ガイド 12c リリース1(12.1) B71397-03 |
|
 前 |
 次 |
無名ブロックとは異なり、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表からひとまとまりの行をフェッチする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]] ;
where
スキーマ
プロシージャを含むスキーマ
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 ; ...
関連項目:
CALL文の詳細は、CALL(実行可能埋込みSQL)を参照してください。
引数の渡し方およびその他の問題の詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
注意:
ログイン・データ領域(LDA)は、Oracleではサポートされなくなりました。Oracleの次のバージョンでは、Pro*C/C++プログラムにOCIリリース7コールを埋め込む機能は廃止になります。
データ型とホスト変数では、ホスト・プログラムにOCIコールを埋め込む方法を説明しています。ライブラリ・ルーチンSQLLDAをコールしてLDAを設定すると、OCIコールodesspを使用して、ストアド・サブプログラムに関する有益な情報を取得できます。odesspをコールするときには、有効なLDAとサブプログラムの名前を渡す必要があります。パッケージ・サブプログラムの場合は、パッケージ名も渡す必要があります。odesspからは、各サブプログラム・パラメータに関する情報(データ型、サイズ、位置など)が戻されます。詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
DBMS_DESCRIBEパッケージでは、DESCRIBE_PROCEDUREストアド・プロシージャを使用できます。このプロシージャの詳細は、Oracle Databaseアドバンスト・アプリケーション開発者ガイドを参照してください。
