DROP PROCEDURE IF EXISTS `nnb_check_procedure`;
delimiter $$
CREATE PROCEDURE nnb_check_procedure()
BEGIN
declare p_id varchar(50);
declare p_est_date varchar(20);
declare p_content varchar(10);
declare done int(10);
declare flag int(2);
declare feeCodeCursor cursor for select id from nnb_dr_xwqy_info;
declare continue handler for not found set done=1;
set done=0;
set @year = year(NOW());
set @month = month(NOW());
set @day = day(now());
set @temptotal=0;
set @i=0;
open feeCodeCursor;
loop_label:LOOP
fetch feeCodeCursor into p_id;
if done = 1 then
leave loop_label;
else
set flag = 0;
end if;
select ifnull(est_date,concat(cast(@year AS CHAR),'-06-30 00:00:00')),content into p_est_date,p_content from nnb_dr_xwqy_info where id=p_id order by est_date desc;
set @nfc = TIMESTAMPDIFF(YEAR,DATE_FORMAT(p_est_date,'%Y-%m-%d'),concat(@year,'-06-30'));
if (@nfc<1) then
insert into nnb_checkresult values(p_id,1);
elseif ((@nfc>1 or @nfc=1) and @nfc<2) then
if (@month<7) then
insert into nnb_checkresult values(p_id,1);
else
if (p_content='个体') then
select count(1) into @temptotal from nnb_gt_djzgjgsh where zch=p_id and njrq like concat(@year-1,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
else
select count(1) into @temptotal from nnb_djzgjgsh where zch=p_id and njrq like concat(@year-1,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
end if;
end if;
else
if(@month<7) then
if (p_content='个体') then
select count(1) into @temptotal from nnb_gt_djzgjgsh where zch=p_id and njrq like concat(@year-2,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
else
select count(1) into @temptotal from nnb_djzgjgsh where zch=p_id and njrq like concat(@year-2,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
end if;
else
if (p_content='个体') then
select count(1) into @temptotal from nnb_gt_djzgjgsh where zch=p_id and njrq like concat(@year-1,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
else
select count(1) into @temptotal from nnb_djzgjgsh where zch=p_id and njrq like concat(@year-1,'%');
if(@temptotal>0) then
insert into nnb_checkresult values(p_id,1);
else
insert into nnb_checkresult values(p_id,0);
end if;
end if;
end if;
end if;
set @i=@i+1;
if @i%1000=0 then
commit;
end if;
end LOOP;
commit;
close feeCodeCursor;
end $$
delimiter ;
mysql存储过程demo
最新推荐文章于 2023-02-24 14:27:40 发布