ストアドルーチン

August 06, 2018

ストアドプロシージャ

ストアドプロシージャは、複数の SQL 文を 1 つにパッケージしてデータベースで実行する仕組みであり、実行が速い、クライアントと RDBMS とのやりとりが少なくて済む、などのメリットがある。
また、よく使う SQL 文の組み合わせに名前をつけて登録することができるため、毎回 1 つずつ SQL を書かなくても、パッケージの名称を記載するだけで、繰り返し使用することができる。
ストアドプロシージャは SQL の文法の 1 つであり、作ったパッケージは CALL という構文を使うことで実行できる。

トランザクションと少し似ているが、トランザクションはデータベースの命令であり、指定した複数の SQL が途中までしか実行されず、不完全な状態になることを防ぐ仕組みである一方、ストアドプロシージャはパッケージとしてデータベースで実行する仕組みなので、複数の SQL 文のうち特定のものが失敗することもある。

プロシージャを定義する際は DELIMITER を ; から // 等に変更しておく。そうでないと、プロシージャ内の SQL において ; が入力された際に文末だと判断されるため。
以下みたいな感じで実行する。

mysql> DELIMITER //  
mysql> CREATE PROCEDURE protopack ()  
    -> BEGIN  
    -> INSERT INTO Protocol (Id, Name, Layer4) VALUES (15, 'OSPF', 'IP (Not Layer 4)');  
    -> INSERT INTO Protocol (Id, Name, Layer4) VALUES (16, 'RIP', 'UDP');  
    -> END //  
Query OK, 0 rows affected (0.04 sec)  
  
mysql> DELIMITER ;  
mysql> CALL protopack ();  
Query OK, 1 row affected (0.06 sec)  
  
mysql> SELECT * FROM Protocol;  
+------+------+------------------+  
| Id   | Name | Layer4           |  
+------+------+------------------+  
|    0 | HTTP | TCP              |  
|    1 | SSH  | TCP              |  
|    2 | FTP  | TCP              |  
|    3 | NFS  | TCP              |  
|    4 | DHCP | UDP              |  
|    5 | SIP  | UDP              |  
|    6 | DNS  | UDP              |  
|    7 | SNMP | UDP              |  
|   15 | OSPF | IP (Not Layer 4) |  
|   16 | RIP  | UDP              |  
+------+------+------------------+  
10 rows in set (0.00 sec)

また、もちろん引数を使うこともできる。

mysql> DELIMITER //  
mysql> CREATE PROCEDURE protopack2 (Id_ INT, Name_ VARCHAR(20), Layer4_ VARCHAR(20))  
    -> BEGIN  
    -> INSERT INTO Protocol (Id, Name, Layer4) VALUES (Id_, Name_, Layer4_);  
    -> SELECT * FROM Protocol WHERE Id = Id_;  
    -> END //  
Query OK, 0 rows affected (0.04 sec)  
  
mysql> DELIMITER ;  
mysql> CALL protopack2(17, 'ARP', 'IP (Not Layer 4)');  
+------+------+------------------+  
| Id   | Name | Layer4           |  
+------+------+------------------+  
|   17 | ARP  | IP (Not Layer 4) |  
+------+------+------------------+  
1 row in set (0.00 sec)  
  
Query OK, 0 rows affected (0.00 sec)

ストアドファンクション

ストアドファンクションは関数を追加する機能であり、独自の集計や計算をしたいときなどに使われる。
ストアドプロシージャと異なり、実行すると何らかの計算結果を戻り値として返す。
また、ストアドプロシージャは CALL ステートメントを使って実行するが、ストアドファンクションは「関数」であるため、SELECT 文や WHERE 句など関数を記述する場所であればどこにでも書けるという違いがある。

mysql> DELIMITER //  
mysql> CREATE FUNCTION Register (Name_ VARCHAR(20), Protocol_ VARCHAR (50))  
    -> RETURNS VARCHAR(50)  
    -> BEGIN  
    -> INSERT INTO Application (Name, Protocol) VALUES (Name_, Protocol_);  
    -> RETURN 'Registration Complited!!';  
    -> END //  
Query OK, 0 rows affected (0.02 sec)  
  
mysql> DELIMITER ;  
mysql> SELECT Register ('Router', 'OSPF');  
+-----------------------------+  
| Register ('Router', 'OSPF') |  
+-----------------------------+  
| Registration Complited!!    |  
+-----------------------------+  
1 row in set (0.01 sec)

また、DECLARE を使って関数内に変数を定義することができる。

mysql> DELIMITER //  
mysql> CREATE FUNCTION SumPopulation (Code VARCHAR(20))  
    -> RETURNS INT  
    -> BEGIN  
    -> DECLARE modori INT;  
    -> SELECT SUM(Population) INTO modori FROM City WHERE CountryCode = Code;  
    -> RETURN modori;  
    -> END //  
Query OK, 0 rows affected (0.02 sec)  
  
mysql> DELIMITER ;  
mysql> SELECT SumPopulation ('AFG');  
+-----------------------+  
| SumPopulation ('AFG') |  
+-----------------------+  
|               2332100 |  
+-----------------------+  
1 row in set (0.00 sec)

ストアドファンクションの表示と削除は以下のように行う。

mysql> SHOW FUNCTION STATUS;  
+------------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
| Db         | Name          | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |  
+------------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
| Networking | Register      | FUNCTION | root@%  | 2018-08-05 23:59:49 | 2018-08-05 23:59:49 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  
| world      | SumPopulation | FUNCTION | root@%  | 2018-08-06 00:10:19 | 2018-08-06 00:10:19 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  
+------------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
2 rows in set (0.01 sec)  
  
mysql> DROP FUNCTION Register;  
Query OK, 0 rows affected (0.03 sec)  
  
mysql> SHOW FUNCTION STATUS;  
+-------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
| Db    | Name          | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |  
+-------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
| world | SumPopulation | FUNCTION | root@%  | 2018-08-06 00:10:19 | 2018-08-06 00:10:19 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  
+-------+---------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  
1 row in set (0.02 sec)

トリガー

MySQL では、テーブルに対して特定の動作が起きた時にそれを検知してストアドルーチンを実行することができる (トリガー)。
具体的には INSERT 、DELETE 、UPDATE のような実際にデータベースに対して書き換えを行うアクションが発生した際に、特定のルーチンを実行する。

実際にトリガーを登録するには以下のように行う。

mysql> DELIMITER //  
mysql> CREATE TRIGGER jushotrigger  
    -> AFTER DELETE ON jusho1 FOR EACH ROW  
    -> BEGIN  
    -> UPDATE jusho2 SET Company = NULL WHERE Number = OLD.Number;  
    -> END //  
Query OK, 0 rows affected (0.02 sec)  
  
mysql> DELIMITER ;  
mysql> SELECT * FROM jusho1;  
+--------+------------+----------+  
| Number | Company    | Location |  
+--------+------------+----------+  
|      1 | Shirius    | Tokyo    |  
|      2 | Vega       | Tokyo    |  
|      3 | Kapera     | Tokyo    |  
|      4 | Rigeru     | Tokyo    |  
|      5 | Peterugius | Tokyo    |  
+--------+------------+----------+  
5 rows in set (0.00 sec)  
  
mysql> SELECT * FROM jusho2;  
+--------+------------+----------+  
| Number | Company    | Location |  
+--------+------------+----------+  
|      6 | Arudebaran | Osaka    |  
|      7 | Peecock    | Osaka    |  
|      8 | Menkarinan | Osaka    |  
|      9 | Prokion    | Osaka    |  
|     10 | Kano-pusu  | Osaka    |  
|     11 | Arcturus   | Osaka    |  
|      2 | Vega       | Tokyo    |  
+--------+------------+----------+  
7 rows in set (0.00 sec)  
  
mysql> DELETE FROM jusho1 WHERE Number = 2;  
Query OK, 1 row affected (0.01 sec)  
  
mysql> SELECT * FROM jusho1;  
+--------+------------+----------+  
| Number | Company    | Location |  
+--------+------------+----------+  
|      1 | Shirius    | Tokyo    |  
|      3 | Kapera     | Tokyo    |  
|      4 | Rigeru     | Tokyo    |  
|      5 | Peterugius | Tokyo    |  
+--------+------------+----------+  
4 rows in set (0.00 sec)  
  
mysql> SELECT * FROM jusho2;  
+--------+------------+----------+  
| Number | Company    | Location |  
+--------+------------+----------+  
|      6 | Arudebaran | Osaka    |  
|      7 | Peecock    | Osaka    |  
|      8 | Menkarinan | Osaka    |  
|      9 | Prokion    | Osaka    |  
|     10 | Kano-pusu  | Osaka    |  
|     11 | Arcturus   | Osaka    |  
|      2 | NULL       | Tokyo    |  
+--------+------------+----------+  
7 rows in set (0.00 sec)