現場での実践SQL(7)完 | IT SKILL MAP

現場での実践SQL(7)完

 今回は、ファンクション、ストアドプロシージャについてまとめます。

目次
2- 1.カーソル
2- 2.IF文

1.ファンクション

 ファンクションは、以下のように一度作成してしまえば、以後、組み込み関数と同じように利用することが出来るものです。MySQLではユーザー定義関数(UDF)というC++で作成したモジュールをMYSQL内で実行する仕組みもあるのですが、このファンクションとは別のものです。実際はこのファンクションがユーザー定義関数と云うべき役割を果たします。
 原則、引数と戻り値を設定して利用します。

DELIMITER//
CREATE FUNCTION tax(in_value INT) RETURNS INT
BEGIN
	RETURN in_value*1.08 ;
END//

DELIMITER ;

select tax(100);

 ~ 結果 ~ 

 108

2.ストアドプロシージャ

 ストアドプロシージャは、複数の手続きをまとめて処理するためのものです。複雑な処理に利用する場合が多く、主に夜間やメンテナンス中、ユーザーからのアクセスがない状態で実行することが多いです。
 原則、戻り値は設定せず利用します。

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文」を良く利用します。

2-1.カーソル

 カーソルとは1レコードごと遷移して処理を行う仕組みです。非常に複雑な処理ができる反面、1レコードごと処理するため処理時間が遅く、業務時間中に実行すると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 -- 最終レコードまで1行ずつループ
		FETCH Cur INTO _KEY,_DATA;
		insert into B values(_KEY,_DATA);
		END WHILE;
	CLOSE Cur;

END//

DELIMITER ;

call cursor_test(); -- ストアドプロシージャ実行

2-2.IF文

 プログラム言語では一般的に利用できるIF文ですが、MySQLにおいては、ファンクションないしはプロシージャ内でしか利用できません。主にカーソルと組み合わせて利用します。


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); -- ストアドプロシージャ実行


おすすめの関連記事

現場での実践SQL(6)
現場での実践的なSQL知識をまとめています。(6)ではINSERT、UPDATE、DELETEのテクニックについてまとめています。