今回は、ファンクション、ストアドプロシージャについてまとめます。
１．ファンクション
ファンクションは、以下のように一度作成してしまえば、以後、組み込み関数と同じように利用することが出来るものです。MySQLではユーザー定義関数(UDF)というC++で作成したモジュールをMYSQL内で実行する仕組みもあるのですが、このファンクションとは別のものです。実際はこのファンクションがユーザー定義関数と云うべき役割を果たします。
原則、引数と戻り値を設定して利用します。
DELIMITER//
CREATE FUNCTION tax(in_value INT) RETURNS INT
BEGIN
RETURN in_value*1.1 ;
END//
DELIMITER ;
select tax(100);
～ 結果 ～
110
２．ストアドプロシージャ
ストアドプロシージャは、複数の手続きをまとめて処理するためのものです。複雑な処理に利用する場合が多く、主に夜間やメンテナンス中、ユーザーからのアクセスがない状態で実行することが多いです。
原則、戻り値は設定せず利用します。
DELIMITER//
CREATE PROCEDURE proc_test()
BEGIN
insert into A values('1','100');-- 処理1
insert into A values('2','200');-- 処理2
END//
DELIMITER ;
call proc_test(); -- ストアドプロシージャ実行
ストアドプロシージャでは以下の「カーソル」、「IF文」を良く利用します。
２－１．カーソル
カーソルとは１レコードごと遷移して処理を行う仕組みです。非常に複雑な処理ができる反面、１レコードごと処理するため処理速度が遅く、業務時間中に実行するとDBに負荷を掛け、DB全体の遅延を招く原因にもなります。このため、どうしてもカーソルを利用しないといけない場合は、主に夜間やメンテナンス中、ユーザーからのアクセスがない状態で実行します。
DELIMITER//
CREATE PROCEDURE cursor_test()
BEGIN
DECLARE done int;
DECLARE _KEY INT;
DECLARE _DATA varchar(10);
DECLARE Cur cursor FOR --カーソルを宣言、レコードをセット
select KEY,DATA from A;
DECLARE EXIT HANDLER FOR NOT FOUND SET done=0;
set done=1;
OPEN Cur; -- カーソルオープン
WHILE done DO -- 最終レコードまで１行ずつループ
FETCH Cur INTO _KEY,_DATA;
insert into B values(_KEY,_DATA);
END WHILE;
CLOSE Cur;
END//
DELIMITER ;
call cursor_test(); -- ストアドプロシージャ実行
２－２．IF文
プログラム言語では一般的に利用できるIF文ですが、MySQLにおいては、ファンクションないしはプロシージャ内でしか利用できません。主にカーソルと組み合わせて利用します。CASE文でどうしても対応できない場合に利用します。
DELIMITER//
CREATE PROCEDURE if_test(in_value INT)
BEGIN
IF in_value<10 THEN SELECT '10未満';
ELSEIF in_value <20 THEN SELECT '20未満';
ELSE SELECT '20以上';
END IF;
END//
DELIMITER ;
call if_test(10); -- ストアドプロシージャ実行
