mysql 存储过程同步数据_MySQL存储过程跨数据库操作同步数据

最新推荐文章于 2024-08-25 03:13:42 发布

冠诚

最新推荐文章于 2024-08-25 03:13:42 发布

阅读量335

点赞数

文章标签： mysql 存储过程同步数据

本文链接：https://blog.csdn.net/weixin_30984749/article/details/114792607

版权

BEGIN

DECLARE deliverstatus,smlen,datacoding,total int;

DECLARE tsid,srcaddr,destaddr,servicetype,smcontent,linkid,sequenceno,recvtime,state,resulttime,extcode,applicationid varchar(50) ;

set total = 0;

while total < 20 do

set tsid='';

select deliver_status,sm_len,data_coding,to_sp_id,src_addr,dest_addr,service_type,sm_content,link_id,sequence_no,recv_time,state,result_time,extcode_,applicationid_ into deliverstatus,smlen,datacoding,tsid,srcaddr,destaddr,servicetype,smcontent,linkid,sequenceno,recvtime,state,resulttime,extcode,applicationid

from tlc_to_sp where applicationid_ = 'sms_db' limit 0,1;

if tsid <> '' then

if resulttime <> '' and recvtime <> '' then

insert into kingterdb.sms_inbox(MASSMSID,SOURCEADDR,RECEIVETIME,MESSAGECONTENT,REQUESTTIME,extcode,applicationid) values(tsid,srcaddr,resulttime,smcontent,recvtime,extcode,applicationid);

elseif resulttime = '' then

insert into kingterdb.sms_inbox(MASSMSID,SOURCEADDR,RECEIVETIME,MESSAGECONTENT,REQUESTTIME,extcode,applicationid) values(tsid,srcaddr,now(),smcontent,recvtime,extcode,applicationid);

elseif recvtime = '' then

insert into kingterdb.sms_inbox(MASSMSID,SOURCEADDR,RECEIVETIME,MESSAGECONTENT,REQUESTTIME,extcode,applicationid) values(tsid,srcaddr,resulttime,smcontent,now(),extcode,applicationid);

else

insert into kingterdb.sms_inbox(MASSMSID,SOURCEADDR,RECEIVETIME,MESSAGECONTENT,REQUESTTIME,extcode,applicationid) values(tsid,srcaddr,now(),smcontent,now(),extcode,applicationid);

end if;

insert into tlc_to_sp_his(deliver_status,sm_len,data_coding,to_sp_id,src_addr,dest_addr,service_type,sm_content,link_id,sequence_no,recv_time,state,result_time,extcode_,applicationid_) values(deliverstatus,smlen,datacoding,tsid,srcaddr,destaddr,servicetype,smcontent,linkid,sequenceno,recvtime,state,resulttime,extcode,applicationid);

end if;

delete from tlc_to_sp where to_sp_id in(select to_sp_id from tlc_to_sp_his where applicationid_ = 'sms_db');

set total = total + 1;

end while;

END此存储过程在newppp数据库中插入数据到kingterdb数据库的表中。