存储过程例子

天云上人

CREATE proc 还书
@barcode char(7)
as
if(select count(*) from borrow where barcode=@barcode and getdate()>returntime)=1
  begin
  declare @读者编号 char(7)
  declare @读者姓名 varchar(10)
  declare @条形码 char(7)
  declare @图书名称 varchar(20)
  declare @借出日期 smalldatetime
  declare @应还日期 smalldatetime
  declare @实还日期 datetime
  declare @超期天数 int
  declare @罚款金额 money

  set @读者编号=(select  readerinfo.readerid   from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @读者姓名=(select  readerinfo.name  from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @条形码=(select bookinfo.barcode  from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @图书名称=(select bookinfo.name  from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @借出日期=(select  borrow.loantime from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @应还日期=(select  borrow.returntime from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @实还日期=(select  getdate())
  set @超期天数=(select  datediff(day,borrow.returntime,getdate()) from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  set @罚款金额=(select  datediff(day,borrow.returntime,getdate())*0.2  from readerinfo,borrow,bookinfo  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)


  select  readerinfo.readerid as 读者编号,readerinfo.name as 读者姓名, bookinfo.barcode as 条形码,bookinfo.name as 图书名称,borrow.loantime as 借出日期,borrow.returntime as 应还日期,getdate() as 实还日期,datediff(day,borrow.returntime,getdate()) as 超期天数,datediff(day,borrow.returntime,getdate())*0.2 as 罚款金额
  from readerinfo,borrow,bookinfo
  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode
  

  
  
  update bookstate
  set state='可借'
  where barcode=@barcode

  update cardinfo
  set penalty=penalty+(select datediff(day,borrow.returntime,getdate())*0.2   from readerinfo,borrow,bookinfo where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)
  where readerid=(select readerinfo.readerid from readerinfo,borrow,bookinfo where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode)

  insert into penalty_book
  values(@读者编号,@读者姓名,@条形码,@图书名称,@借出日期,@应还日期,@实还日期, @超期天数,@罚款金额)
  
  delete from borrow
  where barcode=@barcode
  
  
  end
else
  begin
  select readerinfo.readerid as 读者编号,readerinfo.name as 读者姓名, bookinfo.barcode as 条形码,bookinfo.name as 图书名称,borrow.loantime as 借出日期,borrow.returntime as 应还日期,getdate() as 实还日期,0 as 超期天数,0 as 罚款金额
  from readerinfo,borrow,bookinfo
  where borrow.barcode=bookinfo.barcode and borrow.readerid=readerinfo.readerid and borrow.barcode=@barcode

  update bookstate
  set state='可借'
  where barcode=@barcode

  delete from borrow
  where barcode=@barcode
end
GO