MySQL 5.0のストアドプロシージャをチェック：エンタープライズ市場に向かうMySQL 5.0（後編）（2/3 ページ）

[鶴長鎮一，MySQLユーザ会]

ストアドプロシージャ／ファンクションの動作

　ストアドプロシージャの利便性を向上させるには、より複雑な構文が必要です。以下の例は、指定されたID番号以上の地域、県名を拾い出し、その中に愛知県が含まれている場合はメッセージ「指定範囲に愛知県を含みます」を返します。

mysql> CREATE PROCEDURE simpleproc3(IN param1 int,OUT param2 VARCHAR(50))　（1）
    -> BEGIN
    ->   DECLARE myStr VARCHAR(50);　（2）
    ->   DECLARE myNo INT;
    ->   DECLARE myArea VARCHAR(50);
    ->   DECLARE myPref VARCHAR(50);
    ->   DECLARE myCur CURSOR FOR SELECT * FROM REGION WHERE NO > param1;　（3）
    ->
    ->   SET param2 = '指定範囲に愛知県は見つかりません';
    ->
    ->   OPEN myCur;　（4）
    ->   SELECT COUNT(*) INTO @myCounter FROM REGION WHERE NO > param1;　（5）
    ->   SET @pos = 0;
    ->   WHILE @myCounter > @pos DO　（6）
    ->     FETCH myCur INTO myNo,myArea,myPref;
    ->     IF MyPref ='愛知県' THEN
    ->       SET param2 = '指定範囲に愛知県を含みます';
    ->     END IF;
    ->     SET @pos = @pos +1;
    ->   END WHILE;
    ->
    ->   CLOSE myCur;
    -> END
    -> //

注：上記の作業をスクリプト化したsimpleproc3.sqlを用意しました。

　simpleproc3では、WHILE文やIF文といった制御文を使用するなど、より複雑な処理を実行しています。

　今回は、引数を受け取ります。（1）のように、戻り値と引数はOUT／INで区別します。

　ストアドプロシージャsimpleproc3の中でのみ有効なローカル変数を、（2）以降で宣言します。（3）はカーソルの宣言です。「戻り値を伴う例」のsimpleproc2では、実行されたSQLクエリーの結果は件数を表す単一のものでしたが、今回は複数行の結果セットを必要とします。そこでカーソルを利用し、結果セットをカーソルを用いて操作します。カーソルはDECLARE→OPEN→FETCH→CLOSEの順で使用し、結果セットを1件ずつ処理できます。

　（4）でカーソルをオープンにしますが、あらかじめ何件の該当データがあるのかを（5）を使って調べておき、その件数分だけWHILEループを実行します（6）。（5）でも、先ほどのSELECT …… INTO 変数クエリーを使用します。WHILEループの中では、結果セットを1件ずつ抽出し、ID、地域名、県名をローカル変数に格納しています。愛知県のデータを見つけた場合は、戻り値となるparam2の値を「指定範囲に愛知県を含みます」とします。一件の処理が終了した場合、変数@posをカウントアップし、WHILEで「（5）で調べた該当件数を上回る」が判定され、実行されます。

　では実行します。今回は日本語を含んでいるため、mysqlクライアントに上記のように直接タイプできない場合があります。その場合は、いったんプロシージャの内容をファイルに保存し（simpleproc3.sql）、以下のようにmysqlクライアントでファイルの読み込み操作を行います。

mysql> source simpleproc3.sql
Query OK, 0 rows affected (0.01 sec)

　準備が完了したら実行です。例では引数に10を使用していますが、試しに47までの任意の整数を入力してみましょう。SELECTで変数を展開する際は、ストアドプロシージャ内で宣言した@myCounterや@posの値も見てみましょう。ストアドプロシージャ内でDECLAREで宣言した変数（myStrなど）は、プロシージャ実行後は破棄されます。しかし、@で宣言した変数は、プロシージャ実行後も使用可能です。

mysql> call simpleproc3(10,@a)
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> select @myCounter,@pos,@a
    -> //
+------------+------+----------------------------+
| @myCounter | @pos | @a                         |
+------------+------+----------------------------+
| 37         | 37   | 指定範囲に愛知県を含みます |
+------------+------+----------------------------+
1 row in set (0.00 sec)
mysql> call simpleproc3(20,@a)
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> select @myCounter,@pos,@a
    -> //
+------------+------+----------------------------------+
| @myCounter | @pos | @a                               |
+------------+------+----------------------------------+
| 27         | 27   | 指定範囲に愛知県は見つかりません |
+------------+------+----------------------------------+
1 row in set (0.00 sec)

ストアドファンクションの作成

　ストアドプロシージャと同様の手続きで、ストアドファンクションを定義できます。次の例では、CONCAT()を用いて文字列の結合を行っています。

mysql> CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

注：上記の作業をスクリプト化したhello.sqlを用意しました。

mysql> SELECT hello('world')
    -> //
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.01 sec)

作成したプロシージャ／ファンクションの確認と削除

　サーバ内に作成されたプロシージャやファンクションは、以下のようにすることで確認できます。

mysql> SHOW PROCEDURE STATUS
    -> //
+-------------+-----------+----------------+---------------------+
---------------------+---------------+---------+
| Name        | Type      | Definer        | Modified            |
 Created             | Security_type | Comment |
+-------------+-----------+----------------+---------------------+
---------------------+---------------+---------+
| simpleproc1 | PROCEDURE | root@localhost | 0000-00-00 00:00:00 |
 2004-03-27 15:38:10 | DEFINER       |         |
| simpleproc2 | PROCEDURE | root@localhost | 0000-00-00 00:00:00 |
 2004-03-27 15:49:23 | DEFINER       |         |
| simpleproc3 | PROCEDURE | root@localhost | 0000-00-00 00:00:00 |
 2004-03-27 15:07:39 | DEFINER       |         |
+-------------+-----------+----------------+---------------------+
---------------------+---------------+---------+
3 rows in set (0.00 sec)

リスト1　折り返しなし表示

mysql> SHOW FUNCTION STATUS
    -> //
+-------+----------+----------------+---------------------+--------
-------------+---------------+---------+
| Name  | Type     | Definer        | Modified            | Created
             | Security_type | Comment |
+-------+----------+----------------+---------------------+--------
-------------+---------------+---------+
| hello | FUNCTION | root@localhost | 0000-00-00 00:00:00 | 2004-03
-27 15:43:57 | DEFINER       |         |
+-------+----------+----------------+---------------------+--------
-------------+---------------+---------+
1 row in set (0.00 sec)

リスト2　折り返しなし表示

　プロシージャやファンクションを削除する場合は、DROPクエリーを利用します。

mysql> DROP PROCEDURE simpleproc1
    -> //
Query OK, 0 rows affected (0.00 sec)