mysql存储过程(查询数据库内表 游标循环 if判断 插入别的表内)
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);
/*定义游标 cur_1*/
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_code
from 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 THEN
set incode5=SUBSTRING(incode1,6,LENGTH(incode1));
set incode3='BL_L0';
set incode4=CONCAT(incode3,incode5);
end if;
if incode2!=0 THEN
set 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 do
set 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 THEN
SELECT '添加数据';
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 THEN
SELECT '更新数据';
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
