連載
» 2004年04月20日 00時00分 公開

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)
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)

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

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

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。