6 埋込みPL/SQL
PL/SQLトランザクション処理ブロックをプログラム内に埋め込むことにより、パフォーマンスを改善する方法を説明します。この章の内容は、次のとおりです。
6.1 PL/SQLの埋込み
Pro*COBOLは、PL/SQLブロックを単一の埋込みSQL文のように扱います。PL/SQLブロックは、SQL文を記述できる位置であればホスト・プログラム内のどこにでも記述できます。
ホスト・プログラムにPL/SQLブロックを埋め込むには、PL/SQLとの間で共有される変数を宣言し、PL/SQLブロックをEXEC SQL EXECUTEキーワードおよびEND-EXECキーワードで囲みます。
6.1.3 標識変数
PL/SQLブロックでは、標識変数は単独では参照できません。対応するホスト変数に追加する必要があります。また、標識変数付きでホスト変数を参照する場合、同一ブロック中では常に同じ方法で参照する必要があります。
6.2 PL/SQLの利点
この項では、PL/SQLによって提供される次のような機能および利点を説明します。
-
パフォーマンスの向上
-
Oracleとの統合
-
カーソルFORループ
-
プロシージャおよびファンクション
-
パッケージ
-
PL/SQL表
-
ユーザー定義レコード
関連項目:
PL/SQLの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。6.2.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;
レコード中のフィールドの参照にはドット表記法を使用することに注意してください。
6.2.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を使用してサブプログラムをデータベースに格納できます。)
6.2.4.1 パラメータ・モード
仮パラメータの動作を定義するには、パラメータ・モードを使用します。パラメータ・モードにはIN (デフォルト)、OUTおよびIN OUTの3つがあります。INパラメータを使用すると、コールされるサブプログラムに値を渡せます。OUTパラメータを使用すると、サブプログラムのコール元に値を戻せます。IN OUTパラメータを使用すると、コールされるサブプログラムに初期値を渡し、更新された値をコール元に戻すことができます。
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。表6-1に、データ型間の有効な変換を示します。
6.2.5 パッケージ
PL/SQLでは、論理的に関連する型、プログラム・オブジェクトおよびサブプログラムを1つのパッケージにまとめることができます。パッケージは、コンパイルしてデータベースに格納できます。これにより、パッケージの内容を複数のアプリケーションで共有できるようになります。
パッケージには通常、仕様部および本体の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;
パッケージ仕様部内の宣言のみ参照可能で、アプリケーションからアクセスできます。パッケージ本体中の詳細な実装内容は非表示のためアクセスできません。
6.2.6 PL/SQL表
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) ...
6.2.7 ユーザー定義レコード
%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 ...
6.3 PL/SQLブロックの埋込み
Pro*COBOLは、PL/SQLブロックを単一の埋込みSQL文のように扱います。そのため、PL/SQLブロックは、SQL文を記述できる位置であればホスト・プログラム内のどこにでも記述できます。
PL/SQLブロックをホスト・プログラム内に埋め込むには、次のように、キーワードEXEC SQL EXECUTEおよびEND-EXECでPL/SQLブロックを囲みます。
EXEC SQL EXECUTE
DECLARE
...
BEGIN
...
END;
END-EXEC.
プログラムでPL/SQLブロックを埋め込む場合、Pro*COBOLでPL/SQLを解析する必要があるため、プリコンパイラ・オプションSQLCHECK=SEMANTICSを指定する必要があります。サーバーに接続する場合も、オプションUSERIDを指定する必要があります。
6.4 ホスト変数およびPL/SQL
ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。
たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLを使用してデータベースにアクセスし、ホスト変数を介してその結果をホスト・プログラムに戻せるようになります。
PL/SQLブロック内ではホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる位置であればどこにでも使用できます。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。
6.4.1 PL/SQLの例
次の例では、PL/SQLにおけるホスト変数の使用方法を示します。プログラムはユーザーに従業員番号の入力を要求し、その番号に応じて、従業員の役職名、雇用日および給与を表示します。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(20) VARYING.
01 PASSWORD PIC X(20) VARYING.
01 EMP-NUMBER PIC S9(4) COMP.
01 JOB-TITLE PIC X(20) VARYING.
01 HIRE-DATE PIC X(9) VARYING.
01 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
...
DISPLAY 'Connected to Oracle'.
PERFORM
DISPLAY 'Employee Number (0 to end)? 'WITH NO ADVANCING
ACCEPT EMP-NUMBER
IF EMP-NUMBER = 0
EXEC SQL COMMIT WORK RELEASE END-EXEC
DISPLAY 'Exiting program'
STOP RUN
END-IF.
* ---------------- 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.
END-PERFORM.
...
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
DISPLAY 'Processing error'.
STOP RUN.
ホスト変数EMP-NUMBER はPL/SQLブロックに入る前に設定され、ホスト変数JOB-TITLE、HIRE-DATEおよびSALARYはブロックの中で設定されていることに注意してください。
6.4.2 PL/SQLの複雑な例
次の例では、ユーザーは銀行口座番号、取引の種類および取引金額の入力を要求されます。その後、口座に取引が記帳されます。口座が存在しない場合は、例外が発生します。取引が完了すると、そのステータスが表示されます。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(20) VARYING.
01 ACCT-NUM PIC S9(4) COMP.
01 TRANS-TYPE PIC X(1).
01 TRANS-AMT PIC PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
01 STATUS PIC X(80) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
DISPLAY 'Username? 'WITH NO ADVANCING.
ACCEPT USERNAME.
DISPLAY 'Password? '.
ACCEPT PASSWORD.
EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR.
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD.
PERFORM
DISPLAY 'Account Number (0 to end)? '
WITH NO ADVANCING
ACCEPT ACCT_NUM
IF ACCT-NUM = 0
EXEC SQL COMMIT WORK RELEASE END-EXEC
DISPLAY 'Exiting program' WITH NO ADVANCING
STOP RUN
END-IF.
DISPLAY 'Transaction Type - D)ebit or C)redit? '
WITH NO ADVANCING
ACCEPT TRANS-TYPE
DISPLAY 'Transaction Amount? '
ACCEPT trans_amt
* --------------------- begin PL/SQL block -------------------
EXEC SQL EXECUTE
DECLARE
old_bal NUMBER(9,2);
err_msg CHAR(70);
nonexistent EXCEPTION;
BEGIN
IF :TRANS-TYP-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
END-PERFORM.
...
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
DISPLAY 'Processing error'.
STOP RUN.
6.4.3 VARCHAR擬似型
VARCHAR疑似型は、可変長の文字列の宣言に使用できます。VARCHARが入力ホスト変数の場合は、予測される長さをPro*COBOLに通知する必要があります。このため長さフィールドは、文字列フィールドに格納される値の実際の長さに設定してください。
VARCHARが出力ホスト変数の場合、Pro*COBOLは自動的に長さフィールドを設定します。ただし、PL/SQLブロックでVARCHAR出力ホスト変数を使用するには、ブロックに入る前に長さフィールドを初期化する必要があります。したがって、次の例に示すように、長さフィールドを宣言された(最大の) VARCHAR長に設定してください。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-NUM PIC S9(4) COMP.
01 EMP-NAME PIC X(10) VARYING.
01 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
...
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
...
* -- initialize length field
MOVE 10 TO EMP-NAME-LEN.
EXEC SQL EXECUTE
BEGIN
SELECT ename, sal INTO :EMP-NAME, :SALARY
FROM emp
WHERE empno = :EMP-NUM;
...
END;
END-EXEC. 6.5 標識変数およびPL/SQL
PL/SQLでは、NULLを操作できるため、標識変数は必要ありません。たとえば、PL/SQL内では、次のようにIS NULL演算子を使用してNULLをテストできます。
IF variable IS NULL THEN ...
次のように、代入演算子(:=)を使用してNULLを割り当てることができます。
variable := NULL;
ただし、ホスト言語は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-NUM;
IF :COMMISSION:IND-COMM IS NULL THEN ...
...
END;
END-EXEC.
:COMMISSION:IND-COMMは、PL/SQLでは他の単純な変数と同じように扱われます。PL/SQLブロック内の標識変数は直接参照できませんが、PL/SQLでは、ブロックに入るときに標識変数の値がチェックされ、ブロックから出るときにその値が正しく設定されます。
6.5.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. 6.6 ホスト表およびPL/SQL
入力ホスト表およびインジケータ表は、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になります。
ノート: Pro*COBOLでは、ホスト表の使用方法はチェックされません。たとえば、索引の範囲チェックは行われません。
次の例では、salaryというホスト表を、ファンクション・コールでホスト表を使用するPL/SQLブロックに渡しています。このファンクションは、一連の数値の中央値を検出するため、medianという名前が付いています。その仮パラメータには、num_tabというPL/SQL表が含まれています。このファンクション・コールにより、実パラメータsalary内のすべての値を仮パラメータnum_tab内の行に割り当てます。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 SALARY OCCURS 100 TIMES PIC S9(6)V99 COMP-3.
01 MEDIAN-SALARY PIC S9(6)V99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.
* -- populate the host table
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 END;
END-EXEC.
また、サブプログラム・コールを使用して、PL/SQL表内のすべての行の値をホスト表内の対応する要素に割り当てることもできます。「ストアドPL/SQLおよびJavaサブプログラム」の例を参照してください。
ホスト表とPL/SQLの間のインタフェースによって、データ型が厳密に制御されます。PIC Xのデフォルトの外部型はCHARF (固定長文字列)で、CHAR型のPL/SQL表にのみマップできます。
表6-1に、PL/SQL表内の行の値、およびホスト配列内の要素との間の有効な変換を示します。この表からわかるように、PIC X変数をVARCHAR2型の表に渡すには、データ型の同値化を使用して変数をVARCHAR2に同値化するか、コマンドラインでPICX=VARCHAR2を使用する必要があります。
表6-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 |
- |
- |
- |
可 |
- |
可 |
- |
- |
6.6.1 ARRAYLEN文
入力ホスト表をPL/SQLブロックに渡して、処理する必要があるとします。デフォルトでは、このようなホスト表をバインドする際に、Pro*COBOLは宣言されたディメンションを使用します。ただし、表の一部のみ処理する場合もあります。このような場合には、ARRAYLEN文を使用してより小さい表ディメンションを指定できます。ARRAYLEN文は、ホスト表をより小さいディメンションを格納するホスト変数と関連付けます。構文は次のとおりです。
EXEC SQL ARRAYLEN host_array (dimension) EXECUTE END-EXEC.
dimensionは4バイトの整数ホスト変数であり、リテラルや式ではありません。
ARRAYLEN文は、host_arrayおよびdimensionの宣言の後に置く必要があります。ホスト表にオフセットは指定できません。ただし、そのためにCOBOL機能を使用できる場合があります。
次の例では、ARRAYLENを使用して、ホスト表BONUSのデフォルトのディメンションをオーバーライドしています。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 BONUS OCCURS 100 TIMES PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
01 MY-DIM PIC S9(9) COMP.
...
EXEC SQL ARRAYLEN BONUS (MY-DIM) END-EXEC.
EXEC SQL END DECLARE SECTION END-EXEC.
* -- populate the host table
...
* -- set smaller table dimension
MOVE 25 TO MY-DIM.
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に減らされるため、25の表要素のみPL/SQLブロックに渡されます。結果として、実行のためにPL/SQLブロックがサーバーに送信されるときに、一緒に送られるホスト表は、はるかに小さくなります。これにより、時間を節約し、ネットワーク化された環境でネットワークの通信量を削減できます。
6.6.1.1 ARRAYLEN文のEXECUTEオプション・キーワード
動的SQL方法2の文(「方法2の使用方法」を参照)で使用されるホスト表には、ARRAYLEN文のEXECUTEオプション・キーワードの有無によって2種類の解釈があります。
EXECUTEオプション・キーワードがない場合は、次のようになります。
-
PL/SQLブロックが複数回実行されます。実行回数は使用されるARRAYLENの最小ディメンションによって決まります。
-
ホスト配列は、PL/SQL表にバインドできません。
EXECUTEオプション・キーワードがある場合は、次のようになります。
-
ホスト表は、索引表にバインドする必要があります。
-
PL/SQLブロックは、1回のみ実行されます。
-
EXEC SQL EXECUTE文で指定するホスト変数はすべて、次のいずれかにする必要があります。
-
ARRAYLEN ... EXECUTE文の中で指定。
-
スカラー値。
-
次のPro*COBOLの例に、ホスト表を使用してPL/SQLブロックの実行回数を決定する方法を示します。この例では、PL/SQLブロックは3回実行され、emp表に3行の新規の行が作成されます。
...
01 DYNSTMT PIC X(80) VARYING.
01 EMPNOTAB PIC S9(4) COMPUTATIONAL OCCURS 5 TIMES.
01 ENAMETAB PIC X(10) OCCURS 3 TIMES.
...
MOVE 1111 TO EMPNOTAB(1).
MOVE 2222 TO EMPNOTAB(2).
MOVE 3333 TO EMPNOTAB(3).
MOVE 4444 TO EMPNOTAB(4).
MOVE 5555 TO EMPNOTAB(5).
MOVE "MICKEY" TO ENAMETAB(1).
MOVE "MINNIE" TO ENAMETAB(2).
MOVE "GOOFY" TO ENAMETAB(3).
MOVE "BEGIN INSERT INTO emp(empno, ename) VALUES :b1, :b2; END;"
TO DYNSTMT-ARR.
MOVE 57 TO DYNSTMT-LEN.
EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC.
EXEC SQL EXECUTE s1 USING :EMPNOTAB, :ENAMETAB END-EXEC.
...
次の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*COBOLの例は、動的方法2を使用してホスト表をPL/SQL索引表にバインドする方法を示します。EXEC SQL EXECUTE文に指定されたすべてのホスト配列についてARRAYLEN...EXECUTE文があることに注意してください。
...
01 DYNSTMT PIC X(80) VARYING.
01 II PIC S9(4) COMP VALUE 2.
01 INTTAB PIC S9(9) COMP OCCURS 3 TIMES.
01 DIM PIC S9(9) COMP VALUE 3.
EXEC SQL ARRAYLEN INTTAB (DIM) EXECUTE END-EXEC.
...
MOVE 1 TO INTTAB(1).
MOVE 2 TO INTTAB(2).
MOVE 3 TO INTTAB(3).
MOVE "BEGIN pkg.proc1 (:v1, :v2, :v3); end;";
TO DYNSTMT-ARR.
MOVE 37 TO DYNSTMT-LEN.
EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC.
EXEC SQL EXECUTE s1 USING :INTTAB, :II, :INTTAB END-EXEC.
...
ただし、次のPro*COBOLの例ではINTTAB2にARRAYLEN...EXECUTE文が存在しないため、プリコンパイル時にエラーになります。
...
01 DYNSTMT PIC X(80) VARYING.
01 INTTAB PIC S9(9) COMP OCCURS 3 TIMES.
01 INTTAB2 PIC S9(9) COMP OCCURS 3 TIMES.
01 DIM PIC S9(9) COMP VALUE 3.
EXEC SQL ARRAYLEN INTTAB (DIM) EXECUTE END-EXEC.
...
MOVE 1 TO INTTAB(1).
MOVE 2 TO INTTAB(2).
MOVE 3 TO INTTAB(3).
MOVE "BEGIN pkg.proc1 (:v1, :v2, :v3); end;";
TO DYNSTMT-ARR.
MOVE 37 TO DYNSTMT-LEN.
EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC.
EXEC SQL EXECUTE s1 USING :INTTAB, :INTTAB2, :INTTAB END-EXEC.
... 6.7 埋込みPL/SQLでのカーソルの使用
プログラムで同時に使用できるカーソルの最大数は、データベースの初期化パラメータOPEN_CURSORSで決定します。通常、OPEN_CURSORSを超えないように、プリコンパイラで文のカーソルを管理できます。プリコンパイル・オプションのHOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSが使用されます。埋込みPL/SQLブロックの実行中には、PL/SQLブロック全体に対応付けられた1つの親カーソルと、PL/SQLブロックの実行中に実行される文ごとの1つの子カーソルがあります。PL/SQLブロックは実行時にサーバーに渡されるため、プリコンパイラのランタイム・ライブラリでは親カーソルのみ追跡できます。したがって、この方法で多数のカーソルを使用するアプリケーションでは、カーソルがOPEN_CURSORSで指定された数を上回る可能性があります。図6-1は、使用されるカーソルの最大数を計算する方法を示しています。
開発者はカーソルの状況に注意して、OPEN_CURSORSおよびMAXOPENCURSORSを設定してください。
問題が解決しない場合は、SQL文の実行後に子カーソルをすべて解放することができます。
この場合は、RELEASE_CURSOR=YESおよびHOLD_CURSOR=NOの設定を使用します。最初の設定をプログラム全体に適用するとパフォーマンスが低下するため、これらのオプションの設定は次のようにします。
EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC.
* -- first embedded PL/SQL block
EXEC ORACLE OPTION (RELEASE_CURSOR=NO)END-EXEC.
* -- embedded SQL statements
EXEC ORACLE OPTION (RELEASE_CURSOR=YES)END-EXEC.
* -- second embedded PL/SQL block
EXEC ORACLE OPTION (RELEASE_CURSOR=NO)END-EXEC.
* -- embedded SQL statements 関連項目
6.8 ストアドPL/SQLおよびJavaサブプログラム
無名ブロックとは異なり、PL/SQLサブプログラム(プロシージャおよびファンクション)およびJavaメソッドは、別々にコンパイルしてデータベースに格納し、起動できます。
SQL*PlusなどのOracleツールを使用して明示的に作成したサブプログラムを、ストアド・サブプログラムと呼びます。コンパイルされ、データ・ディクショナリに格納されたストアド・サブプログラムは、データベース・オブジェクトとなり、再コンパイルせずに再実行できます。
PL/SQLブロック内のサブプログラムまたはストアド・サブプログラムは、アプリケーションによってデータベースに送信されると、インライン・サブプログラムになり、データベースでコンパイルされます。Pro*COBOLは文をサーバーに送信し、文が実行されます。
パッケージ内で定義されているサブプログラムは、そのパッケージの一部とみなされ、パッケージ・サブプログラムと呼ばれます。パッケージで定義されていないストアド・サブプログラムは、スタンドアロン・サブプログラムと呼ばれます。
6.8.1 ストアド・サブプログラムの作成
次の例に示すように、SQL文CREATE FUNCTION、CREATE PROCEDUREおよびCREATE PACKAGEをCOBOLプログラムに埋め込むことができます。
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 {FUNCTION | PROCEDURE | PACKAGE}文が失敗した場合、Oracleはエラーではなく警告を発行します。
関連項目:
CREATE文の完全な構文は、Oracle Database SQL言語リファレンスを参照してください。6.8.2 ストアドPL/SQLまたはJavaサブプログラムのコール
ホスト・プログラムからストアド・サブプログラムをコールするには、無名PL/SQLブロックまたはCALL埋込みSQL文のどちらかを使用できます。
6.8.2.1 無名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表、PL/SQLユーザー定義レコード、サブプログラム・コールまたは式を使用できます。これに対してOUT実パラメータには、リテラル、サブプログラム・コールおよび式は使用できません。
埋込みPL/SQLブロックとともにプリコンパイラ・オプションSQLCHECK=SEMANTICSを使用する必要があります。
6.8.2.2 CALL文
前述の埋込みPL/SQLブロックに関する概念は、CALL文にも適用できます。CALL埋込みSQL文の書式は次のようになります。
EXEC SQL
CALL [schema.][package.]stored_proc[@db_link](arg1, ...)
[INTO :ret_var[[INDICATOR]:ret_ind]]
END-EXEC.
各パラメータの意味は次のとおりです。
schema
プロシージャを含むスキーマ
package
プロシージャを含むパッケージ
stored_proc
コールするJavaまたはPL/SQLストアド・プロシージャ
db_link
オプションのリモート・データベース・リンク
arg1...
引き渡す一連の引数(変数、リテラル、式)
ret_var
結果を受け取るオプションのホスト変数
ind_var
ret_varのオプションの標識変数。
CALL文とともにSQLCHECK=SYNTAXまたはSQLCHECK=SEMANTICSのどちらかを使用できます。
6.8.2.3 CALLの例
次に示すように、入力された整数を受け取り、その階乗を整数で戻す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.
Pro*COBOLアプリケーションで factを使用するには、次のように指定します。
...
01 N PIC S9(4) COMP.
01 FACT PIC S9(9) COMP.
...
EXEC SQL CALL mathpkge.fact(:N) INTO :FACT END-EXEC.
...
関連項目:
-
CALL文の詳細は、CALL(実行可能埋込みSQL)を参照してください。
-
引数の受渡しおよびその他の問題の詳細は、複数のプログラミング言語を使用したアプリケーションの開発を参照してください。
6.8.3 動的PL/SQLの使用
6.9 サンプル・プログラム9: ストアド・プロシージャのコール
このサンプル・プログラムを実行する前に、次に示すPro*COBOLが提供するCALLDEMO.SQLスクリプトを実行して、calldemoの名前のPL/SQLパッケージを作成する必要があります。このスクリプトはPro*COBOLに付属のスクリプトで、Pro*COBOLデモ・ライブラリに入っています。このスクリプト名の正確なスペルは、使用しているシステム固有のOracleマニュアルを参照してください。
CREATE OR REPLACE PACKAGE calldemo AS
TYPE name_array IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;
TYPE job_array IS TABLE OF emp.job%type
INDEX BY BINARY_INTEGER;
TYPE sal_array IS TABLE OF emp.sal%type
INDEX BY BINARY_INTEGER;
PROCEDURE get_employees(
dept_number IN number, -- department to query
batch_size IN INTEGER, -- rows at a time
found IN OUT INTEGER, -- rows actually returned
done_fetch OUT INTEGER, -- all done flag
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array);
END calldemo;
/
CREATE OR REPLACE PACKAGE BODY calldemo AS
CURSOR get_emp (dept_number IN number) IS
SELECT ename, job, sal FROM emp
WHERE deptno = dept_number;
-- Procedure "get_employees" fetches a batch of employee
-- rows (batch size is determined by the client/caller
-- of the procedure). It can be called from other
-- stored procedures or client application programs.
-- The procedure opens the cursor if it is not
-- already open, fetches a batch of rows, and
-- returns the number of rows actually retrieved. At
-- end of fetch, the procedure closes the cursor.
PROCEDURE get_employees(
dept_number IN number,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array) IS
BEGIN
IF NOT get_emp%ISOPEN THEN -- open the cursor if
OPEN get_emp(dept_number); -- not already open
END IF;
-- Fetch up to "batch_size" rows into PL/SQL table,
-- tallying rows found as they are retrieved. When all
-- rows have been fetched, close the cursor and exit
-- the loop, returning only the last set of rows found.
done_fetch := 0; -- set the done flag FALSE
found := 0;
FOR i IN 1..batch_size LOOP
FETCH get_emp INTO emp_name(i), job(i), sal(i);
IF get_emp%NOTFOUND THEN -- if no row was found
CLOSE get_emp;
done_fetch := 1; -- indicate all done
EXIT;
ELSE
found := found + 1; -- count row
END IF;
END LOOP;
END;
END;
/
次のサンプルプログラムはデータベースに接続し、ユーザーに部門番号の入力を要求し、その後calldemoパッケージに格納されたget_employeesというPL/SQLプロシージャをコールします。プロシージャでは、3つのPL/SQL表をOUT仮パラメータとして宣言し、その後、従業員のバッチ・データをPL/SQL表にフェッチします。一致する実パラメータはホスト表です。プロシージャの終了時に、PL/SQL表の行の値が、ホスト表の対応する要素に自動的に割り当てられます。プログラムでは、プロシージャを繰り返しコールし、データがなくなるまで、従業員データの各バッチを表示します。
*****************************************************************
* Sample Program 9: Calling a Stored Procedure
*
* This program connects to ORACLE, prompts the user for a
* department number, then calls a PL/SQL stored procedure named
* GET_EMPLOYEES, which is stored in package CALLDEMO. The
* procedure declares three PL/SQL tables ast OUT formal
* parameters, then fetches a batch of employee data into the
* PL/SQL tables. The matching actual parameters are host tables.
* When the procedure finishes, it automatically assigns all row
* values in the PL/SQL tables to corresponding elements in the
* host tables. The program calls the procedure repeatedly,
* displaying each batch of employee data, until no more data
* is found.
* Use option picx=varchar2 when precompiling this sample program.
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CALL-STORED-PROC.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(15) VARYING.
01 PASSWD PIC X(15) VARYING.
01 DEPT-NUM PIC S9(9) COMP.
01 EMP-TABLES.
05 EMP-NAME OCCURS 10 TIMES PIC X(10).
05 JOB-TITLE OCCURS 10 TIMES PIC X(10).
05 SALARY OCCURS 10 TIMES COMP-2.
01 DONE-FLAG PIC S9(9) COMP.
01 TABLE-SIZE PIC S9(9) COMP VALUE 10.
01 NUM-RET PIC S9(9) COMP.
01 SQLCODE PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
01 COUNTER PIC S9(9) COMP.
01 DISPLAY-VARIABLES.
05 D-EMP-NAME PIC X(10).
05 D-JOB-TITLE PIC X(10).
05 D-SALARY PIC Z(5)9.
05 D-DEPT-NUM PIC 9(2).
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL WHENEVER SQLERROR DO
PERFORM SQL-ERROR END-EXEC.
PERFORM LOGON.
PERFORM INIT-TABLES VARYING COUNTER FROM 1 BY 1
UNTIL COUNTER > 10.
PERFORM GET-DEPT-NUM.
PERFORM DISPLAY-HEADER.
MOVE ZERO TO DONE-FLAG.
MOVE ZERO TO NUM-RET.
PERFORM FETCH-BATCH UNTIL DONE-FLAG = 1.
PERFORM LOGOFF.
INIT-TABLES.
MOVE SPACE TO EMP-NAME(COUNTER).
MOVE SPACE TO JOB-TITLE(COUNTER).
MOVE ZERO TO SALARY(COUNTER).
GET-DEPT-NUM.
MOVE ZERO TO DEPT-NUM.
DISPLAY " ".
DISPLAY "ENTER DEPARTMENT NUMBER: "
WITH NO ADVANCING.
ACCEPT D-DEPT-NUM.
MOVE D-DEPT-NUM TO DEPT-NUM.
DISPLAY-HEADER.
DISPLAY " ".
DISPLAY "EMPLOYEE JOB TITLE SALARY".
DISPLAY "-------- --------- ------".
FETCH-BATCH.
EXEC SQL EXECUTE
BEGIN
CALLDEMO.GET_EMPLOYEES
(:DEPT-NUM, :TABLE-SIZE,
:NUM-RET, :DONE-FLAG,
:EMP-NAME, :JOB-TITLE, :SALARY);
END;
END-EXEC.
PERFORM PRINT-ROWS VARYING COUNTER FROM 1 BY 1
UNTIL COUNTER > NUM-RET.
PRINT-ROWS.
MOVE EMP-NAME(COUNTER) TO D-EMP-NAME.
MOVE JOB-TITLE(COUNTER) TO D-JOB-TITLE.
MOVE SALARY(COUNTER) TO D-SALARY.
DISPLAY D-EMP-NAME, " ",
D-JOB-TITLE, " ",
D-SALARY.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
LOGOFF.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY.".
DISPLAY " ".
EXEC SQL COMMIT WORK RELEASE END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。また、ストアド・サブプログラムが終了する前にすべてのOUT仮パラメータに値を割り当てる必要があります。そうしないと、対応する実パラメータの値が未確定になります。
6.9.1 リモート・アクセス
PL/SQLを使用すると、データベース・リンクを経由してリモート・データベースにアクセスできます。通常、データベース・リンクは、DBAが作成し、データ・ディクショナリに格納されます。データベース・リンクは、データベースの位置、そのデータベースへのパス、および使用するユーザー名とパスワードをプログラムに示します。次の例では、データベース・リンク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;
6.10 カーソル変数
Pro*COBOLプログラムでカーソル変数を使用して、静的埋込みSQLによって複数行の問合せを処理できます。カーソル変数は、PL/SQLによってデータベース・サーバーで定義およびオープンされるカーソル参照を示します。
カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セットの中のカレント行を指します。カーソルとカーソル変数との違いは、定数と変数との違いと同じです。カーソルは静的で、カーソル変数は特定の問合せに結び付けられていないため、動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。
カーソル変数に新しい値を割り当てて、サブプログラム(データベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中化できます。
まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の文を使用してカーソル変数を制御します。
-
ALLOCATE
-
OPEN ... FOR
-
FETCH
-
CLOSE
-
FREE
カーソル変数を宣言してメモリーを割り当てた後、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。次に、サーバー側でOPEN、FORを使用して複数行の問合せ用にオープンし、クライアント側でFETCHしてから、サーバー側かクライアント側のどちらかでCLOSEします。
カーソル変数の利点は、次のとおりです。
-
メンテナンスのしやすさ。問合せは、カーソル変数をオープンするストアド・プロシージャに集中されます。カーソルを変更する必要がある場合は、ストアド・プロシージャの変更のみで済みます。各アプリケーションを変更する必要はありません。
-
セキュリティの強化。アプリケーションのユーザー(Pro*COBOLアプリケーションがデータベースに接続した時のユーザー名)は、カーソルをオープンするストアード・プロシージャに対する実行権限を持っている必要があります。ただし、ユーザーには、問合せで使用される表に対する権限は必要ありません。この機能を使用して、表の列へのアクセスを制限できます。
関連項目:
カーソル変数の詳細は、カーソル変数を参照してください。6.10.2 カーソル変数の割当て
カーソル変数をOPENするとき、またはその後で情報をFETCHするときには、Pro*COBOLのALLOCATEコマンドを使用してカーソル変数を初期化しておく必要があります。たとえば、前の項で宣言したカーソル変数CUR-VARを初期化するには、次の文を使用します。
EXEC SQL ALLOCATE :CUR-VAR END-EXEC.
カーソル変数の割当てには、プリコンパイル時も実行時もサーバーをコールする必要はありません。
ALLOCATE文ではAT句は使用できません。
注意: カーソル変数を割り当てると、ヒープ・メモリーが使用されます。したがって、プログラム・ループではカーソル変数を割り当てないでください。
6.10.3 カーソル変数のオープン
6.10.3.1 PL/SQLストアド・プロシージャによる間接的なオープン
次のPL/SQLパッケージがデータベースに格納されているとします。
CREATE PACKAGE demo_cur_pkg AS
TYPE EmpName IS RECORD (name VARCHAR2(10));
TYPE cur_type IS REF CURSOR RETURN EmpName;
PROCEDURE open_emp_cur (
curs IN OUT curtype,
dept_num IN number);
END;
CREATE PACKAGE BODY demo_cur_pkg AS
CREATE PROCEDURE open_emp_cur (
curs IN OUT curtype,
dept_num IN number) IS
BEGIN
OPEN curs FOR
SELECT ename FROM emp
WHERE deptno = dept_num
ORDER BY ename ASC;
END;
END;
このパッケージが格納された後、まずPro*COBOLプログラムからopen_emp_curストアド・プロシージャをコールし、プログラム内のカーソル変数emp_cursorからFETCHを発行して、カーソルcursをオープンできます。次に例を示します。
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 emp_cursor sql-cursor.
01 DEPT-NUM PIC S9(4).
01 EMP-NAME PIC X(10) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
* Allocate the cursor variable.
EXEC SQL
ALLOCATE :emp-cursor
END-EXEC.
...
MOVE 30 TO dept_num.
* Open the cursor on the Oracle Server.
EXEC SQL EXECUTE
begin
demo_cur_pkg.open_emp_cur(:emp-cursor, :dept-num);
END;
END-EXEC.
EXEC SQL
WHENEVER NOT FOUND DO PERFORM SIGN-OFF
END-EXEC.
FETCH-LOOP.
EXEC SQL
FETCH :emp_cursor INTO :EMP-NAME
END-EXEC.
DISPLAY "Employee Name: ",:EMP-NAME.
GO TO FETCH-LOOP.
...
SIGN-OFF.
...6.10.4 カーソル変数からのフェッチ
複数行の問合せ用にカーソル変数をオープンした後、FETCH文を使用して、静的カーソルの場合と同じように行を取り出します。構文は次のとおりです。
EXEC SQL FETCH cursor_variable_name
INTO {record_name | variable_name[, variable_name, ...]}
END-EXEC.
カーソル変数が戻す各列値は、データ型に互換性がある場合、INTO句内の対応するフィールドまたは変数に割り当てられます。
FETCH文はクライアント側で実行する必要があります。次の例では、EMP-RECの名前のホスト・レコードに行をフェッチします。
* -- exit loop when done fetching
EXEC SQL
WHENEVER NOT FOUND DO PERFORM NO-MORE
END-EXEC.
PERFORM
* -- fetch row into record
EXEC SQL FETCH :EMP-CUR INTO :EMP-REC END-EXEC
* -- test for transfer out of loop
...
* -- process the data
...
END-PERFORM.
...
NO-MORE.
...
埋込みSQLのFETCH ....INTO命令を使用して、カーソル変数をオープンしたときに選択した行を取得します。次に例を示します。
EXEC SQL
FETCH :emp_cursor INTO :EMP-INFO:EMP-INFO-IND
END-EXEC.
カーソル変数からFETCHするには、そのカーソル変数を初期化し、オープンしておく必要があります。オープンされていないカーソル変数からFETCHはできません。
6.10.5 カーソル変数のクローズ
カーソル変数をクローズするには、埋込みSQLのCLOSE文を使用します。その時点で、アクティブ・セットは未定義になります。構文は次のとおりです。
EXEC SQL CLOSE cursor_variable_name END-EXEC.
CLOSE文はクライアント側でもサーバー側でも実行できます。次の例では、最後の行が処理されたときにカーソル変数CUR-VARをクローズします。
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* Declare the cursor variable.
01 CUR-VAR SQL-CURSOR.
...
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
* Allocate and open the cursor variable, then
* Fetch one or more rows.
...
* Close the cursor variable.
EXEC SQL
CLOSE :CUR-VAR
END-EXEC.6.10.6 カーソル変数の解放
カーソル変数CUR-VARに割り当てられたメモリーを解放するには、CLOSEの後でFREE文を使用します。
* Free the cursor variable memory.
EXEC SQL
FREE :CUR-VAR
END-EXEC.6.10.7 カーソル変数の制限
カーソル変数の使用には、次の制限が適用されます。
-
カーソル変数は、動的SQLではサポートされません。
-
カーソル変数は、ALLOCATE、FETCH、FREEおよびCLOSEコマンドのみで使用できます。DECLARE CURSORコマンドは、カーソル変数には適用されません。
-
ALLOCATEコマンドではAT句を使用できません。
6.10.8 サンプル・プログラム11: カーソル変数
6.10.8.1 SAMPLE11.SQL
このサンプル・プログラムは、カーソル変数を宣言してオープンするパッケージを作成するためのPL/SQLソース・コードです。
CONNECT SCOTT/TIGER
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number);
END emp_demo_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS
PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number) IS
BEGIN
OPEN cursor FOR SELECT * FROM emp
WHERE deptno = dept_num
ORDER BY ename ASC;
END;
END emp_demo_pkg;
/6.10.8.2 SAMPLE11.PCO
次に、前述のSAMPLE11.sqlで宣言したカーソル変数を使用して、EMP表から従業員名、給与および歩合給をフェッチするPro*COBOLサンプル・プログラムSAMPLE11.PCOを示します。
*****************************************************************
* Sample Program 11: Cursor Variable Operations *
* *
* This program logs on to ORACLE, allocates and opens a cursor *
* variable fetches the names, salaries, and commissions of all *
* salespeople, displays the results, then closes the cursor. *
*****************************************************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CURSOR-VARIABLES.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(15) VARYING.
01 PASSWD PIC X(15) VARYING.
01 HOST PIC X(15) VARYING.
01 EMP-CUR SQL-CURSOR.
01 EMP-INFO.
05 EMP-NUM PIC S9(4) COMP.
05 EMP-NAM PIC X(10) VARYING.
05 EMP-JOB PIC X(10) VARYING.
05 EMP-MGR PIC S9(4) COMP.
05 EMP-DAT PIC X(10) VARYING.
05 EMP-SAL PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 EMP-COM PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 EMP-DEP PIC S9(4) COMP.
01 EMP-INFO-IND.
05 EMP-NUM-IND PIC S9(4) COMP.
05 EMP-NAM-IND PIC S9(4) COMP.
05 EMP-JOB-IND PIC S9(4) COMP.
05 EMP-MGR-IND PIC S9(4) COMP.
05 EMP-DAT-IND PIC S9(4) COMP.
05 EMP-SAL-IND PIC S9(4) COMP.
05 EMP-COM-IND PIC S9(4) COMP.
05 EMP-DEP-IND PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 DISPLAY-VARIABLES.
05 D-DEP-NUM PIC Z(3)9.
05 D-EMP-NAM PIC X(10).
05 D-EMP-SAL PIC Z(4)9.99.
05 D-EMP-COM PIC Z(4)9.99.
05 D-EMP-DEP PIC 9(2).
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL
WHENEVER SQLERROR DO PERFORM SQL-ERROR
END-EXEC.
PERFORM LOGON.
EXEC SQL
ALLOCATE :EMP-CUR
END-EXEC.
DISPLAY "Enter department number (0 to exit): "
WITH NO ADVANCING.
ACCEPT D-EMP-DEP.
MOVE D-EMP-DEP TO EMP-DEP.
IF EMP-DEP <= 0
GO TO SIGN-OFF
END-IF.
MOVE EMP-DEP TO D-DEP-NUM.
EXEC SQL EXECUTE
BEGIN
emp_demo_pkg.open_cur(:EMP-CUR, :EMP-DEP);
END;
END-EXEC.
DISPLAY " ".
DISPLAY "For department ", D-DEP-NUM, ":".
DISPLAY " ".
DISPLAY "EMPLOYEE SALARY COMMISSION".
DISPLAY "---------- ---------- ----------".
FETCH-LOOP.
EXEC SQL
WHENEVER NOT FOUND GOTO CLOSE-UP
END-EXEC.
MOVE SPACES TO EMP-NAM-ARR.
EXEC SQL FETCH :EMP-CUR
INTO :EMP-NUM:EMP-NUM-IND,
:EMP-NAM:EMP-NAM-IND,
:EMP-JOB:EMP-JOB-IND,
:EMP-MGR:EMP-MGR-IND,
:EMP-DAT:EMP-DAT-IND,
:EMP-SAL:EMP-SAL-IND,
:EMP-COM:EMP-COM-IND,
:EMP-DEP:EMP-DEP-IND
END-EXEC.
MOVE EMP-SAL TO D-EMP-SAL.
IF EMP-COM-IND = 0
MOVE EMP-COM TO D-EMP-COM
DISPLAY EMP-NAM-ARR, " ", D-EMP-SAL,
" ", D-EMP-COM
ELSE
DISPLAY EMP-NAM-ARR, " ", D-EMP-SAL,
" N/A"
END-IF.
GO TO FETCH-LOOP.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
MOVE "INST1_ALIAS" TO HOST-ARR.
MOVE 11 TO HOST-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
CLOSE-UP.
EXEC SQL
CLOSE :EMP-CUR
END-EXEC.
EXEC SQL
FREE :EMP-CUR
END-EXEC.
SIGN-OFF.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY.".
DISPLAY " ".
EXEC SQL
COMMIT WORK RELEASE
END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL
WHENEVER SQLERROR CONTINUE
END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL
ROLLBACK WORK RELEASE
END-EXEC.
STOP RUN.