Procedures are standalone SPL programs that are invoked or called as an individual SPL program statement. When called, procedures may optionally receive values from the caller in the form of input parameters and optionally return values to the caller in the form of output parameters.
The CREATE PROCEDURE statement defines and names a standalone procedure that will be stored in the database.
If a schema name is included, the procedure is created in the specified schema. Otherwise, it is created in the current schema. The name of the new procedure must not match any existing procedure with the same input argument types in the same schema. However, procedures of different input argument types may share a name. This is called overloading. Overloading of procedures is a feature of the PolarDB for PostgreSQL(Compatible with Oracle) - overloading of stored, standalone procedures is not compatible with Oracle databases.
To update the definition of an existing procedure, use CREATE OR REPLACE PROCEDURE. It is not possible to change the name or argument types of a procedure this way (if you tried, you would actually be creating a new, distinct procedure). When using OUT parameters, you cannot change the types of any OUT parameters except by dropping the 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 ];
Argument | Description |
---|---|
name | name is the identifier of the procedure. |
parameters | parameters is a list of formal parameters. |
declarations | declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations. |
statements | statements are SPL program statements (the BEGIN - END block may contain an EXCEPTION section). |
IMMUTABLE STABLE VOLATILE | These attributes inform the query optimizer about the behavior of the procedure. You can specify only one option. VOLATILE is the default behavior.
|
DETERMINISTIC | DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC procedure cannot modify the database and always reaches the same result when given the same argument values. It does not do database lookups or use information not directly present in its argument list. If you include this clause, any call of the procedure with all-constant arguments can be immediately replaced with the procedure value. |
[ NOT ] LEAKPROOF | LEAKPROOF 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 |
|
[ EXTERNAL ] SECURITY DEFINER | SECURITY DEFINER specifies that the procedure will execute with the privileges of the user that created it. This is the default value. The keyword EXTERNAL is allowed for SQL conformance, but is optional. |
[ EXTERNAL ] SECURITY INVOKER | SECURITY INVOKER specifies that the procedure will execute 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 |
|
PARALLEL { UNSAFE | RESTRICTED | SAFE } | PARALLEL enables the use of parallel sequential scans (parallel mode). In contrast to a serial sequential scan, a parallel sequential scan uses multiple workers to scan a relation in parallel during a query.
|
COST execution_cost | execution_cost is a positive number giving the estimated execution cost for the procedure. Units: 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 that the planner expects the procedure to return. This is allowed only when the procedure is declared to return a set. The default assumption is 1,000 rows. |
SET configuration_parameter { TO value | = value | FROMCURRENT } | SET causes the specified configuration parameter to be set 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 current parameter value of the session as the value to be applied when the procedure is entered. If a SET clause is attached to a procedure, then the effects of a SET LOCAL statement executed inside the procedure for the same variable are restricted to the procedure. The prior value of the configuration parameter is restored at procedure exit. An ordinary SET statement (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL statement, with the effects of such a statement persisting 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. |
The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB for PostgreSQL(Compatible with Oracle) but are not supported by Oracle.
Example
The following example shows a simple procedure that takes no parameters:
CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!') ;
END simple_procedure;
The procedure is stored in the database by entering the procedure code in a PolarDB for PostgreSQL(Compatible with Oracle).
The following example describes how to use 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 is 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 search path of the procedure to public and the work memory to 1 MB. Other procedures, functions, and objects are 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 procedure. To instruct the server to use the privileges associated with the role invoking the procedure, replace the AUTHID DEFINER clause with the AUTHID CURRENT_USER clause.