入力パラメータ、出力パラメータおよびバインド変数を使用する例

この項の例では、PL/SQLの外部からのバインド変数(ホスト変数)を含む、IN、OUTおよびIN OUTパラメータを使用します。

INおよびOUTパラメータの使用

この例では、従業員情報を取得するプロシージャquery_empを作成して、employee_id値171をプロシージャに渡し、名前および給与を取得して2つのOUTパラメータに入れます。

Command> CREATE OR REPLACE PROCEDURE query_emp
           (p_id IN employees.employee_id%TYPE,
            p_name  OUT employees.last_name%TYPE,
            p_salary OUT employees.salary%TYPE) IS
         BEGIN
           SELECT last_name, salary INTO p_name, p_salary
           FROM employees
           WHERE employee_id = p_id;
         END query_emp;
         /
 
Procedure created.

Command> -- Execute the procedure
         DECLARE
          v_emp_name employees.last_name%TYPE;
          v_emp_sal  employees.salary%TYPE;
         BEGIN
          query_emp (171, v_emp_name, v_emp_sal);
          DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
           TO_CHAR (v_emp_sal, '$999,999.00'));
         END;
         /
Smith earns    $7,400.00
 
PL/SQL procedure successfully completed.

IN OUTパラメータの使用

電話番号を書式化する場合を考えてみます。次の例では、電話番号の桁のような10文字の文字列を受け取り、この書式が設定されていない文字列をIN OUTパラメータとしてプロシージャに渡します。プロシージャの実行が完了すると、IN OUTパラメータには書式化された電話番号の値が含まれます。

Command> CREATE OR REPLACE PROCEDURE format_phone
           (p_phone_no IN OUT VARCHAR2 ) IS
         BEGIN
          p_phone_no := '('  || SUBSTR (p_phone_no,1,3) ||
                        ') ' || SUBSTR (p_phone_no,4,3) ||
                        '-'  || SUBSTR (p_phone_no,7);
         END format_phone;
         /

Procedure created.

バインド変数を作成し、プロシージャを実行して、結果を確認します。

Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';

PL/SQL procedure successfully completed.

Command> PRINT b_phone_no;
B_PHONE_NO           : 8006330575
Command> BEGIN
           format_phone (:b_phone_no);
         END;
         /

PL/SQL procedure successfully completed.

Command> PRINT b_phone_no
B_PHONE_NO           : (800) 633-0575

連想配列の使用

次の例では、ttIsqlを使用して、NUMBER配列およびVARCHAR2配列を、PL/SQLの相当するOUT連想配列にバインドします。

「アプリケーションからの連想配列の使用」を参照してください。

次のSQL設定を想定します。

DROP TABLE FOO;
 
CREATE TABLE FOO (CNUM INTEGER,
                  CVC2 VARCHAR2(20));
 
INSERT INTO FOO VALUES ( null,
     'VARCHAR  1');
INSERT INTO FOO VALUES (-102,
     null);
INSERT INTO FOO VALUES ( 103,
     'VARCHAR  3');
INSERT INTO FOO VALUES (-104,
     'VARCHAR  4');
INSERT INTO FOO VALUES ( 105,
     'VARCHAR  5');
INSERT INTO FOO VALUES ( 106,
     'VARCHAR  6');
INSERT INTO FOO VALUES ( 107,
     'VARCHAR  7');
INSERT INTO FOO VALUES ( 108,
     'VARCHAR  8');
 
COMMIT;

次のPL/SQLパッケージ定義を想定します。これには、プロシージャP1の定義内の出力連想配列c1およびc2のそれぞれに使用される、INTEGER連想配列タイプNUMARRTYPおよびVARCHAR2連想配列タイプVCHARRTYPなどがあります。

CREATE OR REPLACE PACKAGE PKG1 AS
  TYPE NUMARRTYP IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
  TYPE VCHARRTYP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP);
 
END PKG1;
/
 
CREATE OR REPLACE PACKAGE BODY PKG1 AS
 
  CURSOR CUR1 IS SELECT CNUM, CVC2 FROM FOO;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP) IS
  BEGIN
    IF NOT CUR1%ISOPEN  THEN
      OPEN CUR1;
    END IF;
    FOR i IN 1..8 LOOP
      FETCH CUR1 INTO c1(i), c2(i);
      IF CUR1%NOTFOUND THEN
        CLOSE CUR1;
        EXIT;
      END IF;
    END LOOP;
  END P1;
 
END PKG1;

これで、ttIsqlはPKG1.P1をコールし、配列をP1出力連想配列にバインドして、これらの連想配列のコンテンツを出力します。

Command> var c1[10] number;
Command> var c2[10] varchar2(20);
Command> print;
C1                   : ARRAY [ 10 ] (Current Size 0)
C2                   : ARRAY [ 10 ] (Current Size 0)
Command> BEGIN PKG1.P1(:c1, :c2); END; /
 
PL/SQL procedure successfully completed.
 
Command> print
C1                   : ARRAY [ 10 ] (Current Size 8)
C1[1] : <NULL>
C1[2] : -102
C1[3] : 103
C1[4] : -104
C1[5] : 105
C1[6] : 106
C1[7] : 107
C1[8] : 108
C2                   : ARRAY [ 10 ] (Current Size 8)
C2[1] : VARCHAR  1
C2[2] : <NULL>
C2[3] : VARCHAR  3
C2[4] : VARCHAR  4
C2[5] : VARCHAR  5
C2[6] : VARCHAR  6
C2[7] : VARCHAR  7
C2[8] : VARCHAR  8