SQLストアドプロシージャとパラメータ

SQLストアドプロシージャとパラメータ

ストアドプロシージャは、データベースに格納されるプリコンパイル済みのSQL文の集合です。プロシージャは複雑なロジックをカプセル化し、プリコンパイルによりパフォーマンスを向上させ、データへの制御されたインターフェースを提供します。

PostgreSQLの構文

CREATE OR REPLACE PROCEDURE transfer_funds(
  IN sender_id INTEGER,
  IN receiver_id INTEGER,
  IN amount DECIMAL(10,2)
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
  COMMIT;
END;
$$;

CALL transfer_funds(1, 2, 500.00);

MySQLの構文

DELIMITER //
CREATE PROCEDURE get_employee_by_dept(
  IN dept_name VARCHAR(50),
  OUT employee_count INT
)
BEGIN
  SELECT COUNT(*) INTO employee_count
  FROM employees
  WHERE department = dept_name;
END //
DELIMITER ;

CALL get_employee_by_dept('Engineering', @count);
SELECT @count;

エラー処理

CREATE OR REPLACE PROCEDURE safe_transfer(
  IN sender_id INT, IN receiver_id INT, IN amount DECIMAL
)
LANGUAGE plpgsql AS $$
DECLARE
  sender_balance DECIMAL;
BEGIN
  SELECT balance INTO sender_balance
  FROM accounts WHERE id = sender_id FOR UPDATE;

  IF sender_balance < amount THEN
    RAISE EXCEPTION 'Insufficient funds: balance is %', sender_balance;
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
END;
$$;

プロシージャと関数の比較

機能	プロシージャ	関数
戻り値	OUTパラメータ経由	値を返す
SELECTでの使用	不可	可能
トランザクション制御	COMMIT/ROLLBACK可能	通常不可
呼び出し方法	CALL	SELECTまたは式

ベストプラクティス

• トランザクション制御や複数ステップが必要な操作にはストアドプロシージャを使用する
• プロシージャは単一の責務に集中させる
• 常にエラー処理と入力バリデーションを含める
• パラメータと期待される動作をドキュメント化する
• クエリ内で戻り値が必要な場合は、プロシージャではなく関数を使用する
• プロシージャもアプリケーションコードと一緒にバージョン管理する

使用すべき場合

• 複数のアプリケーション間で一貫性が必要な複雑なビジネスロジック
• トランザクション管理が必要な操作
• バッチ処理やデータ変換
• セキュリティ: テーブルへの直接アクセスなしにEXECUTE権限を付与する

ストアドプロシージャはビジネスロジックをカプセル化するための強力なツールですが、メンテナンス性のためにアプリケーションレベルのコードとバランスを取る必要があります。