Create a stored procedure|V4.4.2| docs|Distributed Database

Create a stored procedure

Last Updated：2026-03-06 07:02:41 Updated

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements. PL processes 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 provides only MySQL-compatible 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 how to create 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 third-party development tools 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 a 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.

AUTHID attribute
ACCESSIBLE BY clause

AUTHID

You can define the AUTHID clause when you define a stored procedure to specify which user's privileges are granted to the stored procedure during its execution. The privilege types are as follows:

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 the current session user are granted to the stored procedure during its execution. The current session user may be the same as or different from the logged-in user (you can use the ALTER SESSION SET CURRENT_SCHEMA statement to change the schema of the invoker).

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 privilege of the userlogin stored procedure to other users. Therefore, 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 the stored procedure is executed.

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 that the privileges of the current session user are granted to the stored procedure during its execution. 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 executes the userlogin stored procedure again and receives an error. This is because the SCOTT user does not have the access privilege to the hr.loghistory table. If the SCOTT user creates the loghistory table, 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 specifies the callers of an object.

The access list specifies the units that may have access privileges. You can define the access list on a subprogram package. A package checks the access list defined on it, if any. The access 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 for restricting access and the other for allowing 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 TYPE statement
CREATE FUNCTION statement
CREATE PROCEDURE statement
CREATE PACKAGE statement
CREATE TYPE statement
CREATE TYPE BODY statement

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.

The following example shows how to create an object:

-- 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 required:

Call the log_message procedure to write a log.
Insert data into the demotable table in a transaction.
Call the log_message procedure 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 autonomous transactions, 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.