:CREATE PROCEDURE

構文

CREATE [OR REPLACE] PROCEDURE name [ (parameters) ]
   [
          IMMUTABLE
        | STABLE
        | VOLATILE
        | DETERMINISTIC
        | [ NOT ] LEAKPROOF
        | CALLED ON NULL INPUT
        | RETURNS NULL ON NULL INPUT
        | STRICT
        | [ EXTERNAL ] SECURITY INVOKER
        | [ EXTERNAL ] SECURITY DEFINER
        | AUTHID DEFINER
        | AUTHID CURRENT_USER
        | PARALLEL { UNSAFE | RESTRICTED | SAFE }
        | COST execution_cost
        | ROWS result_rows
        | SET configuration_parameter
          { TO value | = value | FROM CURRENT }
   ...]
{ IS | AS }
    [ PRAGMA AUTONOMOUS_TRANSACTION; ]
    [ declarations ]
  BEGIN
    statements
  END [ name ];

説明

CREATE PROCEDURE により、ストアドプロシージャが作成されます。 CREATE OR REPLACE PROCEDURE により、新しいストアドプロシージャが作成されるか、または既存の定義が置き換えられます。

スキーマ名を指定すると、ストアドプロシージャは指定したスキーマに作成されます。 それ以外の場合、ストアドプロシージャは現在のスキーマに作成されます。 新しいストアドプロシージャの名前は、同じスキーマにある同じ入力引数の型を持つ既存のストアドプロシージャと同じものにはできません。 ただし、異なる入力引数の型のストアドプロシージャであれば名前を共有できます。 これはオーバーロードと呼ばれています。 ストアドプロシージャのオーバーロードは、Oracle と互換性のある PolarDB データベースの機能です。 スタンドアロンストアドプロシージャのオーバーロードは、Oracle データベースと互換性がありません。

既存のストアドプロシージャの定義を更新するには、CREATE OR REPLACE PROCEDURE 文を使用します。 CREATE OR REPLACE PROCEDURE 文を使用して、ストアドプロシージャの名前や引数の型を変更することはできません。 試行した場合、新しい別のストアドプロシージャが作成されます。 OUT パラメーターを使用する場合は、ストアドプロシージャを削除しない限り、OUT パラメーターの型は変更できません。

パラメーター

例

次のストアドプロシージャは、emp テーブルの従業員の一覧です。

CREATE OR REPLACE PROCEDURE list_emp
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    CURSOR emp_cur IS
        SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
    OPEN emp_cur;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur INTO v_empno, v_ename;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_cur;
END;

EXEC list_emp;

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER

次のストアドプロシージャでは、IN OUT および OUT 引数を使用して、従業員の番号、名前、および仕事が返されます。 まず、検索は指定された従業員番号に基づいています。 結果が見つからない場合は、指定された名前が使用されます。 無記名ブロックでストアドプロシージャが呼び出されます。

CREATE OR REPLACE PROCEDURE emp_job (
    p_empno         IN OUT emp.empno%TYPE,
    p_ename         IN OUT emp.ename%TYPE,
    p_job           OUT    emp.job%TYPE
)
IS
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
BEGIN
    SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno;
    p_ename := v_ename;
    p_job   := v_job;
    DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        BEGIN
            SELECT empno, job INTO v_empno, v_job FROM emp
                WHERE ename = p_ename;
            p_empno := v_empno;
            p_job   := v_job;
            DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' ||
                    'number, ' || p_empno || ' nor name, '  || p_ename);
                p_empno := NULL;
                p_ename := NULL;
                p_job   := NULL;
        END;
END;

DECLARE
    v_empno      emp.empno%TYPE;
    v_ename      emp.ename%TYPE;
    v_job        emp.job%TYPE;
BEGIN
    v_empno := 0;
    v_ename := 'CLARK';
    emp_job(v_empno, v_ename, v_job);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
END;

Found employee CLARK
Employee No: 7782
Name       : CLARK
Job        : MANAGER

次の例は、プロシージャの宣言で AUTHID DEFINER および SET 句を使用する方法を示しています。 update_salary ストアドプロシージャでは、ストアドプロシージャを定義したロールの特権を、ストアドプロシージャを呼び出しているロールに付与します。

CREATE OR REPLACE PROCEDURE update_salary(id INT, new_salary NUMBER)
  SET SEARCH_PATH = 'public' SET WORK_MEM = '1MB'
  AUTHID DEFINER IS
BEGIN
  UPDATE emp SET salary = new_salary WHERE emp_id = id;
END;

SET 句を使用して、ストアドプロシージャの検索パスをパブリックに設定し、作業メモリを 1 MB に設定できます。 他のストアドプロシージャ、関数、およびオブジェクトは、これらの設定の影響を受けません。

この例では、AUTHID DEFINER 句で、ストアドプロシージャ内の文の実行を許可されていない可能性があるロールに特権を一時的に付与します。 ストアドプロシージャを呼び出すロールに関連付けられている特権を使用するようにサーバーに指示するには、AUTHID DEFINER 句を AUTHID CURRENT_USER 句に置き換えます。