A stored procedure is a precompiled collection of SQL statements and optional control-flow statements. PL processes 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;
Here is an example of a stored procedure without 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 define the AUTHID clause when you define a stored procedure to specify the user to which the procedure grants permissions during runtime. The permission types are as follows:
AUTHID DEFINER(DEFINER privilege): By default, the stored procedure inherits the privileges of the owner.AUTHID CURRENT_USER(CURRENT_USER privilege): The stored procedure grants privileges to the current session user during runtime. 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 user and specify the AUTHID DEFINER clause.
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 permission of the userlogin procedure to other users. The scott user can then execute the userlogin 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 permission to the hr.loghistory table. However, it can execute the userlogin procedure.
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 successfully inserts data into the hr.loghistory table by running the userlogin procedure created by the HR user. This indicates that the scott user has the privileges of the HR user when running the userlogin 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. The HR user redefines the userlogin 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 again executes the userlogin procedure and receives an error because it does not have the access permission to the hr.loghistory table. If the scott user creates a table named loghistory, it can execute the userlogin procedure successfully. The data is actually 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
The ACCESSIBLE BY clause can restrict the callers of an object.
The access list explicitly lists the units that may have access permissions. 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 for restricting access and another for allowing access.
The ACCESSIBLE BY clause can be used in the declaration of an object type, object type body, package, or subprogram.
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 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 that has 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:
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 stored procedure log_message that has an autonomous transaction only 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 an autonomous transaction, all modifications are performed in the same transaction. In the second call to the log_message procedure, the COMMIT statement in the procedure commits the entire transaction, including the INSERT INTO demotable VALUES (1) statement.