MySQL stored procedure (query database cursor loop If judge inserts another database)

Source: Internet
Author: User

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); /* Whether to reach the end of the record control variable */
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) to 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 isn't null then
Select COUNT (*) into A2 from bl_b_companyevent WHERE BKLX = 1 and sslx= ' "and eventstatus= '" 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 ',
' Other ',
1,
' 01 ',
' 02 ',
' Under review ',
' 05 ',
' Suspension of 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= ' Audit '
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; /* Remove a record */

End while;

Close cur_1;
END

MySQL stored procedure (query database cursor loop If judge inserts another database)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.