mysql 创建存储过程以及游标的使用

-- 创建存储过程
CREATE PROCEDURE batch_update_xxx()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE nl int DEFAULT 20;
DECLARE jia varchar(255);

DECLARE contractNumber  varchar(100);
-- 定义变量
DECLARE contractTaxAmountOld decimal(25, 2) DEFAULT 0;
DECLARE createdByOld varchar(50);
DECLARE contractTaxAmountNew decimal(25, 2)  DEFAULT 0;

-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR 
        select contract_number, count(1) from  xxx 
        where del_flag=0
        and   contract_number is not null 
        group by  contract_number having count(1)>1;
 

-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    -- 打开游标
    open report;
-- 将游标中的值赋值给变量，
-- 注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
    fetch report into contractNumber,nl;
    -- 当s不等于1，也就是未遍历完时，会一直循环
    while s<>1 do
--         union select * from student where age=nl and home=jia;
        drop table if exists tmp_table1;
        create temporary table  tmp_table1(
        id  varchar(32) NOT NULL COMMENT '主键ID',
        version varchar(100) DEFAULT NULL COMMENT '合同版本',
        contract_number varchar(100) DEFAULT NULL COMMENT '合同编号',
        status varchar(100) DEFAULT NULL COMMENT '合同状态',
        status_name varchar(100) DEFAULT NULL COMMENT '合同状态名称',
        
        settle_status  varchar(100) DEFAULT NULL COMMENT '结算状态code，SRM_CON_SETTLEMENT_STATUS',
        settle_status_name  varchar(100) DEFAULT NULL COMMENT '结算状态名称',
        is_main_contract varchar(1) DEFAULT NULL COMMENT '是否是主合同',
        is_apportion int(1) DEFAULT NULL COMMENT '是否涉及分摊：0、否；1、是',
        contract_tax_amount decimal(25, 2) DEFAULT NULL COMMENT '合同金额(含税)',
        
        del_flag varchar(1) DEFAULT NULL COMMENT '是否删除',
        last_updated_date  datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        creation_date datetime DEFAULT NULL COMMENT '创建时间',
                created_by varchar(50) DEFAULT NULL COMMENT '创建人'
        );
        
        -- 存最大时间的
        insert into tmp_table1 (id,version,contract_number,status,status_name,
        settle_status,settle_status_name,is_main_contract,is_apportion,contract_tax_amount,
        del_flag,last_updated_date,creation_date,created_by)
        select 
        id,version,contract_number,status,status_name,
        settle_status,settle_status_name,is_main_contract,is_apportion,contract_tax_amount,
        del_flag,last_updated_date,creation_date,created_by
        from xxx a 
        where 
        a.del_flag=0
        and a.contract_number =contractNumber 
        order by  a.last_updated_date desc   
        limit 1;
        
        
        drop table if exists tmp_table2;
        create temporary table  tmp_table2(
        id  varchar(32) NOT NULL COMMENT '主键ID',
        version varchar(100) DEFAULT NULL COMMENT '合同版本',
        contract_number varchar(100) DEFAULT NULL COMMENT '合同编号',
        status varchar(100) DEFAULT NULL COMMENT '合同状态',
        status_name varchar(100) DEFAULT NULL COMMENT '合同状态名称',
        
        settle_status  varchar(100) DEFAULT NULL COMMENT '结算状态code，SRM_CON_SETTLEMENT_STATUS',
        settle_status_name  varchar(100) DEFAULT NULL COMMENT '结算状态名称',
        is_main_contract varchar(1) DEFAULT NULL COMMENT '是否是主合同',
        is_apportion int(1) DEFAULT NULL COMMENT '是否涉及分摊：0、否；1、是',
        contract_tax_amount decimal(25, 2) DEFAULT NULL COMMENT '合同金额(含税)',
        
        del_flag varchar(1) DEFAULT NULL COMMENT '是否删除',
        last_updated_date  datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        creation_date datetime DEFAULT NULL COMMENT '创建时间',
                created_by varchar(50) DEFAULT NULL COMMENT '创建人'
        );
        
        -- 存最小时间的
        insert into tmp_table2 (id,version,contract_number,status,status_name,
        settle_status,settle_status_name,is_main_contract,is_apportion,contract_tax_amount,
        del_flag,last_updated_date,creation_date,created_by)
        select 
        id,version,contract_number,status,status_name,
        settle_status,settle_status_name,is_main_contract,is_apportion,contract_tax_amount,
        del_flag,last_updated_date,creation_date,created_by
        from xxx a 
        where 
        a.del_flag=0
        and a.contract_number =contractNumber 
        and a.id not in (
            select id from tmp_table1
        )
        order by  a.last_updated_date asc   
        limit 1;
        
        -- 判断合同金额是否一致，一致才处理
   
        select contract_tax_amount,created_by into contractTaxAmountOld,createdByOld from tmp_table2;
        select contract_tax_amount into contractTaxAmountNew from tmp_table1;
        -- 1.如果合同金额相等，且旧的数据不是初始化，则删除旧的数据，用旧的数据的状态更新新数据的状态。
        IF contractTaxAmountOld=contractTaxAmountNew and ('sys'!=createdByOld  or createdByOld is null) THEN 
        
            update xxx a ,tmp_table2 b  set 
            a.settle_status=b.settle_status,
            a.settle_status_name=b.settle_status_name,
            a.status=b.status,
            a.status_name=b.status_name,
            a.is_main_contract=b.is_main_contract,
            a.is_apportion=b.is_apportion,
            a.last_updated_by='sys_procedure',
            a.last_updated_date=now() 
            where a.id in(select id from tmp_table1)
                        and a.contract_number=b.contract_number
                        ;
 

            update xxx set del_flag=1,last_updated_by='sys_procedure',last_updated_date=now() 
             where id in(select id from tmp_table2);
        END IF;
                
                -- 2. 如果合同金额相等，且旧的数据是初始化，则删除新的数据，保留旧的数据。
                IF contractTaxAmountOld=contractTaxAmountNew and createdByOld='sys' THEN 

            update xxx set del_flag=1,last_updated_by='sys_procedure',last_updated_date=now() 
             where id in(select id from tmp_table1);
                END IF;
                
        -- 将游标中的值再赋值给变量，供下次循环使用
        fetch report into contractNumber,nl;
     -- 当s等于1时表明遍历以完成，退出循环
    end while;
    -- 关闭游标
    close report;
END;