tushuguangli.sql

图书管理系统源代码！！很好！希望对大家有用·！

as
if @yh_id is null
begin
	print'用户的id不得为空！'
	return -1
end
if not exists(select * from borrow_view where yhid=@yh_id)
begin
	print'无该条记录！'
	return -2
end
select * from borrow_view where yhid=@yh_id
go

--exec search_borrow '05111712'

--×××××借书，存储过程×××××
create PROCEDURE p_borrow
(@b_id1 varchar(10),@yh_id varchar(10))
as 
if @b_id1 is null  or @yh_id is null
begin
	print'用户的id或图书的id不得为空！'
	return -2
end

if not exists(select * from booklib where b_id=@b_id1)
begin
	print'该书不存在！'
	return -3
end

declare @yg_name varchar(10)

select @yg_name=(select ygname from ygrecord where yhid=@yh_id)

declare @stu_name varchar(10)

select @stu_name=(select stuname from sturecord where yhid=@yh_id)

declare @b_name1 varchar(10)
select @b_name1=(select b_name from booklib where b_id=@b_id1)

declare @time datetime
select @time=getdate()


if 0=(select bor_num from ygrecord where yhid=@yh_id)
begin
	print'该员工的借书数已到上限，请先还书，再来借书！'
	return -1
end

if 0=(select bor_num from sturecord where yhid=@yh_id)
begin
	print'该学生的借书数已到上限，请先还书，再来借书！'
	return -1
end
if exists(select * from sturecord where yhid=@yh_id)
begin
	insert into borrow values(@yh_id,@stu_name,@b_id1,@b_name1,@time)

	declare @b_num int--该处为图书库更新信息
	select @b_num=(select left_num from booklib where b_id=@b_id1)
	update booklib set left_num=@b_num-1
	where b_id=@b_id1

	declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from sturecord where yhid=@yh_id)
	update sturecord set bor_num=@p_num-1
	where yhid=@yh_id
	return
end
else
begin
	goto yg
end

yg:
if exists(select * from ygrecord where yhid=@yh_id)
begin
	 insert into borrow values(@yh_id,@yg_name,@b_id1,@b_name1,@time)

	--declare @b_num int--该处为图书库更新信息
	select @b_num=(select left_num from booklib where b_id=@b_id1)
	update booklib set left_num=@b_num-1
	where b_id=@b_id1

	--declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from ygrecord where yhid=@yh_id)
	update ygrecord set bor_num=@p_num-1
	where yhid=@yh_id
	 return
end
else
begin
	print'该员工不存在！'
	return -3
end
go

--drop proc p_borrow
--execute p_borrow 'ls1','jw1'
--execute p_borrow 'gs1','dw1'
--execute p_borrow 'gs1','05111711'
--execute p_borrow 'gy1','05111712'
--execute p_borrow 'wy1','st1'
--execute p_borrow 'wl1','05111713'
--execute p_borrow 'xd1','05111714'
--execute p_borrow 'gy1','05111715'



--还书表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[returnb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[returnb]
GO

CREATE TABLE returnb(
	yhid varchar(10)NOT NULL,
	yh_name varchar (10)NOT NULL,
	b_id varchar(10)NOT NULL foreign key references booklib(b_id),
	b_name varchar (15)NOT NULL,
	ret_date datetime not null
)
GO


create trigger new_ret
on returnb
for insert 
as print'还书成功！'
go
create trigger del_ret
on returnb
for delete 
as print'删除还书信息！'
go
--为还书表创建查询视图
create view returnb_view as
select * 
from returnb
go


--×××××还书，存储过程×××××
create PROCEDURE p_return
(@b_id1 varchar(10),@yh_id varchar(10))
as 
if @b_id1 is null  or @yh_id is null
begin
	print'用户的id或图书的id不得为空！'
	return -2
end

if not exists(select * from booklib where b_id=@b_id1)
begin
	print'该书不存在！'
	return -3
end
--查询还书的基本信息，为写入表做准备
declare @yg_name varchar(10)

select @yg_name=(select ygname from ygrecord where yhid=@yh_id)

declare @stu_name varchar(10)

select @stu_name=(select stuname from sturecord where yhid=@yh_id)

declare @b_name1 varchar(10)
select @b_name1=(select b_name from booklib where b_id=@b_id1)



declare @today datetime
select @today=getdate()

declare @befor_day datetime
select @befor_day=(select bor_date from borrow where yhid=@yh_id and b_id=@b_id1)

declare @via_day int
--select @via_day=select DateDiff(day,@befor_day,@today) 

--检查是否欠费
if @via_day > 20
begin
	print'已欠费'print @via_day-20 print '元'
end



if exists(select * from sturecord where yhid=@yh_id)
begin
	delete from borrow where yhid=@yh_id and b_id=@b_id1
	insert into returnb values(@yh_id,@stu_name,@b_id1,@b_name1,@today)

	declare @b_num int--该处为图书库更新信息
	select @b_num=(select left_num from booklib where b_id=@b_id1)
	update booklib set left_num=@b_num+1
	where b_id=@b_id1

	declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from sturecord where yhid=@yh_id)
	update sturecord set bor_num=@p_num+1
	where yhid=@yh_id
	return
end
else
begin
	goto yg
end

yg:
if exists(select * from ygrecord where yhid=@yh_id)
begin
	delete from borrow where yhid=@yh_id and b_id=@b_id1
	insert into returnb values(@yh_id,@yg_name,@b_id1,@b_name1,@today)
	--declare @b_num int--该处为图书库更新信息
	select @b_num=(select left_num from booklib where b_id=@b_id1)
	update booklib set left_num=@b_num+1
	where b_id=@b_id1

	--declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from ygrecord where yhid=@yh_id)
	update ygrecord set bor_num=@p_num+1
	where yhid=@yh_id
	 return
end
else
begin
	print'该员工不存在！'
	return -3
end
go

--drop proc p_return
--execute p_return 'gs1','jw1'
--execute p_return 'gy1','05111712'
--execute p_return 'wl1','05111713'
--execute p_return 'xd1','05111714'
--select datediff(day,'2007-11-07 15:50:09.107', '2007-11-27 16:16:13.890' )


--还书表信息查询,存储过程
create procedure search_returnb
(@yh_id varchar(10))
as
if @yh_id is null
begin
	print'用户的id不得为空！'
	return -1
end
if not exists(select * from returnb_view where yhid=@yh_id)
begin
	print'无该条记录！'
	return -2
end
select * from returnb where yhid=@yh_id
go

--execute search_returnb '05111712'
--drop proc search_returnb


--lost表，挂失信息记录表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lost]
GO

CREATE TABLE lost(
	yhid varchar(10)NOT NULL,
	yh_name varchar (10)NOT NULL,
	b_id varchar(10)NOT NULL,
	b_name varchar (15)NOT NULL,
	price int not null,
	lost_date datetime
)
GO



--×××××书籍挂失，存储过程×××××
create PROCEDURE p_book_lost
(@b_name varchar(15))
as 
if @b_name is null
begin
	print'图书名不得为空！'
	return -2
end


if not exists(select * from booklib where b_name=@b_name)
begin
	print'该书不存在！'
	return -3
end

declare @b_price int
select @b_price=(select price from booklib where b_name=@b_name)

print'请按原价交纳罚金！'
print'该书原价为：'
print @b_price
go


--exec p_book_lost '大学化学'



--×××××交罚金，存储过程×××××
create PROCEDURE p_pay_fine
(@b_name varchar(15),@yh_id varchar(10))
as 
if @b_name is null or @yh_id is null
begin
	print'图书名或用户ID不得为空！'
	return -2
end

if not exists(select * from booklib where b_name=@b_name)
begin
	print'该书不存在！'
	return -3
end

delete from borrow--删除借书信息 
where yhid=@yh_id

declare @b_price int
select @b_price=(select price from booklib where b_name=@b_name)

declare @yg_name varchar(10)
select @yg_name=(select ygname from ygrecord where yhid=@yh_id)

declare @stu_name varchar(10)
select @stu_name=(select stuname from sturecord where yhid=@yh_id)

declare @b_id varchar(10)
select @b_id=(select b_id from booklib where b_name=@b_name)

declare @time datetime
select @time=getdate()


if exists(select * from sturecord where yhid=@yh_id)
begin
	declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from sturecord where yhid=@yh_id)
	update sturecord set bor_num=@p_num+1
	where yhid=@yh_id

	insert into lost values(@yh_id,@stu_name,@b_id,@b_name,@b_price,@time)
	return
end
else
begin
	goto yg
end

yg:
if exists(select * from ygrecord where yhid=@yh_id)
begin
	--declare @p_num int--该处为用户表（借书者信息表）更新信息
	select @p_num=(select bor_num from ygrecord where yhid=@yh_id)
	update ygrecord set bor_num=@p_num+1
	where yhid=@yh_id

	insert into lost values(@yh_id,@yg_name,@b_id,@b_name,@b_price,@time)
	 return
end
else
begin
	print'该员工不存在！'
	return -3
end
go

--exec p_pay_fine '大学化学','dw1'

--系统用户信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO

CREATE TABLE users(
	uid varchar (10)NOT NULL primary key,
	pwd varchar (10)NULL,
	des varchar (10)NULL 
)
GO

create unique index users_order on users(uid)

--创建用户，储存过程
create procedure create_user
(@user_id varchar(10),@user_pwd varchar(10),@user_des varchar(10))
as
insert into users values(@user_id,@user_pwd,@user_des)
go
--删除用户，储存过程
create procedure del_user
(@user_id varchar(10))
as
delete from users where uid=@user_id
go
--sp_addrole sys_user
--grant all to sys_user
--sp_addrole nor_user
--grant select on booklib to nor_user
--sp_addlogin ly,123
--sp_droplogin ly
--grant create database to ly(只能在 master 数据库中授予 CREATE DATABASE 权限。)
--grant all to ly


--数据库信息备份与恢复，存储过程

--备份整个数据库
create PROCEDURE back_up
as
backup database library
to disk='c:\lib_backup\db_back_up.bak'

--增量备份
backup database library
to disk='c:\lib_backup\add_db_back_up.bak'
with differential

--drop proc back_up
--execute back_up

--恢复数据库，存储过程
create PROCEDURE db_recovery
as
--全备份的恢复
restore database library
from disk='c:\lib_backup\db_back_up.bak'
with norecovery
--增量备份
restore database library
from disk='c:\lib_backup\add_db_back_up.bak'
with norecovery
go
--drop proc db_recovery
--execute db_recovery