mysql(5.1) + カーソルloop使ったプログラムのテンプレ

たまに、ツールとしてデータベースプログラムを書くのですが、１から書くのではなく、いつもどこかの作成済みプログラムを流用しているので、流用も面倒くさくなり、テンプレ化することにしました。
他の人にも使ってもらえるよう、コメント多めです。

【概略】
データを取得して、繰り返しする処理のテンプレです。
データベースへの書き込み処理も考慮してトランザクションも記載してあります。

【コード】

-- プロシージャ入れ替え用のDROP
DROP PROCEDURE IF EXISTS PROCEDURENAME;
delimiter ///
CREATE DEFINER = `root`@`localhost` PROCEDURE `PROCEDURENAME`
 (IN param1 VARCHAR(10)
 ,IN param2 VARCHAR(10))
BEGIN
    -- Loop検知・保持する変数
    DECLARE done INT DEFAULT 0;
    -- 通常変数を記載する際にはカーソルの前に書く
    DECLARE local_aa VARCHAR(10);
    DECLARE local_bb VARCHAR(10);
    -- カーソル定義
    DECLARE l_cur CURSOR FOR select a ,b from xx; -- カーソル定義

-- データなしを検知するハンドラ
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 例外を検知するハンドラ
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET autocommit=1;
    SELECT 0 AS RESULT;
END;

-- メイン処理
-- オートコミットを切って、自立トランザクションにしています
    SET autocommit=0;
    START TRANSACTION;

-- カーソルを開く
    open l_cur;
-- 繰り返し処理
    REPEAT
-- 1件データ取得
        FETCH l_cur into local_aa , local_bb;
-- EOF判定
        IF NOT done THEN
            -- データ処理
            -- これを書いておくとコンソールにデバッグのように出力される
            SELECT now() AS CURRENT_DATETIME local_aa AS aa , local_bb AS bb;

            -- この例では別ストアドを呼ぶ
            call sub_procedure(param1,param2,local_aa,local_bb);
        END IF;

    UNTIL done END REPEAT;

-- 後片づけ
    close l_cur;

    COMMIT;
    SET autocommit=1;

-- 呼び出し元用に正常終了を返す    
    SELECT 1 AS RESULT;

end;
///
delimiter ;

【Tips】mysqlのfunctionでテキストの行数を数える

いつ何のために作ったか忘れたのですが、ごみ箱行きはもったいないのでここに残しておきます。

キーワード：
・テキストの行数を数える
・functionの作り方
・Loopの使い方

drop function if exists util_getRowCount;
DELIMITER ///
CREATE DEFINER=`root`@`localhost` function util_getRowCount(target longtext)
returns varchar(1000)
-- returns decimal(11,0)
BEGIN
    declare lineMaxCount    int;
    declare sLine           VARCHAR(100);
    declare retValue        decimal(11,0);
    declare lineCount       int;
    declare i               int;
    declare isComment       int;
    declare isBlank         int;
    declare lCodeCharCount  int;
    declare currentTarget   longtext;
    declare vDebug          VARCHAR(10000);
    -- 引数チェック
    if CHAR_LENGTH(target) = 0 THEN
        return 0;
    END IF;

    -- 変数初期化
    set i = 0;
    set lineCount = 0;
    set vDebug    = '';
    set lCodeCharCount = 0;

    -- 利用変数準備
    select CHAR_LENGTH(target) - CHAR_LENGTH(REPLACE(target,'\n','')) +1 into lineMaxCount;
    set currentTarget = REPLACE(target,'\r\n','\n');
    set currentTarget = REPLACE(target,'\r','\n');
    charLoopLabel: LOOP
        -- increment 
        SET i = i + 1;

        -- 抜ける条件
        IF i > lineMaxCount THEN
            LEAVE charLoopLabel;
        END IF;

        -- 行として判定する
        set sLine = replace(substring_index(currentTarget,'\n',1),'\t',' ');
        set isComment = 0;
        set isBlank   = 0;
        
        -- コメント判定
        if substr(trim(sLine),1,2)= '--' THEN
            set isComment = 1;
        end if;

        -- 空行判定
        if length(trim(REPLACE(sLine,'\t',''))) = 0 THEN
            set isBlank = 1;
        end if;

        if (isComment = 0 and isBlank = 0) THEN
            SET lineCount = lineCount + 1;
            set lCodeCharCount = lCodeCharCount + CHAR_LENGTH(sLine);
        end if;
        -- 判定した行を取り除く
        set currentTarget = substr(currentTarget,CHAR_LENGTH(sLine) + 2);
        
        -- set vDebug = CONCAT(vDebug,isComment,isBlank,'xx',sLine,'_');
    END LOOP charLoopLabel;
    return CONCAT(lineCount , ',' , lCodeCharCount);
    -- return lineCount;
    -- set vDebug = CONCAT(vDebug,'__result__',lineCount);
    -- return vDebug;

-- return CHAR_LENGTH(target);

end;
///
delimiter ;
-- select util_getRowCount('12345\n22345\n33345') AS '3';
-- select util_getRowCount('12345\n22345\n33345\n\n\n\n\n') AS '3';
-- select util_getRowCount('12345\n22345\n33345\n\n\n\n\n    -- comment \n -- comment2\n44444') AS '4';
-- select util_getRowCount('日本語\n日本語２\n日本語３\n\n\n\n\n    -- コメント１ \n -- comment2\n44444\n') AS '4';