CREATE PROCEDURE

Function

CREATE PROCEDURE creates a stored procedure.

Stored procedures are sets of precompiled SQL statements that are stored in a database and repeatedly executed.

Precautions

When creating a stored procedure, you need to explicitly specify the schema (for example, schema.table) that the table object belongs to, or there maybe an execution exception.
current_schema and search_path specified by SET during the stored procedure creation are invalid. search_path and current_schema before and after function execution should be the same.
When creating a stored procedure, it is not allowed to nest other aggregate functions or system functions outside the avg function.
The precision values of the parameters or return values of a stored procedure are not checked.
In a cluster with multiple CNs, the input or output parameters of a stored procedure cannot be set to the temporary table type. When a stored procedure is created on a CN that is not currently connected, the correct temporary schema cannot be obtained based on the table name, resulting in an incorrect table type.
A stored procedure can have multiple return values or no return value. After a stored procedure without a return value is invoked, the command output is empty.
If a stored procedure has output parameters, the SELECT statement uses the default values of the output parameters when calling the procedure. When the CALL statement calls the stored procedure, it requires that the output parameter values are adapted to Oracle. When the CALL statement calls a non-overloaded function, output parameters must be specified. When the CALL statement calls an overloaded PACKAGE function, it can use the default values of the output parameters. For details, see the examples in CALL.
A stored procedure with the PACKAGE attribute can use overloaded functions.
Stored procedures cannot be submitted by segment.
The content in CREATE FUNCTION also applies to CREATE PROCEDURE. For details, see CREATE FUNCTION. After a stored procedure is created, the definition of CREATE FUNCTION is returned when the definition is queried.

Syntax

     
      
        
        
          CREATE [ OR REPLACE ] PROCEDURE procedure_name
    [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
    [
       { IMMUTABLE | STABLE | VOLATILE }
       | { SHIPPABLE | NOT SHIPPABLE }
       | {PACKAGE}
       | [ NOT ] LEAKPROOF
       | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
       | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER}
       | COST execution_cost
       | ROWS result_rows
       | SET configuration_parameter { [ TO | = ] value | FROM CURRENT }
    ][ ... ]
 { IS | AS } 
plsql_body 
/

         

       

     
    

Parameter Description

**Table 1** CREATE PROCEDURE parameters
Parameter	Description	Value Range
OR REPLACE	Replaces the original definition when two stored procedures are with the same name.	-
procedure_name	Specifies the name of the stored procedure that is created (optionally with schema names).	A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.
argmode	Specifies the mode of the parameter. OUT and INOUT cannot be used in procedure definition of RETURNS TABLE.	IN, OUT, IN OUT, or VARIADIC. IN (default value): input parameter. OUT: output parameter. Only this type can include VARIADIC. INOUT: both input and output parameters. OUT VARIADIC: output parameter, which is defined as an array type.
argname	Specifies the name of the parameter. There is no strict requirement on the sequence of argname and argmode. You are advised to use argname, argmode, and argtype in sequence.	A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.
argtype	Specifies the type of the parameter.	A valid data type.
IMMUTABLE \| STABLE \| VOLATILE	Specifies a constraint. Parameters here are similar to those of CREATE FUNCTION. For details, see CREATE FUNCTION.	-
SHIPPABLE \| NOT SHIPPABLE	Specifies whether a stored procedure can be pushed down to DNs for execution. IMMUTABLE stored procedures can always be pushed down to DNs for execution. STABLE and VOLATILE stored procedures can be pushed down to DNs for execution only when the SHIPPABLE attribute is used. SHIPPABLE indicates that the entire stored procedure will be pushed down to DNs for execution. If SHIPPABLE is incorrectly set, serious problems such as incorrect results may occur. SHIPPABLE, like IMMUTABLE, comes with usage limits. It requires stored procedures to exclude elements that prevent pushdown. Once pushed down to a DN, the stored procedures must rely solely on the DN's dataset in the internal computing. Examples: If a stored procedure references a hash table, you cannot define the procedure as SHIPPABLE. If factors such as stored procedures and syntax cannot be pushed down, the stored procedure cannot be defined as SHIPPABLE. For details, see Optimizing Statement Pushdown. If a stored procedure's calculation process involves data across DNs, the procedure cannot be defined as SHIPPABLE. For example, some aggregation operations involve data across DNs.	-
PACKAGE	Specifies whether the stored procedure can be overloaded. PostgreSQL-style stored procedures can be overloaded, and this parameter is designed for Oracle-style stored procedures. All PACKAGE and non-PACKAGE stored procedures cannot be overloaded or replaced. Stored procedures with the PACKAGE attribute functions do not support parameters in the VARIADIC mode. The PACKAGE attribute of a stored procedure cannot be modified.	-
LEAKPROOF	Specifies that the parameters of the stored procedure include only return values. LEAKPROOF can be set only by the system administrator.	-
CALLED ON NULL INPUT	Declares that some parameters of the stored procedure can be called in normal mode if the parameter values are NULL. It can be omitted.	-
RETURNS NULL ON NULL INPUT \| STRICT	STRICT specifies that the stored procedure always returns NULL whenever any of its parameters are NULL. If it is specified, the stored procedure is not executed when there are NULL parameters; instead a NULL result is returned automatically. The usage of RETURNS NULL ON NULL INPUT is the same as that of STRICT.	-
EXTERNAL	The keyword EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all stored procedures not only external ones.	-
SECURITY INVOKER \| AUTHID CURRENT_USER	Specifies that the stored procedure is to be executed with the permissions of the user that calls it. It can be omitted. SECURITY INVOKER and AUTHID CURRENT_USER have the same functions.	-
SECURITY DEFINER \| AUTHID DEFINER	Specifies that the stored procedure will be executed with the permissions of the user who created it. AUTHID DEFINER and SECURITY DEFINER have the same functions.	-
COST execution_cost	Estimates the execution cost of the stored procedure. The unit of execution_cost is cpu_operator_cost.	Positive
ROWS result_rows	Estimates the number of rows returned by the stored procedure. This is only allowed when the stored procedure is declared to return a set.	Its value is a positive number. The default value is 1000.
configuration_parameter	Specifies parameter values configured in the stored procedure. value Sets a specified database session parameter to a specified value. Value range: a string Specifies the default value. DEFAULT: The setting will be used in new sessions. OFF: The setting is disabled. RESET: The setting will be used in new sessions. from current Uses the value of configuration_parameter of the current session.	-
plsql_body	Specifies the PL/SQL stored procedure body. When you create a user, or perform other operations requiring password input in a stored procedure, the system catalog and csv log records the unencrypted password. You are advised not to perform such operations in the stored procedure body.	-

Examples

Create the stored procedure prc_add, where param1 is an input parameter and param2 is an input and output parameter. The procedure calculates the sum of param1 and param2, assigns the result to param2, and outputs the result.

       
            CREATE OR REPLACE PROCEDURE prc_add
(
    param1 IN INTEGER,
    param2 IN OUT INTEGER
)
AS
BEGIN
   param2:= param1 + param2;
   dbms_output.put_line('result is: '||to_char(param2));
END;
/

Call the stored procedure prc_add.

       
            CALL prc_add(2,3);

Create the stored procedure pro_variadic. The parameter mode of param1 is VARIADIC. The integer array is converted to a TEXT format and then output via param2.

       
            CREATE OR REPLACE PROCEDURE pro_variadic (param1 VARIADIC int4[],param2 OUT TEXT)
AS
BEGIN
    param2:= param1::text;
END;
/

Execute the stored procedure pro_variadic.

       
            SELECT pro_variadic(VARIADIC param1=> array[1,2,3,4]);

Create the stored procedure package_func_overload with the PACKAGE attribute.

       
            CREATE OR REPLACE PROCEDURE package_func_overload(col int, col2 out varchar)
PACKAGE
AS
DECLARE
    col_type text;
BEGIN
     col2 := '122';
         dbms_output.put_line('two varchar parameters ' || col2);
END;
/