一.存储过程(stored procedure)
- 为了完成一定功能的一组sql语句
- 经过编译后存储到数据库
- 系统存储过程_sp, 拓展存储过程_xp, 用户自定义存储过程_usp
二.优点(为什么要用存储过程)
①模块化编程
②减少网络的流量
③提高执行速度
④提高数据的安全性
三.创建使用
1.创建语句
create procedure 存储过程名
参数
as
功能
--执行
exec 存储过程名
--调用语句为批处理的第一条语句时,可省略exec
示例:
2.不带参数的存储过程:创建一个存储过程,查看所有读者的姓名、可借本数、可借天数和已借书本数。
--1、 不带参数的存储过程:创建一个存储过程,查看所有读者的姓名、可借本数、可借天数和已借书本数。
create procedure usp_selelctReader
as
select rdName,canLendQty,canLendDay,rdBorrowQty
from Reader, ReaderType
where Reader.rdType=ReaderType.rdType
--测试执行
exec usp_selelctReader
执行运行结果:
3.带输入参数和输出参数的存储过程:创建一个存储过程,输入读者的编号,输出该读者的姓名
--2、 带输入参数和输出参数的存储过程:创建一个存储过程,输入读者的编号,输出该读者的姓名;
create procedure usp_getName
@rdID char(9),
@rdNmae varchar(20) output
as
select @rdNmae=rdName from Reader where rdID=@rdID
--测试执行
declare @rdNmae varchar(20)
exec usp_getName'rd2018007',@rdNmae output
print @rdNmae
运行结果:卢小川
4. 带输入参数的存储过程:创建一个存储过程,实现借书功能
--3、 带输入参数的存储过程:创建一个存储过程,实现借书功能;
alter procedure usp_borrow
@rdID char(9),
@bkID char(9),
@DateBorrow datetime,
@DateLendPlan datetime
as
begin tran
print'begin tran'
begin try
--先判断图书状态
declare @bkState int
select @bkState=bkState from Book where bkID=@bkID
if(@bkState!=1)
raiserror('借书失败!!该图书不在馆!',16,1)
else
begin
--判断读者借书是否已满
declare @rdBorrowQty int,@canLendQty int
select @canLendQty=canLendQty,@rdBorrowQty=rdBorrowQty from Reader,ReaderType
where rdID=@rdID and Reader.rdType=ReaderType.rdType;
if(@rdBorrowQty>=@canLendQty)
raiserror('借书失败!!该读者借书已达上限!',15,1)
else
begin
insert into Borrow(rdID,bkID,DateBorrow,DateLendPlan)
values(@rdID,@bkID, @DateBorrow,@DateLendPlan);
--修改图书状态
update Book set bkState=0 where bkID=@bkID;
--修改借书数量
update Reader set rdBorrowQty=rdBorrowQty+1 where rdID=@rdID;
print @rdID+'借'+@bkID+'成功!!'
commit
print'commit'
end
end
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000)
select @ErrorMessage = ERROR_MESSAGE()
raiserror(@ErrorMessage,16,1)
rollback
print'rollback'
end catch
测试代码1:
--测试执行
declare @date datetime,@date_plan datetime
--获取当前时间为借书时间
set @date=GETDATE()
--计划还书时间为借书后的第十天
set @date_plan=GETDATE()+10
exec usp_borrow'rd2018001','bk2018001',@date,@date_plan
运行结果:
再次运行
查询结果: