val sel har ont top pla ddr length
BEGIN declare f_age int;DECLARE incode1 VARCHAR(100);DECLARE incode2 VARCHAR(100);DECLARE incode3 VARCHAR(100);DECLARE incode4 VARCHAR(100);DECLARE incode5 VARCHAR(100);DECLARE incode6 VARCHAR(100); declare a1 int; declare a2 int; declare a3 int; declare a4 int; declare b int default 0; declare b1 int default 0; declare f_id varchar(100); declare f_eventtime varchar(100); declare f_eventdata varchar(100); declare f_addtime varchar(100); declare f_uptime varchar(100); /*是否达到记录的末尾控制变量*/ declare f_name varchar(100); declare f_shortname varchar(100); declare f_logo varchar(100); declare f_qyxz2 varchar(100); declare f_sshy2 varchar(100); declare f_ssqy2 varchar(100); declare f_lxdh varchar(100); declare f_lxcz varchar(100); declare f_zcd varchar(100); declare f_fddbr varchar(100); declare f_qsjg varchar(100); declare f_qsjgdbr varchar(100); declare f_lssws varchar(100); declare f_qzlv varchar(100); declare f_kjssws varchar(100); declare f_qzkjs varchar(100); declare f_brief varchar(100); declare f_delflag varchar(100); declare f_code varchar(100); declare a varchar(100); DECLARE cur_1 CURSOR FOR select name as f_name,shortname f_shortname,logo f_logo,qyxz2 f_qyxz2,sshy2 f_sshy2,ssqy2 f_ssqy2 ,lxdh f_lxdh,lxcz f_lxcz,zcd f_zcd,fddbr f_fddbr,qsjg f_qsjg,qsjgdbr f_qsjgdbr,lssws f_lssws,qzlv f_qzlv,kjssws f_kjssws,qzkjs f_qzkjs,brief f_brief,delflag f_delflag,batchno f_eventdata,code f_codefrom bl_b_companyenenthistory_stop where isnew=1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;set f_id=‘BL_L‘;SELECT MAX(incode) into incode1 from bl_b_companyevent;set incode2=SUBSTRING(incode1,5,1);if incode2=0 THENset incode5=SUBSTRING(incode1,6,LENGTH(incode1));set incode3=‘BL_L0‘;set incode4=CONCAT(incode3,incode5);end if;if incode2!=0 THENset incode5=SUBSTRING(incode1,5,LENGTH(incode1));set incode3=‘BL_L‘;set incode4=CONCAT(incode3,incode5);end if; OPEN cur_1; FETCH cur_1 INTO f_name,f_shortname,f_logo,f_qyxz2,f_sshy2,f_ssqy2,f_lxdh,f_lxcz,f_zcd,f_fddbr,f_qsjg,f_qsjgdbr,f_lssws,f_qzlv,f_kjssws,f_qzkjs,f_brief,f_delflag,f_eventdata,f_code;
while b<>1 doset incode5 =incode5+1;set incode6=CONCAT(incode3,incode5); select f_name,f_shortname,f_logo,f_qyxz2,f_sshy2,f_ssqy2; IF f_name is not null then select count(*) into a2 from bl_b_companyevent WHERE bklx = 1 and sslx=‘01‘ and eventstatus=‘01‘ and isnew=‘1‘ and name=f_name ; if a2=0 THEN select count(*) into a3 from bl_b_companyevent WHERE bklx = 1 and sslx=‘01‘ and eventstatus=‘02‘ AND checkstatus=‘05‘ and isnew=‘1‘ and name=f_name;
if a3=0 THENSELECT ‘添加数据‘; insert into bl_b_companyevent(id,sclx,sclx2,bklx,sslx,eventstatus,eventstatus2,checkstatus,checkstatus2,eventtime,eventdata,incode,name,namehistory,shortname,shortnamehistory,logo,qyxz2,sshy2,ssqy2,lxdh,lxcz,zcdz,fddbr,qsjg,qsjgmc,qsjbrmc,lsswsmc,lsjbrmc,kjsswsmc,kjsswsjbrmc,gsjj,addr,addtime,uptr,upttime,isnew,delflag,sslx2)VALUES(CONCAT(f_id,REPLACE(UUID(),‘-‘,‘‘)),‘09‘,‘其他‘,1,‘01‘,‘02‘,‘审核中‘,‘05‘,‘中止审查‘,date_format(REPLACE(f_eventdata,"-",""),‘%Y-%m-%d %H:%i:%s‘),date_format(REPLACE(f_eventdata,"-",""),‘%Y-%m-%d‘),incode6,f_name,f_name,f_shortname,f_shortname,f_logo,f_qyxz2,f_sshy2,f_ssqy2,f_lxdh,f_lxcz,f_zcd,f_fddbr,f_qsjg,f_qsjg,f_qsjgdbr,f_lssws,f_qzlv,f_kjssws,f_qzkjs,f_brief,pd_addr,NOW(),pd_uptr,NOW(),‘1‘,f_delflag,‘IPO‘);end IF;if a3!=0 THENSELECT ‘更新数据‘; UPDATE bl_b_companyevent SET code=f_code, sshy2=f_sshy2, ssqy2=f_ssqy2, lxdh=f_lxdh, lxcz=f_lxcz, zcdz=f_zcd, fddbr=f_fddbr, qsjg=f_qsjg, qsjgmc=f_qsjg, qsjbrmc=f_qsjgdbr, lsswsmc=f_lssws, lsjbrmc=f_qzlv, kjsswsmc=f_kjssws, kjsswsjbrmc=f_qzkjs, gsjj=f_brief, uptr=pd_uptr, upttime=NOW(), eventstatus=‘02‘, eventstatus2=‘审核中‘ WHERE name=f_name;
end if;
end IF; END if;
FETCH cur_1 INTO f_name,f_shortname,f_logo,f_qyxz2,f_sshy2,f_ssqy2,f_lxdh,f_lxcz,f_zcd,f_fddbr,f_qsjg,f_qsjgdbr,f_lssws,f_qzlv,f_kjssws,f_qzkjs,f_brief,f_delflag,f_eventdata,f_code; /*取下一条记录*/
end while; close cur_1; END
mysql存储过程(查询数据库 游标循环 if判断 插入别的数据库)
来源: http://www.bubuko.com/infodetail-2030531.html