PolarDB:Stored procedures

Overview

A stored procedure is a set of SQL statements. You can execute a stored procedure by specifying the name and parameters of the stored procedure.

How it works

A stored procedure is persistently stored in the Meta Center, and can be loaded to compute nodes as needed. The SQL statements are sent to SQL engine for execution and the execution result is returned. The control flow and other logics of a stored procedure are executed in the procedural language (PL) engine.

Before execution, the stored procedure is registered to the runtime procedure manager, and the memory consumed by the stored procedure is limited.

Usage notes

• This feature is supported in PolarDB-X V5.4.15 and later.
• Condition handling is not supported.
• Only one exception handler that has a fixed format is supported. The handler can be defined as declare not found for cursor begin {iterate label | leave label| set statement}; end. This indicates that when no more data is available when a cursor is used to fetch data, the loop restarts or ends or a specific variable is set. The following statement shows you how to use this handler:

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

• Permissions management is not supported for stored procedures.

Syntax

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 procedure name;

• SELECT * FROM information_schema. Routines WHERE ROUTINE_NAME ='procedure name';

show processlist where info like 'call%';

Sample result:

+------+---------------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+
| 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 |
+------+---------------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+

kill {query | connection} connection_id;

Manage the cache of stored procedures

All metadata of a stored procedure, which indicates whether the procedure exists, is stored in the cache. The specific content of the stored procedure is only loaded to the cache when needed.

show procedure cache;

Sample result:

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

Example:

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)

## The memory consumed by pro_test is released so that the overall size does not exceed 150 bytes.

clear procedure cache;

Example:

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)

## All the memory consumed by the stored procedures has been released.

reload procedures;

Manage the memory usage of stored procedures

The memory is mainly occupied by cached cursors during the execution of a stored procedure. Therefore, PolarDB-X sets the maximum memory that a single cursor and the entire stored procedure can occupy during execution. Such limits are set by using parameters PL_CURSOR_MEMORY_LIMIT and PL_MEMORY_LIMIT.

PL_CURSOR_MEMORY_LIMIT sets the maximum memory that each cursor can occupy. If the limit is exceeded, the cached data will be saved to hard disks. PL_MEMORY_LIMIT sets the maximum memory that each stored procedure can occupy.