預存程序 - PolarDB

本文介紹了預存程序的原理和使用方法。

功能介紹

預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，您可以通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。

原理

456789

預存程序會被持久化到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用於控制每個預存程序所能使用的最大記憶體。