mysql存过游标if_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);

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

原文：http://www.cnblogs.com/tutu21ybz/p/6727039.html