SQL: ストアドプロシージャとSQL関数

1. 実世界のアナロジー

銀行を想像してください：

ストアドプロシージャは銀行の「標準業務プロセス」のようなもの — 窓口係は定義済みの手順に従い、あなたは「預金したい」と言うだけで、全体のプロセスが自動的に実行されます。
関数は電卓の関数キーのようなもの — パラメータを入力し、結果を返し、より大きな計算に組み込むことができます。

2. コア概念

(1) ストアドプロシージャとは

ストアドプロシージャは、データベースに保存された事前コンパイル済みのSQL文のセットで、以下が可能です：

入力パラメータを受け取る
複雑なビジネスロジックを実行する
結果セットやステータスコードを返す
パフォーマンスを向上させる（事前コンパイル、ネットワークトラフィックの削減）

💡 SQLiteはストアドプロシージャをサポートしていません。以下の例は概念とMySQL/PostgreSQLの構文を示しています。SQLiteユーザーはカスタム関数を代替として使用できます。

(2) MySQLストアドプロシージャ構文

SQL

-- MySQLストアドプロシージャの基本構造
DELIMITER //
CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- 変数宣言
    -- ビジネスロジック
    -- 結果を返す
END //
DELIMITER ;

(3) PostgreSQLストアドプロシージャ構文

SQL

-- PostgreSQLストアドプロシージャの基本構造
CREATE OR REPLACE FUNCTION procedure_name(parameter_list)
RETURNS return_type AS $$
DECLARE
    -- 変数宣言
BEGIN
    -- ビジネスロジック
    -- RETURN文
END;
$$ LANGUAGE plpgsql;

(4) SQLiteのカスタム関数

SQLiteはストアドプロシージャをサポートしていませんが、プログラミング言語を通じてユーザー定義関数の作成をサポートしています：

SQL

-- SQLite自体はCREATE FUNCTIONをサポートしていない
-- Python、C、その他のプログラミング言語を通じてカスタム関数を登録する必要がある

-- Pythonの例（sqlite3モジュールを使用）
import sqlite3

def calculate_bonus(salary, rate):
    return salary * rate

conn = sqlite3.connect('company.db')
conn.create_function('calculate_bonus', 2, calculate_bonus)

-- 登録後、SQLで使用可能
SELECT name, salary, calculate_bonus(salary, 0.1) AS bonus
FROM employees;

(5) フロー制御文

IF/ELSE条件ロジック

SQL

-- MySQLの例
DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    
    SELECT salary INTO emp_salary
    FROM employees WHERE id = emp_id;
    
    IF emp_salary > 10000 THEN
        SELECT '高給与' AS status;
    ELSEIF emp_salary > 5000 THEN
        SELECT '中給与' AS status;
    ELSE
        SELECT '低給与' AS status;
    END IF;
END //
DELIMITER ;

WHILEループ

SQL

-- MySQLの例：1からNまでの合計を計算
DELIMITER //
CREATE PROCEDURE calculate_sum(IN n INT, OUT total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

LOOPループ

SQL

-- MySQLの例：LOOPとLEAVE
DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(100);
    
    DECLARE cur CURSOR FOR SELECT name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 各レコードを処理
        SELECT CONCAT('処理中: ', emp_name);
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

(6) カーソルの概念

カーソルはクエリの結果セットを1行ずつ処理するために使用されます：

SQL

-- 基本的なカーソルの使用フロー
-- 1. カーソルを宣言
DECLARE cur CURSOR FOR SELECT column FROM table;

-- 2. カーソルを開く
OPEN cur;

-- 3. データを取得
FETCH cur INTO variable;

-- 4. データを処理（通常ループ内で）

-- 5. カーソルを閉じる
CLOSE cur;

(7) ストアドファンクション

ストアドファンクションはストアドプロシージャに似ていますが、重要な違いがあります：

SQL

-- MySQLストアドファンクション
DELIMITER //
CREATE FUNCTION get_annual_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN monthly_salary * 12;
END //
DELIMITER ;

-- 関数を使用
SELECT name, get_annual_salary(salary) AS annual_salary
FROM employees;

(8) ストアドプロシージャと関数の比較

特徴	ストアドプロシージャ	ストアドファンクション
呼び出し方法	CALL文	SQL内で直接呼び出し
戻り値	複数の結果セットを返せる	単一の値を返す必要がある
ユースケース	複雑なビジネスロジック	計算と変換
トランザクション制御	トランザクションを管理可能	トランザクションを管理不可
SQL文内での使用	SELECTでは使用不可	SELECTで使用可能

3. 基本構文

(1) ストアドプロシージャの作成（MySQL）

SQL

-- パラメータ付きストアドプロシージャを作成
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(
    IN dept_name VARCHAR(50),
    IN min_salary DECIMAL(10,2)
)
BEGIN
    SELECT e.name, e.salary, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE d.department_name = dept_name
    AND e.salary >= min_salary
    ORDER BY e.salary DESC;
END //
DELIMITER ;

-- ストアドプロシージャを呼び出し
CALL get_employees_by_dept('Tech', 8000);

(2) ストアドファンクションの作成（PostgreSQL）

SQL

-- ボーナスを計算する関数を作成
CREATE OR REPLACE FUNCTION calculate_bonus(
    base_salary NUMERIC,
    performance_rating INTEGER
)
RETURNS NUMERIC AS $$
DECLARE
    bonus_rate NUMERIC;
BEGIN
    CASE performance_rating
        WHEN 1 THEN bonus_rate := 0.10;
        WHEN 2 THEN bonus_rate := 0.15;
        WHEN 3 THEN bonus_rate := 0.20;
        WHEN 4 THEN bonus_rate := 0.25;
        WHEN 5 THEN bonus_rate := 0.30;
        ELSE bonus_rate := 0.05;
    END CASE;
    
    RETURN base_salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;

-- 関数を使用
SELECT 
    name,
    salary,
    performance_rating,
    calculate_bonus(salary, performance_rating) AS bonus
FROM employees;

💡 ヒント： ストアドプロシージャは複雑なビジネスロジックのカプセル化に適し、関数は再利用可能な計算に適しています。実際のニーズに基づいて適切なアプローチを選択してください。

(3) サンプル

(4) サンプル：従業員入社処理ストアドプロシージャの作成（難易度⭐⭐）

新入社員の入社プロセスを処理するストアドプロシージャを作成します。社員番号の割り当て、初期給与の設定、入社情報の記録を含みます。

SQL

-- MySQL版
DELIMITER //
CREATE PROCEDURE hire_employee(
    IN p_name VARCHAR(100),
    IN p_department_id INT,
    IN p_position VARCHAR(50),
    IN p_salary DECIMAL(10,2),
    OUT p_employee_id INT,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE dept_exists INT DEFAULT 0;
    DECLARE max_id INT DEFAULT 0;
    
    -- 部門が存在するかチェック
    SELECT COUNT(*) INTO dept_exists
    FROM departments WHERE id = p_department_id;
    
    IF dept_exists = 0 THEN
        SET p_message = 'エラー：指定された部門は存在しません';
        SET p_employee_id = -1;
    ELSE
        -- 最大従業員IDを取得
        SELECT COALESCE(MAX(id), 0) INTO max_id FROM employees;
        SET p_employee_id = max_id + 1;
        
        -- 新しい従業員レコードを挿入
        INSERT INTO employees (id, name, department_id, position, salary, hire_date)
        VALUES (p_employee_id, p_name, p_department_id, p_position, p_salary, CURDATE());
        
        SET p_message = CONCAT('成功：従業員 ', p_name, ' がID ', p_employee_id, ' で入社しました');
    END IF;
END //
DELIMITER ;

-- 呼び出し例
CALL hire_employee('John Doe', 1, 'Junior Engineer', 8000.00, @new_id, @msg);
SELECT @new_id AS employee_id, @msg AS message;

▶ 試してみよう

(5) サンプル：PostgreSQL版 — 従業員給与調整関数（難易度⭐⭐）

SQL

-- PostgreSQL版
CREATE OR REPLACE FUNCTION adjust_salary(
    p_employee_id INTEGER,
    p_adjustment_type VARCHAR(10),  -- 'RAISE' または 'REDUCE'
    p_percentage NUMERIC
)
RETURNS TABLE(
    employee_name VARCHAR,
    old_salary NUMERIC,
    new_salary NUMERIC,
    adjustment_amount NUMERIC
) AS $$
DECLARE
    v_old_salary NUMERIC;
    v_new_salary NUMERIC;
    v_name VARCHAR;
BEGIN
    -- 現在の給与を取得
    SELECT name, salary INTO v_name, v_old_salary
    FROM employees WHERE id = p_employee_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION '従業員ID % は存在しません', p_employee_id;
    END IF;
    
    -- 新しい給与を計算
    IF p_adjustment_type = 'RAISE' THEN
        v_new_salary := v_old_salary * (1 + p_percentage / 100);
    ELSE
        v_new_salary := v_old_salary * (1 - p_percentage / 100);
    END IF;
    
    -- データベースを更新
    UPDATE employees SET salary = v_new_salary WHERE id = p_employee_id;
    
    -- 結果を返す
    employee_name := v_name;
    old_salary := v_old_salary;
    new_salary := v_new_salary;
    adjustment_amount := v_new_salary - v_old_salary;
    
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM adjust_salary(1, 'RAISE', 10);

▶ 試してみよう

4. 応用シナリオ

(1) シナリオ1：バッチデータ処理

SQL

-- MySQL：注文ステータスの一括更新
DELIMITER //
CREATE PROCEDURE update_order_status()
BEGIN
    DECLARE affected_rows INT DEFAULT 0;
    
    -- トランザクション開始
    START TRANSACTION;
    
    -- 30日以上保留中の注文をキャンセル
    UPDATE orders 
    SET status = 'cancelled', 
        updated_at = NOW()
    WHERE status = 'pending' 
    AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    SET affected_rows = ROW_COUNT();
    
    -- 処理をログに記録
    INSERT INTO process_log (process_name, affected_rows, executed_at)
    VALUES ('update_order_status', affected_rows, NOW());
    
    -- トランザクションをコミット
    COMMIT;
    
    SELECT CONCAT(affected_rows, ' 件の期限切れ注文をキャンセルしました') AS result;
END //
DELIMITER ;

(2) シナリオ2：複雑なレポート生成

SQL

-- PostgreSQL：部門給与レポートを生成
CREATE OR REPLACE FUNCTION generate_salary_report()
RETURNS TABLE(
    department_name VARCHAR,
    employee_count BIGINT,
    avg_salary NUMERIC,
    min_salary NUMERIC,
    max_salary NUMERIC,
    total_salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.department_name,
        COUNT(e.id) AS employee_count,
        ROUND(AVG(e.salary), 2) AS avg_salary,
        MIN(e.salary) AS min_salary,
        MAX(e.salary) AS max_salary,
        SUM(e.salary) AS total_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name
    ORDER BY total_salary DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用方法
SELECT * FROM generate_salary_report();

▶ サンプル

SQL

CREATE PROCEDURE get_user(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END;

▶ 試してみよう

❓ よくある質問

Q SQLiteはストアドプロシージャをまったくサポートしていないのですが、どうすればいいですか？

Q ストアドプロシージャとビューの違いは何ですか？

Q いつストアドプロシージャを使うべきですか？

Q ストアドプロシージャはどうデバッグしますか？

📖 まとめ

このレッスンで学んだこと：

ストアドプロシージャの概念と目的
MySQLとPostgreSQLのストアドプロシージャ構文
SQLiteのカスタム関数による代替
フロー制御文（IF/ELSE、WHILE、LOOP）
カーソルの概念と使い方
ストアドファンクションとストアドプロシージャの違い
実際のビジネスシナリオでの応用

📝 練習問題

基礎演習：部門IDをパラメータとして受け取り、その部門の給与上位3名を返すストアドプロシージャを作成してください。
中級演習：従業員の勤続年数（入社日から現在の日付まで）に基づいて勤続手当を計算する関数を作成してください：
- 1〜3年：基本給の5%
- 3〜5年：基本給の10%
- 5年以上：基本給の15%
思考問題：アプリケーション層のコードとデータベースのストアドプロシージャでビジネスロジックを処理する場合の長所と短所を比較してください。

次のレッスン → 23-triggers-events.md

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて