ストアドプロシージャーって何？基本的な使い方まとめ

仕事でデータ移行するときに、ストアドプロシージャを使ったので基本的なことをまとめておく。

※ MySQLでしかストアドプロシージャを使ったことがないので、MySQL前提で。

プロシージャ必要なら Ruby/PHP/Python/Go とかでスクリプト書けば良くないって思うけど、どうなんだろね？

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

DBへの一連の操作をひとまとめにして、関数のように呼び出せるもの。

プロシージャを使えば、データを1万件ずつループしてINSERT/UPDATEするみたいな処理を書くことが可能。

大規模なデータ移行のときに役立ちそう！！

ストアドプロシージャの使い方

CREATE PROCEDURE プロシージャ名で作成し、CALL プロシージャ名で実行、DROP PROCEDURE プロシージャ名で削除といった流れ。

-- プロシージャの定義
DELIMITER //
CREATE PROCEDURE sample() -- プロシージャ名
BEGIN
  -- プロシージャで実行したい処理
END
//
DELIMITER ;

-- プロシージャの実行
CALL sample();

-- プロシージャの削除
-- 「sample」部分はプロシージャ名
DROP PROCEDURE sample;
-- 個人的には、とりあえずIF EXISTSつけておく
DROP PROCEDURE IF EXISTS sample;

MySQLでのストアドプロシージャのサンプルコード

サンプルコード

プロシージャには引数を渡すこともできる。

引数として、何件ずつINSERTするのか（step）を指定できるようにしている。

DELIMITER //
CREATE PROCEDURE insert_city_new(IN step INT)
BEGIN
    -- 移行先のテーブルの最大IDから実行
    SET @start = (SELECT CASE WHEN max(city_id) IS NULL THEN 0 ELSE max(city_id) END FROM city_new);
    -- 移行元のテーブルの最大IDまで
    SET @end = (SELECT max(city_id) FROM city);
    -- ループしてstep数ずつINSERT
    WHILE @end > @start DO
        START TRANSACTION;

        INSERT INTO city_new (city_id, city, country_id, last_update)
        SELECT city_id, city, country_id, last_update FROM city
        WHERE city_id > @start AND city_id <= @start + step;

        SET @insert_count = (SELECT row_count());

        COMMIT;

        -- 進捗表示
        SELECT @insert_count, @start start_city_id;
        SET @start = @start + step;

        -- スリープしたければ
        -- SELECT sleep(1);
    END WHILE;
END
//
DELIMITER ;

実行

mysql> CALL insert_city_new(100);
+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |             0 |
+---------------+---------------+
1 row in set (0.01 sec)

+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |           100 |
+---------------+---------------+
1 row in set (0.01 sec)

+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |           200 |
+---------------+---------------+
1 row in set (0.02 sec)

+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |           300 |
+---------------+---------------+
1 row in set (0.02 sec)

+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |           400 |
+---------------+---------------+
1 row in set (0.02 sec)

+---------------+---------------+
| @insert_count | start_city_id |
+---------------+---------------+
|           100 |           500 |
+---------------+---------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

プロシージャ関連のコマンドまとめ

プロシージャの作成

DELIMITER //
CREATE PROCEDURE [プロシージャ名]
BEGIN
    -- 処理
END
//
DELIMITER ;

作成されているプロシージャ一覧

SHOW PROCEDURE STATUS \G

↑だと色々出力されるので、表示内容を絞りたい場合は information_schema.ROUTINESをSELECTする

SELECT
  ROUTINE_SCHEMA, /* ストアドプロシージャがあるデータベース */
  ROUTINE_NAME,
FROM
  information_schema.ROUTINES
WHERE 
  ROUTINE_TYPE = 'PROCEDURE';

作成されているプロシージャの内容確認

SHOW CREATE PROCEDURE [プロシージャ名];

プロシージャの削除

プロシージャが無い場合にエラーにしたくない場合はIF EXISTSをつける。

エラーになった方が良ければなくてもOK。

DROP PROCEDURE IF EXISTS [プロシージャ名];