ストアドプロシージャ, ストアドファンクションの使い方

2022/11/13

ストアドプロシージャ ストアドファンクション を利用すると、複数のSQLをまとめて実行することができます。アプリケーション側の言語( PHP Python Java など)に依存せず、一連の処理を定義できるので便利なケースがあります。ここでは、基本的な使い方について確認します。

概要

ストアドルーチン
- 複数のSQLをまとめて実行するための仕組みです。
- 条件分岐 や 例外処理 も記述できます。
- ストアドプロシージャ と ストアドファンクション が存在します。
ストアドプロシージャ
- CALL ステートメント で呼び出します。
ストアドファンクション
- ユーザー定義関数 とも呼ばれます。
- 値を一つだけ返します。
- SUM COUNT などの標準関数と同様の形式で呼び出します。

ストアドプロシージャ

作成

CREATE PROCEDURE で作成できます。

例として、以下プロシージャを作成してみます。

CREATE PROCEDURE test_proc (IN x INT, OUT y INT, OUT z INT)
BEGIN
  SELECT x;
  SELECT COUNT(*) INTO y FROM table_a;
  SELECT COUNT(*) INTO z FROM table_b;
  SELECT x, y, z;
END//

引数 として x y z を受け取ります。
y z の引数は 戻り値 として動作します。
4つのSQLを実行します。

上記プロシージャの定義を実行します。プロシージャの定義内で、複数のSQLを実行するため 終端文字(;) を利用しています。

そのため、プロシージャを定義するときだけ、一時的に終端文字を変更する必要があります。

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE test_proc (IN x INT, OUT y INT, OUT z INT)
    -> BEGIN
    ->   SELECT x;
    ->   SELECT COUNT(*) INTO y FROM table_a;
    ->   SELECT COUNT(*) INTO z FROM table_b;
    ->   SELECT x, y, z;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;

確認

作成したプロシージャを確認します。SHOW CREATE PROCEDURE <プロシージャ名> で確認できます。

mysql> SHOW CREATE PROCEDURE test_proc\G;
*************************** 1. row ***************************
           Procedure: test_proc
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `test_proc`(IN x INT, OUT y INT, OUT z INT)
BEGIN
  SELECT x;
  SELECT COUNT(*) INTO y FROM table_a;
  SELECT COUNT(*) INTO z FROM table_b;
  SELECT x, y, z;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

プロシージャの定義は、information_schemaデータベース の ROUTINESテーブル に格納されているので、以下のように確認することもできます。

mysql> SELECT *
    -> FROM information_schema.ROUTINES
    -> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'test_proc'\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: test_proc
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: test_proc
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  SELECT x;
  SELECT COUNT(*) INTO y FROM table_a;
  SELECT COUNT(*) INTO z FROM table_b;
  SELECT x, y, z;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2019-01-25 09:33:34
            LAST_ALTERED: 2019-01-25 09:33:34
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@%
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

呼び出し

CALLステートメント で作成したプロシージャを呼び出してみます。

mysql> CALL test_proc(333, @y, @z);
+------+
| x    |
+------+
|  333 |
+------+
1 row in set (0.00 sec)

+------+------+------+
| x    | y    | z    |
+------+------+------+
|  333 |    5 |    4 |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

複数のクエリがまとめて実行されました。

戻り値 に値が格納されているか確認します。

mysql> SELECT @y;
+------+
| @y   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT @z;
+------+
| @z   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

削除

DROP PROCEDURE で削除できます。

mysql> DROP PROCEDURE test_proc;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SHOW CREATE PROCEDURE test_proc\G;
ERROR 1305 (42000): PROCEDURE test_proc does not exist
ERROR:
No query specified

ストアドファンクション

作成

CREATE FUNCTION で作成できます。

例として、以下ファンクションを作成してみます。

CREATE FUNCTION test_function() RETURNS DOUBLE
BEGIN
DECLARE avg_old DOUBLE;
SELECT AVG(`old`) INTO avg_old FROM `testtable`;
RETURN avg_old;
END

上記ファンクションの定義を実行します。プロシージャ同様に 終端文字(;) を一時的に変更します。

mysql> delimiter //
mysql>
mysql> CREATE FUNCTION test_function() RETURNS DOUBLE
    -> BEGIN
    -> DECLARE avg_old DOUBLE;
    -> SELECT AVG(`old`) INTO avg_old FROM `testtable`;
    -> RETURN avg_old;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;

確認

作成したファンクションを確認します。SHOW CREATE FUNCTION <ファンクション名> で確認できます。

mysql> SHOW CREATE FUNCTION test_function\G;
*************************** 1. row ***************************
            Function: test_function
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS double
BEGIN
DECLARE avg_old DOUBLE;
SELECT AVG(`old`) INTO avg_old FROM `testtable`;
RETURN avg_old;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

呼び出し

作成したファンクションを呼び出してみます。

mysql> SELECT test_function();
+-----------------+
| test_function() |
+-----------------+
|            39.4 |
+-----------------+
1 row in set (0.00 sec)

削除

DROP FUNCTION で削除できます。

mysql> DROP FUNCTION test_function;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE FUNCTION test_function\G;
ERROR 1305 (42000): FUNCTION test_function does not exist
ERROR: 
No query specified

登録済みストアドルーチンの確認方法

ストアドルーチンの定義は、information_schemaデータベース の ROUTINESテーブル に格納されています。

そのため、以下クエリで登録済みストアドルーチンを確認できます。

SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE 
FROM information_schema.ROUTINES;

AWSのRDSの場合

あらかじめ定義されたストアドプロシージャが存在します。

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE 
    -> FROM information_schema.ROUTINES 
    -> WHERE ROUTINE_NAME LIKE 'rds%';
+-----------------------------------+----------------+--------------+
| ROUTINE_NAME                      | ROUTINE_SCHEMA | ROUTINE_TYPE |
+-----------------------------------+----------------+--------------+
| rds_collect_global_status_history | mysql          | PROCEDURE    |
| rds_disable_gsh_collector         | mysql          | PROCEDURE    |
| rds_disable_gsh_rotation          | mysql          | PROCEDURE    |
| rds_enable_gsh_collector          | mysql          | PROCEDURE    |
| rds_enable_gsh_rotation           | mysql          | PROCEDURE    |
| rds_external_master               | mysql          | PROCEDURE    |
| rds_innodb_buffer_pool_dump_now   | mysql          | PROCEDURE    |
| rds_innodb_buffer_pool_load_abort | mysql          | PROCEDURE    |
| rds_innodb_buffer_pool_load_now   | mysql          | PROCEDURE    |
| rds_kill                          | mysql          | PROCEDURE    |
| rds_kill_query                    | mysql          | PROCEDURE    |
| rds_next_master_log               | mysql          | PROCEDURE    |
| rds_reset_external_master         | mysql          | PROCEDURE    |
| rds_rotate_general_log            | mysql          | PROCEDURE    |
| rds_rotate_global_status_history  | mysql          | PROCEDURE    |
| rds_rotate_slow_log               | mysql          | PROCEDURE    |
| rds_set_configuration             | mysql          | PROCEDURE    |
| rds_set_external_master           | mysql          | PROCEDURE    |
| rds_set_fk_checks_off             | mysql          | PROCEDURE    |
| rds_set_fk_checks_on              | mysql          | PROCEDURE    |
| rds_set_gsh_collector             | mysql          | PROCEDURE    |
| rds_set_gsh_rotation              | mysql          | PROCEDURE    |
| rds_show_configuration            | mysql          | PROCEDURE    |
| rds_skip_repl_error               | mysql          | PROCEDURE    |
| rds_start_replication             | mysql          | PROCEDURE    |
| rds_stop_replication              | mysql          | PROCEDURE    |
+-----------------------------------+----------------+--------------+
26 rows in set (0.00 sec)

例えば、slow_logテーブル のレコードを全て削除したい場合、 rds_rotate_slow_logプロシージャ が利用できます。

rds_rotate_slow_logプロシージャ の内容は以下の通りです。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> SHOW CREATE PROCEDURE rds_rotate_slow_log\G;
*************************** 1. row ***************************
           Procedure: rds_rotate_slow_log
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`rdsadmin`@`localhost` PROCEDURE `rds_rotate_slow_log`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE sql_logging BOOLEAN;
  select @@sql_log_bin into sql_logging;
  set @@sql_log_bin=off;
  CREATE TABLE IF NOT EXISTS mysql.slow_log_template LIKE mysql.slow_log;
  CREATE TABLE IF NOT EXISTS mysql.slow_log2 LIKE mysql.slow_log_template;
  DROP TABLE IF EXISTS mysql.slow_log_backup;
  RENAME TABLE mysql.slow_log TO mysql.slow_log_backup, mysql.slow_log2 TO mysql.slow_log;
  set @@sql_log_bin=sql_logging;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.04 sec)

ERROR: 
No query specified