• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE PROCEDURE

CREATE PROCEDURE

Function

CREATE PROCEDURE creates a stored procedure.

Precautions

  • The precision values (if any) of the parameters or return values of a stored procedure are not checked.
  • When creating a stored procedure, you are advised to display the specified schema for the operations on the table objects in the stored procedure definition. Otherwise, the stored procedure may fail to be executed.
  • current_schema and search_path specified by SET during stored procedure creation are invalid. search_path and current_schema before and after function execution should be the same.
  • If a stored procedure has output parameters, the SELECT statement calling it must use the default values of the output parameters. When the CALL statement calls it, the output parameter values must be specified to adapt to Oracle. For details, see examples in CALL.
  • Currently, stored procedures cannot be reloaded.
  • When you create a procedure, you cannot insert aggregate functions or other functions out of the avg function.

Syntax

CREATE [ OR REPLACE ] PROCEDURE procedure_name
    [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
   { IS | AS } plsql_body
/

Parameter Description

  • 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).

    Value range: A string. It must comply with the naming convention rule.

  • argmode

    Specifies the mode of an argument.

    VARIADIC specifies arguments of array types.

    Value range: IN, OUT, IN OUT, INOUT, and VARIADIC. The default value is IN. Only the argument of OUT mode can be followed by VARIADIC. The parameters of OUT and INOUT cannot be used in procedure definition of RETURNS TABLE.

  • argname

    Specifies the name of an argument.

    Value range: A string. It must comply with the naming convention rule.

  • argtype

    Specifies the type of a parameter.

    Value range: A valid data type.

  • plsql_body

    Indicates 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. Therefore, you are advised not to perform such operations in the stored procedure.

NOTE:

No specific order is applied to argument_name and argmode. The following order is advised: argument_name, argmode, and argument_type.

Examples

-- Create a stored procedure:
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:
SELECT prc_add(2,3);

-- Create a stored procedure whose parameter type is VARIADIC:
CREATE OR REPLACE PROCEDURE pro_variadic (var1 VARCHAR2(10) DEFAULT 'hello!',var4 VARIADIC int4[])
AS
BEGIN
    dbms_output.put_line(var1);
END;
/

-- Execute the stored procedure:
SELECT pro_variadic(var1=>'hello', VARIADIC var4=> array[1,2,3,4]);

-- Delete the stored procedure:
DROP PROCEDURE prc_add;
DROP PROCEDURE pro_variadic;

Helpful Links

DROP PROCEDURE