Stored Procedure ​

In openGauss, business rules and logics are saved as stored procedures.

A stored procedure is a combination of SQL and PL/SQL. Stored procedures can move the code that executes business rules from applications to databases. Therefore, the code storage can be used by multiple programs at a time.

For details about how to create and call a stored procedure, see CREATE PROCEDURE.

The application methods for PL/pgSQL functions mentioned in PL/pgSQL Functions are similar to those for stored procedures. Unless otherwise specified, the following sections apply to stored procedures and PL/pgSQL functions.

Syntax ​

• Create a stored procedure.

  CREATE PROCEDURE procedure_name
      [ ( {[ argname ] [ argmode ] argtype [ = expression ]}[,...]) ]
      { IS | AS } 
      BEGIN
        procedure_body
      END
  /

• Call a stored procedure.

  CALL procedure_name ( param_expr );

• Delete a stored procedure.

  DROP PROCEDURE [ IF EXISTS ] procedure_name 
  [ ( [ {[ argname ] [ argmode ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ];

Parameter Description ​

• procedure_name

  Specifies the name of the stored procedure to be created.

• argname

  Specifies the parameter name.

• argmode

  Specifies the mode of a parameter. Value range: IN, OUT, INOUT, and VARIADIC. VARIADIC specifies parameters of array type. The default value is IN.

  • IN

    Specifies an input parameter. The value of the parameter must be specified when the stored procedure is called. If the value of the parameter is changed in the stored procedure, the value cannot be returned.

  • OUT

    Specifies an output parameter. The value can be changed in the stored procedure and can be returned.

  • INOUT

    Specifies input and output parameters. The value can be specified when the stored procedure is called and can be changed and returned.

• argtype

  Specifies the data type of the parameter.

• expression

  Sets the default value.

• IS, AS

  Required for the syntax. One of them must be provided. They have the same function.

• BEGIN, END

  Required for the syntax.

• procedure_body

  Specifies the stored procedure content.

• param_expr

  Specifies the parameter list. Use commas (,) to separate parameters. Use := or => to separate parameter names and parameter values.

Examples ​

-- Create a table
openGauss=# CREATE TABLE graderecord  
(  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
);

-- Define a stored procedure.
openGauss=# CREATE PROCEDURE insert_data  (param1 INT = 0, param2 CHAR(20),param3 CHAR(20),param4 INT = 0 ) 
IS
 BEGIN 
 INSERT INTO graderecord VALUES(param1,param2,param3,param4);  
END;
/


-- Call the stored procedure.
openGauss=# CALL  insert_data(param1:=210101,param2:='Alan',param3:='21.01',param4:=92);

-- Delete the stored procedure.
openGauss=# DROP PROCEDURE insert_data;