Mysql Stored Procedure

Mysql Stored Procedure

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 );

/* Define the cursor 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 'add data ';
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 ',
'Others ',
1,
'01 ',
'02 ',
'Reviewing ',
'05 ',
'Abort Review ',
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 data ';
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 = 'under review'
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;/* obtain the next record */

End while;

Close cur_1;
END