A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements. PL treats a stored procedure as a unit. 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 does not support this feature.
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 of a stored procedure with 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, you can call it in OBClient, OceanBase Developer Center, or a third-party development tool by granting the corresponding privileges.
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
Subprogram attributes
Each subprogram attribute can appear only once in a subprogram declaration and can appear in any order. The attribute appears before the IS or AS keyword of the subprogram. The attribute cannot appear in a nested subprogram.
The ACCESSIBLE BY attribute can appear only in a package. The following attributes can appear in the declaration of an independent subprogram.
AUTHIDattributeACCESSIBLE BYclause
AUTHID
You can specify the AUTHID clause when you define a stored procedure to specify the privileges granted to the user who runs the stored procedure. The privilege types are as follows:
AUTHID DEFINER: The default privilege type. In this case, the stored procedure inherits the privileges of the owner.AUTHID CURRENT_USER: The privileges of the current session user are granted to the stored procedure. The current session user may or may not be the same as the login user. You can use theALTER SESSION SET CURRENT_SCHEMAstatement to change the schema of the current session user.
Example: Create a stored procedure named userlogin in the HR schema and specify the AUTHID DEFINER attribute.
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 grants the execution privilege of the userlogin stored procedure to other users. Then, 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 access privilege to the hr.loghistory table, but can execute the userlogin stored procedure.
obclient> SELECT USER FROM DUAL;
+-------+
| USER |
+-------+
| SCOTT |
+-------+
1 row in set
obclient> SELECT * FROM loghistory;
OBE-00942: table or view 'SCOTT.LOGHISTORY' does not exist
obclient> SELECT * FROM hr.loghistory;
OBE-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 runs the userlogin stored procedure created by the HR user and successfully inserts data into the hr.loghistory table. This indicates that the scott user has the privileges of the HR user when running the 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
The AUTHID CURRENT_USER clause is used to specify the privileges of the current session user. The HR user redefines the userlogin stored procedure by using the AUTHID CURRENT_USER clause.
obclient> CREATE OR REPLACE PROCEDURE userlogin
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
The scott user executes the userlogin stored procedure again. The execution fails because the scott user does not have the access privilege to the hr.loghistory table. If the scott user creates a table named loghistory, the execution succeeds and 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;
/
OBE-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;
/
OBE-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
The ACCESSIBLE BY clause can be used to restrict the callers of an object.
The access list explicitly lists the units that may have access privileges. The access list can be defined on a subprogram package. The package checks the access list, if defined. This list may restrict access to subprograms but cannot expand access to prevent unnecessary use of internal subprograms. For example, you cannot split a package into two packages: one to restrict access and the other to provide public access.
The ACCESSIBLE BY clause can be used in the declarations of object types, object type bodies, packages, and subprograms.
The ACCESSIBLE BY clause can appear in the following SQL statements:
ALTER TYPEstatementCREATE FUNCTIONstatementCREATE PROCEDUREstatementCREATE PACKAGEdeclarationCREATE TYPEstatementCREATE TYPE BODYstatement
The syntax is as follows:
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. An autonomous transaction is independent of its parent transaction and can be committed or rolled back without affecting the parent transaction.
Here is an example of creating an autonomous transaction:
-- 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 stored procedure with an autonomous transaction.
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;
The following steps are performed:
Call the
log_messageprocedure to write a log.Insert data into the
demotabletable in a transaction.Call the
log_messageprocedure again 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 insertion into the demotable table is rolled back. The autonomous transaction stored procedure log_message commits only its own transaction and writes data to the log table.
Recreate the log_message stored procedure without the autonomous transaction attribute and observe the 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 log_message 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
In the second execution, the transaction on the demotable table is not rolled back. If you do not use an autonomous transaction, all modifications are performed in the same transaction. In the second call to the log_message procedure, the COMMIT statement commits the entire transaction, including the INSERT INTO demotable VALUES (1) statement.