一、概念
存储过程(Stored Procedure)是一组预先编译好的Transact-SQL语句。将其放在服务器上,由用户通过指定存储过程的名字来执行。存储过程可以作为一个独立的数据库对象,也可以作为一个单元被应用程序调用。存储过程可以接受和输出参数,返回执行存储过程的状态值,还可以嵌套调用。
二、优点
1.执行速度快
因为存储过程在创建时就经过了语法检查和性能优化,因此在执行时不必再重复这些步骤。存储过程在第一次调用后,就驻留在内存中不必再经过编译和优化,所以执行速度快。
2.模块化设计
一次创建,多次调用
3.减少网络通信
可以包含大量的Transact-SQL语句,在调用的时候只需要一条语句
4.保证系统的安全性
这是存储过程的最大优点,可以设置用户通过存储过程来对某些关键数据进行访问,但是不允许用户直接室友Transact-SQL语句或企业管理器来对数据进行访问
三、分类
1.系统存储过
系统存储过程在“master”数据库中创建并创建,并以SP_为前缀,主要用来从系统表中获取信息,用户可从任何数据库中执行系统存储过程,而无需使用“master”名称来完全限定存储过程的名称。
2.本地存储过程
用户根据需要,在自己创建的数据库中所创建的存储过程
3.临时存储过程
临时存储过程通常分为局部临时存储过程(#存储过程名)和全局临时存储过程(##存储过程名)。
局部临时存储过程:只有创建本地存储过程的链接才能执行该过程,关闭联接时自动删除临时存储过程
全局临时存储过程:任何联接都能启动全局临时存储过程,前提是创建该存储过程的链接必须处于联接状态,因为只有这时全局临时过程才存在,当创建该存储过程的链接关闭时,并且所有正在执行该存储过程的链接执行完毕存储过程时,全局临时存储过程将自动删除
4.远程存储过程
非本地服务器上的存储过程
5.、扩展存储过程
存储过程写在动态联接库中,通过SP_addextendeproc命令注册到SQL Server中
四、创建
注意:在给存储过程取名字的时候最好不要以Sp_开头,因为在执行以sp_开头的存储过程时是先在master数据库中进行查找,如果没有根据数据库名或者所有者进行查找,如果没有数据库名和所有者则使用dbo作为所有者查找,这样一来当用户创建的存储过程与系统的存储过程同名时,用户创建的存储过程将不会被执行。
1.无参存储过程
create procedure 存储过程名字
as
......
2.有参存储过程
create procedure 存储过程名字
@参数名 参数类型 , --传入参数
@参数名 参数类型 output, --传出参数
@参数名 参数类型 out, --传出参数
as
......
3. 有默认值的存储过程
create procedure 存储过程名字
@参数名 参数类型 = 默认值 , --传入参数
@参数名 参数类型 output, --传出参数
@参数名 参数类型 out, --传出参数
as
......
五、返回值
1.通过传出参数返回
create procedure 存储过程名字
@参数名 参数类型 , --传入参数
@参数名 参数类型 output, --传出参数
@参数名 参数类型 out, --传出参数
as
......
客户端通过参数接受返回值
2.通过return返回
create procedure 存储过程名字
参数列表
as
return 返回值
此种返回值方式,只能返回int类型(本人认为,还不知道怎么返回其他的类型,希望大侠帮忙)
客户端(ADO.net)通过参数接受返回值,参数的名字可以自定义,参数的Direction为ParameterDirection.ReturnValue
3.通过select返回
create procedure 存储过程名字
参数列表
as
select * from 表名
......
客户端通过返回值接受
六、执行
执行存储过程一般有两种方法
1.按存储过程中参数的顺序对参数进行赋值
exec 存储过程名 参数1值,参数2值.....
2.任意顺序对参数进行赋值
exec 存储过程名 @参数名=参数值,@参数名=参数值....