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.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Syntax for creating a stored procedure:
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;
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> 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.
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
Subprogram properties
Each subprogram property can appear only once in the subprogram declaration. The properties can appear in any order. Properties appear before the IS or AS keyword in the subprogram. The properties cannot appear in nested subprograms.
Only the ACCESSIBLE BY property can appear in program packages. Standalone subprograms may have the following properties in their declarations.
AUTHIDACCESSIBLE BY
AUTHID
While you define a stored procedure, you can define the AUTHID clause to determine whether the stored procedure runs with the privileges of its owner or the current session user. Privileges are divided into the following two types:
AUTHID DEFINER: The definer privileges. By default, the privileges of the stored procedure come from its owner.AUTHID CURRENT_USER: The caller privileges. The stored procedure runs with the privileges of the current session user, which may be different from the current logged-on user. You can change the caller schema by usingALTER SESSION SET CURRENT_SCHEMA.
Example: Create a stored procedure userlogin as the HR user and specify AUTHID DEFINER.
obclient> CREATE OR REPLACE PROCEDURE userlogin
AUTHID DEFINER
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
The HR user assigns the execution privilege on userlogin to public users so that the scott user can execute the stored procedure.
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
+--------+-----------+
obclient>GRANT EXECUTE ON userlogin TO PUBLIC;
Query OK, 0 rows affected
The scott user does not have the privilege to access the hr.loghistory table but still can execute the stored procedure userlogin.
obclient> SELECT USER FROM DUAL;
+-------+
| USER |
+-------+
| SCOTT |
+-------+
1 row in set
obclient> SELECT * FROM loghistory;
ORA-00942: table or view 'SCOTT.LOGHISTORY' does not exist
obclient> SELECT * FROM hr.loghistory;
ORA-00942: table or view does not exist
obclient> BEGIN
hr.userlogin;
END;
/
Query OK, 0 rows affected
obclient> COMMIT;
Query OK, 0 rows affected
The scott user has inserted data into the hr.loghistory table by executing the stored procedure userlogin created by the HR user. This indicates that the scott user has the privileges of the HR user during the execution of this stored procedure.
obclient> SELECT USER FROM DUAL;
+------+
| USER |
+------+
| HR |
+------+
1 row in set
obclient> SELECT * FROM hr.loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
| SCOTT | 27-SEP-20 |
+--------+-----------+
2 rows in set
About the AUTHID CURRENT_USER clause: Use the AUTHID CURRENT_USER clause as the HR user to redefine the stored procedure userlogin.
obclient> CREATE OR REPLACE PROCEDURE userlogin
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
An error occurs when the scott user executes the stored procedure userlogin again, because the scott user does not have the privilege to access the hr.loghistory table. Creating the loghistory table as the scott user succeeded, but the data is inserted into the scott.loghistory table.
obclient> SELECT USER FROM DUAL;
+-------+
| USER |
+-------+
| SCOTT |
+-------+
1 row in set
obclient> BEGIN
hr.userlogin;
END;
/
ORA-00942: table or view 'SCOTT.LOGHISTORY' does not exist
obclient>ALTER SESSION SET current_schema=hr;
Query OK, 0 rows affected
obclient> BEGIN
hr.userlogin;
END;
/
ORA-00942: table or view does not exist
obclient>ALTER SESSION SET current_schema=scott;
Query OK, 0 rows affected
obclient> CREATE TABLE loghistory
(userid VARCHAR2(20),
logdate date default sysdate);
Query OK, 0 rows affected
obclient> BEGIN
hr.userlogin;
END;
/
Query OK, 0 rows affected
obclient> COMMIT;
Query OK, 0 rows affected
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| SCOTT | 27-SEP-20 |
+--------+-----------+
1 row in set
ACCESSIBLE BY
ACCESSIBLE BY can constrain the caller of an object.
The accessor list explicitly lists units that may have access. The accessor list can be defined on a subprogram package. The program package will check the accessor list (if any) it defines. To prevent unnecessary calls to internal subprograms, the accessor list may limit only access to subprograms and cannot extend the access. For example, a program package cannot be reorganized into two program packages, with one used for limiting access to a few users and the other used for public access.
The ACCESSIBLE BY clause may appear in the declarations of object types, object type bodies, packages, and subprograms.
The ACCESSIBLE BY clause can appear in the following SQL statements:
ALTER TYPECREATE FUNCTIONCREATE PROCEDURECREATE PACKAGECREATE TYPECREATE TYPE BODY
Syntax:
unit_kind:
FUNCTION { $$[0] = SP_FUNCTION; }
| PROCEDURE { $$[0] = SP_PROCEDURE; }
| PACKAGE_P { $$[0] = SP_PACKAGE; }
| TRIGGER { $$[0] = SP_TRIGGER; }
| TYPE { $$[0] = SP_TYPE; }
;
accessor:
pl_schema_name
{
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSOR, 2, NULL, $1);
}
| unit_kind pl_schema_name
{
ParseNode *accessor_kind = NULL;
malloc_terminal_node(accessor_kind, parse_ctx->mem_pool_, T_SP_ACCESSOR_KIND);
accessor_kind->value_ = $1[0];
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSOR, 2, accessor_kind, $2);
}
;
accessor_list:
accessor_list ',' accessor
{
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_LINK_NODE, 2, $1, $3);
}
| accessor
{
$$ = $1;
}
;
accessible_by:
ACCESSIBLE BY '(' accessor_list ')'
{
ParseNode *accessor_list = NULL;
merge_nodes(accessor_list, parse_ctx->mem_pool_, T_SP_ACCESSOR_LIST, $4);
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSIBLE_BY, 1, accessor_list);
}
;
Autonomous transactions
OceanBase Database supports autonomous transactions. Autonomous transactions are fully independent of the main transaction. Committing or rolling back an autonomous transaction does not affect the main transaction.
Create an object based on the following example:
-- Create a log table.
CREATE TABLE logtable(
username VARCHAR2(20),
date_time DATE,
message VARCHAR2(60)
);
-- Create a temporary table.
CREATE TABLE demotable( N number);
-- Create a procedure that owns autonomous transactions.
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO logtable VALUES ( user, sysdate, p_message );
COMMIT;
END log_message;
Then, perform the following steps:
Call
log_messageto write a log.Insert data into the
demotabletable during the transaction.Call
log_messageagain to write another log.Roll back the current transaction.
obclient> SELECT * FROM logtable;
Empty set
obclient> SELECT * FROM demotable;
Empty set
obclient> BEGIN
Log_message ('About to insert into demotable.');
INSERT INTO demotable VALUES (1);
Log_message ('Rollback the transaction.');
ROLLBACK;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM logtable;
+----------+-----------+---------------------------------+
| USERNAME | DATE_TIME | MESSAGE |
+----------+-----------+---------------------------------+
| HR | 28-SEP-20 | About to insert into demotable.
| HR | 28-SEP-20 | Rollback the transaction.
+----------+-----------+---------------------------------+
2 rows in set
In the preceding example, the INSERT operation on the demotable table was rolled back. The stored procedure log_message that has autonomous transactions has committed only its own transactions and written data into the log table.
Re-create the stored procedure log_message, remove the autonomous transaction property, and view the database behavior.
obclient> CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
BEGIN
INSERT INTO logtable VALUES ( user, sysdate, p_message );
COMMIT;
END log_message;
/
Query OK, 0 rows affected
Execute the preceding stored procedure again:
obclient> SELECT * FROM logtable;
Empty set
obclient> SELECT * FROM demotable;
Empty set
obclient> BEGIN
Log_message ('About to insert into demotable.');
INSERT INTO demotable VALUES (1);
Log_message ('Rollback the transaction.');
ROLLBACK;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM logtable;
+----------+-----------+---------------------------------+
| USERNAME | DATE_TIME | MESSAGE |
+----------+-----------+---------------------------------+
| HR | 28-SEP-20 | About to insert into demotable.
| HR | 28-SEP-20 | Rollback the transaction.
+----------+-----------+---------------------------------+
2 rows in set
obclient>SELECT * FROM demotable;
+------+
| N |
+------+
| 1 |
+------+
1 row in set
Compare the two execution processes. In the second execution, transactions on demotable were not rolled back. If autonomous transactions are not used, all modifications are made in the same transaction. When the stored procedure log_message is executed for the second time, the COMMIT statement commits the entire transaction, including INSERT INTO demotable VALUES (1).