存储过程学习
1. 存储过程入门
1.1 存储过程概念
存储过程就是一组预先编译好的Transact-SQL语句.将其放在服务器上,由用户通过 指定的存储过程的名字执行它.存储过程也可以接收和输出参数.
1.2 存储过程特点
存储过程可以接收参数,并以接收参数的形式返回多个参数给调用存储过程和批处理
包含执行数据库操作的编程语句,也可以调用其他的存储过程.
向调用存储过程或批处理返回状态值,以反映存储过程的执行情况.
1.3 存储过程的优点
封装.
在面向对象的编程领域里,存储过程是可用于操作数据库对象的方法.使用SQL的安全性不允许对SQL语句和数据库对象直接访问,从而在存储过程中既可以封装语句也可封装对象.强制用户只可以使用存储过程操作数据库中的数据,可以很好的防止用户通过在使用数据库应用程序中不应用规则或是跳过完整性检查来绕过商业规则.
改善性能.
当向数据库提交执行的SQL语句时,DBMS在可执行语句之前,必须分析,确定,优化并且事先为存储过程中的整个语句序列生成执行计划.当调用存储过程的时候,DBMS可快速地执行存储过程中的语句,因为可直接转到语句的执行,无需分析,确认,优化和生成执行计划的过程.
减少网络流量
当向DSMS提交要执行的SQL语句时,工作站必须通过网络向SQL Server发送语句,而DBMS必须向工作站返回语句的结果集.如果有大量的批处理语句存在,那么网络流量是很大的,但是如果用存储过程的话,可能只是发送最后的结果集,或许是单一值,相对来说流量就小很多了.
重要性
在编译存储过程之后,许多用户和应用程序都可以执行相同的语句序列,而不必重新 键入和重新向DBMS提交.
安全性
如果系统管理员DBO或者系统管理员sa编译并保存了存储过程,存储过程就有了 对它所使用的数据库对象的所有访问权限.因此,系统管理员可以向单独的用户授予对数 据库对象的最小访问权限,而不是直接允许用户用户使用数据库.
2. 存储过程实例代码
2.1 最简单的,利用存储过程检测表(CXTD的tbStudent表)中是否有数据
/************************************************
* Created Date:
* Author: Walter
* Project: T-SQL学习
* Description: 利用存储过程检测表中是否有数据
************************************************/
use CXTD --使用CXTD数据库
go --就像一个分隔符,到这里标志着一个批处理的开始或者结束
create procedure procExists
as
if exists(select * from tbStudent where studentNum='06082113') --存在学号为的学生
--等价于(select count(*) from tbStudent where studentNum='06082113')<>0
print 'tbStudent表中存在学号为的学生'
else
print 'tbStudent表中不存在学号为的学生'
go
--开始执行
execute procExists
2.2 传入参数的简单存储过程
/************************************************
* Created Date:
* Author: Walter
* Project: 存储过程学习
* Description: 第一个使用传入参数的存储过程,并且输出传入参数
************************************************/
use CXTD
go
--下面开始进行存储过程
create procedure procInputPara
@studentName varchar(50)
as
if exists(select * from tbStudent where studentName=@studentName)
print '存在叫'+@studentName+'的人!'
else
print '不存在叫'+@studentName+'的人!'
go
execute procInputPara '鸟蛋'
--execute procInputPara @studentName='妞妞' 第二种传入参数的方法,感觉这种传入方法更好
2.3用RaisError()函数返回存储过程的错误信息
/************************************************
* Created Date:
* Author: Walter
* Project: 存储过程学习
* Description: 用RaisError()函数返回存储过程的错误信息
************************************************/
use CXTD
go
create procedure procRaisErrorTest
@errorMsg varchar(100) --传入参数
as
select @errorMsg = @errorMsg + ',这是一个什么样的错误呢?';
RaisError(@errorMsg,17,1)
go
--执行存储过程
execute procRaisErrorTest @errorMsg ='1001'
2.4存储过程返回值的利用
/************************************************
* Created Date:
* Author: Walter
* Project: 存储过程学习
* Description: 存储过程返回值的利用
************************************************/
use CXTD
go
--开始创建存储过程
create procedure procReturnValue
@ID int=0,
@intOutput int output
as
if @ID=0
begin
print '错误,必须传入参数@ID'
return 1
end
else
begin
if (select count(*) from tbStudent where Id=@ID)=0
begin
print '错误,没有这个ID的学生'
return 2
end
end
select * from tbStudent where Id=@ID --将学生数据检索出来
if @@error=0
return 0
else
return 3
go
--执行存储过程
declare @flag int,@out int
execute @flag=procReturnValue @ID=1,@intOutput=@out OutPut
if @flag=0
print '查询成功!'
if @flag=1
print '参数不能为!'
if @flag=2
print '参数不能超出范围!'
if @flag=3
print '查询时发生错误!'
2.5 存储过程返回值的利用
/************************************************
* Created Date:
* Author: Walter
* Project: 存储过程学习
* Description: 存储过程返回值的利用
************************************************/
use CXTD
go
create procedure procReturn
@ave int output
as
select @ave=avg(Id) from [tbStudent]
go
--执行存储过程
declare @average int --自定义局部变量
execute procReturn @average output
print @average
