この章では、スタンドアロンのプロシージャおよびファンクションを作成および実行する方法について説明します。 例では、入力パラメータ、出力パラメータおよびバインド変数を使用して、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.