Go to Qiita Advent Calendar 2024 Top
LoginSignup
850
810

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@setsuna82001

ストアドプロシージャの基本的ななにか

Last updated at Posted at 2014-03-10

あんまり知識ないけどがんばる
SQLにも存在する様だけど, MySQLでしか経験がないからMySQLで書くよ

対象

以下に該当する方が対象です。
ある程度, SQLクエリが読める
ある程度, DBの知識がある

背景

資料を探せど探せど, (個人的に)良いドキュメントが見つからなかったので
『じゃあ書いちゃおう』と思いました。

※悪い資料だらけってわけじゃないけど,
物足りないとか惜しい記事ばかりだったので。

ストアドプロシージャって？

DB上での一連処理に, 名前をつけて関数のように, 呼び出して使用できるもの。
DB上で動作を完結させちゃうから, 開発言語に依存しないよ！

Ruby とか PHP だとか Perl でも Python だろうと
CALL できれば結果は同じになるはずだよ！

権限まわり

作成

CREATE ROUTINE

変更

ALTER ROUTINE

実行

EXECUTE

さんぷる

①超初級

手始めに簡単なものから。

  • 現在時間を表示
CREATE PROCEDURE sample01()
SELECT NOW();
実行結果
mysql> CALL sample01;
+---------------------+
| NOW()               |
+---------------------+
| 2014-03-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

②引数

関数として使うから入力( 引数 )も欲しいよね。

  • 入力 ( x ) に +1 して結果を表示
CREATE PROCEDURE sample02( IN x INT )
SELECT x + 1;
実行結果
mysql> CALL sample02( 2 );
+-------+
| x + 1 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

※引数が増えたら...

IN 句を増やすだけだよ。

CREATE PROCEDURE sample02_sub( IN x INT, IN y INT )
SELECT x + y;

③戻り値( 返り値 )

出力( 戻り値 )も欲しいよね。

  • 出力 ( 3 ) を @x に保存。
CREATE PROCEDURE sample03( OUT x INT )
SET x = 3;
実行結果
mysql> CALL sample03( @x );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

※戻り値を増やしたい...

IN と同じく, OUT 句を増やすだけだよ。

設定周り

①一覧の表示

一覧を見たい時ってあるよね。

  • 定義済プロシージャの一覧を表示
SHOW PROCEDURE STATUS;
実行結果
mysql> SHOW PROCEDURE STATUS;
+------+-----------------+-----------+----------------+----------
| Db   | Name            | Type      | Definer        | Modified 
+------+-----------------+-----------+----------------+----------
| test | sample01        | PROCEDURE | root@localhost | 2014-03-0
| test | sample02        | PROCEDURE | root@localhost | 2014-03-0
| test | sample02_sub    | PROCEDURE | root@localhost | 2014-03-0
| test | sample03        | PROCEDURE | root@localhost | 2014-03-0
+------+-----------------+-----------+----------------+----------
4 rows in set (0.00 sec)

②確認

あのプロシージャの中身って, どうしてたっけ？ってとき

  • 定義済プロシージャの詳細を表示
SHOW CREATE PROCEDURE sample01;
実行結果
mysql> SHOW CREATE PROCEDURE sample01;
+-----------+----------+-----------------------------------------
| Procedure | sql_mode | Create Procedure                        
+-----------+----------+-----------------------------------------
| sample01  |          | CREATE DEFINER=`root`@`localhost` PROCED
+-----------+----------+-----------------------------------------
1 row in set (0.00 sec)

③削除

このプロシージャ, もういらないよね。

  • 定義済プロシージャを削除
DROP PROCEDURE sample01;
実行結果
mysql> DROP PROCEDURE sample01;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE sample01;
ERROR 1305 (42000): PROCEDURE sample01 does not exist

おうよう

①複数定義

セミコロン( ; )で定義が完了しちゃうから, 複数のクエリが発行できないよね。
そんな時は DELIMITER を使って, 区切り文字を一時的に変更！
( 変更した区切り文字は, 戻してあげないと変更されたままだよ )
BEGIN ~ END 内に, 複数行のプロシージャを記述しよう。

  • 1 と 2 を表示するプロシージャ
DELIMITER //
CREATE PROCEDURE sample01()
BEGIN
  SELECT 1;
  SELECT 2;
END
//
DELIMITER ;
実行結果
mysql> CALL sample01;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

②分岐

入力値で分岐とかしたいね。
基本的な IF (条件) THEN ~ ELSEIF (条件) THEN ~ ELSE ~ END IF 文だよ。

  • 入力値 ( x ) によって条件分岐した結果を表示
DELIMITER //
CREATE PROCEDURE sample02( IN x INT )
BEGIN
IF x = 1 THEN
  SELECT "input is 1";
ELSEIF x = 2 THEN
  SELECT "input is 2";
ELSE
  SELECT "input is else";
END IF;
END
//
DELIMITER ;
実行結果
mysql> CALL sample02( 2 );
+------------+
| input is 2 |
+------------+
| input is 2 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

③SELECT ~ INTO ~

計算結果とか欲しいよね。
SELECT ~ INTO ~ 文で, INTO の後ろに指定した変数に, SELECT 結果をセットできるよ！
SELECT ~ INTO ~ FROM ~ WHERE ~ みたいに, 通常の SELECT 文と同じ動きをするよ。

  • 入力値 ( x , y ) の 和 と 差 を 変数へ出力
DELIMITER //
CREATE PROCEDURE sample03( IN x INT, IN y INT, OUT sum INT, OUT sub INT )
BEGIN
  SELECT x + y INTO @sum;
  SELECT x - y INTO @sub;
  SET sum = @sum;
  SET sub = @sub;
END
//
DELIMITER ;
実行結果
mysql> CALL sample03( 10, 1, @x, @y );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

mysql> SELECT @y;
+------+
| @y   |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

④DECLARE, CURSOR, FETCH

レコード単位で処理したい時ってあるよね！
DECLARE で 変数宣言 するよ。
CURSOR 型 っていう, レコード用変数定義を DECLARE ~ CURSOR ってふうに宣言できるよ！
続けて FOR SELECT ~ で, SELECT 結果を定義するよ！

読み出しは, FETCH ~ CURSOR FOR ~ って記述すると, 各カラムに代入できるよ。
ただし, カーソルは FETCH する前に OPEN してあげる必要があって,
処理が終わったら CLOSE してあげよう！

  • 処理テーブル ( test_tbl ) から, 指定したID( in_id ), 文字列( in_str )を探索
  • 指定ID ( in_id ) か 指定文字列( in_str )が見つかれば, 若いID と, その文字列を返す
  • 見つからなければ, 0 と 空文字( '' ) を返す
処理テーブル
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

DELIMITER //
CREATE PROCEDURE sample04
( IN in_id INT, IN in_str VARCHAR(10), OUT out_id INT, OUT out_str VARCHAR(10) )
BEGIN
  # 変数宣言！
  DECLARE myId  INT;            #  id カラムの格納用だよ
  DECLARE myStr VARCHAR( 10 );  # str カラムの格納用だよ
  DECLARE myCur CURSOR FOR SELECT id, str FROM test_tbl ;
  # test_tbl の SELECT 結果を格納する カーソルだよ

  # ループ用に変数宣言！
  SET @pos    = 0;                           # 何レコード目か
  SELECT COUNT(*) INTO @total FROM test_tbl; # 全部で何レコードか
  
  SET out_id  = 0;  # 入力ID   ( in_id  )が見つからなかった時のための宣言
  SET out_str = ''; # 入力文字列( in_str )が見つからなかった時のための宣言
  
  # カーソル開けごま！
  OPEN myCur;
  
  
  WHILE @total > @pos DO
    # 1レコード読みだして,  各変数へ代入
    FETCH myCur INTO myId, myStr;

    # 入力ID か 入力文字列 が見つかったら,  出力値として設定
    IF myId = in_id OR myStr = in_str THEN
      SET out_id  = myId;
      SET out_str = myStr;
      SET @pos    = @total; # WHILE を抜け出すために設定！
    END IF;

    # 見つからなかったら, @posが増えるね！
    SET @pos = @pos +1;
  END WHILE;
  
  # カーソルありがとう！
  CLOSE myCur;

END
//
DELIMITER ;
実行結果：入力値見つかったVer.
mysql> CALL sample04( 4, 'cc', @id, @str );
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @id;
+------+
| @id  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> SELECT @str;
+------+
| @str |
+------+
| cc   |
+------+
1 row in set (0.00 sec)
実行結果:入力値なかったよVer.
mysql> CALL sample04( 0, 'ee', @id, @str );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @id;
+------+
| @id  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT @str;
+------+
| @str |
+------+
|      |
+------+
1 row in set (0.00 sec)

⑤例外処理

エラーなんて出してなんぼだよね！←
MySQL上にも例外処理の考えがあるよ！

DECLARE ~ HANDLER FOR ~ って宣言を使うよ！

FOR 以降の条件をハンドラがキャッチして,
DECLARE ~ HANDLER 間に記述した ハンドラタイプ で, 処理を実行しちゃいます！

ハンドラタイプには次の２つがるよ。

ハンドラタイプ 処理内容
CONTINUE 実行制御を元の場所に戻す
EXIT プログラムを終了

キャッチするハンドラは, 主に次の３パターンじゃないかな？と思ってる！

ハンドラ 処理内容
SQLWARNING '01'で始まるSQLSTATE
NOT FOUND '02'で始まるSQLSTATE
SQLEXCEPTION '02', '01' 以外で始まるSQLSTATE

SQLSTATEについては『ここ』とか『ここ』とか『ここ』を参考にするといいかも。

④で使用した, 処理テーブル( test_tbl )を使って例示するね！

●サンプルA

  • 処理テーブル ( test_tbl ) から, 指定したID( in_id )を探索
  • 指定ID ( in_id )が見つかれば, そのIDを返す
  • 見つからなければ, -2を返す
DROP PROCEDURE sample05a;
DELIMITER //
CREATE PROCEDURE sample05a ( IN in_id INT, OUT num INT )
BEGIN
  # ハンドラの宣言！
  DECLARE EXIT HANDLER FOR NOT FOUND SET num = -2;
  # SELECT 結果が見つからなかった( NOT FOUND )場合
  # 『 SET num = -2 』を実行して, 処理を終了( EXIT ) する
  # という宣言


  # @num に SELECT 結果を代入
  SELECT id INTO @num FROM test_tbl WHERE id = in_id;

  # in_idが見つかれば, 見つかったIDが, 
  # 見つからなかった場合は -2 が 返ります。
  SET num = @num;
END
//
DELIMITER ;
実行結果:入力値見つかったVer.
mysql> CALL sample05a( 4, @x );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)
実行結果:入力値なかったよVer.
mysql> CALL sample05a( 0, @x );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|   -2 |
+------+
1 row in set (0.00 sec)

●サンプルB

  • 処理テーブル ( test_tbl ) から, 指定したID( in_id )を探索
  • 指定ID ( in_id )が見つかれば, そのIDを -1 へ更新し, -1 を返す
  • 見つからなければ, 0 を返す

※前提条件として, test_tbl の id カラムは UNSIGNED INT とします

DELIMITER //
CREATE PROCEDURE sample05b ( IN in_id INT, OUT num INT )
BEGIN
  # ハンドラの宣言！
  DECLARE EXIT HANDLER FOR SQLWARNING SET num = -1;
  # 警告 が 発生( SQLWARNING )した場合
  # 『 SET num = -1 』を実行して, 処理を終了( EXIT ) する
  # という宣言

  # 警告が発生する前に戻り値を宣言しておきます
  SET num = 0;

  # 指定ID ( in_id ) の id を -1 へ変更するというクエリ
  UPDATE test_tbl SET id = -1 WHERE id = in_id;
  
END
//
DELIMITER ;
実行結果:入力値見つかったVer.
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

mysql> CALL sample05b( 1, @x );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|   -1 |
+------+
1 row in set (0.00 sec)

mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  0 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)
実行結果:入力値なかったよVer.
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

mysql> CALL sample05b( 6, @x );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

●サンプルC

SQLSTATE を直に指定する方法もあるよ！
DECLARE ~ HANDLER FOR SQLSTATE 'xxxxx' って感じで宣言してね！

  • 処理テーブル ( test_tbl ) に, 指定したID( in_id )と文字列 ** を新規登録
  • 指定ID ( in_id )が重複していれば -3 を返す
  • 登録に成功すれば, 0 を返す
DELIMITER //
CREATE PROCEDURE sample05c ( IN in_id INT, OUT num INT )
BEGIN
  # ハンドラの宣言！
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET num = -3;
  # 重複キーエラー が 発生( SQLSTATE '23000' )した場合
  # 『 SET num = -3 』を実行して, 処理を続行( CONTINUE ) する
  # という宣言

  # 警告が発生する前に戻り値を宣言しておきます
  SET num = 0;

  # 指定ID ( in_id ) の レコード を 追加するクエリ
  INSERT INTO test_tbl VALUES ( in_id, '**' );
  
END
//
DELIMITER ;
実行結果:重複しちゃったVer.
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

mysql> CALL sample05c( 3, @x );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|   -3 |
+------+
1 row in set (0.00 sec)

mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)
実行結果:登録に成功Ver.
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)

mysql> CALL sample05c( 6, @x );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mmysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
|  6 | **  |
+----+-----+
5 rows in set (0.00 sec)

⑥宣言順序

変数, カーソル, ハンドラの宣言を同時に行う際は注意が必要だよ！
変数, カーソル, ハンドラの順に宣言しないとエラーになるから気をつけてね！

宣言に失敗ver.
mysql> DELIMITER //
mysql> CREATE PROCEDURE sample06 ( OUT num INT )
    -> BEGIN
    ->
    ->   #カーソル宣言！
    ->   DECLARE myCur CURSOR FOR SELECT * FROM test_tbl ;
    ->
    ->   # ハンドラの宣言！
    ->   DECLARE EXIT HANDLER FOR SQLWARNING   SET num = -1;
    ->
    ->   # 変数宣言！
    ->   DECLARE myId  INT;
    ->
    ->   # 戻り値を宣言
    ->   SET num = 0;
    -> END
    -> //
ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration
宣言に成功ver.
mysql> DELIMITER //
mysql> CREATE PROCEDURE sample06 ( OUT num INT )
    -> BEGIN
    ->
    ->   # 変数宣言！
    ->   DECLARE myId  INT;
    ->
    ->   #カーソル宣言！
    ->   DECLARE myCur CURSOR FOR SELECT * FROM test_tbl ;
    ->
    ->   # ハンドラの宣言！
    ->   DECLARE EXIT HANDLER FOR SQLWARNING   SET num = -1;
    ->
    ->   # 戻り値を宣言
    ->   SET num = 0;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

以上です！
何かありましたらコメントください。

850
810
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
850
810

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?