ストアド・サブプログラム

無名ブロックとは異なり、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;

ストアド・サブプログラムに関する情報の取得

プログラム要件への対応では、ホスト・プログラムにOCIコールを埋め込む方法を学習しました。ライブラリ・ルーチンSQLLDAをコールして、LDAを設定すると、OCIコールODESSPを使用して、ストアド・サブプログラムに関する有益な情報を取得できます。ODESSPをコールするときには、有効なLDAとサブプログラムの名前を渡す必要があります。パッケージ・プログラムの場合は、パッケージ名も渡す必要があります。ODESSPからは、各サブプログラム・パラメータについて、そのデータ型、サイズ、位置などの情報が戻されます。

Oracle付属のDBMS_DESCRIBEパッケージでは、describe_procedureプロシージャも使用できます。