存储过程的原理和使用方法_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了存储过程的原理和使用方法。

功能介绍

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，您可以通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

原理

存储过程会被持久化到Meta center中，按需加载到计算节点中执行，SQL相关的执行逻辑会发送到SQL engine中执行，然后获取执行结果，存储过程的控制流程等相关的逻辑会在PL engine中执行。

存储过程在真正执行前会注册到存储过程管理中心，同时整个执行过程中存储过程所占用的内存大小会被严格限制。

注意事项

仅5.4.15及以上版本支持此功能。
不支持condition handling。

只支持一种异常处理（当fetch cursor时没有更多数据时），且格式固定，declare not found for cursor begin {iterate label | leave label| set statement}; end表示当没有更多数据时，重新开始一个循环，跳出一个循环或者set某个变量，示例如下：

CREATE PROCEDURE pro_test()
  BEGIN
  DECLARE a CHAR(16);
  DECLARE b, c int;
  DECLARE cur1 CURSOR FOR SELECT data, id FROM t1 order by id;
  DECLARE cur2 CURSOR FOR SELECT id FROM t2 order by id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND begin LEAVE read_loop; end;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF b < c THEN
    INSERT INTO t3 VALUES (b, a);
    ELSE
    INSERT INTO t3 VALUES (c, a);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
  END;|

存储过程不支持权限管理。

语法

创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }
　  PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

调用存储过程

CALL PROCEDURE_NAME(params list);

删除存储过程

DROP PROCEDURE [IF EXISTS] PROCEDURE_NAME;

查看所有已定义的存储过程

SELECT * FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE';

查看某个特定的存储过程

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]

```
SHOW CREATE PROCEDURE 存储过程名;
```

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = '存储过程名';

查看正在执行的存储过程

show processlist where info like 'call%';

返回信息示例如下：

+------+---------------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+
| Id   | User          | Host            | db   | Command | Time | State | Info                                         | TraceId          |
+------+---------------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+
|  710 | test                     | 127.0.0.1:53951 | test | Query   |   79 |       | CALL test.test_procedure: executing pl logic | 14c93b7c7bf00000 |
+------+---------------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+

说明

show processlist的info中以call schema.procedure_name开始的，即表示该connection正在执行存储过程。若此时正在执行存储过程中的SQL，则会显示正在执行的SQL，否则会显示execute pl logic。

取消正在执行的存储过程

kill {query | connection} connection_id;

存储过程缓存管理

存储过程的所有元信息，即是否存在某存储过程，始终会存在于缓存中，但具体的存储过程内容仅会在需要时被加载至缓存。

说明

以下示例中size列的单位为byte。

查看缓存

show procedure cache;

返回信息示例如下：

+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 10.215.21.156:9090 | test                    | bug6129   | 46   |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test  | 110  |
+--------------------+-------------------------+-----------+------+

设置缓存大小

resize procedure cache num;

示例如下：

resize procedure cache 150;
Query OK, 0 rows affected (0.10 sec)

how procedure cache;
+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 10.215.21.156:9090 | test                    | bug6129   | 46   |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test  | 0    |
+--------------------+-------------------------+-----------+------+
2 rows in set (0.01 sec)

## pro_test所占用的内存空间被释放掉了，以便整体占用大小不超过150

清空缓存

clear procedure cache;

示例如下：

clear procedure cache;
Query OK, 0 rows affected (0.08 sec)

show procedure cache;
+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 30.225.XX.XX:9090 | test                    | bug6129   | 0    |
| 30.225.XX.XX:9090 | drds_polarx1_qatest_app | pro_test  | 0    |
+--------------------+-------------------------+-----------+------+
2 rows in set (0.00 sec)

## 所有存储过程占用的空间都已被释放

重新加载存储过程

reload procedures;

存储过程内存管理

存储过程执行过程中的内存占用主要为缓存的cursor，因此PolarDB-X对单个cursor所能使用的最大内存以及整个存储过程在执行时占用的内存进行了限制，由参数PL_CURSOR_MEMORY_LIMIT和PL_MEMORY_LIMIT进行控制。

其中，参数PL_CURSOR_MEMORY_LIMIT用于控制每个Cursor所占用的内存，超过该阈值时会溢出到硬盘中，参数PL_MEMORY_LIMIT用于控制每个存储过程所能使用的最大内存。