PolarDB:Create a stored procedure

name

The identifier of the procedure.

parameters

A list of formal parameters.

declarations

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

statements

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

IMMUTABLE | STABLE | VOLATILE

These attributes tell the query optimizer about the procedure's behavior. Specify only one. VOLATILE is the default.

• Use IMMUTABLE to indicate that the procedure never modifies the database and always returns the same result for the same argument values. It performs no database lookups and uses no information beyond its argument list. Calls with all-constant arguments can be immediately replaced with the procedure value.

• Use STABLE to indicate that the procedure never modifies the database and returns consistent results for the same argument values within a single table scan, though results may differ across SQL statements. Suitable for procedures that depend on database lookups and parameter variables such as the current time zone.

• Use VOLATILE (the default) to indicate that the procedure value can change within a single table scan, so no optimizations apply. Any procedure with side effects must be declared VOLATILE even if its result is predictable, to prevent the optimizer from eliminating calls.

DETERMINISTIC

A synonym for IMMUTABLE. A DETERMINISTIC procedure never modifies the database and always returns the same result for 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

Use LEAKPROOF to indicate that the procedure has no side effects and reveals no information about the values passed to it.

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

• Use CALLED ON NULL INPUT (the default) to call the procedure normally when any 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 argument is NULL. The procedure is not executed when NULL arguments are present.

[EXTERNAL] SECURITY DEFINER

Use SECURITY DEFINER to run the procedure with the privileges of the user who created it. This is the default. The keyword EXTERNAL is allowed for SQL conformance but is optional.

[EXTERNAL] SECURITY INVOKER

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

AUTHID DEFINER | AUTHID CURRENT_USER

• AUTHID DEFINER is a synonym for [EXTERNAL] SECURITY DEFINER. If the AUTHID clause is omitted, AUTHID DEFINER applies by default: the procedure owner's rights determine access to database objects.

• AUTHID CURRENT_USER is a synonym for [EXTERNAL] SECURITY INVOKER. The rights of the user executing the procedure determine access to database objects.

PARALLEL { UNSAFE | RESTRICTED | SAFE }

Controls whether the procedure can run in parallel mode.

• UNSAFE (default when PARALLEL is omitted): the procedure cannot run in parallel mode. Its presence forces a serial execution plan.

• RESTRICTED: the procedure can run in parallel mode but is restricted to the parallel group leader. Relations with parallel-restricted qualifications are excluded from parallelism.

• SAFE: the procedure can run in parallel mode with no restrictions.

COST execution_cost

A positive number estimating the execution cost in cpu_operator_cost units. If the procedure returns a set, this is the cost per returned row. Higher values cause the planner to avoid evaluating the procedure more often than necessary.

ROWS result_rows

A positive number estimating the number of rows the planner expects the procedure to return. Only valid for procedures declared to return a set. The default is 1,000 rows.

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

Sets a configuration parameter to the specified value when the procedure is entered, then restores it to the prior value when the procedure exits. SET FROM CURRENT saves the current session value of the parameter as the value to apply on entry.

If a SET clause is attached to a procedure, a SET LOCAL statement inside the procedure for the same variable is restricted to the procedure — the prior value is restored at exit. An ordinary SET statement (without LOCAL) overrides the SET clause; its effects persist after procedure exit unless the current transaction is rolled back.

PRAGMA AUTONOMOUS_TRANSACTION

Declares the procedure as an autonomous transaction.

Basic procedure

The following procedure takes no parameters and prints a message:

CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END simple_procedure;

Procedure with AUTHID DEFINER and SET clauses

The following procedure uses AUTHID DEFINER to run with the privileges of the procedure owner, and uses SET to configure the search path and working memory:

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;

The SET clauses set the search path to public and set working memory to 1 MB for the duration of the procedure. These settings affect other procedures, functions, and objects called within this procedure.

To run the procedure with the caller's privileges instead, replace AUTHID DEFINER with AUTHID CURRENT_USER.