SQL におけるトリガ、ストアドプロシージャ、ストアドファンクション、カーソルの活用

1 トリガ

1.1 トリガの概要

トリガは、特定のデータ베이스イベント（INSERT・UPDATE・DELETE）の発火に応じて自動実行される特別なストアドオブジェクトです。アプリケーションからの明示的な呼び出しではなく、データ操作によって内在的に起動され、ビジネスルールの強制や整合性の維持に広く利用されます。

例：学生テーブルに新規レコードが追加または削除されたタイミングで、学生総数を別途管理する集計テーブルを自動更新するなど。

1.2 トリガの作成

CREATE TRIGGER トリガ名
  BEFORE | AFTER イベント类型（INSERT | UPDATE | DELETE）
  ON 対象テーブル名
  FOR EACH ROW
BEGIN
  -- 処理内容（SQL文）
END;

各要素の補足

  • BEFORE | AFTER：イベント発火タイミング（操作前／操作後）
  • FOR EACH ROW：行単位で実行（ステートメント単位ではない）
  • OLD／NEW：操作前／操作後の行の値を参照可能（UPDATEでは両方利用）

1.3 実装例

-- 学生数を追跡する集計用テーブル（初期値は1）
CREATE TABLE student_counts (
  total_count INT NOT NULL
);
INSERT INTO student_counts VALUES (1);

-- 新規登録時にカウントをインクリメント
CREATE TRIGGER trg_student_insert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
  UPDATE student_counts SET total_count = total_counts + 1;
END;

-- 削除時にカウントをデクリメント
CREATE TRIGGER trg_student_delete
AFTER DELETE ON student
FOR EACH ROW
BEGIN
  UPDATE student_counts SET total_count = total_counts - 1;
END;
-- 職員表と給与表を同期する別の事例
CREATE TABLE employee (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  sex CHAR(1),
  age INT
);

CREATE TABLE salary (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  amount DECIMAL(10,2)
);

-- 職員追加時に給与レコードも挿入
CREATE TRIGGER trg_emp_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
  INSERT INTO salary (name, amount) VALUES (NEW.name, 5000.00);
END;

-- 名前の変更時に給与テーブルも更新
CREATE TRIGGER trg_emp_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
  UPDATE salary SET name = NEW.name WHERE name = OLD.name;
END;

-- 職員削除時に給与レコードも削除
CREATE TRIGGER trg_emp_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
  DELETE FROM salary WHERE name = OLD.name;
END;

2 ストアドプロシージャ

2.1 概要

ストアドプロシージャは、事前にコンパイルされデータベース内にオブジェクトとして保存された一連のSQL文の集合です。アプリケーションから呼び出すことで、処理の集中化やネットワーク通信の削減、実行パフォーマンスの向上が得られます。

2.2 ストアドプロシージャとストアドファンクションの比較

項目 ストアドプロシージャ ストアドファンクション
戻り値 任意（OUT／INOUTパラメータ経由） 必須（RETURN文で単一値）
パラメータ種別 IN / OUT / INOUT 可能 IN のみ

2.3 主な利点

  • 事前コンパイルにより実行頻度が高い処理の高速化
  • 複雑なロジックを再利用可能なモジュールとしてカプセル化
  • 権限付与により安全なAPI的利用が可能

2.4 ストアドプロシージャの構文と実装

CREATE PROCEDURE プロシージャ名 (
  [IN|OUT|INOUT] パラメータ名 データ型 [デフォルト値],
  ...
)
BEGIN
  -- 処理本体（宣言・制御文・SQL文）
END;
-- 入出力なし、単純な集計
DELIMITER $$ 
CREATE PROCEDURE sp_get_total()
BEGIN
  SELECT COUNT(*) FROM student;
END $$
DELIMITER ;
-- 入力あり、操作のみ
DELIMITER $$
CREATE PROCEDURE sp_bulk_insert(IN cnt INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= cnt DO
    INSERT INTO test_table VALUES (i, SHA1(i));
    SET i = i + 1;
  END WHILE;
END $$
DELIMITER ;
-- 出力パラメータあり
DELIMITER $$
CREATE PROCEDURE sp_count_by_grade(IN grade_id INT, OUT result_count INT)
BEGIN
  SELECT COUNT(*) INTO result_count
  FROM student
  WHERE grade_id = grade_id;
END $$
DELIMITER ;
-- INOUTパラメータ（入力値を加工して返す）
DELIMITER $$
CREATE PROCEDURE sp_inout_demo(INOUT val INT)
BEGIN
  IF val IS NOT NULL THEN
    SET val := val + 10;
  ELSE
    SET val := 100;
  END IF;
END $$
DELIMITER ;

3 ストアドファンクション

3.1 概要

ストアドファンクションは、single value を返すカスタム関数です。WHERE句やSELECT句での直接的な計算に使用可能で、再利用性の高いビジネスロジックの実装に適しています。

3.2 定義と使用

CREATE FUNCTION 関数名 (
  引数名 データ型,
  ...
)
RETURNS 帰り値の型
DETERMINISTIC  -- （任意）結果が固定であることを明示
BEGIN
  DECLARE 変数宣言;
  -- 処理と返却値
  RETURN 値;
END;
-- 行数を返す関数
CREATE FUNCTION fn_student_count()
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE cnt INT DEFAULT 0;
  SELECT COUNT(*) INTO cnt FROM student;
  RETURN cnt;
END;

-- 氏名からIDを取得
CREATE FUNCTION fn_id_by_name(stu_name VARCHAR(40))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE sid INT;
  SELECT id INTO sid FROM student WHERE name = stu_name;
  RETURN sid;
END;
-- 使用方法（ обычно SELECT 文内または式として）
SELECT fn_student_count();
SELECT id, name FROM student WHERE id = fn_id_by_name('田中太郎');
-- 削除
DROP FUNCTION fn_student_count;

4 カーソル

4.1 概要

カーソルは、SELECT結果セットの各行を順次アクセスするためのﾌﾟﾛｾｯ Breakup mechanism です。集合操_keys と行単位処理を bridge する仕組みで、特に動的データ更新や条件分岐が必要なループ処理において有用です。

4.2 宣言・オープン・フェッチ・クローズの流れ

-- 宣言
DECLARE cursor_name CURSOR FOR SELECT col1, col2 FROM table_name;

-- ループ脱出用フラグ
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- オープン
OPEN cursor_name;

-- データ取得と処理
FETCH cursor_name INTO var1, var2;

-- クローズ
CLOSE cursor_name;

4.3 実装例：二つのテーブルを同期

-- 権限元と同期対象テーブル
CREATE TABLE sys_user (
  id INT PRIMARY KEY,
  username VARCHAR(50)
);
INSERT INTO sys_user VALUES
  (1,'Alice'),(2,'Bob'),(3,'Carol');

CREATE TABLE app_user (
  id INT,
  full_name VARCHAR(50)
);
INSERT INTO app_user VALUES
  (1,'Ann'),(2,'Ben'),(3,'Cathy');

-- 同期プロシージャ
DELIMITER $$
CREATE PROCEDURE sp_sync_users()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_username VARCHAR(50);
  
  DECLARE cur CURSOR FOR SELECT id, username FROM sys_user;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;

  sync_loop: LOOP
    FETCH cur INTO v_id, v_username;
    
    IF done = 1 THEN
      LEAVE sync_loop;
    END IF;

    UPDATE app_user SET full_name = v_username WHERE id = v_id;
  END LOOP;

  CLOSE cur;
END $$
DELIMITER ;

-- 実行
CALL sp_sync_users();

タグ: MySQL トリガ ストアドプロシージャ ストアドファンクション カーソル

6月22日 23:00 投稿

ホットタグ

©2026 異端開発室