proc.sql

本人自己编写的!!!!!看看这样做的怎么样

1.对xs表进行操作
use xsbook
creat procedure xs_insert
  @jszh char(8),@xm char(8),@zym char(12),@xb bit,@cssj datetime
as
  insert into xs(借书证号,姓名,专业名,性别,出生时间)
        values(@jszh,@xm,@zym,@xb,@cssj)
go


use xsbook
creat procedure xs_update
  @jszh char(8),@xm char(8),@zym char(12),@xb bit,@cssj datetime,@jss int
as
  update xs set 姓名=@xm,专业名=@zym,性别=@xb,出生时间=@cssj,借书数=@jss


use xsbook
creat procedure xs_update0
  @jszh char(8),@xm char(8),@zym char(12),@xb bit,@cssj datetime
as 
  delete xs
        where 借书证号=@jszh
  insert xs(借书证号,姓名,专业名,性别,出生时间,借书数)
        values(@jszh,@xm,@zym,@xb,@cssj,@jss


use xsbook
creat procedure xs_delete
   @jszh char(8),@flag int output
as
   if exists(select* from xs
             where 借书证号=@jszh and 借书数=0)
      begin
           delete
              from xs
              where 借书证号=@jszh and 借书数=0
           set @flag = 0
      end
   else
set @flag= 1




2.对book表进行操作
 
use xsbook
creat procedure book_insert
  @isbn char(16),@sm char(26),@zz char(812),@cbs char(20),@jg float(8),@fbl int,@kcl int
as
      insert into book(isbn,书名,作者,出版社,价格,复本量,库存量)
        values(@isbn,@sm, @zz,@cbs,@jg,@fbl,@kcl)
go


use xsbook
creat procedure book_update
  @isbn char(16),@sm char(26),@zz char(812),@cbs char(20),@jg float(8),@fbl int,@kcl int
as
      update book set
       书名=@sm,作者=@zz,出版社=@cbs,价格=@jg,复本量=@fbl,库存量=@kcl
      where isbn=@isbn


use xsbook
creat procedure book_delete
   @isbn char(16),@flag int output
as
   if exists(select* from book
             where isbn=@isbn and 复本量=库存量)
      begin
           delete
              from book
              where isbn=@isbn 
           set @flag = 0
      end
   else
set @flag= 1



3.对jy表进行操作
use xsbook
creat procedure jy_insert
  @jszh char(8),@isbn char(16),@ssh char(10),@jssj datetime,@flag int output 
as
  set @flag=(select 库存量 from book where isbn=@isbn)
 if @flag>0
   begin
       insert into jy(借书证号,isbn,索书号,借书时间)
          values (@jszh,@isbn,@ssh,@jssj)
    end 
  else
set @flag=0   


use xsbook
creat procedure jy_delete
  @ssh char(10),@flag int output
as
   declare @jszh char(8),@isbn char(16)
   declare @jssj datetime,@hssj datetime
  if exists(select* from jy
             where 索书号=@ssh)
      begin
          set @flag=1
          select @jszh=借书证号,@isbn=isbn,@jssj=借书时间 from jy 
              where 索书号=@ssh
          set @hssj=getdate()
          insert into jyls
             values (@jszh,@isbn,@ssh,@jssj,@hssj)
           delete from jy
              where 索书号=@ssh
       end
   else
set @flag= 0

 4.对jy表的触发器设计
use xsbook
 create trigger tjy_insert on jy
     for insert as 
     begin 
         update xs
              set 借书数=借书数+1
                 where xs.借书证号 in
                   (select inserted.借书证号 from inserted)
              update book
                   set 库存量=库存量-1
                       where book.isbn in
                            (selet inserted.isbn from inserted)
     end


use xsbook
 create trigger tjy_delete on jy
     for delete as 
     begin 
         update xs
              set 借书数=借书数-1
                 where xs.借书证号 in
                   (select deleted.借书证号 from deleted)
              update book
                   set 库存量=库存量+1
                       where book.isbn in
                            (selet deleted.isbn from deleted)
     end