ヘッダーをスキップ
Pro*COBOL®プログラマーズ・ガイド
11gリリース2(11.2)
E50141-01
  ドキュメント・ライブラリへ移動
ライブラリ
製品リストへ移動
製品
目次へ移動
目次
索引へ移動
索引

前
 
次
 

6 埋込みPL/SQL

PL/SQLトランザクション処理ブロックをプログラム内に埋め込むことにより、パフォーマンスを改善する方法を説明します。この章の内容は、次のとおりです。

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変数およびデータベース・オブジェクトとを区切ります。

VARCHAR変数

PL/SQLブロックに入ると、Oracleは自動的にVARCHARホスト変数の長さフィールドをチェックします。このため、ブロックに入るに長さフィールドを設定する必要があります。入力変数の長さフィールドは、文字列フィールドに格納される値の長さに設定します。出力変数の場合は、長さフィールドにその文字列フィールドに許される最大の長さを設定します。

標識変数

PL/SQLブロックでは、標識変数は単独では参照できません。対応するホスト変数に追加する必要があります。また、標識変数付きでホスト変数を参照する場合、同一ブロック中では常に同じ方法で参照する必要があります。

NULLの処理

ブロックに入るとき、標識変数の値が-1であれば、PL/SQLによってNULLがホスト変数に自動的に割り当てられます。ブロックから出るとき、ホスト変数がNULLであれば、PL/SQLによって値-1が標識変数に自動的に割り当てられます。

切り捨てられた値の処理

PL/SQLでは、切り捨てられた文字列の値がホスト変数に割り当てられても、例外とはみなされません。ただし、標識変数を指定している場合には、PL/SQLによってその標識変数が文字列の元の長さに設定されます。

SQLCHECK

埋込みPL/SQLブロックを持つプログラムをプリコンパイルする際には、SQLCHECK=SEMANTICSを指定する必要があります。また、USERIDオプションも使用する必要があります。詳細は、第14章「プリコンパイラ・オプション」を参照してください。

PL/SQLの利点

この項では、PL/SQLによって提供される次のような機能および利点を説明します。

PL/SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

パフォーマンスの向上

PL/SQLによって、オーバーヘッドの削減、パフォーマンスの改善および生産性の向上が図れます。たとえば、PL/SQLを使用しないと、Oracleは一度に1つずつSQL文を処理する必要があります。その結果、各SQL文によってサーバーへの別のコールが発生し、オーバーヘッドが増加します。ただし、PL/SQLを使用すると、サーバーにSQL文のブロック全体を送信することができます。これによって、アプリケーションとサーバーとの間の通信は最小限になります。

Oracleとの統合

PL/SQLはサーバーと密接に統合されます。たとえば、PL/SQLデータ型の大部分は、データ・ディクショナリに固有のデータ型です。さらに、次の例に示すとおり、データ・ディクショナリ内に格納された列定義に基づいて変数を宣言するための%TYPE属性が使用できます。

job_title  emp.job%TYPE; 

したがって、列の厳密なデータ型を知る必要はありません。しかも、列定義を変更すると、変数宣言もそれに応じて自動的に変更されます。これによって、データ独立性を提供し、メンテナンス・コストを削減し、データベース変更時にプログラムが順応できるようになります。

カーソル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; 

レコード中のフィールドの参照にはドット表記法を使用することに注意してください。

サブプログラム

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表

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 ... 

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を指定する必要があります。詳細は、「Pro*COBOLプリコンパイラ・オプションの使用」を参照してください。

ホスト変数およびPL/SQL

ホスト変数は、ホスト言語とPL/SQLブロック間の通信を仲介します。ホスト変数はPL/SQLと共有できるので、PL/SQLではホスト変数の設定および参照ができます。

たとえば、ユーザーに情報の提供を求め、この情報をPL/SQLブロックに渡すためのホスト変数を使用するようにユーザーに指示できます。これにより、PL/SQLを使用してデータベースにアクセスし、ホスト変数を介してその結果をホスト・プログラムに戻せるようになります。

PL/SQLブロック内ではホスト変数はブロック全体のグローバル変数として扱われ、PL/SQL変数を使用できる位置であればどこにでも使用できます。SQL文内におけるホスト変数と同様、PL/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はブロックの中で設定されていることに注意してください。

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. 

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. 

標識変数およびPL/SQL

PL/SQLでは、NULLを操作できるため、標識変数は必要ありません。たとえば、PL/SQL内では、次のようにIS NULL演算子を使用してNULLをテストできます。

IF variable IS NULL THEN ... 

次のように、代入演算子(:=)を使用してNULLを割り当てることができます。

variable := NULL; 

ただし、ホスト言語はNULLを扱えないため、標識変数が必要です。埋込みPL/SQLでこの要件を満たすには、標識変数を次の用途に使用します。

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では、ブロックに入るときに標識変数の値がチェックされ、ブロックから出るときにその値が正しく設定されます。

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. 

切り捨てられた値の処理

PL/SQLでは、切り捨てられた文字列の値がホスト変数に割り当てられても、例外とはみなされません。ただし、標識変数を指定している場合には、PL/SQLによってその標識変数が文字列の元の長さに設定されます。次の例では、ホスト・プログラムは、IND-NAMEの値をチェックして、切り捨てられた値がEMP-NAMEに割り当てられたかどうかを判別できます。

     EXEC SQL EXECUTE 
     DECLARE 
        ... 
        new_name  CHAR(10); 
     BEGIN 
        ... 
        :EMP_NAME:IND-NAME := new_name; 
        ... 
     END; 
     END-EXEC. 

ホスト表および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

-

-

-

-

-

-


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ブロックがサーバーに送信されるときに、一緒に送られるホスト表は、はるかに小さくなります。これにより、時間を節約し、ネットワーク化された環境でネットワークの通信量を削減できます。

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. 
       ... 

埋込みPL/SQLでのカーソルの使用

プログラムで同時に使用できるカーソルの最大数は、データベースの初期化パラメータ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は、使用されるカーソルの最大数を計算する方法を示しています。

図6-1 使用されるカーソルの最大数の計算方法

使用されるカーソルの最大数の計算方法
「図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サブプログラム

無名ブロックとは異なり、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}文が失敗した場合、Oracleはエラーではなく警告を発行します。CREATE文のすべての構文は、『Oracle Database SQL言語リファレンス』を参照してください。

ストアドPL/SQLまたはJavaサブプログラムのコール

ホスト・プログラムからストアド・サブプログラムをコールするには、無名PL/SQLブロックまたはCALL埋込みSQL文のどちらかを使用できます。

無名PL/SQLブロック

次の例では、raise_salaryの名前のスタンドアロン・プロシージャをコールします。

     EXEC SQL EXECUTE 
     BEGIN 
        raise_salary(:emp_id, :increase); 
     END; 
     END-EXEC. 

ストアド・サブプログラムにパラメータを組み込めることに注意してください。この例では、実パラメータemp_idおよびincreaseはホスト変数です。

次の例では、プロシージャraise_salaryemp_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を使用する必要があります。

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のどちらかを使用できます。

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(実行可能埋込みSQL)」を参照してください。引数の引渡しなどの説明は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』の「外部ルーチン」の章を参照してください。

動的PL/SQLの使用

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」を参照してください。

サブプログラムの制限事項

動的SQL方法4では、型がTABLEのパラメータを指定してホスト表をPL/SQLプロシージャにバインドできません。

サンプル・プログラム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仮パラメータに値を割り当てる必要があります。そうしないと、対応する実パラメータの値が未確定になります。

リモート・アクセス

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言語リファレンス』を参照してください。

カーソル変数は、カーソルと同じように、複数行の問合せのアクティブ・セットの中のカレント行を指します。カーソルとカーソル変数との違いは、定数と変数との違いと同じです。カーソルは静的で、カーソル変数は特定の問合せに結び付けられていないため、動的です。カーソル変数は、型の互換性のある任意の問合せに対してオープンできます。

カーソル変数に新しい値を割り当てて、サブプログラム(データベースに格納されているサブプログラムなど)にパラメータとして渡せます。これにより、データ検索を簡単に集中化できます。

まず、カーソル変数を宣言します。カーソル変数を宣言した後、次の文を使用してカーソル変数を制御します。

カーソル変数を宣言してメモリーを割り当てた後、そのカーソル変数を入力ホスト変数(バインド変数)としてPL/SQLに渡します。次に、サーバー側でOPEN、FORを使用して複数行の問合せ用にオープンし、クライアント側でFETCHしてから、サーバー側かクライアント側のどちらかでCLOSEします。

カーソル変数の利点は、次のとおりです。

カーソル変数の宣言

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ストアド・プロシージャによる間接的なオープン

次の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.
     ...

Pro*COBOLアプリケーションからの直接的なオープン

Pro*COBOLプログラム内で無名PL/SQLブロックを使用してカーソルをオープンするには、無名ブロックの中でカーソルを定義します。次の例を考えてみます。

 PROCEDURE DIVISION.
     ...
     EXEC SQL EXECUTE
         begin
             OPEN :emp_cursor FOR SELECT ename FROM emp
                 WHERE deptno = :DEPT-NUM;
         end;
     END-EXEC.
     ...

カーソル変数からのフェッチ

複数行の問合せ用にカーソル変数をオープンした後、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句を使用できません。

サンプル・プログラム11: カーソル変数

次のサンプル・プログラム、SQLスクリプト(SAMPLE11.sql)およびPro*COBOLプログラム(SAMPLE11.pco)では、Pro*COBOLにおけるカーソル変数の使用方法を示します。

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;
/

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.