Procedures are standalone SPL programs that are called as an individual SPL program statement. When called, stored procedures can receive values from the caller in the form of input parameters and return values to the caller in the form of output parameters.

Create a stored procedure

The CREATE PROCEDURE command defines and names a standalone procedure that will be stored in the database.
CREATE [ OR REPLACE ] PROCEDURE name [ (parameters) ] [ AUTHID { DEFINER | CURRENT_USER } ] { IS | AS }
[ declarations ] BEGIN
statements END [ name ];                

name is the identifier of the stored procedure. If you specify the [OR REPLACE] clause and a procedure with the same name already exists in the schema, the new procedure will overwrite the existing one. If you do not specify the [OR REPLACE] clause, the new procedure will not overwrite the existing procedure with the same name in the same schema. parameters is a list of formal parameters. If the AUTHID clause is omitted or if AUTHID DEFINER is specified, the rights of the stored procedure owner are used to determine access privileges to database objects. In addition, the search path of the procedure owner is used to resolve unqualified object references. If the CURRENT_USER clause is specified, the rights of the current user who call the stored procedure are used to determine access privileges. In addition, the search path of the current user is used to resolve unqualified object references. declarations are variable, cursor, or type declarations. statements are SPL program statements. The BEGIN - END block can contain an EXCEPTION section.

The following example shows a simple stored procedure that does not require any parameters.
CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('That''s all folks!') ;
END simple_procedure;

As shown in the preceding example, you can store the procedure in the database by entering the procedure code in PolarDB.

Call a stored procedure

To call a stored procedure from another SPL program, you can specify the stored procedure name followed by parameters and a semicolon.
name [ ([ parameters ]) ];
name is the identifier of the stored procedure. parameters is a list of actual parameters.
Note
  • If no actual parameters are specified, the stored procedure can be called with an empty parameter list, or the opening and closing parenthesis can be omitted.
  • The syntax for calling a stored procedure is the same as that in the preceding syntax diagram when executing it with the EXEC command in psql or PolarDB.
The following example shows how to call the stored procedure from an anonymous block.
BEGIN
    simple_procedure;
END;

That's all folks!
Note Each application has its own unique method to call a stored procedure. For example, in a Java application, the application programming interface, JDBC, is used.

Delete a stored procedure

You can run the DROP PROCEDURE command to delete a procedure from the database.
DROP PROCEDURE name;

name is the name of the stored procedure to be deleted.

The following example shows how to run the DROP PROCEDURE command to delete a procedure.
DROP PROCEDURE simple_procedure;