データベース・SQL

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

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

※ 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;

DELIMITERって

SQLの区切り文字といえば「セミコロン( ; )」だが、プロシージャを書くときは厄介もの。

CREATE PROCEDURE print_now() SELECT NOW(); な感じでプロシージャを作れるが、「;」で終了しちゃうので、複数処理書けない。

そこで、DELIMITER をつかって一時的に区切り文字を「//」に変更している。

※ 変更した区切り文字は最後にセミコロンに戻してあげる

プロシージャを書くときは、複数処理まとめたい場合が多いはずだから、「DELIMITER で囲ってBEGIN ~ ENDの間に書くもの」って覚えても良いと思う。

 

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

シナリオ

次の構成のcityテーブルがあり、これと全く同じ構成の city_new テーブルを作成して、city テーブルから city_new テーブルに指定件ずつデータを移行していく。

Field Type Key
city_id smallint unsigned PRI
city varchar(50)
country_id smallint unsigned MUL
last_update timestamp

サンプルコード

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

引数として、何件ずつ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 [プロシージャ名];

-データベース・SQL

© 2021 フリエン生活 Powered by AFFINGER5