6 スタンドアロンのサブプログラムを使用する例

この章では、スタンドアロンのプロシージャおよびファンクションを作成および実行する方法について説明します。例では、入力パラメータ、出力パラメータおよびバインド変数を使用して、PL/SQLブロックからプロシージャおよびファンクションを実行します。この章の構成は、次のとおりです。

入力パラメータ、出力パラメータおよびバインド変数を使用する例
システム・ビューを問い合せる例
スタンドアロン・ファンクションを作成および起動する例

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

この項の例では、INパラメータ、OUTパラメータ、IN OUTパラメータおよびバインド変数（ホスト変数）を使用します。

例6-1 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.

例6-2 バインド変数を使用したプロシージャの実行

この例では、バインド変数を使用して、例6-1のプロシージャquery_empを実行します。（この例を実行する前に、このプロシージャを作成する必要があります。）データ型に互換性があることを必ず確認します。

Command> VARIABLE b_name VARCHAR2 (25);
Command> VARIABLE b_sal  NUMBER;
Command> BEGIN
       >  query_emp (171, :b_name, :b_sal);
       > END;
       > /

PL/SQL procedure successfully completed.

Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

例6-3 IN OUTパラメータおよびバインド変数の使用

電話番号を書式化する場合を考えてみます。 IN OUTパラメータを使用して、書式化されていない電話番号をプロシージャに渡すことにします。プロシージャの実行が完了すると、IN OUTパラメータには書式化された電話番号の値が含まれます。この例のプロシージャFORMAT_PHONEは、電話番号の数字を含む10文字の文字列を受け取り、書式化の処理を実行します。バインド変数b_phone_noは、最初はFORMAT_PHONEに渡す入力値になり、実行後は更新された文字列を返す出力値として使用されます。

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

システム・ビューを問い合せる 例

この項では、システム・ビューを問い合せる例を示します。

例6-4 システム・ビューUSER_SOURCEの問合せ

この例では、USER_SOURCEシステム・ビューを問い合せて、例6-1のプロシージャquery_empのソース・コードを確認します。（この例を実行する前に、このプロシージャを作成する必要があります。）

Command> SELECT SUBSTR (text, 1, LENGTH(text)-1)
       > FROM user_source
       > WHERE name = 'QUERY_EMP' AND type = 'PROCEDURE';

これにより、次のような出力が作成されます。

< 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; >
9 rows found.

注意:

他のUSER_*システム・ビューと同様に、USER_SOURCEシステム・ビューのSELECT権限はすべてのユーザーにあります。

スタンドアロン・ファンクションを作成および起動する 例

この項では、スタンドアロン・ファンクションを実行する例を示します。

例6-5 スタンドアロン・ファンクションの作成および起動

この例では、ファンクションget_salを作成して起動します。このファンクションは1つの入力パラメータであり、salaryをNUMBER型で返します。

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >   v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /

Function created.
Command> BEGIN
       >   DBMS_OUTPUT.PUT_LINE (get_sal (100));
       > END;
       > /
24000

PL/SQL procedure successfully completed.