从头‘不达标’开始，每一个都可以；从后面‘不合格’ ，存储过程成功，但无数据插入
困扰
create procedure jn(IN ypbh VARCHAR(255), in pz varchar(255),IN sf VARCHAR(255))
begin
declare str,hgstr,ycstr, jyxmm3,jyjgm3,jyxmm4,jyjgm4,jyxmm5,jyjgm5,jyxmm1,jyjgm1,jyxmm2,jyjgm2,jyxm9,jyjg9,jyxmm0,jyjgm0,jyxm8,jyjg8,jyxm7,jyjg7,jyxm1,jyjg1,jyxm2,jyjg2,jyxm3,jyjg3,jyxm4,jyjg4,jyxm5,jyjg5,jyxm6,jyjg6,bdbstr,bhgstr,bycstr varchar(255);
declare num int;
declare i int ;
set i=1;
SELECT SKT205.SKF7885,SKT205.SKF7885,SKT205.SKF7892,SKT205.SKF7892,SKT205.SKF7887,
SKT205.SKF7887,SKT205.SKF7888,SKT205.SKF7888,SKT205.SKF7891,
SKT205.SKF7891,SKT205.SKF7890,SKT205.SKF7890,
SKT205.SKF7886 ,SKT205.SKF7886 ,SKT205.SKF7889 ,SKT205.SKF7889 ,SKT205.SKF7911 ,
SKT205.SKF7911 ,SKT205.SKF7912,SKT205.SKF7912,SKT205.SKF7915,SKT205.SKF7915,SKT205.SKF7916,SKT205.SKF7916,
SKT205.SKF7897,SKT205.SKF7897,SKT205.SKF7894,SKT205.SKF7894,SKT205.SKF7896,
SKT205.SKF7896
INTO jyxm1,jyjg1,jyxm2,jyjg2,jyxm3,jyjg3,jyxm4,jyjg4,jyxm5,jyjg5,jyxm6,jyjg6,jyxm7,jyjg7,jyxm8,jyjg8,jyxm9,jyjg9,jyxmm0,jyjgm0,jyxmm1,jyjgm1,jyxmm2,jyjgm2,
jyxmm3,jyjgm3,jyxmm4,jyjgm4,jyxmm5,jyjgm5 from SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
select concat_WS(',',SKT205.SKF7885,SKT205.SKF7892,SKT205.SKF7887,SKT205.SKF7888,SKT205.SKF7891,SKT205.SKF7890) into bdbstr
FROM SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
select concat_WS(',',SKT205.SKF7911,SKT205.SKF7912,SKT205.SKF7915,SKT205.SKF7916) into bhgstr
FROM SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
select concat_WS(',',SKT205.SKF7897,SKT205.SKF7894,SKT205.SKF7896) into bycstr
FROM SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
if sf='不达标' then
if pz ='玉米' then
if (select SKT205.SKF8309 from SKT205 where SKT205.SKF7864 =ypbh limit 1 )= 0 then
set jyxm1 =null;
set jyjg1 = null;
else
set jyxm1='容重';
SELECT substring_index(bdbstr,',',1) into jyjg1 ;
end if;
if (select SKT205.SKF8310 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm2=null;
set jyjg2=null;
else
set jyxm2='色泽气味';
SELECT substring_index(substring_index(bdbstr,',',2),',',-1) into jyjg2 ;
end if;
if (select SKT205.SKF8311 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm3=null;
set jyjg3=null;
else
set jyxm3='水分';
SELECT substring_index(substring_index(bdbstr,',',3),',',-1) into jyjg3 ;
end if;
if (select SKT205.SKF8312 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) then
set jyxm4=null;
set jyjg4=null;
else
set jyxm4='杂质';
SELECT substring_index(substring_index(bdbstr,',',4),',',-1) into jyjg4 ;
end if;
if (select SKT205.SKF8313 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm5=null;
set jyjg5=null;
else
set jyxm5='霉变粒';
SELECT substring_index(substring_index(bdbstr,',',5),',',-1) into jyjg5 ;
end if;
if (select SKT205.SKF8314 from SKT205 where SKT205.SKF7864 =ypbh )=0 then
set jyxm6=null;
set jyjg6=null;
else
set jyxm6='不完善粒';
SELECT substring_index(substring_index(bdbstr,',',6),',',-1) into jyjg6 ;
end if;
SELECT concat_ws(',',jyxm1,jyjg1,jyxm2,jyjg2,jyxm3,jyjg3,jyxm4,jyjg4,jyxm5,jyjg5,jyxm6,jyjg6) into str ;
end if;
if pz like concat('%','稻谷') then
select concat_WS(',',SKT205.SKF7892,SKT205.SKF7887,SKT205.SKF7888,
SKT205.SKF7885,SKT205.SKF7890,SKT205.SKF7886,SKT205.SKF7889) into bdbstr
from SKT205 where SKT205.SKF7864 =ypbh ;
if (select SKT205.SKF8310 from SKT205 where SKT205.SKF7864 =ypbh limit 1 )= 0 then
set jyxm2 =null;
set jyjg2 = null;
else
set jyxm2='色泽气味';
SELECT substring_index(bdbstr,',',1) into jyjg2 ;
end if;
if (select SKT205.SKF8311 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm3=null;
set jyjg3=null;
else
set jyxm3='水分';
SELECT substring_index(substring_index(bdbstr,',',2),',',-1) into jyjg3 ;
end if;
if (select SKT205.SKF8312 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm4=null;
set jyjg4=null;
else
set jyxm4='杂质';
SELECT substring_index(substring_index(bdbstr,',',3),',',-1) into jyjg4 ;
end if;
if (select SKT205.SKF8309 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) then
set jyxm1=null;
set jyjg1=null;
else
set jyxm1='出糙率';
SELECT substring_index(substring_index(bdbstr,',',4),',',-1) into jyjg1 ;
end if;
if (select SKT205.SKF8314 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm6=null;
set jyjg6=null;
else
set jyxm6='黄粒米';
SELECT substring_index(substring_index(bdbstr,',',5),',',-1) into jyjg6 ;
end if;
if (select SKT205.SKF8316 from SKT205 where SKT205.SKF7864 =ypbh )=0 then
set jyxm7=null;
set jyjg7=null;
else
set jyxm7='整精米';
SELECT substring_index(substring_index(bdbstr,',',6),',',-1) into jyjg7 ;
end if;
if (select SKT205.SKF8315 from SKT205 where SKT205.SKF7864 =ypbh )=0 then
set jyxm8=null;
set jyjg8=null;
else
set jyxm8='互混';
SELECT substring_index(bdbstr,',',-1) into jyjg8 ;
end if;
SELECT concat_ws(',',jyxm1,jyjg1,jyxm2,jyjg2,jyxm3,jyjg3,jyxm4,jyjg4,jyxm6,jyjg6,jyxm7,jyjg7,jyxm8,jyjg8) into str ;
end if;
if pz like concat('%','小麦') then
select concat_WS(',',SKT205.SKF7892,SKT205.SKF7887,SKT205.SKF7888,SKT205.SKF7885,SKT205.SKF7890,SKT205.SKF7889) into bdbstr
FROM SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
if (select SKT205.SKF8310 from SKT205 where SKT205.SKF7864 =ypbh )= 0 then
set jyxm3 =null;
set jyjg3 = null;
else
set jyxm3='色泽气味';
SELECT substring_index(bdbstr,',',1) into jyjg3;
end if;
if (select SKT205.SKF8311 from SKT205 where SKT205.SKF7864 =ypbh ) =0 then
set jyxm4=null;
set jyjg4=null;
else
set jyxm4='水分';
SELECT substring_index(substring_index(bdbstr,',',2),',',-1) into jyjg4 ;
end if;
if (select SKT205.SKF8312 from SKT205 where SKT205.SKF7864 =ypbh ) =0 then
set jyxm5=null;
set jyjg5=null;
else
set jyxm5='杂质';
SELECT substring_index(substring_index(bdbstr,',',3),',',-1) into jyjg5 ;
end if;
if (select SKT205.SKF8309 from SKT205 where SKT205.SKF7864 =ypbh ) =0 then
set jyxm1=null;
set jyjg1=null;
else
set jyxm1='容重';
SELECT substring_index(substring_index(bdbstr,',',4),',',-1) into jyjg1 ;
end if;
if (select SKT205.SKF8314 from SKT205 where SKT205.SKF7864 =ypbh ) =0 then
set jyxm7=null;
set jyjg7=null;
else
set jyxm7='不完善粒';
SELECT substring_index(substring_index(bdbstr,',',5),',',-1) into jyjg7 ;
end if;
if (select SKT205.SKF8315 from SKT205 where SKT205.SKF7864 =ypbh )=0 then
set jyxm8=null;
set jyjg8=null;
else
set jyxm8='矿物质';
SELECT substring_index(bdbstr,',',-1) into jyjg8 ;
end if;
SELECT concat_ws(',',jyxm1,jyjg1,jyxm3,jyjg3,jyxm4,jyjg4,jyxm5,jyjg5,jyxm7,jyjg7,jyxm8,jyjg8) into str ;
end if;
if pz like concat('%','大豆','%') then
select concat_WS(',',SKT205.SKF7892,SKT205.SKF7887,SKT205.SKF7888,SKT205.SKF7885,SKT205.SKF7890,SKT205.SKF7891) into bdbstr
FROM SKT205 WHERE SKT205.SKF7864 =ypbh limit 1 ;
if (select SKT205.SKF8310 from SKT205 where SKT205.SKF7864 =ypbh limit 1 )= 0 then
set jyxm2 =null;
set jyjg2 = null;
else
set jyxm2='色泽气味';
SELECT substring_index(bdbstr,',',1) into jyjg2 ;
end if;
if (select SKT205.SKF8311 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm3=null;
set jyjg3=null;
else
set jyxm3='水分';
SELECT substring_index(substring_index(bdbstr,',',2),',',-1) into jyjg3 ;
end if;
if (select SKT205.SKF8312 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm4=null;
set jyjg4=null;
else
set jyxm4='杂质';
SELECT substring_index(substring_index(bdbstr,',',3),',',-1) into jyjg4 ;
end if;
if (select SKT205.SKF8309 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) then
set jyxm1=null;
set jyjg1=null;
else
set jyxm1='完整粒率';
SELECT substring_index(substring_index(bdbstr,',',4),',',-1) into jyjg1 ;
end if;
if (select SKT205.SKF8314 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxm6=null;
set jyjg6=null;
else
set jyxm6='损伤粒率';
SELECT substring_index(substring_index(bdbstr,',',5),',',-1) into jyjg6 ;
end if;
if (select SKT205.SKF8313 from SKT205 where SKT205.SKF7864 =ypbh )=0 then
set jyxm5=null;
set jyjg5=null;
else
set jyxm5='热损伤粒率';
SELECT substring_index(substring_index(bdbstr,',',6),',',-1) into jyjg5 ;
end if ;
SELECT concat_ws(',',jyxm1,jyjg1,jyxm2,jyjg2,jyxm3,jyjg3,jyxm4,jyjg4,jyxm5,jyjg5,jyxm6,jyjg6) into str ;
end if;
select (length (str)-length(replace(str,',',''))+1) into num;
if num =2 THEN
if exists (select * from SKT243 where SKT243.SKF10207 =ypbh) then
UPDATE SKT243 set SKT243.SKF10208= substring_index(str,',',1),SKT243.SKF10209 =substring_index(str,',',-1)
where SKT243.SKF10207 =ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10208 ,SKT243.SKF10209)
SELECT ypbh, substring_index(str,',',1),substring_index(str,',',-1)
;
end if;
ELSEif num=4 then
if exists (select * from SKT243 where SKT243.SKF10207 =ypbh) then
UPDATE SKT243 set SKT243.SKF10208= substring_index(str,',',1),SKT243.SKF10209 =substring_index( substring_index(str,',',2),',',-1),SKT243.SKF10210=substring_index(substring_index(str,',',3),',',-1),SKT243.SKF10211=substring_index(str,',',-1)
where SKT243.SKF10207 =ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10208 ,SKT243.SKF10209,SKT243.SKF10210,SKT243.SKF10211)
SELECT ypbh, substring_index(str,',',1),substring_index( substring_index(str,',',2),',',-1),substring_index(substring_index(str,',',3),',',-1),substring_index(str,',',-1)
;
end if;
ELSE
if exists (select * from SKT243 where SKT243.SKF10207 =ypbh) then
UPDATE SKT243 set SKT243.SKF10208= substring_index(str,',',1),SKT243.SKF10209 =substring_index( substring_index(str,',',2),',',-1),SKT243.SKF10210=substring_index(substring_index(str,',',3),',',-1),SKT243.SKF10211=substring_index(substring_index(str,',',4),',',-1), SKT243.SKF10216=substring_index(substring_index(str,',',5),',',-1),SKT243.SKF10217=substring_index(str,',',-1)
where SKT243.SKF10207 =ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10208 ,SKT243.SKF10209,SKT243.SKF10210,SKT243.SKF10211, SKT243.SKF10216,SKT243.SKF10217)
SELECT ypbh, substring_index(str,',',1),substring_index( substring_index(str,',',2),',',-1),substring_index(substring_index(str,',',3),',',-1),substring_index(substring_index(str,',',4),',',-1),substring_index(substring_index(str,',',5),',',-1),substring_index(str,',',-1)
;
end if ;
end if;
/*不合格*/
if sf='不合格' then
if (select SKT205.SKF7928 from SKT205 where SKT205.SKF7864 =ypbh limit 1 )= 0 then
set jyxm9 = null;
set jyjg9 = null;
else
set jyxm9='黄曲霉毒素B1';
SELECT substring_index(bhgstr,',',1) into jyjg9 ;
end if;
if (select SKT205.SKF7929 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxmm0=null;
set jyjgm0=null;
else
set jyxmm0='玉米赤霉烯酮';
SELECT substring_index(substring_index(bhgstr,',',2),',',-1) into jyjgm0 ;
end if;
if (select SKT205.SKF7930 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) =0 then
set jyxmm1=null;
set jyjgm1=null;
else
set jyxmm1='呕吐霉素';
SELECT substring_index(substring_index(bhgstr,',',3),',',-1) into jyjgm1 ;
end if;
if (select SKT205.SKF7931 from SKT205 where SKT205.SKF7864 =ypbh limit 1 ) then
set jyxmm2=null;
set jyjgm2=null;
else
set jyxmm2='赭曲霉素';
SELECT substring_index(bhgstr,',',-1) into jyjgm2 ;
end if;
SELECT concat_ws(',',jyxm9,jyjg9,jyxmm0,jyjgm0,jyxmm1,jyjgm1,jyxmm2,jyjgm2) into hgstr ;
select (length (hgstr)-length(replace(hgstr,',',''))+1) into num;
select jyxm9;
if num =2 THEN
if exists (select * FROM SKT243 WHERE SKT243.SKF10207=ypbh) then
update SKT243 set SKT243.SKF10218 =substring_index(hgstr,',',1) ,SKT243.SKF10219=substring_index(hgstr,',',-1) WHERE SKT243.SKF10207=ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10218 ,SKT243.SKF10219 )
SELECT ypbh, substring_index(hgstr,',',1),substring_index(hgstr,',',-1);
end if;
ELSEif num=4 then
if exists (select * FROM SKT243 WHERE SKT243.SKF10207=ypbh) then
UPDATE SKT243 set SKT243.SKF10218 =substring_index(hgstr,',',1) ,SKT243.SKF10219=substring_index( substring_index(hgstr,',',2),',',-1),SKT243.SKF10220=substring_index(substring_index(hgstr,',',3),',',-1) ,SKT243.SKF10221=substring_index(hgstr,',',-1) WHERE SKT243.SKF10207=ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10218 ,SKT243.SKF10219,SKT243.SKF10220 ,SKT243.SKF10221)
SELECT ypbh, substring_index(hgstr,',',1),substring_index( substring_index(hgstr,',',2),',',-1),substring_index(substring_index(hgstr,',',3),',',-1),substring_index(hgstr,',',-1);
end if;
ELSE
if exists (select * FROM SKT243 WHERE SKT243.SKF10207=ypbh) then
UPDATE SKT243 set SKT243.SKF10218 =substring_index(hgstr,',',1) ,SKT243.SKF10219=substring_index( substring_index(hgstr,',',2),',',-1),SKT243.SKF10220=substring_index(substring_index(hgstr,',',3),',',-1) ,SKT243.SKF10221=substring_index(substring_index(hgstr,',',4),',',-1),SKT243.SKF10222=substring_index(substring_index(hgstr,',',5),',',-1) ,SKT243.SKF10223=substring_index(hgstr,',',-1)
WHERE SKT243.SKF10207=ypbh;
else
insert into SKT243(SKT243.SKF10207,SKT243.SKF10218 ,SKT243.SKF10219,SKT243.SKF10220 ,SKT243.SKF10221,SKT243.SKF10222 ,SKT243.SKF10223)
SELECT ypbh, substring_index(hgstr,',',1),substring_index( substring_index(hgstr,',',2),',',-1),substring_index(substring_index(hgstr,',',3),',',-1),substring_index(substring_index(hgstr,',',4),',',-1),substring_index(substring_index(hgstr,',',5),',',-1),substring_index(hgstr,',',-1);
end if;
end if;
end if;
end
你好，我是有问必答小助手，非常抱歉，本次您提出的有问必答问题，技术专家团超时未为您做出解答
本次提问扣除的有问必答次数，将会以问答VIP体验卡（1次有问必答机会、商城购买实体图书享受95折优惠）的形式为您补发到账户。
因为有问必答VIP体验卡有效期仅有1天，您在需要使用的时候【私信】联系我，我会为您补发。