存储过程与函数 - 创建存储过程 - 《OceanBase v2.2.77 官方教程》

调用存储过程
子程序属性
自治事务

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

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;

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

CREATE table loghistory 
(userid VARCHAR2(20), 
logdate date default sysdate);
CREATE OR REPLACE PROCEDURE userlogin 
    IS
BEGIN
    INSERT INTO loghistory (userid) VALUES (USER);
END;

调用存储过程

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

obclient> select * from loghistory;
Empty set (0.02 sec)
obclient> BEGIN
    -> userlogin;
    -> END;
    -> /
Query OK, 0 rows affected (0.03 sec)
obclient> select * from loghistory;
+--------+-----------+
| USERID | LOGDATE   |
+--------+-----------+
| HR     | 27-SEP-20 |
+--------+-----------+
1 row in set (0.00 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)

子程序属性

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

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

AUTHID 属性
ACCESSIBLE BY 子句

AUTHID

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

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

如下示例为，用 HR 用户创建过程 userlogin，指定 AUTHID DEFINER。

CREATE OR REPLACE PROCEDURE userlogin 
    AUTHID DEFINER
    IS
BEGIN
    INSERT INTO loghistory (userid) VALUES (USER);
END;

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 (0.03 sec)

SCOTT 用户对 hr.loghistory 并没有访问权限，但是可以执行过程 userlogin。

obclient> select user from dual;
+-------+
| USER  |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
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 (0.02 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)

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

obclient> select user from dual;
+------+
| USER |
+------+
| HR   |
+------+
1 row in set (0.00 sec)
obclient> select * from hr.loghistory;
+--------+-----------+
| USERID | LOGDATE   |
+--------+-----------+
| HR     | 27-SEP-20 |
| SCOTT  | 27-SEP-20 |
+--------+-----------+
2 rows in set (0.01 sec)

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

CREATE OR REPLACE PROCEDURE userlogin 
    AUTHID CURRENT_USER
    IS
BEGIN
    INSERT INTO loghistory (userid) VALUES (USER);
END;

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

obclient> select user from dual;
+-------+
| USER  |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
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 (0.00 sec)
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 (0.00 sec)
obclient> CREATE table loghistory
    -> (userid VARCHAR2(20),
    -> logdate date default sysdate);
Query OK, 0 rows affected (0.06 sec)
obclient> BEGIN
    -> hr.userlogin;
    -> END;
    -> /
Query OK, 0 rows affected (0.02 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient> select * from loghistory;
+--------+-----------+
| USERID | LOGDATE   |
+--------+-----------+
| SCOTT  | 27-SEP-20 |
+--------+-----------+
1 row in set (0.01 sec)

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 (0.00 sec)
obclient> SELECT * FROM demotable;
Empty set (0.01 sec)
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 (0.05 sec)
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 (0.00 sec)

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

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

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
    AS
BEGIN
    INSERT INTO logtable VALUES ( user, sysdate, p_message );
    COMMIT;
END log_message;

再次执行上述过程：

obclient> SELECT * FROM logtable;
Empty set (0.01 sec)
obclient> SELECT * FROM demotable;
Empty set (0.01 sec)
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 (0.04 sec)
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 (0.00 sec)
obclient> SELECT * FROM demotable;
+------+
| N    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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