CREATE PROCEDURE v17

Name

CREATE PROCEDURE — Define a new stored procedure.

Synopsis

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

Description

CREATE PROCEDURE defines a new stored procedure. CREATE OR REPLACE PROCEDURE either creates a new procedure or replaces an existing definition.

If you include a schema name, then the procedure is created in the specified schema. Otherwise it's created in the current schema. The name of the new procedure can't match any existing procedure with the same input argument types in the same schema. However, procedures of different input argument types can share a name. This is called overloading.

To update the definition of an existing procedure, use CREATE OR REPLACE PROCEDURE. You can't change the name or argument types of a procedure this way. That syntax creates a new, distinct procedure. When using OUT parameters, you can't change the types of any OUT parameters except by dropping the procedure.

Parameters

name

The identifier of the procedure.

parameters

A list of formal parameters.

declarations

Variable, cursor, type, or subprogram declarations. If you include subprogram declarations, declare them after all other variable, cursor, and type declarations.

statements

SPL program statements. The BEGIN - END block can contain an EXCEPTION section.

IMMUTABLE

STABLE

VOLATILE

These attributes inform the query optimizer about the behavior of the procedure. You can specify only one of these attributes.

• Use IMMUTABLE to indicate that the procedure can't modify the database and always reaches the same result when given the same argument values. It doesn't perform database lookups and uses only information directly present in its argument list. If you include this clause, you can immediately replace any call of the procedure with all-constant arguments with the procedure value.

• Use STABLE to indicate that the procedure can't modify the database and that, in a single table scan, it consistently returns the same result for the same argument values. However, its result might change across SQL statements. Use this selection for procedures that depend on database lookups, parameter variables (such as the current time zone), and so on.

• Use VOLATILE (the default) to indicate that the procedure value can change even in a single table scan, so no optimizations can be made. You must classify any function that has side effects as volatile, even if its result is predictable, to prevent calls from being optimized away.

DETERMINISTIC

DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC procedure can't modify the database and always reaches the same result when given the same argument values. It doesn't perform database lookups and uses only information directly present in its argument list. If you include this clause, you can immediately replace any call of the procedure with all-constant arguments with the procedure value.

[ NOT ] LEAKPROOF

A LEAKPROOF procedure has no side effects and reveals no information about the values used to call the procedure.

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

• Use CALLED ON NULL INPUT (the default) to call the procedure normally when some of its arguments are NULL. Check for NULL values, if needed, and respond appropriately.

• Use RETURNS NULL ON NULL INPUT or STRICT to indicate that the procedure always returns NULL when any of its arguments are NULL. If you specify these clauses, the procedure doesn't execute when there are NULL arguments. Instead a NULL result is assumed.

[ EXTERNAL ] SECURITY DEFINER

Use SECURITY DEFINER (the default) to specify that the procedure executes with the privileges of the user that created it. The keyword EXTERNAL is allowed for SQL conformance but is optional.

[ EXTERNAL ] SECURITY INVOKER

Use the SECURITY INVOKER clause to execute the procedure with the privileges of the user that calls it. The keyword EXTERNAL is allowed for SQL conformance but is optional.

AUTHID DEFINER

AUTHID CURRENT_USER

• The AUTHID DEFINER clause is a synonym for [EXTERNAL] SECURITY DEFINER. If you omit the AUTHID clause or specify AUTHID DEFINER, the rights of the procedure owner determine access privileges to database objects.

• The AUTHID CURRENT_USER clause is a synonym for [EXTERNAL] SECURITY INVOKER. If you specify AUTHID CURRENT_USER, the rights of the current user executing the procedure determine access privileges.

PARALLEL { UNSAFE | RESTRICTED | SAFE }

The PARALLEL clause enables the use of parallel sequential scans (parallel mode). A parallel sequential scan uses multiple workers to scan a relation in parallel during a query, in contrast to a serial sequential scan.

• When set to UNSAFE (the default), the procedure can't execute in parallel mode. The presence of such a procedure forces a serial execution plan.

• When set to RESTRICTED, the procedure can execute in parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation isn't chosen for parallelism.

• When set to SAFE, the procedure can execute in parallel mode with no restriction.

COST execution_cost

execution_cost is a positive number giving the estimated execution cost for the procedure, in units of cpu_operator_cost. If the procedure returns a set, this is the cost per returned row. Larger values cause the planner to try to avoid evaluating the function more often than necessary.

ROWS result_rows

result_rows is a positive number giving the estimated number of rows for the planner to expect the procedure to return. This option is allowed only when the procedure is declared to return a set. The default is 1000 rows.

SET configuration_parameter { TO value | = value | FROM CURRENT }

The SET clause sets the specified configuration parameter to the specified value when the procedure is entered and then restored to its prior value when the procedure exits. SET FROM CURRENT saves the session's current value of the parameter as the value to apply when the procedure is entered.

If a SET clause is attached to a procedure, then the effects of a SET LOCAL command executed inside the procedure for the same variable are restricted to the procedure. The configuration parameter's prior value is restored at procedure exit. An ordinary SET command (without LOCAL) overrides the SET clause, similar to a previous SET LOCAL command. The effects of such a command persist after procedure exit, unless the current transaction is rolled back.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the procedure as an autonomous transaction.

Examples

This procedure lists the employees in the emp table:

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

This procedure uses IN OUT and OUT arguments to return an employee’s number, name, and job based on a search using the given employee number. If that isn't found, then the search uses the given name. An anonymous block calls the procedure.

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

This example uses the AUTHID DEFINER and SET clauses in a procedure declaration. The update_salary procedure conveys the privileges of the role that defined the procedure to the role that's calling the procedure while the procedure executes.

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;

Include the SET clause to set the procedure's search path to public and the work memory to 1MB. Other procedures, functions, and objects aren't affected by these settings.

In this example, the AUTHID DEFINER clause temporarily grants privileges to a role that otherwise might not be allowed to execute the statements in the procedure. To use the privileges associated with the role invoking the procedure, replace the AUTHID DEFINER clause with the AUTHID CURRENT_USER clause.

See also

DROP PROCEDURE, ALTER PROCEDURE