创建存储过程 - OceanBase 数据库 V5.0.1

创建存储过程

更新时间：2026-07-29 10:39:51

存储过程是 SQL 语句和可选控制流语句的预编译集合，PL 将其作为一个单元进行处理。一个存储过程可以引用其它存储过程，并且可以返回多个变量。

功能适用性

该内容仅适用于 OceanBase 数据库企业版。OceanBase 数据库社区版暂不支持。

创建存储过程的语法如下：

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;

无参数的存储过程示例如下：

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

调用存储过程

存储过程建立完成后，用户通过授权可以在 OBClient 、OceanBase Developer Center 或第三方开发工具中来调用运行。

示例如下：

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

子程序属性

每个子程序属性只能在子程序声明中出现一次，并可以按任何顺序出现。属性位于子程序的 IS 或 AS 关键字之前。该属性不能出现在嵌套子程序中。

程序包中只能出现 ACCESSIBLE BY 属性。独立子程序在其声明中可能具有以下属性。

AUTHID 属性
ACCESSIBLE BY 子句

AUTHID

定义存储过程时可以定义 AUTHID 子句，来获取存储过程在运行时被授予哪个用户的权限。权限类型包括以下两种：

AUTHID DEFINER（定义者权限）：默认情况下，存储过程的权限来自于所有者。
AUTHID CURRENT_USER（调用者权限）：运行时被授予当前会话用户的权限，这可能和当前登录用户相同或不同（ALTER SESSION SET CURRENT_SCHEMA 可以改变调用者 Schema）

示例：使用 HR 用户创建存储过程 userlogin，指定 AUTHID DEFINER。

obclient> CREATE OR REPLACE PROCEDURE userlogin
    AUTHID DEFINER
    IS
BEGIN
    INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected

HR 用户把 userlogin 的执行权限授予给其他用户，这样 scott 用户可以执行存储过程。

obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE   |
+--------+-----------+
| HR     | 27-SEP-20 |
+--------+-----------+
obclient>GRANT EXECUTE ON userlogin TO PUBLIC;
Query OK, 0 rows affected

scott 用户对 hr.loghistory 并没有访问权限，但是可以执行存储过程 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

scott 用户通过运行 HR 用户创建的存储过程 userlogin 成功插入 hr.loghistory，说明 scott 用户在运行这个存储过程时，拥有的是 HR 的权限。

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

关于 AUTHID CURRENT_USER 子句。HR 用户使用 AUTHID CURRENT_USER 子句重新定义存储过程 userlogin。

obclient> CREATE OR REPLACE PROCEDURE userlogin
     AUTHID CURRENT_USER
     IS
 BEGIN
     INSERT INTO loghistory (userid) VALUES (USER);
 END;
 /
Query OK, 0 rows affected

scott 用户再次执行 userlogin 报错，因为 scott 没有访问 hr.loghistory 这张表的权限。用 scott 用户创建表 loghistory，可以执行成功，数据实际插入了 scott.loghistory。

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 可以约束一个对象的调用者。

访问者列表明确列出了可能具有访问权限的单元。访问者列表可以在子程序包上定义。程序包会对其定义的访问者列表（如果有）进行检查。该列表可能仅限制对子程序的访问，而不能扩展访问，以防止非必要的使用内部子程序。例如，不支持将一个程序包重组为两个程序包：一个程序用于限制少数访问，另一个程序用于公开访问。

ACCESSIBLE BY 子句可用于对象类型、对象类型主体、包和子程序的声明中。

ACCESSIBLE BY 子句可以出现在以下 SQL 语句中：

ALTER TYPE 陈述式
CREATE FUNCTION 语句
创建过程语句
创建包声明
CREATE TYPE 语句
CREATE TYPE BODY 语句

语法如下：

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);
  }
;

自治事务

OceanBase 数据库支持自治事务。自治事务独立于父事务存在，单独进行提交或者回滚而不会对主事务产生影响。

根据如下示例创建对象：

-- 创建日志表
CREATE TABLE logtable(
    username VARCHAR2(20),
    date_time DATE,
    message VARCHAR2(60)
);
-- 创建临时表
CREATE TABLE demotable( N number);

-- 创建拥有自制事务的过程
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;

接下来的操作步骤如下：

调用 log_message 写日志。
在事务中向 demotable 中插入数据。
调用 log_message 再次写一条日志。
回滚当前事务。

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

上述示例中，对 demotable 的插入操作被回滚了，拥有自治事务的存储过程 log_message 只提交了自己的事务，并向日志表里写入数据。

重新创建存储过程 log_message，去除自治事务属性，查看数据库的行为。

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

再次执行上述存储过程：

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

对比两次执行过程，第二次执行中 demotable 上的事务并没有被回滚。如果不使用自治事务，所有修改都在同一个事务中进行，第二次调用 log_message 过程时，其中的 commit 语句会把整个事务提交，包括 INSERT INTO demotable VALUES (1)。