mysqlでストアドプロシージャを試す

mysqlでストアドを作ったのでメモ。
oracleのストアドとそう変わらない様子。

とりあえずちっちゃいのを作って動作確認

• • テストテーブルをセレクトするプロシージャー↓

（元々test_tableが存在するとした場合）

DELIMITER //
CREATE PROCEDURE sample1()
BEGIN
select * from test_table;
END
//
DELIMITER;
call sample1;

自分がはまった注意点を以下に。

１．mysqlだとストアドパッケージがない
＞procedureとfunction別々で書きましょう。

２．ストアド内で;を使っている場合は終了符を別のものに変更しましょう。
delimiter //
とすると;から//に終了符が変わる。

３．文字の代入はsetを使う
oracleだと a := 5 とかってやるところをmysqlだと
set a = 5;という表記にしてやる。

４．変更を加える場合は一旦dropしましょう
mysqlの場合ストアドのアップデートはできないっぽい。
一旦、drop procedure プロシージャー名
として削除。

５．カーソルも普通に使えます。

oracleの場合

	procedure aaa is
	    cursor c is
	        select
	            a,b,c
	        from
	            aaaa
	        ;
	    begin open c
	        loop fetch c into
	            local_a,local_b,local_c
	        exit when c%notfound;
	    end loop;
	    close c;
	end aaa;


mysqlの場合
create procedure aaa()
    begin
        declare cur cursor for 
            select
	            a,b,c
	        from
	            aaaa
	        ;
        declare exit handler for not found set done = 0;
    set done = 1;
    open cur;
        while done do
                fetch cur into
                     local_a,local_b,local_c;
    end while;
    close cur;
end;
    

６．コマンドラインで実行するとき、タブは使っては駄目。

７．その他コマンド色々

・ストアドの呼び出し方
call プロシージャ名
call プロシージャ名(引数)

・プロシージャの確認
SHOW PROCEDURE STATUS プロシージャ名//

・プロシージャの内容確認
SHOW CREATE PROCEDURE プロシージャ名//

・プロシージャの削除
DROP PROCEDURE プロシージャ名

ということで、あるゲーム内部の計算バッチで使用しているプロシージャーを作成したので、
メモついでに載せておこっと。後で、また書式等忘れた頃に役立つかも！？

delimiter //

drop procedure daily_status_manage//

create procedure daily_status_manage()

begin
    declare done int;
    declare local_mura_member_code int;
    declare local_kome_amount int;
    declare local_jinko int;
    declare local_seisan_point int;
    declare local_seiji_point int;
    declare local_keizai_point int;
    declare local_chian_point int;
    declare local_kotu_point int;
    declare local_gakumon_point int;
    declare local_boeki_point int;
    declare local_geijyutu_points int;
    declare local_syukyo_point int;

    declare sum_jinko int;
    declare sum_seisan_point int;
    declare sum_seiji_point int;
    declare sum_keizai_point int;
    declare sum_chian_point int;
    declare sum_kotu_point int;
    declare sum_gakumon_point int;
    declare sum_boeki_point int;
    declare sum_geijyutu_points int;
    declare sum_syukyo_point int;
    declare message_txt longtext;

    declare message_txt_1 longtext;
    declare message_txt_2 longtext;
    declare message_txt_3 longtext;
    declare message_txt_4 longtext;
    declare message_txt_5 longtext;
    declare message_txt_6 longtext;
    declare message_txt_7 longtext;
    declare message_txt_8 longtext;


    declare cur cursor for
        select
            mura_member_code,
            kome_amount,
            jinko,
            seisan_point,
            seiji_point,
            keizai_point,
            chian_point,
            kotu_point,
            gakumon_point,
            boeki_point,
            geijyutu_points,
            syukyo_point
        from
            mura_member
        where
            last_update_date <= date(current_date()-1)
        ;
    declare exit handler for not found set done = 0;

    set done = 1;
    open cur;

    while done do
        fetch cur into
            local_mura_member_code,
            local_kome_amount,
            local_jinko,
            local_seisan_point,
            local_seiji_point,
            local_keizai_point,
            local_chian_point,
            local_kotu_point,
            local_gakumon_point,
            local_boeki_point,
            local_geijyutu_points,
            local_syukyo_point
        ;

        select
            sum(jinko) as jinko,
            sum(seisan_point) as seisan_point,
            sum(seiji_point) as seiji_point,
            sum(keizai_point) as keizai_point,
            sum(chian_point) as chian_point,
            sum(kotu_point) as kotu_point,
            sum(gakumon_point) as gakumon_point,
            sum(boeki_point) as boeki_point,
            sum(geijyutu_points) as geijyutu_points,
            sum(syukyo_point) as syukyo_point
        into
            sum_jinko,
            sum_seisan_point,
            sum_seiji_point,
            sum_keizai_point,
            sum_chian_point,
            sum_kotu_point,
            sum_gakumon_point,
            sum_boeki_point,
            sum_geijyutu_points,
            sum_syukyo_point
        from
            mura_position,m_item
        where
            mura_position.item_code = m_item.item_code and
            mura_position.position_flag = 1 and
            mura_position.mura_member_code = local_mura_member_code
        group by
            mura_position.mura_member_code
        ;

        update mura_member set
            jinko = sum_jinko,
            kome_amount = local_kome_amount + truncate(sum_jinko * local_seisan_point,0),
            seisan_point = local_seisan_point,
            seiji_point = local_seiji_point + sum_seiji_point,
            keizai_point = local_keizai_point + sum_keizai_point,
            chian_point = local_chian_point + sum_chian_point,
            kotu_point = local_kotu_point + sum_kotu_point,
            gakumon_point = local_gakumon_point + sum_gakumon_point,
            boeki_point = local_boeki_point  + sum_boeki_point,
            geijyutu_points = local_geijyutu_points + sum_geijyutu_points,
            syukyo_point =  local_syukyo_point  + sum_syukyo_point
        where
            mura_member_code = local_mura_member_code
        ;

        if truncate(sum_jinko * local_seisan_point,0) > 0 then
            set message_txt_8 = concat('お金',sum_seiji_point,'両↑,');
        else
            set message_txt_8 = '';
        end if;


        if sum_seiji_point <> 0 then
            set message_txt_1 = concat('政治',sum_seiji_point,'↑,');
        else
            set message_txt_1 = '';
        end if;
        if sum_keizai_point <> 0 then
            set message_txt_2 = concat('経済',sum_keizai_point,'↑,');
        else
            set message_txt_2 = '';
        end if;
        if sum_chian_point <> 0 then
            set message_txt_3 = concat('治安',sum_chian_point,'↑,');
        else
            set message_txt_3 = '';
        end if;
        if sum_kotu_point <> 0 then
            set message_txt_4 = concat('交通',sum_kotu_point,'↑,');
        else
            set message_txt_4 = '';
        end if;
        if sum_gakumon_point <> 0 then
            set message_txt_5 = concat('学問',sum_gakumon_point,'↑,');
        else
            set message_txt_5 = '';
        end if;
        if sum_boeki_point <> 0 then
            set message_txt_6 = concat('貿易',sum_boeki_point,'↑,');
        else
            set message_txt_6 = '';
        end if;
        if sum_geijyutu_points <> 0 then
            set message_txt_7 = concat('宗教',sum_syukyo_point,'↑,');
        else
            set message_txt_7 = '';
        end if;

        select concat('税金が納められました＞内訳, ',message_txt_8,message_txt_1,message_txt_2,message_txt_3,message_txt_4,message_txt_5,message_txt_6,message_txt_7) into message_txt;

        insert into mura_member_message
        (mura_member_code,
        mixi_account_code,
        mura_message_category,
        mura_message_txt,
        message_accept_date
        )values(
        local_mura_member_code,
        0,
        2,
        message_txt,
        now()
        );

        call member_status_manage(local_mura_member_code);

    end while;
    close cur;
end
//