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
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.
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
name [ ([ parameters ]) ];
- 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.
BEGIN
simple_procedure;
END;
That's all folks!
Delete a stored procedure
DROP PROCEDURE name;
name is the name of the stored procedure to be deleted.
DROP PROCEDURE simple_procedure;