Creates a stored procedure.

Syntax

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 creates a stored procedure. CREATE OR REPLACE PROCEDURE either creates a new stored procedure or replaces an existing definition.

If you specify a schema name, the stored procedure is created in the specified schema. Otherwise, the stored procedure is created in the current schema. The name of the new stored procedure cannot be the same as an existing stored procedure that has the same input argument types in the same schema. However, stored procedures of different input argument types can share a name. This is called overloading. Overloading of stored procedures is a feature of PolarDB for PostgreSQL(Compatible with Oracle). Overloading of standalone stored procedures is incompatible with Oracle databases.

To update the definition of an existing stored procedure, you can use the CREATE OR REPLACE PROCEDURE statement. You cannot use the statement to change the name or argument types of a stored procedure. If you have tried, a new distinct stored procedure is created. When using the OUT parameters, you cannot change the types of OUT parameters unless you delete the stored procedure.

Parameters

ParameterDescription
ParameterDescription
nameThe identifier of the stored procedure.
parametersA list of parameter values.
declarationsVariable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statementsThe SPL program statements. The BEGIN - END block can contain an EXCEPTION section.
IMMUTABLE | STABLE | VOLATILEThese attributes are used to inform the query optimizer about the behavior of the stored procedure. You can specify only one of them. VOLATILE is the default behavior.
  • IMMUTABLE indicates that the stored procedure does not modify the database and always returns the same result if the same argument value is specified. The stored procedure does not perform database lookups or use information that is excluded from the argument list. If this clause is included, a call to the stored procedure with all-constant arguments can be immediately replaced with the stored procedure value.
  • STABLE indicates that the stored procedure does not modify the database and that the stored procedure returns the same result for the same argument value within a single table scan. In this case, the result can change across SQL statements. This attribute is suitable for stored procedures that depend on database lookups and parameter variables such as the current time zone.
  • VOLATILE indicates that the stored procedure value changes within a single table scan. In this case, no optimizations can be made. Note that functions with negative effects must be classified as a volatile function, even if the results are predictable. This prevents calls from being removed due to optimization.
DETERMINISTICDETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC stored procedure does not modify the database and always returns the same result if the same argument value is specified. The stored procedure does not perform database lookups or use information that is excluded from the argument list. If this clause is included, a call to the stored procedure with all-constant arguments can be immediately replaced with the stored procedure value.
[ NOT ] LEAKPROOFA LEAKPROOF stored procedure has no negative effects and reveals no information about the values used to call the stored procedure.
CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  • CALLED ON NULL INPUT is the default value. It indicates that the stored procedure is called when some arguments are NULL. If necessary, the author is responsible for checking NULL values and making proper responses.
  • RETURNS NULL ON NULL INPUT or STRICT indicates that the stored procedure returns NULL whenever some arguments are NULL. If these clauses are specified, the stored procedure is not executed when NULL arguments exist. A NULL result is returned automatically.
[ EXTERNAL ] SECURITY DEFINERSECURITY DEFINER specifies that the stored procedure executes with the privileges of the user that created it. This is the default value. The EXTERNAL keyword is allowed for SQL conformance but it is optional.
[ EXTERNAL ] SECURITY INVOKERThe SECURITY INVOKER clause indicates that the stored procedure executes with the privileges of the user that calls it. The EXTERNAL keyword is allowed for SQL conformance but it is optional.
AUTHID DEFINER | AUTHID CURRENT_USER
  • The AUTHID DEFINER clause is a synonym for [EXTERNAL] SECURITY DEFINER. If the AUTHID clause is omitted or AUTHID DEFINER is specified, the rights of the stored procedure owner are used to determine access privileges to database objects.
  • The AUTHID CURRENT_USER clause is a synonym for [EXTERNAL] SECURITY INVOKER. If AUTHID CURRENT_USER is specified, the rights of the current user executing the stored procedure are used to 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.
  • If this parameter is set to UNSAFE, the stored procedure cannot be executed in the parallel mode. If such a stored procedure exists in an SQL statement, a serial execution plan is enforced. If the PARALLEL clause is omitted, this is the default setting.
  • If this parameter is set to RESTRICTED, the stored procedure can be executed in the parallel mode, but the execution is restricted to the parallel group leader. If the qualification for a particular relation has content that is parallel restricted, the relation is not selected for parallel execution.
  • If this parameter is set to SAFE, the stored procedure can be executed in the parallel mode without restrictions.
COST execution_costexecution_cost is a positive value that indicates the estimated execution cost of the stored procedure. The unit is cpu_operator_cost. If the stored procedure returns a set, this is the cost of each returned row. Larger values cause the planner to try to avoid evaluating the stored procedure more often than necessary.
ROWS result_rowsresult_rows is a positive value that indicates the estimated number of rows that the planner expects the stored procedure to return. This value can be used only when the stored procedure is declared to return a set. The default value is 1000 rows.
SET configuration_parameter { TO value | = value | FROM CURRENT }The SET clause causes the specified configuration parameter to be set to the specified value when the stored procedure is entered, and then restored to its prior value when the stored procedure exits. SET FROM CURRENT saves the current value of the parameter as the value to be applied when the stored procedure is entered.

If a SET clause is attached to a stored procedure, the effects of a SET LOCAL command executed inside the stored procedure for the same variable are restricted to the stored procedure. The configuration parameter is restored to its prior value when the stored procedure exits. When the stored procedure exits, the configuration parameter is restored to its prior value. An ordinary SET command without LOCAL overrides the SET clause. This is similar to a previous SET LOCAL command. The effects of such a command persist after the stored procedure exits, unless the current transaction is rolled back.

PRAGMA AUTONOMOUS_TRANSACTIONPRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the stored procedure as an autonomous transaction.
Note
  • The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB for PostgreSQL(Compatible with Oracle). However, these keywords are not supported by Oracle databases.
  • The IMMUTABLE, STABLE, STRICT, LEAKPROOF, COST, ROWS and PARALLEL { UNSAFE | RESTRICTED | SAFE } attributes are supported only by stored procedures of PolarDB for PostgreSQL(Compatible with Oracle).
  • Stored procedures are created as SECURITY DEFINERS by default. Stored procedures defined in plpgsql are created as SECURITY INVOKERS.

Examples

The following stored 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

The following stored procedure uses IN OUT and OUT arguments to return the number, name, and job of an employee. First, the search is based on the specified employee number. If no results are found, the specified name is used. An anonymous block calls the stored 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

The following example shows how to use the AUTHID DEFINER and SET clauses in a procedure declaration. The update_salary stored procedure grants the privileges of the role that defined the stored procedure to the role that is calling the stored procedure:

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;

You can use the SET clause to set the search path of the stored procedure to public and set the working memory to 1 MB. Other stored procedures, functions, and objects are not affected by these settings.

In this example, the AUTHID DEFINER clause temporarily grants privileges to a role that may not be allowed to execute the statements within the stored procedure. To instruct the server to use the privileges associated with the role that calls the stored procedure, replace the AUTHID DEFINER clause with the AUTHID CURRENT_USER clause.