A stored procedure is a precompiled collection of SQL statements and optional control flow statements. PL treats it as a unit. A stored procedure can reference other stored procedures and can return multiple variables.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
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;
The following example shows 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 with the appropriate 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 stored procedure at runtime. The following two privilege types are supported:
AUTHID DEFINER(definer's rights): By default, the privileges of a stored procedure are inherited from its owner.AUTHID CURRENT_USER(invoker's rights): The privileges of a stored procedure are inherited from the current session user at runtime. The current session user may be the same as or different from the logged-in 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 with the AUTHID DEFINER attribute as the HR user.
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. The SCOTT user can then 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 on 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 successfully inserts data into the hr.loghistory table by running the userlogin stored procedure created by the HR user. 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 specifies the privileges inherited by a stored procedure at runtime. The HR user redefines the userlogin stored procedure with 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 attempts to execute the userlogin stored procedure again but receives an error because the SCOTT user does not have the access privilege on the hr.loghistory table. If the SCOTT user creates a table named loghistory, the userlogin stored procedure can be executed successfully, and the data will be 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 specifies 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 it is defined. The access list can 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 another 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 PACKAGEstatementCREATE 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:
-- 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;
```
Here are the steps:
1. Call the `log_message` procedure to write a log.
2. Insert data into the `demotable` table in a transaction.
3. Call the `log_message` procedure again to write another log.
4. Roll back the current transaction.
```javascript
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 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 autonomous transactions, all modifications are performed in the same transaction. When you call the log_message procedure again, the COMMIT statement in the procedure will commit the entire transaction, including the INSERT INTO demotable VALUES (1) statement.