Create a stored procedure

Last Updated：2025-11-27 02:38:06 Updated

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 the 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 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 OceanBase Client (OBClient), OceanBase Developer Center (ODC), or a third-party development tool.

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 the subprogram declaration. The attributes can appear in any order. Attributes appear before the IS or AS keyword in the subprogram. The attributes cannot appear in nested subprograms.

Only the ACCESSIBLE BY attribute can appear in program packages. Standalone subprograms may have the following attributes in their declarations:

AUTHID attribute
ACCESSIBLE BY clause

AUTHID

When 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 using ALTER SESSION SET CURRENT_SCHEMA.

Example: Create a stored procedure named 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 the stored procedure 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;
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 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

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;
     /
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

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 access from 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 TYPE
CREATE FUNCTION
CREATE PROCEDURE
CREATE PACKAGE
CREATE TYPE
CREATE TYPE BODY

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. 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_message to write a log.
Insert data into the demotable table during the transaction.
Call log_message again to write another log.
Rolls 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).