deposit.sql

用dephi写的一个银行管理系统

create procedure Deposit
  @type varchar(50),
  @Number char(15),
  @money1 float(8),
  @date datetime,
  @id char(6),
  @TT char(10),
  @zhj float
as
begin transaction
 
update bank
set Asset=asset+@money1
where card_type=@type;
if @@Error<>0 goto PRO
/*先支付透支*/

update overdraft
set amount=amount-@money1,@money1=0.0
where card_type=@type and card_no=@Number and @zhj>@money1
if @@Error<>0 goto PRO

update overdraft
set @money1=@money1-amount,@zhj=-0.1
where card_type=@type and card_no=@Number and @zhj<@money1
if @@Error<>0 goto PRO


delete
from overdraft
where card_type=@type and card_no=@Number and @zhj<@money1
if @@Error<>0 goto PRO


/*先计算利息，并写进去*/
update fund_bill
set fortune_amount=fortune_amount+fortune_amount*0.005*datediff(day,deposit_date,getdate())
where card_type=@type and card_no=@Number
if @@Error<>0 goto PRO

update Fund_bill
set Fortune_amount=Fortune_amount+@money1,Deposit_date=@date
where card_type=@type and card_no=@Number

if @@Error<>0 goto PRO
insert into fetch_Depo_log
 values(@id,@type,@date,@money1,@TT)


update customer
set pay_abi=@money1*10
where customer_id in(
   select Customer.Customer_id 
   from customer_Fund,customer
   where customer_Fund.card_no=@Number and Customer.Customer_id=customer_Fund.Customer_id and ((Customer.pay_abi/10)<@money1))
if @@Error<>0 goto PRO
commit transaction
return 0

PRO:
Rollback Transaction
return 1