読者です 読者をやめる 読者になる 読者になる

ただの技術者ぶろぐ

大阪に住んでる技術者が書く覚書ぶろぐ。

MySQLのストアドとファンクションで遊ぶ その2

今回のテーマは引数、変数、返り値あたり。

前回の例文

DELIMITER $$
CREATE  PROCEDURE `demo_procedure`()
BEGIN
   select 'hogehoge' as display_text;
END$$
DELIMITER ;

このストアドを使うとdisplay_textというカラムの'hogehoge'という文字列を取得できるのですが、大体のケースではこのような使い方はしないと思います。

ストアドプロシージャの特徴として「戻り値がない」事が挙げられます。つまり、'hogehoge'を返すよう使い方が実際の運用では使えないのです。(管理ツール上、MySQLコマンドラインなどで利用するのは可能です)

こういう場合は、返したい値の内容に合わせて色々使いわけをする必要があります。たとえば、単数の返り値を取得したい場合であればファンクション、結果テーブルを取得したい場合などでは、実テーブルやテンポラリーテーブルを利用する必要があります。

余談ですが、MySQLにもOracle的なテンポラリーテーブルが使えるのですが、あんまり流行ってないご様子です。もったいないなぁ。

ファンクションの利用:

 例:都道府県コードから都道府県名のみを取得したい場合

pref_table

 CREATE TABLE `pref_table` (
  `code` smallint(11) NOT NULL,
  `name` varchar(50) DEFAULT '',
  PRIMARY KEY (`code`)
);
code name
1 北海道
2 青森県
3 岩手県
... ...

今回、テーブルの値を検索して結果を返すという処理が必要なのでテーブルを準備します。

get_pref

DELIMITER $$
CREATE FUNCTION `get_pref` 
($pref_code int)
RETURNS varchar(50)
BEGIN
-- 都道府県名
DECLARE pref_name varchar(50) default ''; 

-- 都道府県名を取得する
SELECT 
    name
into pref_name
From pref_table
where code = $pref_code
;

-- 取得結果を返す
RETURN pref_name;
END$$
DELIMITER ;

結果

SET @pref_code = 1;
select get_pref(@pref_code);
 =>北海道

前回のストアドの説明のときになかった項目のひとつに、

CREATE FUNCTION後の「($pref_code int)」という記述があります。また、その後にRETURNS varchar(50)という構文が追加されています。

これらはそれぞれ「($pref_code int)」を引数やパラメーター、「RETURNS varchar(50)」を返り値と呼ばれたりします。

これは、ファンクションに必須の構文で、返り値として変数型の内容を返すという表現です。数値を返す場合ならRETURNS integerやRETURNS smallintと記述します。

DECLARE pref_name varchar(50) default ''; はストアド・ファンクションの中で変数を宣言する構文で、 「DECLARE 変数名 型 デフォルト値(省略可)」と記述します。

'''mysql SELECT name into pref_name From pref_table where code = $pref_code ''' 以上のSELECT~INTO文は単項目のデータを取得しています。

WHERE句のキーに一致した値を変数に格納してくれますが、 複数行返るようなケースではエラーが発生します。

複数件を返すような処理は次回以降の記事にして、 今日はおしまいです。