huandai.sql

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

create procedure huandai1
  @id varchar(10),
  @loan_no char(16),
  @type nvarchar(50),
  @mon float,
  @date datetime,
  @cishu int
as
begin transaction
 
/* 计算贷款利息*/
update loan_pack
set rest_money=rest_money+rest_money*rate*datediff(day,borrow_date,getdate())
where loan_pack_no=@loan_no and card_type=@type
if @@Error<>0 goto PRO

update loan_pack
set rest_money=rest_money-@mon,borrow_date=getdate()
where loan_pack_no=@loan_no and card_type=@type
if @@Error<>0 goto PRO

insert into payoff_pro
values(@id,@cishu,@mon,@loan_no,@type)
if @@Error<>0 goto PRO

/* 如果第一次还清了，则消户*/
delete
from customer_loan 
where exists(
         select loan_pack_no,card_type
         from loan_pack 
         where loan_pack_no=@loan_no and card_type=@type and rest_money=0.0  and customer_loan.loan_pack_no=loan_pack.loan_pack_no and customer_loan.card_type=loan_pack.card_type)
if @@Error<>0 goto PRO

delete 
from loan_pack
where loan_pack_no=@loan_no and card_type=@type and rest_money=0.0
commit transaction
return 0

PRO:
Rollback Transaction
return 1