求指导:mysql存储过程中 select 单独查询的结果数量 和 循环遍历游标 插入到表中的数据数量为什么不一致呢?

zsj2643 发布于 2017/04/08 19:14
阅读 435
收藏 0

求指导:mysql存储过程中 select 单独查询的结果数量 和 循环遍历游标 插入到表中的数据数量为什么不一致呢?

BEGIN

DECLARE azcx0089 VARCHAR(36);
DECLARE ahax0001 VARCHAR(36);
DECLARE azcp0001 VARCHAR(36);
DECLARE ahax0006 datetime; -- 申请日期
DECLARE azex0005 datetime; -- 当前操作时间
DECLARE azcx0072 VARCHAR(2);  -- 证件号码
DECLARE azcx0073 VARCHAR(100); -- 身份证号
DECLARE azcp0003 VARCHAR(15); -- 户主姓名
DECLARE azcp0004 VARCHAR(2); -- 性别
DECLARE ahax0022 VARCHAR(2); -- 民族
DECLARE ahax0025 VARCHAR(100); -- 居住地址
DECLARE ahax0024 VARCHAR(2); -- 邮政编码
DECLARE ahax0026 VARCHAR(13); -- 联系电话
DECLARE ahax0252 VARCHAR(12); -- 享受保障类别
DECLARE azzx0001 VARCHAR(12); -- 行政区划代码
DECLARE ahax0247 VARCHAR(12); -- 居住居委
DECLARE azcx0056 VARCHAR(36); -- 开户银行
DECLARE azcx0058 VARCHAR(25);  -- 卡号
DECLARE azcp000401 VARCHAR(2); -- 性别
DECLARE azcp0005 datetime;  -- 出生日期
DECLARE ahax0250 VARCHAR(2);  -- 是否保障人
DECLARE ahax02501 VARCHAR(2);  -- 是否保障人
DECLARE ahax0035 VARCHAR(4);  -- 申请人残疾类别
DECLARE ahax0251 VARCHAR(2);  -- 是否三无人员
DECLARE azcx0024 VARCHAR(36);  -- 家庭申请编号
DECLARE azcx0029 VARCHAR(4);  -- 就业状态
DECLARE azcx0028 VARCHAR(4);  -- 是否为学生
DECLARE azcx002901  INT;  -- 就业状态
DECLARE azcx002904  INT;  -- 就业状态
DECLARE azcx002903  INT;  -- 就业状态
DECLARE azcx002902  INT;  -- 就业状态
DECLARE azcx002801  INT;  -- 是否为学生
DECLARE azcx002802  INT;  -- 是否为学生
DECLARE azdx0020 decimal(10,3);  -- 发放金额
DECLARE ahax0078 decimal(12,2);  -- 收入金额
DECLARE ahax0116 VARCHAR(2);  -- 操作类型,01业务受理;02信息修改;03待遇调整;04复审;05复核
DECLARE azdx0020_1 decimal(10,3);  -- 发放金额
DECLARE CF_TYPE_ID BIGINT ;  -- 字典类类型id

DECLARE CF_INFO_NAME VARCHAR(10000) ;  -- 字典中文名称


DECLARE no_more_record TINYINT default 0; 
#IF(SUBSTR(ha11.azcx0073,17,1)%2=1,'男','女') 性别,(select `NAME` from dic_nation where ID=ha11.ahax0022) 民族,
#(select `NAME` from dic_bank_type where ID=ha01.azcx0056) as azcx0056
#定义游标 查询出审批结束的家庭信息  AHAX0247
declare ha01list cursor FOR SELECT
ha11.azcp0009  azzx0001,
ha11.ahax0001,
ha11.azcx0089  azcx0089,
ha11.AZCP0001,ha11.azex0005,
(SELECT ha01.ahax0006 from ha01 where ha01.ahax0001=ha11.ahax0001) as ahax0006,
ha11.azcx0073,ha11.azcp0003,
IF (SUBSTR(ha11.azcx0073, 17, 1) % 2 = 1,'男','女') AS azcp0004,
(SELECT    `NAME`    FROM    dic_nation    WHERE        ID = ha11.ahax0022) AS ahax0022,
 ha11.azcx0056, ha11.azcx0058, ha11.ahax0025, ha11.ahax0024, ha11.ahax0026,
 ha11.ahax0252,
(SELECT    count(CASE WHEN  h11.azcp0004 = '2' THEN  h11.azcp0004    END    )    FROM ha11 AS h11    WHERE     h11.azcx0089 = ha11.azcx0089) AS azcp000401,
(SELECT    count(CASE WHEN zd01.azdx0007 = '01' THEN    zd01.azdx0007    END) FROM    zd01 AS zd01 WHERE zd01.azdx0015 in (SELECT zd02.azdx0015 from zd02 zd02 where zd02.azcx0089=ha11.azcx0089)) AS ahax0250,
(SELECT count(CASE    WHEN h11.ahax0035 <> '' THEN    h11.ahax0035    END    )    FROM    ha11 h11    WHERE        h11.azcx0089=ha11.azcx0089) AS ahax0035,
##(select count(case when ha04.ahax0251='1' then ha04.ahax0251 end) from ha04 as ha04 WHERE ha04.azcx0024=ha01.azcx0024) as  ahax0251,
(select count(case when ( (year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) >60)then h11.azcp0005 end)  FROM ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcp0005,
(select count(case when (h11.ahax0029='01' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) >=18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002901,
(select count(case when (h11.ahax0029='04' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) >=18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002904,
(select count(case when (h11.ahax0029='02' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) >=18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002903,
(select count(case when (h11.ahax0029='03' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) >=18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002902,
(select count(case when (h11.ahax0028='01' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) <18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002801,
(select count(case when (h11.ahax0028='02' and ((year(now())-year(h11.azcp0005)-1) + ( DATE_FORMAT(h11.azcp0005, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) <18))then h11.azcp0005 end) from ha11 h11 WHERE h11.azcx0089=ha11.azcx0089) as azcx002802,
##ha01.ahax0116 as ahax0116 ,
(select sum(case when zd03.azdx0020 then zd03.azdx0020 end) from zd03 as zd03 WHERE zd03.azcp0001=ha11.azcp0001 and zd03.azdx0021='01' and zd03.azex0006='1' and ha11.ahax0045='1' and zd03.azfx0001='1') as  azdx0020
##(select sum(case when ha08.ahax0078 then ha08.ahax0078 end) from ha08 as ha08 WHERE ha08.ahax0001=ha01.ahax0001) as  ahax0078,
FROM
    Zd01 zd01,
    Ha11 ha11    
WHERE
zd01.azcp0001 = ha11.azcp0001
AND zd01.azdx0007 = '01'
AND zd01.azex0006 = '1'
AND ha11.azcp0019 = '01'
AND ha11.AZCX0021 = '640323000000'
#AND DATE_FORMAT(ha11.azex0005, '%Y%m') = '201608'
AND zd01.azfx0001 = '1'
AND zd01.AZDX0005<=DATE_FORMAT(CONCAT(tAhax0006,'31'),'%Y-%m-%d 00:00:00')
AND (zd01.AZDX0006>DATE_FORMAT(CONCAT(tAhax0006,'01'),'%Y-%m-%d 00:00:00') 
      or zd01.AZDX0006='' or zd01.AZDX0006 is NULL);
#(select zd03.azdx0020 from zd03 as zd03 WHERE zd03.azcp0001=ha04.azcp0001) as  azdx0020_1

/* 异常处理 */
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1;  #循环终止的标志,游标中如果没有数据就设置no_more_record为1
    
 open ha01list;
    FETCH ha01list 
            INTO azzx0001,ahax0001,azcx0089,azcp0001,azex0005,ahax0006,azcx0073,azcp0003,
            azcp0004,ahax0022,azcx0056,azcx0058,ahax0025,ahax0024,ahax0026,
            ahax0252,azcp000401,ahax0250,ahax0035,
            azcp0005,azcx002901,azcx002904,azcx002903,        
            azcx002902,azcx002801,azcx002802,
            azdx0020;
        WHILE no_more_record <> 1 DO

    insert into nxmzjh.t204_f3(ahax0001,_QYDM,SQ,F1_1,F1_3,F1_4,F1_5,F1_6,F1_7,F1_8,F1_9,F1_10,F1_11,F1_12,F1_13,F1_14,F1_15,
            F1_16,F1_17,F1_18,F1_19,F1_20,F1_21,F1_22,F1_23,F1_24,F1_25,F1_26,F1_27,F1_28,F1_29,F1_30,F1_60)
            values(azcx0089,azzx0001,REPLACE(DATE_FORMAT(CONCAT(tAhax0006,'01'),'%Y00%m')+1, '00', 'B00'),ahax0006,azcx0073,azcp0003,azcp0004,ahax0022,ahax0025,ahax0024,ahax0026,ahax0252,ahax0250,azcp000401,ahax0035,null,azcp0005,
                azcx002901,azcx002904,azcx002903,azcx002902,azcx002801,azcx002802,null,null,azdx0020,null,null,azcx0056,azcx0058,null,ahax0006,null
);
SELECT 3;

CALL Updatet204_f3list_2(azcx0089);

    FETCH NEXT  FROM  ha01list                
          INTO azzx0001,ahax0001,azcx0089,azcp0001,azex0005,ahax0006,azcx0073,azcp0003,azcp0004,ahax0022,azcx0056,azcx0058,ahax0025,ahax0024,ahax0026,
            ahax0252,azcp000401,ahax0250,ahax0035,azcp0005,azcx002901,azcx002904,azcx002903,azcx002902,azcx002801,azcx002802,azdx0020;
      end WHILE;
      close ha01list;
    #CALL Updatet204_f3list();

END

 

加载中
返回顶部
顶部