A stored procedure is a pre-compiled collection of SQL statements and optional control flow statements and is processed as a unit by the procedural language (PL) engine. A stored procedure can reference other stored procedures and return multiple variables.
Structure of a stored procedure
The following sample code shows the structure of a stored procedure:
PROCEDURE name [ ( parameter_list ) ]
{ IS | AS }
[ declarative_part ]
BEGIN -- Start execution
statement; [ statement; ]...
[ EXCEPTION ]
exception_handler; [ exception_handler; ]... ]
END;
Create a stored procedure
You can use the CREATE PROCEDURE statement to create a stored procedure. The syntax for creating a stored procedure is as follows:
CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
delarification_block
BEGIN
procedure_body
EXCEPTION
exception_handler
END;
Here is an example for creating a stored procedure that has no parameters:
obclient> CREATE TABLE loghistory
(userid VARCHAR2(20),
logdate DATE DEFAULT SYSDATE);
Query OK, 0 rows affected
obclient> DELIMITER /
obclient> CREATE OR REPLACE PROCEDURE userlogin
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
Call a stored procedure
After a stored procedure is created, an authorized user can call and run the procedure from OBClient, OceanBase Developer Center (ODC), or a third-party development tool.
Here is an example:
obclient> SELECT * FROM loghistory;
Empty set
obclient> BEGIN
userlogin;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
+--------+-----------+
1 row in set
obclient> COMMIT;
Query OK, 0 rows affected