Create Procedure sp_addMoneyCalue
AS
Declare @Atran_money money
Declare @Acard_ID char(8)
Declare @Atran_time datetime
Declare @Acard_money money
Declare @Asch_id char(8);
Begin
-------------------清空银行表中数据
Declare Tran_day_addMoney Cursor For Select card_id,school_id,tran_money,tran_time From stu_tran_day_addMoney
Open Tran_day_addMoney
While (@@fetch_status='0') Begin
Fetch Next From Tran_day_addMoney Into @Acard_id,@Asch_id,@Atran_money,@Atran_time
--数据表操作
Select * From stu_card_addMoney Where card_id=@Acard_id and charge_time=@Atran_time;
If @@rowcount=0 Begin ---------判断是否有重复记录
Select @Acard_money=(Select card_money From stu_card_charge Where card_id=@Acard_id)
Begin Tran
------------------------------更新卡交易表
Update stu_card_charge Set card_money=@Acard_money+@Atran_money Where card_id=@Acard_ID
------------------------------插入到交易历史表
Insert Into stu_card_addMoney(card_id,charge_money,charge_time,stu_id,card_money,card_status,charge_status)
Values(@Acard_id,@Atran_money,@Atran_time,@Acard_id,@Acard_money+@Atran_money,'正常','充值')
-------------------------删除日交易表中记录
Delete From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time
Commit Tran
IF @@Error!=0 ROLLBACK TRAN
End
Else Begin
Insert Into Err_addmoney_repeat
Select * From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time;
Delete From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time;
End;
End
Close Tran_day_addMoney
Deallocate Tran_day_addMoney
end
GO
Create Procedure sp_DataCalue
As
Declare @tran_money money
Declare @tran_id char(8)
Declare @card_ID char(8)
Declare @tran_time datetime
Declare @card_money money
Declare @card_chargemount money
Declare @card_chargecount int
Declare @sch_id char(8)
Declare @Error_EBS int;
Begin
-------------------清空银行表中数据
Select * From Bank_s
If @@rowcount!=0
Begin
Insert Into Err_Bank_s Select * From Bank_s_b;
Delete From Bank_s;
Select @Error_EBS=-255;
End
Else Select @Error_EBS=255; Declare Tran_day Cursor For Select tran_id,card_ID,tran_time,tran_money From stu_tran_day
Open Tran_day
Fetch Next From Tran_day Into @tran_id,@card_ID,@tran_time,@tran_money
While (@@fetch_status = 0)
Begin
--数据表操作
Select tran_id,card_ID,tran_time From stu_tran_his where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
If @@rowcount=0
Begin ---------判断是否有重复记录
Begin Tran
Select @card_money = (Select card_money From stu_card_charge Where card_id = @card_ID);
Select @card_chargemount = (Select card_chargemount From stu_card_charge Where card_id = @card_id);
Select @card_chargecount = (Select card_chargecount From stu_card_charge Where card_id=@card_id);
Select @sch_id=(Select school_id From stu_students_info Where stu_id = @card_id);
------------------------------更新卡交易表
Update stu_card_charge Set card_money=(@card_money-@tran_money),card_chargemount=(@card_chargemount+@tran_money),card_chargecount=(@card_chargecount+1) where card_id=@card_ID
------------------------------插入到交易历史表
Insert Into stu_tran_his(tran_id,card_ID,school_ID,tran_money,card_balance,tran_time,up_time,tran_state,bank_card_ID,dev_ID,oper_id,dev_row,get_time)
Select tran_id,card_ID,@sch_id,tran_money,card_balance,tran_time,up_time,tran_state,bank_card_ID,dev_ID,oper_id,dev_row,get_time from stu_tran_day where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
---------------------------插入到银行效验表
Insert Into Bank_s(Didno,cardid,Buytime,BuyMon,TotalMon,SchID,MidID)
Select @tran_id,@card_id,@tran_time,@tran_money,card_balance,@sch_id,dev_ID From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
-------------------------插入到银行备份表
Insert Into Bank_s_b(Didno,cardid,Buytime,BuyMon,TotalMon,SchID,MidID)
Select @tran_id,@card_id,@tran_time,@tran_money,card_balance,@sch_id,dev_ID From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
-------------------------删除日交易表中记录
Delete From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time Commit Tran IF @@Error!=0 Rollback Tran
End
Else
Begin
Insert Into Err_repeat
Select * From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
Delete From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
End;
Fetch Next From Tran_day Into @tran_id,@card_ID,@tran_time,@tran_money
End Close Tran_day
Deallocate Tran_day
End;GO
exec sp_addMoneyCalue
exec sp_DataCalue
AS
Declare @Atran_money money
Declare @Acard_ID char(8)
Declare @Atran_time datetime
Declare @Acard_money money
Declare @Asch_id char(8);
Begin
-------------------清空银行表中数据
Declare Tran_day_addMoney Cursor For Select card_id,school_id,tran_money,tran_time From stu_tran_day_addMoney
Open Tran_day_addMoney
While (@@fetch_status='0') Begin
Fetch Next From Tran_day_addMoney Into @Acard_id,@Asch_id,@Atran_money,@Atran_time
--数据表操作
Select * From stu_card_addMoney Where card_id=@Acard_id and charge_time=@Atran_time;
If @@rowcount=0 Begin ---------判断是否有重复记录
Select @Acard_money=(Select card_money From stu_card_charge Where card_id=@Acard_id)
Begin Tran
------------------------------更新卡交易表
Update stu_card_charge Set card_money=@Acard_money+@Atran_money Where card_id=@Acard_ID
------------------------------插入到交易历史表
Insert Into stu_card_addMoney(card_id,charge_money,charge_time,stu_id,card_money,card_status,charge_status)
Values(@Acard_id,@Atran_money,@Atran_time,@Acard_id,@Acard_money+@Atran_money,'正常','充值')
-------------------------删除日交易表中记录
Delete From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time
Commit Tran
IF @@Error!=0 ROLLBACK TRAN
End
Else Begin
Insert Into Err_addmoney_repeat
Select * From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time;
Delete From stu_tran_day_addMoney Where card_id=@Acard_id and tran_time=@Atran_time;
End;
End
Close Tran_day_addMoney
Deallocate Tran_day_addMoney
end
GO
Create Procedure sp_DataCalue
As
Declare @tran_money money
Declare @tran_id char(8)
Declare @card_ID char(8)
Declare @tran_time datetime
Declare @card_money money
Declare @card_chargemount money
Declare @card_chargecount int
Declare @sch_id char(8)
Declare @Error_EBS int;
Begin
-------------------清空银行表中数据
Select * From Bank_s
If @@rowcount!=0
Begin
Insert Into Err_Bank_s Select * From Bank_s_b;
Delete From Bank_s;
Select @Error_EBS=-255;
End
Else Select @Error_EBS=255; Declare Tran_day Cursor For Select tran_id,card_ID,tran_time,tran_money From stu_tran_day
Open Tran_day
Fetch Next From Tran_day Into @tran_id,@card_ID,@tran_time,@tran_money
While (@@fetch_status = 0)
Begin
--数据表操作
Select tran_id,card_ID,tran_time From stu_tran_his where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
If @@rowcount=0
Begin ---------判断是否有重复记录
Begin Tran
Select @card_money = (Select card_money From stu_card_charge Where card_id = @card_ID);
Select @card_chargemount = (Select card_chargemount From stu_card_charge Where card_id = @card_id);
Select @card_chargecount = (Select card_chargecount From stu_card_charge Where card_id=@card_id);
Select @sch_id=(Select school_id From stu_students_info Where stu_id = @card_id);
------------------------------更新卡交易表
Update stu_card_charge Set card_money=(@card_money-@tran_money),card_chargemount=(@card_chargemount+@tran_money),card_chargecount=(@card_chargecount+1) where card_id=@card_ID
------------------------------插入到交易历史表
Insert Into stu_tran_his(tran_id,card_ID,school_ID,tran_money,card_balance,tran_time,up_time,tran_state,bank_card_ID,dev_ID,oper_id,dev_row,get_time)
Select tran_id,card_ID,@sch_id,tran_money,card_balance,tran_time,up_time,tran_state,bank_card_ID,dev_ID,oper_id,dev_row,get_time from stu_tran_day where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
---------------------------插入到银行效验表
Insert Into Bank_s(Didno,cardid,Buytime,BuyMon,TotalMon,SchID,MidID)
Select @tran_id,@card_id,@tran_time,@tran_money,card_balance,@sch_id,dev_ID From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
-------------------------插入到银行备份表
Insert Into Bank_s_b(Didno,cardid,Buytime,BuyMon,TotalMon,SchID,MidID)
Select @tran_id,@card_id,@tran_time,@tran_money,card_balance,@sch_id,dev_ID From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time
-------------------------删除日交易表中记录
Delete From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time Commit Tran IF @@Error!=0 Rollback Tran
End
Else
Begin
Insert Into Err_repeat
Select * From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
Delete From stu_tran_day Where tran_id=@tran_id and card_ID=@card_ID and tran_time=@tran_time;
End;
Fetch Next From Tran_day Into @tran_id,@card_ID,@tran_time,@tran_money
End Close Tran_day
Deallocate Tran_day
End;GO
exec sp_addMoneyCalue
exec sp_DataCalue
exec sp_addMoneyCalue
exec sp_DataCalue
两条语句