CALL AddColumnIfNotExists ('rpt_standard_report', 'sort', 'tinyint default 0 comment ''排序字段'' NOT');
DROP PROCEDURE IF EXISTS updateRptStandardReportSortValueByChenhb02;

DELIMITER //
CREATE PROCEDURE updateRptStandardReportSortValueByChenhb02()
BEGIN
declare _name varchar(255);
declare _sort int;
declare done int;
declare sortValue int;

# 定义游标
declare rs_cursor cursor for 
    select name,sort from rpt_standard_report
            where name not in ('应用规模','业务监控统计','成交类型统计','销售月报','客户成交周期统计','客户转化率','客户转化率-来电明细','客户转化率-来访明细'
            ,'客户转化率-来电且来访明细','客户转化率-来访且认购明细','客户转化率-来访且签约明细','客户类型统计','客户类型统计-成交客户明细','客户类型统计-来电客户明细'
            ,'客户类型统计-来访客户明细') ;
declare continue handler for not found set done=1;
# 将所有的sort值设置为0
update rpt_standard_report set sort='0';
# 首先为默认排序名称设置sort值
update rpt_standard_report set sort='15' where name='应用规模';
update rpt_standard_report set sort='14' where name='业务监控统计';
update rpt_standard_report set sort='13' where name='成交类型统计';
update rpt_standard_report set sort='12' where name='销售月报';
update rpt_standard_report set sort='11' where name='客户成交周期统计';
update rpt_standard_report set sort='10' where name='客户转化率';
update rpt_standard_report set sort='9' where name='客户转化率-来电明细';
update rpt_standard_report set sort='8' where name='客户转化率-来访明细';
update rpt_standard_report set sort='7' where name='客户转化率-来电且来访明细';
update rpt_standard_report set sort='6' where name='客户转化率-来访且认购明细';
update rpt_standard_report set sort='5' where name='客户转化率-来访且签约明细';
update rpt_standard_report set sort='4' where name='客户类型统计';
update rpt_standard_report set sort='3' where name='客户类型统计-成交客户明细';
update rpt_standard_report set sort='2' where name='客户类型统计-来电客户明细';
update rpt_standard_report set sort='1' where name='客户类型统计-来访客户明细';

# 打开游标
open rs_cursor;
cursor_loop:loop
    fetch rs_cursor into _name,_sort;
    if done=1 then 
        leave cursor_loop;
    end if;
    # 操作数据
    # 将最大的值给sortValue
    select max(sort) into sortValue from rpt_standard_report; 
    # 判断sortValue的值，如果最大的sortValue小于16，就设置为16，否则为sortValue
    if sortValue<16 then
        set sortValue=16;
    else
    set sortValue=sortValue+1; 
    end if;
     # select _name,_sort,sortValue;
    update rpt_standard_report set sort=sortValue where name=_name;
end loop cursor_loop;
close rs_cursor;
END //
DELIMITER ;
call updateRptStandardReportSortValueByChenhb02();
DROP PROCEDURE IF EXISTS updateRptStandardReportSortValueByChenhb02;

转载请注明：MitNick » MySQL存储过程简单实践