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 } 
        BRGIN
          procedure_body
        END
    /
    
  • Call a stored procedure.

    CALL procedure_name ( param_expr );
    
  • Delete a stored procedure.

    DROP PROCEDURE procedure_name ;
    

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.

  • BRGIN, 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;
Feedback
编组 3备份
    openGauss 2024-04-22 00:47:24
    cancel