No data

BEGINdeclare 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

-----------------------------------------------------------------------------------------------------------------

增加标红代码