はじめに
こんにちは、NRIネットコムの山家谷です。 筆者が入社してからもうすぐ2年が経過しようとしています。月日の流れる早さに驚きを隠せません。
最近業務でプロシージャを使う機会がありましたので、今回はデータベースにおけるプロシージャ・ファンクション・トリガーについてまとめたいと思います。
プロシージャとは
プロシージャとは、簡単に言うと「ループや分岐が入った複雑な処理を一括で実行できる仕組み」になります。
プロシージャのサンプルコード（MySQL8.0）を以下に記載します。
事前作業として、データ操作用のテーブルを作成します。
create table user ( id INT not null auto_increment comment 'id' , name VARCHAR(256) not null comment 'ユーザー名' , age INT not null comment '年齢' , birthday DATE not null comment '生年月日' , constraint product_PKC primary key (id) ) ;
その後、プロシージャ登録用に、終端文字を「//」に変更します。（デフォルトだと終端文字は「;」）
delimiter //
事前作業が完了したので、プロシージャを登録します。 今回はまとめてユーザーを登録できるプロシージャを登録します。
CREATE PROCEDURE insert_user_row(name VARCHAR(256), age INT, birthday DATE, limits INT)
BEGIN
DECLARE count INT;
SET count = 1;
WHILE limits >= count DO
INSERT INTO user (name,age,birthday) VALUES (concat(name,count),age,birthday);
SET count = count + 1;
END WHILE;
END
//
最後に、プロシージャ登録用に変更した終端文字を「;」に戻します。
delimiter ;
これでプロシージャを使うために必要な設定は完了です。 プロシージャの登録方法は、以下になります。
CREATE PROCEDURE プロシージャ名(引数)
BEGIN
//ここに実行したい処理を記載
END
サンプルコードだと、insert_user_rowがプロシージャ名、name・age・birthday・limitsが引数になります。
次に、細かい処理箇所について見ていきます。
DECLARE count INT;
SET count = 1;
WHILE limits >= count DO
INSERT INTO user (name,age,birthday) VALUES (concat(name,count),age,birthday);
SET count = count + 1;
END WHILE;
DECLAREで変数宣言し、次のSETで値を代入しています。 WHILEの部分でループ処理を行っており、引数で指定したlimitsの値だけINSERT処理を行います。
上記までではプロシージャ登録できただけで実行できていません。 設定したプロシージャを実行するには、callを使って呼び出します。 呼び出し方は以下になります。
CALL プロシージャ名(引数);
なので、今回登録したプロシージャを呼び出す際は以下のようになります。
CALL insert_user_row('sampleUser',15,'2010/1/1',10);
実行した後のuserテーブルを確認すると、レコードが挿入されていることを確認できました。
SELECT * from user; +----+--------------+-----+------------+ | id | name | age | birthday | +----+--------------+-----+------------+ | 1 | sampleUser1 | 15 | 2010-01-01 | | 2 | sampleUser2 | 15 | 2010-01-01 | | 3 | sampleUser3 | 15 | 2010-01-01 | | 4 | sampleUser4 | 15 | 2010-01-01 | | 5 | sampleUser5 | 15 | 2010-01-01 | | 6 | sampleUser6 | 15 | 2010-01-01 | | 7 | sampleUser7 | 15 | 2010-01-01 | | 8 | sampleUser8 | 15 | 2010-01-01 | | 9 | sampleUser9 | 15 | 2010-01-01 | | 10 | sampleUser10 | 15 | 2010-01-01 | +----+--------------+-----+------------+
メリットとしては、以下が挙げられます。
プログラム的なこと（ループや分岐など）をしたい
1回ずつSQLを実行するより実行時間が短くなる
例えば、開発環境に1万件のレコードを追加したい時はプロシージャを使うと効率よくデータを追加することができます。 実際に筆者が業務で大量のレコードを作成する際も、プロシージャを利用して大量のレコードを追加しました。
個人的には、アプリケーションサーバー側のコードに手を加えずに大量のデータを用意する必要がある場面にプロシージャを使うと良いと思います。
プロシージャを残しておくと、意図せずにプロシージャを実行してしまうことがあります。 一時的に利用する場合は、使った後に登録したプロシージャの削除を忘れないようにしましょう。
//insert_user_rowプロシージャを削除する DROP PROCEDURE IF EXISTS insert_user_row;
ファンクションとは
ファンクションとは、簡単に言うと「関数を自作できる仕組み」になります。
SQL側で用意されている関数として、文字列を結合できる「concat」があります。 すでに用意されている関数以外で関数を使いたい場合は、ファンクションを使います。
ファンクションのサンプルコード（MySQL8.0）を以下に記載します。
ファンクション登録用に、終端文字を「//」に変更します。（デフォルトだと終端文字は「;」）
delimiter //
ファンクションの登録方法は、以下になります。
CREATE FUNCTION ファンクション名(引数)
RETURNS 返り値の型 DETERMINISTIC
BEGIN
//ここに実行したい処理を記載
RETURN 返り値;
END
例えば、誕生してからの日数を取得するファンクションを登録する場合は以下のように記載します。
CREATE FUNCTION sample_function(birthday DATE)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE today DATE;
SET today = CURDATE();
RETURN DATEDIFF(today,birthday);
END
//
最後に、ファンクション登録用に変更した終端文字を「;」に戻します。
delimiter ;
使い方はデフォルトの関数と同じような形になります。以下が実行方法の一例です。
//ファンクションを実行する（実行結果は2025/03/06実行時のもの）
SELECT sample_function('2010/1/1');
+------------------------------+
| sample_function('2010/1/1') |
+------------------------------+
| 5543 |
+------------------------------+
プロシージャはSQL内に組み込むことはできませんが、ファンクションは「concat」と同様SQL内に組み込むことができます。
ファンクションのメリットとしては、同じ処理のコードの再利用ができ重複が排除されることで保守性が高まる点が挙げられます。 個人的には、複雑な計算処理を簡潔にまとめたい際にファンクションを使うと良いかと思います。
使用する値が異なるだけで同じ処理を複数回実行する場合はプロシージャを利用するのが良いかと思いますが、 部分的に他の処理と同じ箇所があるような場合はファンクションを使って共通化するのが良いと思います。
トリガーとは
トリガーとは簡単に言うと、データ操作に連動して実行されるプロシージャになります。
トリガーでは、データに特定の操作を行った際に実行されます。 トリガーのサンプルコード（MySQL8.0）を以下に記載します。
トリガーを設定する前に、まずはトリガー用のテーブルを作成します。
create table log ( id INT not null auto_increment comment 'id' , user_id INT not null comment 'user_id' , crud VARCHAR(16) not null comment 'crud' , name VARCHAR(256) not null comment 'ユーザー名' , age INT not null comment '年齢' , birthday DATE not null comment '生年月日' , created_at DATETIME not null comment '作成日時' , constraint product_PKC primary key (id) ) ;
次に、トリガー用の設定を登録します。 トリガーを設定方法は以下になります。
create trigger トリガー名 [beforeまたはafter] [updateまたはinsertまたはdelete] on user for each row
//ここに実行したい処理を記載
before・afterを指定することでトリガーに記載した処理の実行タイミングを、対象となるデータ操作が実行される前・後に設定することができます。 また、対象となるデータ操作をupdate・insert・deleteから指定することができます。
例えば、更新処理の直後に、更新ログを残しておきたい場合は以下のように設定します。
create trigger update_trigger after update on user for each row
insert into log (user_id, crud , name, age , birthday ,created_at) values(new.id,'Update',new.name,new.age,new.birthday, now());
これでトリガーの登録は完了です。 上記の例の場合だと、userテーブルにUPDATEが実行された際に、そのログをlogテーブルに登録する処理となっています。
実際にuserテーブルのレコードにUPDATEを実行してみましょう。
//UPDATE実行前にlogテーブルにレコードがないことを確認 SELECT * from log; //UPDATEを実行する UPDATE user set name="updatedUser1" where name = "sampleUser1"; //UPDATE実行後、それぞれのテーブルの結果を取得 SELECT * from user; +----+--------------+-----+------------+ | id | name | age | birthday | +----+--------------+-----+------------+ | 1 | updatedUser1 | 15 | 2010-01-01 | | 2 | sampleUser2 | 15 | 2010-01-01 | | 3 | sampleUser3 | 15 | 2010-01-01 | | 4 | sampleUser4 | 15 | 2010-01-01 | | 5 | sampleUser5 | 15 | 2010-01-01 | | 6 | sampleUser6 | 15 | 2010-01-01 | | 7 | sampleUser7 | 15 | 2010-01-01 | | 8 | sampleUser8 | 15 | 2010-01-01 | | 9 | sampleUser9 | 15 | 2010-01-01 | | 10 | sampleUser10 | 15 | 2010-01-01 | +----+--------------+-----+------------+ SELECT * from log; +----+---------+--------+--------------+-----+------------+---------------------+ | id | user_id | crud | name | age | birthday | created_at | +----+---------+--------+--------------+-----+------------+---------------------+ | 1 | 1 | Update | updatedUser1 | 15 | 2010-01-01 | 2025-03-07 18:48:06 | +----+---------+--------+--------------+-----+------------+---------------------+
logテーブルに実行ログが登録されていることが確認できました。
トリガーを使うことで一回のSQLに連動して他のデータを操作することができます。 また、一回のSQLに連動して他のデータを操作する処理をまとめることができます。
メリットとしては、SQLでレコードが挿入された時点で処理が走るため手作業が不要となる点になります。 上記のサンプルコードのように、常にテーブルの情報を最新化したい（レコードが更新された時点で更新ログを作成したい）場合はトリガーがおすすめです。
逆に、トリガー特有のデメリットとして、トリガーはデータ操作に連動して実行されるため、トリガーが設定されていることを知らない開発者がデータ操作すると意図せずにトリガーが実行される場合があるので注意が必要です。 その他、トリガーで生成したデータをさらに別処理で更新・削除する場合は、トリガーがいつ実行されるか分からないのでデッドロックを考慮する必要があります。
サンプルコードのように、単にログを残しておきたい、などの場面では問題ありませんが、複雑な処理にトリガーを組み込む場合は注意が必要です。
一時的にまとめて更新したい場合は、意図的に呼び出さないと実行されないプロシージャの方がリスクが小さいのでプロシージャが良いと思います。
まとめ
簡単にですが、プロシージャ・ファンクション・トリガーについてご紹介し、それぞれのメリット・使う場面について説明してきました。 一方で3つに共通するデメリットもあり、アプリケーションの一部として利用すると処理がアプリケーションサーバー側と分散してしまい、保守性の観点ではあまり良くないので注意が必要です。
アプリケーション開発そのものではあまり登場しませんが、アプリケーションのテストで大量のデータが欲しい！となった際にこんな方法があるよ、と先輩から教えていただきました。
筆者と同じように大量のデータを用意する方法で悩んでいる方がいれば、ぜひ参考にしていただけると幸いです！
