BEGIN
declare typeerr int default 0;
declare end_ char(1) default '0';
declare total int default 0;
declare Id_ int(20);
declare MerchantCode_ varchar(20);
declare TransactionNum_ varchar(10);
declare CentralDateTime_ varchar(12);
declare MerchantContact_ varchar(10);
declare CardNum_ varchar(30);
declare Account_ varchar(20);
declare TerminalNum_ varchar(10);
declare IssuingBank_ varchar(10);
declare BankCode_ varchar(5);
declare ClearingMark_ varchar(2);
declare ReferenceNum_ varchar(20);
declare DealingCode_ varchar(5);
declare SendOrganization_ varchar(10);
declare Poundage_ varchar(20);
declare NetAmount_ varchar(20);
declare MaintainOrgan_ varchar(10);
declare IsIC_ varchar(5);
declare IsRF_ varchar(5);
declare UnionpayPro_ varchar(5);
DECLARE State_ varchar(2);
declare exist_ int(10);
declare/*定义游标*/ mycur CURSOR for SELECT
Id,MerchantCode,TransactionNum,CentralDateTime,MerchantContact,CardNum,Account,TerminalNum,IssuingBank,
BankCode,ClearingMark,ReferenceNum,DealingCode,SendOrganization,Poundage,NetAmount,
MaintainOrgan,IsIC,IsRF,UnionpayPro,State from bank_record where State ='0';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET end_ = '1';
OPEN mycur;/*开游标*/
fetch mycur/*游标向下走一步*//*把当前游标提取的值赋值给变量*/ into Id_,MerchantCode_,TransactionNum_,CentralDateTime_,
MerchantContact_,CardNum_,Account_,TerminalNum_,IssuingBank_,BankCode_,ClearingMark_,ReferenceNum_,DealingCode_,
SendOrganization_,Poundage_,NetAmount_,MaintainOrgan_,IsIC_,IsRF_,UnionpayPro_,State_;
while end_ <> '1' DO/*循环体*/
/*首先test表与excel表进行比对*/
select count(*) into exist_ from merchant_info where MerchantCode=MerchantCode_ and TerminalNum=TerminalNum_;
/*如果EXCEL表中已经存在记录,忽略,不存在,添加*/
if exist_=0 THEN
insert into merchant_info (MerchantCode,MerchantContact,TerminalNum,CreateTime)
values(MerchantCode_,MerchantContact_,TerminalNum_,SYSDATE());
end if;
/*将两个表的内容拼接到record中*/
insert into record (MerchantCode,TerminalNum,MerchantContact,Account,Poundage,NetAmount,IsIC,IsRF)
values(MerchantCode_,TerminalNum_,MerchantContact_,Account_,Poundage_,NetAmount_,IsIC_,IsRF_);
update bank_record set State = '1' where id = Id_;
set total = total + 1;
fetch mycur/*游标向下走一步*//*把当前游标提取的值赋值给变量*/ into Id_,MerchantCode_,TransactionNum_,CentralDateTime_,
MerchantContact_,CardNum_,Account_,TerminalNum_,IssuingBank_,BankCode_,ClearingMark_,ReferenceNum_,DealingCode_,
SendOrganization_,Poundage_,NetAmount_,MaintainOrgan_,IsIC_,IsRF_,UnionpayPro_,State_;
end while;/*循环结束*/
close mycur;/*关闭游标*/
END
-----------------------------------------------------------------------------------------------------------------
增加标红代码