PL/SQLトランザクション処理ブロックをプログラム内に埋め込むことにより、パフォーマンスを改善する方法を説明します。この章の構成は、次のとおりです。
Pro*COBOLは、PL/SQLブロックを単一の埋込みSQL文のように扱います。PL/SQLブロックは、SQL文を記述できる位置であればホスト・プログラム内のどこにでも記述できます。
ホスト・プログラムにPL/SQLブロックを埋め込むには、PL/SQLとの間で共有される変数を宣言し、PL/SQLブロックをEXEC SQL EXECUTEキーワードおよびEND-EXECキーワードで囲みます。
PL/SQLブロックの中では、ホスト変数はブロック全体にわたるグローバルなものとして扱われ、PL/SQL変数を記述できる位置であればどこにでも記述できます。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数とPL/SQL変数およびデータベース・オブジェクトとを区切ります。
PL/SQLブロックに入ると、Oracle9iは自動的にVARCHARホスト変数の長さフィールドをチェックします。このため、ブロックに入る前に長さフィールドを設定する必要があります。入力変数の長さフィールドは、文字列フィールドに格納される値の長さに設定します。出力変数の場合は、長さフィールドにその文字列フィールドに許される最大の長さを設定します。
PL/SQLブロックでは、標識変数のみを参照することはできません。標識変数は、対応するホスト変数の後に記述する必要があります。また、標識変数によってホスト変数を参照する場合は、同じブロック内では常に標識変数を使用してそのホスト変数を参照する必要があります。
埋込みPL/SQLブロックを持つプログラムをプリコンパイルする際には、SQLCHECK=SEMANTICSを指定する必要があります。また、USERIDオプションも使用する必要があります。詳細は、第14章「プリコンパイラ・オプション」を参照してください。
この項では、PL/SQLによって提供される次のような機能および利点を説明します。
パフォーマンスの向上
Oracle9iとの統合
カーソルFORループ
プロシージャおよびファンクション
パッケージ
PL/SQL表
ユーザー定義レコード
PL/SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLによって、オーバーヘッドの削減、パフォーマンスの改善および生産性の向上が図れます。たとえば、PL/SQLを使用しないと、Oracle9iは一度に1つずつSQL文を処理する必要があります。その結果、各SQL文によってサーバーへの別のコールが発生し、オーバーヘッドが増加します。しかし、PL/SQLを使用すると、サーバーにSQL文のブロック全体を送信することができます。これによって、アプリケーションとサーバーとの間の通信は最小限になります。
PL/SQLはサーバーと密接に統合されます。たとえば、PL/SQLデータ型の大部分は、データ・ディクショナリに固有のデータ型です。さらに、次の例に示すとおり、データ・ディクショナリ内に格納された列定義に基づいて変数を宣言するための%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表の中に挿入されます。次に、新しい番号がコール元に戻ります。
サブプログラムをそのつど再コンパイルせずに、複数のアプリケーションからコールできます。(CREATE FUNCTIONおよびCREATE PROCEDUREを使用してサブプログラムをデータベースに格納できます。)
仮パラメータの動作を定義するには、パラメータ・モードを使用します。パラメータ・モードにはIN(デフォルト)、OUTおよびIN OUTの3つがあります。INパラメータを使用すると、コールされるサブプログラムに値を渡せます。OUTパラメータを使用すると、サブプログラムのコール元に値を戻せます。IN OUTパラメータを使用すると、コールされるサブプログラムに初期値を渡し、更新された値をコール元に戻すことができます。
それぞれの実パラメータのデータ型は、対応する仮パラメータのデータ型に変換可能であることが必要です。表6-1に、データ型間の有効な変換を示します。
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;
パッケージ仕様部内の宣言のみ参照可能で、アプリケーションからアクセスできます。パッケージ本体中の詳細な実装内容は非表示のためアクセスできません。
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 := 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 ...
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を指定する必要があります。詳細は、「Pro*COBOLプリコンパイラ・オプションの使用」を参照してください。
ホスト変数はホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できます。これにより、PL/SQLでのホスト変数の設定および参照が可能になります。
たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLを使用してデータベースにアクセスし、ホスト変数を介してその結果をホスト・プログラムに戻せるようになります。
PL/SQLブロック内ではホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる位置であればどこにでも使用できます。SQL文内におけるホスト変数と同様、PL/SQLブロック内のホスト変数も先頭にコロンを付ける必要があります。コロンは、ホスト変数と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はブロックの中で設定されていることに注意してください。
次の例では、ユーザーは銀行口座番号、取引の種類および取引金額の入力を要求されます。その後、口座に取引が記帳されます。口座が存在しない場合は、例外が発生します。取引が完了すると、そのステータスが表示されます。
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.
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.
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では、ブロックに入るときに標識変数の値がチェックされ、ブロックから出るときにその値が正しく設定されます。
ブロックに入るとき、標識変数の値が-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になります。
注意: Pro*COBOLでは、ホスト表の使用方法はチェックされません。たとえば、索引の範囲チェックは行われません。
次の例では、salaryの名前のホスト表をPL/SQLブロックに渡します。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 |
- |
- |
- |
可 |
- |
可 |
- |
- |
入力ホスト表を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ブロックがサーバーに送信されるときに、一緒に送られるホスト表は、はるかに小さくなります。これにより、時間を節約し、ネットワーク化された環境でネットワークの通信量を削減できます。
動的SQL方法2の文(「方法2の使用方法」を参照)で使用されるホスト表には、ARRAYLEN文のEXECUTEオプション・キーワードの有無によって2種類の解釈があります。
EXECUTEオプション・キーワードがない場合は、次のようになります。
PL/SQLブロックが複数回実行されます。実行回数は使用されるARRAYLENの最小ディメンションによって決まります。
ホスト配列は、PL/SQL表にバインドできません。
EXECUTEオプション・キーワードがある場合は、次のようになります。
ホスト表は、PL/SQL索引表にバインドする必要があります。
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. ...
プログラムで同時に使用できるカーソルの最大数は、データベースの初期化パラメータOPEN_CURSORSで決定します。通常、OPEN_CURSORSを超えないように、プリコンパイラで文のカーソルを管理できます。プリコンパイル・オプションのHOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSが使用されます。(詳細は、「埋込みPL/SQLに関する考慮事項」を参照。)埋込み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
無名ブロックとは異なり、PL/SQLサブプログラム(プロシージャおよびファンクション)およびJavaメソッドは、別々にコンパイルしてデータベースに格納し、起動できます。
SQL*PlusなどのOracleツールを使用して明示的に作成したサブプログラムを、ストアド・サブプログラムと呼びます。コンパイルされ、データ・ディクショナリに格納されたストアド・サブプログラムは、データベース・オブジェクトとなり、再コンパイルせずに再実行できます。
PL/SQLブロック内のサブプログラムまたはストアド・サブプログラムは、アプリケーションによってデータベースに送信されると、インライン・サブプログラムになり、データベースでコンパイルされます。Pro*COBOLは文をサーバーに送信し、文が実行されます。
パッケージ内で定義されているサブプログラムは、そのパッケージの一部とみなされ、パッケージ・サブプログラムと呼ばれます。パッケージで定義されていないストアド・サブプログラムは、スタンドアロン・サブプログラムと呼ばれます。
次の例に示すように、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}文が失敗した場合、Oracle9iはエラーではなく警告を発行します。CREATE文のすべての構文は、『Oracle Database SQL言語リファレンス』を参照してください。
ホスト・プログラムからストアド・サブプログラムをコールするには、無名PL/SQLブロックまたはCALL埋込み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を使用する必要があります。
前述の埋込み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のどちらかを使用できます。
次に示すように、入力された整数を受け取り、その階乗を整数で戻す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(実行可能埋込みSQL)」を参照してください。引数の引渡しなどの説明は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』の「外部ルーチン」の章を参照してください。
Pro*COBOLは、PL/SQLブロック全体を1つのSQL文のように扱います。つまり、PL/SQLブロックをホスト変数の文字列に格納できることを意味します。その場合、ブロックにホスト変数が含まれていなければ、動的SQL方法1を使用してPL/SQL文字列を実行できます。ブロックにホスト変数が含まれていて、その数がわかっている場合は、動的SQL方法2を使用してPL/SQL文字列を準備し、実行します。ブロックに含まれるホスト変数の数がわからない場合は、動的SQL方法4を使用する必要があります。詳細は、第9章「Oracle動的SQL」、第10章「ANSI動的SQL」および第11章「Oracle動的SQL: 方法4」を参照してください。
このサンプル・プログラムを実行する前に、次に示す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; /
次のサンプル・プログラムは、データベースに接続し、ユーザーに部門番号を要求してget_employeesという名前のPL/SQLプロシージャをコールします。このPL/SQLプロシージャはパッケージcalldemoに格納されています。プロシージャは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仮パラメータに値を割り当てる必要があります。そうしないと、対応する実パラメータの値が未確定になります。
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;
Pro*COBOLプログラムでカーソル変数を使用して、静的埋込みSQLによって複数行の問合せを処理できます。カーソル変数は、PL/SQLによってデータベース・サーバーで定義およびオープンされるカーソル参照を示します。カーソル変数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セットの中のカレント行を指します。カーソルとカーソル変数との違いは、定数と変数との違いと同じです。カーソルは静的で、カーソル変数は特定の問合せに結び付けられていないため、動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。
カーソル変数に新しい値を割り当てて、サブプログラム(データベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中化できます。
まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の文を使用してカーソル変数を制御します。
ALLOCATE
OPEN...FOR
FETCH
CLOSE
FREE
カーソル変数を宣言してメモリーを割り当てた後、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。次に、サーバー側でOPEN、FORを使用して複数行の問合せ用にオープンし、クライアント側でFETCHしてから、サーバー側かクライアント側のどちらかでCLOSEします。
カーソル変数の利点は、次のとおりです。
メンテナンスの手軽さ:カーソル変数をオープンするストアド・プロシージャ内に問合せを集中できます。カーソルを変更する必要がある場合には、ストアド・プロシージャのみ変更してください。アプリケーションごとに変更する必要はありません。
セキュリティの向上:アプリケーションのユーザー(Pro*COBOLアプリケーションがデータベースに接続するときのユーザー名)には、カーソルをオープンするストアド・プロシージャの実行権限が必要です。ただし、このユーザーには問合せで使用される表の読取り権限は必要ありません。このセキュリティ機能を使用して、表の列へのアクセスを制限できます。
Pro*COBOLカーソル変数はSQL-CURSOR疑似型を使用して宣言します。たとえば、次のようにします。
WORKING-STORAGE SECTION. ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... 01 CUR-VAR SQL-CURSOR. ... EXEC SQL END DECLARE SECTION END-EXEC.
SQL-CURSOR変数は、Pro*COBOLが生成するコードにCOBOLグループ項目として実装されます。カーソル変数は、他のPro*COBOLホスト変数とまったく同じです。
カーソル変数をOPENするとき、またはその後で情報をFETCHするときには、Pro*COBOLのALLOCATEコマンドを使用してカーソル変数を初期化しておく必要があります。たとえば、前の項で宣言したカーソル変数CUR-VARを初期化するには、次の文を使用します。
EXEC SQL ALLOCATE :CUR-VAR END-EXEC.
カーソル変数の割当てには、プリコンパイル時も実行時もサーバーをコールする必要はありません。
ALLOCATE文ではAT句は使用できません。
注意: カーソル変数を割り当てると、ヒープ・メモリーが使用されます。したがって、プログラム・ループではカーソル変数を割り当てないでください。
データベース・サーバーでカーソル変数をオープンするには、埋込み無名PL/SQLブロックを使用する必要があります。無名PL/SQLブロックは、カーソルをオープンする(また、同じ文でカーソルを定義する)PL/SQLストアド・プロシージャをコールして間接的にカーソルをオープンするか、Pro*COBOLプログラムから直接カーソルをオープンします。
次の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. ...
複数行の問合せ用にカーソル変数をオープンした後、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はできません。
カーソル変数をクローズするには、埋込み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.
カーソル変数CUR-VARに割り当てられたメモリーを解放するには、CLOSEの後でFREE文を使用します。
* Free the cursor variable memory. EXEC SQL FREE :CUR-VAR END-EXEC.
カーソル変数は、動的SQLではサポートされません。
カーソル変数は、ALLOCATE、FETCH、FREEおよびCLOSEコマンドのみで使用できます。DECLARE CURSORコマンドは、カーソル変数には適用されません。
ALLOCATEコマンドではAT句を使用できません。
次のサンプル・プログラム、SQLスクリプト(SAMPLE11.sql)およびPro*COBOLプログラム(SAMPLE11.pco)では、Pro*COBOLにおけるカーソル変数の使用方法を示します。
このサンプル・プログラムは、カーソル変数を宣言してオープンするパッケージを作成するための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; /
次に、前述の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.