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

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

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


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

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

# ストアドプロシージャって？
DB上での一連処理に, 名前をつけて関数のように, 呼び出して使用できるもの。
DB上で動作を完結させちゃうから, 開発言語に依存しないよ！

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



# 権限まわり
## 作成
```sql
CREATE ROUTINE
```

## 変更
```sql
ALTER ROUTINE
```

## 実行
```sql
EXECUTE
```


# さんぷる
## ①超初級
手始めに簡単なものから。

* 現在時間を表示


```sql
CREATE PROCEDURE sample01()
SELECT NOW();
```

```bash:実行結果
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` して結果を表示

```sql
CREATE PROCEDURE sample02( IN x INT )
SELECT x + 1;
```

```bash:実行結果
mysql> CALL sample02( 2 );
+-------+
| x + 1 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
```

### ※引数が増えたら...
`IN` 句を増やすだけだよ。

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


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

* 出力 ( `3` ) を `@x` に保存。

```sql
CREATE PROCEDURE sample03( OUT x INT )
SET x = 3;
```

```bash:実行結果
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` 句を増やすだけだよ。


# 設定周り
## ①一覧の表示
一覧を見たい時ってあるよね。

* 定義済プロシージャの一覧を表示

```sql
SHOW PROCEDURE STATUS;
```

```bash:実行結果
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)
```


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

* 定義済プロシージャの詳細を表示

```sql
SHOW CREATE PROCEDURE sample01;
```

```bash:実行結果
mysql> SHOW CREATE PROCEDURE sample01;
+-----------+----------+-----------------------------------------
| Procedure | sql_mode | Create Procedure                        
+-----------+----------+-----------------------------------------
| sample01  |          | CREATE DEFINER=`root`@`localhost` PROCED
+-----------+----------+-----------------------------------------
1 row in set (0.00 sec)
```

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

* 定義済プロシージャを削除

```sql
DROP PROCEDURE sample01;
```

```bash:実行結果
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` を表示するプロシージャ

```sql
DELIMITER //
CREATE PROCEDURE sample01()
BEGIN
  SELECT 1;
  SELECT 2;
END
//
DELIMITER ;
```

```bash:実行結果
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` ) によって条件分岐した結果を表示


```sql
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 ;
```

```bash:実行結果
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` ) の 和 と 差 を 変数へ出力

```sql
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 ;
```

```bash:実行結果
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` と `空文字( '' )` を返す

```bash:処理テーブル
mysql> select * from test_tbl;
+----+-----+
| id | str |
+----+-----+
|  1 | aa  |
|  2 | bb  |
|  3 | cc  |
|  4 | dd  |
+----+-----+
4 rows in set (0.00 sec)
```

```sql
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 ;
```

```bash:実行結果：入力値見つかった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)
```

```bash:実行結果:入力値なかったよ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については『[ここ](http://msdn.microsoft.com/ja-jp/library/ms714687.aspx "Microsoft")』とか『[ここ](http://dev.mysql.com/doc/refman/4.1/ja/error-returns.html "MySQLリファレンス")』とか『[ここ](http://software.fujitsu.com/jp/manual/manualfiles/M060051/J2X01637/02Z200/sqlbgab/sqlbg092.html "富士通")』を参考にするといいかも。


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

### ●サンプルA

* 処理テーブル ( `test_tbl` ) から, 指定したID( `in_id` )を探索
* 指定ID ( `in_id` )が見つかれば, そのIDを返す
* 見つからなければ, `-2`を返す

```sql
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 ;
```

```bash:実行結果:入力値見つかった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)
```

```bash:実行結果:入力値なかったよ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` とします

```sql
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 ;
```

```bash:実行結果:入力値見つかった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)
```

```bash:実行結果:入力値なかったよ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` を返す

```sql
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 ;
```

```bash:実行結果:重複しちゃった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)
```

```bash:実行結果:登録に成功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)
```


## ⑥宣言順序

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

```bash:宣言に失敗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
```

```bash:宣言に成功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)
```

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