Stored Procedures

A stored procedure is a set of SQL statements that can implement a specific function. You can repeatedly call the stored procedure to reduce the number of repeated SQL statements and improve work efficiency.

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;