MySQL | SQLite経験者向け、30日で習得するMySQL：実務SQL力 － Day11 ストアドプロシージャ①

SQL MySQL
スポンサーリンク

Day11 前半のゴール

「“SQL版の関数”としてストアドプロシージャをイメージできるようになる」

ここからは、いよいよ「DBの中で動くプログラム」、ストアドプロシージャの世界に入ります。
SQLite には基本的にストアドプロシージャはなく、アプリ側のコード（Python / PHP / JavaScript など）にロジックを書いてきたはずです。

MySQL では、こういうことができます。

複数のSQLをまとめて「1つの処理」としてDB側に定義する
引数を受け取って、条件に応じてINSERT / UPDATE / SELECTを切り替える
IF や WHILE など、プログラミングっぽい制御構文をSQLの世界で使う

Day11 前半のゴールは、

ストアドプロシージャ＝「DBの中に置く関数」のイメージを持つ
CREATE PROCEDURE の基本構文を理解する
CALL で呼び出す流れを、具体例でイメージできる

ここまで行ければ、後半の「変数・IF・トランザクション」の話がスムーズに入ってきます。

ストアドプロシージャとは何か

「“DBの中に置く関数”であり、“SQLの塊に名前を付けたもの”」

まずはイメージから固めます。

テーブル
データを入れておく箱

ビュー
SELECT文に名前を付けた“仮想テーブル”

ストアドプロシージャ
複数のSQLやロジックに名前を付けた“DB内の関数”

アプリ側から見ると、

「この処理をやりたい」ときに
毎回長いSQLを組み立てて送るのではなく、

DB側にあらかじめ「処理」を定義しておいて、
CALL プロシージャ名(引数...) と呼び出すだけで済む、というイメージです。

いちばんシンプルなストアドプロシージャ

「“特定ユーザーの注文一覧を返す”処理に名前を付ける」

まずは「SELECTをラップするだけ」の超シンプルな例からいきます。

前提として、いつものテーブルがあるとします。

CREATE TABLE users (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  user_id  INT NOT NULL,
  total    INT NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
) ENGINE=InnoDB;
SQL

「特定ユーザーの注文一覧を返すSELECT」は、普通こう書きます。

SELECT
  o.id,
  o.total
FROM orders o
WHERE o.user_id = 1;
SQL

これを「ユーザーIDを引数に取るストアドプロシージャ」にすると、こうなります。

CREATE PROCEDURE の基本構文

「DELIMITER と BEGIN … END にまず慣れる」

MySQL のストアドプロシージャは、だいたいこんな形をしています。

DELIMITER //

CREATE PROCEDURE プロシージャ名(引数...)
BEGIN
  -- ここに複数のSQLを書く
END //

DELIMITER ;
SQL

ここで初登場のポイントが2つあります。

DELIMITER
MySQLクライアントは通常 ; を「SQLの終わり」として認識しますが、ストアドプロシージャの中では ; をたくさん使いたいので、一時的に区切り文字を // などに変えています。

BEGIN … END
複数のSQL文を1つのブロックとしてまとめるための構文です。プログラミング言語の { ... } に近いイメージです。

これを踏まえて、さっきの「特定ユーザーの注文一覧」をプロシージャにしてみます。

例：ユーザーの注文一覧を返すプロシージャ

「IN パラメータで値を受け取る」

DELIMITER //

CREATE PROCEDURE get_user_orders(IN p_user_id INT)
BEGIN
  SELECT
    o.id,
    o.total
  FROM orders o
  WHERE o.user_id = p_user_id;
END //

DELIMITER ;
SQL

ここでのポイントを分解します。

プロシージャ名：get_user_orders
引数：IN p_user_id INT

IN は「呼び出し側から値を受け取る」パラメータであることを示します。
p_user_id はプロシージャ内で使う変数名です（慣習的に p_ を付ける人も多い）。

プロシージャの中身は、
さっきの SELECT とほぼ同じですが、
WHERE o.user_id = 1 が WHERE o.user_id = p_user_id に変わっています。

プロシージャの呼び出し方

「CALL で“関数を実行する”イメージ」

定義したプロシージャは、こう呼び出します。

CALL get_user_orders(1);
SQL

これで、

ユーザーID=1 の注文一覧

が結果セットとして返ってきます。

アプリ側から見ると、

毎回 SELECT ... WHERE user_id = ? を組み立てる代わりに、
CALL get_user_orders(?) を呼ぶだけで済む、という形です。

ここで大事なのは、

ストアドプロシージャは「SQLを返す」のではなく、
「中でSQLを実行して、その結果を返す」

という点です。

なぜストアドプロシージャを使うのか

「“DB側にロジックを寄せる”ことの意味」

「これ、アプリ側でSELECT書けばよくない？」
と感じるかもしれません。

シンプルな例ではその通りですが、
ストアドプロシージャが本領を発揮するのは、
次のような場面です。

複数のテーブルにまたがるINSERT / UPDATE / DELETEを、1つの処理としてまとめたい
条件によって、実行するSQLを切り替えたい（IF / CASE）
トランザクションをDB側で完結させたい

例えば、

注文を作る
在庫を減らす
ログを残す

といった一連の処理を、
アプリ側からバラバラにSQLを投げるのではなく、
DB側のストアドプロシージャにまとめておくと、

処理のまとまりがはっきりする
途中でエラーが起きたときにロールバックしやすい
アプリ側のコードがシンプルになる

といったメリットがあります。

Day11 前半では、
まだ「SELECTをラップするだけ」で十分ですが、
頭の片隅に「複数SQLをまとめる器」というイメージを置いておいてください。

SQLite と MySQL の“ロジックの置き場所”の違い

「SQLite時代は“全部アプリ側”、MySQLでは“DB側に寄せる”選択肢が増える」

SQLite を使っていたときは、

DBはあくまで“データを置く場所”
ロジックは全部アプリ側のコード

という構成がほとんどだったはずです。

MySQL では、

ビューで「複雑なSELECT」をDB側に寄せる
ストアドプロシージャで「複数SQL＋ロジック」をDB側に寄せる

という選択肢が増えます。

これにより、

複数のアプリから同じ処理を呼び出せる
DB側で一貫したルールを守れる（セキュリティ・整合性）
アプリごとに微妙に違うSQLを書く、という事故を減らせる

といったメリットが出てきます。

もちろん「何でもかんでもストアドに入れろ」という話ではありません。
Day11〜12 では、「どこまでDB側に寄せるのが気持ちいいか」を探る回でもあります。

セキュリティの観点から見たストアドプロシージャ

「“やってよい操作”をプロシージャ単位で定義できる」

ストアドプロシージャは、セキュリティ的にも面白い武器になります。

例えば、

アプリユーザーにはテーブルへの直接INSERT権限を与えず、
create_order というプロシージャだけ実行できるようにする

という構成が取れます。

この場合、

どのテーブルにどうINSERTするか
どんなバリデーションをするか
どんなログを残すか

は、すべてストアドプロシージャの中に閉じ込められます。

アプリ側は「許可されたプロシージャを呼ぶだけ」なので、
うっかり危険なSQLを直接叩くリスクを減らせます。

Day11 前半では、
まだそこまで踏み込みませんが、

ストアドプロシージャ＝「DB側に置く、安全な入り口」

という視点も、頭の片隅に置いておくと後で効いてきます。

Day11 前半のまとめ

ストアドプロシージャは「DBの中に置く関数」のようなもので、CREATE PROCEDURE 名前(IN 引数 型, ...) BEGIN ... END の形で定義し、CALL 名前(値...) で実行すると、中に書かれた複数のSQLがまとめて実行され、その結果セットが返ってくる。
MySQL では、ストアドプロシージャを定義するときに DELIMITER を一時的に変更し、BEGIN ... END ブロックの中で ; を自由に使えるようにしてから、最後に元の ; に戻す、という書き方に慣れる必要があり、これは「1つのプロシージャの中に複数のSQLを書くための器」を用意していると考えると理解しやすい。
シンプルな例として、「特定ユーザーの注文一覧を返すSELECT」を get_user_orders(IN p_user_id INT) のようなプロシージャにしておくと、アプリ側は CALL get_user_orders(?) と呼ぶだけで済み、今後はここにロジック（条件分岐・トランザクション・ログ記録など）を足していくことで、「複数SQLを1つの処理としてDB側に寄せる」設計ができるようになる。

後半では、
プロシージャ内の変数、IF / CASE、トランザクション制御、
そして「どこまでストアドに寄せるか」という設計の考え方を、
具体的な例と一緒に掘り下げていきます。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました