SQLServer 存储过程
SQL Server 存储过程
by:授客 QQ：1033553122
什么是存储过程
存储过程是数据库中，一组为了完成特定功能，预编译的SQL语句集
1. 存储过程类似于C语言中的函数，可以带参数，可返回结果
int sum(int a, int b)
{
int s;
s = a + b;
return s;
}
2. 用来执行管理任务或应用复杂的业务规则
存储过程的分类
1. 系统存储过程
a) 由系统定义，存放在master数据库中
b) 类似C语言中的系统函数
c) 系统存储过程中的名称都以“sp_”或“xp_”开头
d) 返回0(表成功)，1(表失败)
2. 用户自定义存储过程
a) 由用户在自己的数据库中创建的存储过程
b) 类似C语言中的用户自定义函数
常用的系统存储过程
举例：
#列出当前系统中的数据库
exec sp_databases
#修改数据库的名称(单用户访问)
exec sp_renamedb ‘Northwind’,’Northwind1’
#查看当前数据库中查询的对象的列表
exec sp_tables
#返回某个表列的信息
exec sp_columns stuinfo
#查看表stuinfo的信息
exec sp_help stuinfo
#查看表stuinfo的约束
exec sp_helpconstraint stuinfo
#查看表stuMarks的索引
exec sp_helpindex stuMarks
#查看视图的语句文本
exec help_text ‘view_stuinfo_stuMarks’
#查看当前数据库中的存储过程
exec sp_stored_procedures
常用的存储过程扩展：xp_cmdshell
1．可以执行dos命令下的一些操作
2．以文本方式返回任何输出
3．调用语法：exec xp_cmdshell Dos命令[NO_OUTPUT]
例子：查看数据库文件（先创建数据库bankDB，保存在D:\bank）
exec xp_cmdshell ‘dir d:\bank\’
如何创建存储过程
定义存储过程
create proc[edure] 存储过程名
@参数1 数据类型=默认值output,
……,
@参数n 数据类型=默认值output
as
sql语句
go
说明：
1.参数分为输入参数input，输出参数output，参数可选
2.参数允许有默认值
例子：创建存储过程，查看本次考试平均分以及未通过考试的学员名单
create procedure proc_stu
AS
#声明平均分和机试平均分变量
DECLARE @wirttenAvg float,@labAvg flot
SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM stuMarks
print ‘笔试平均分：’ + convert(varchar(5),@writtenAvg)
print ’机试平均分：’ + convert(varchar(5),@labAvg)
IF(@writtenAvg>70 AND @labAvg>70)
print ‘本班考试成绩：优秀’
ELSE
print’本班考试成绩：较差’
print’-------------------------------------------------’
print ‘参加本次考试没有通过的学员：’
SELECT stuName,stuInfo.stuNo,writtenExam,labExam
FROM
FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNO
WHERE writtenExam<60 OR labExam<60
调用存储过程
execute语句来执行存储过程
语法：exec 过程名 [参数]
exec proc_stu;
存储过程的参数
1. 输入参数 用于向存储过程传入值，类似C语言的按值传递，如图a,b；
2. 输出参数 用于在调用存储过程后，返回结果，类似C语言的按引用传递,如图中s
例子：修改上例，由于每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化
分析：
在述存储过程添加2个输入参数：
@writtenPass 笔试及格线
@labPass 机试及格线
CREATE PROCEDURE proc_stu
@writtenPass int, #输入参数：笔试及格线
@labPass int #输入参数：机试及格线
AS
print ‘------------------------------------------------’
print ‘参加本次考试没通过的学员’
SELECT stuName,stuInfo.stuNo,WrittenExam,
labExam FROM stuInfo
INNER JOIN stuMarks ON
stuInofo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
调用带参数的存储过程
假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分
EXEC proc_stu 60,55
或EXEC proc_stu @labPass=55,@wirttenPass=60
创建输入参数带默认值的存储过程
如果试卷的难易程度合适，则调用者还是必须如此调用： EXEC proc_stu 60,60，比较麻烦
这样调用就比较合理：
EXEC proc_stu 55 #笔试及格线55分，机试及格线默认为60分
EXEC proc_stu #笔试和机试及格线都默认为标准的60分
CREATE PROCEDURE proc_stu
@writtenPass int=60,
@labPass int=60
AS
print’------------------------------------------’
print’参加本次考试没通过的学员’
SELECT stuName,stuInfo.stuNO,writtenExam,labExam
FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNO
WHERE writtenExam<@writtenPass OR labExam<@labExam
GO
说明：inner join 等同于等值连接=，，仅返回符合join on后面的条件的记录
调用输入参数带默认值的存储过程
EXEC proc_stu --都采用默认值
EXEC proc_stu 64 --机试采用默认值
EXEC proc_stu 60,55 --都不采用默认值
注意这里有个参数的先后顺序：也就是说，带有默认值参数默认放在最后面
--错误的调用方式：希望笔试采用默认值，机试及格线55分
EXEC proc_stu ,55
--正确的方法：
EXEC proc_stu @labPass=55
创建带输出参数的存储过程
如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出(output)参数了
例子：修改上例，返回未通过考试的学员人数
create procedure proc_stu
@notpassSum int output #输出(返回)参数，表示没有通过的人数
@writtenPass int=60,
@labPass int=60 #推荐将默认参数放后
as
……
select stuName,stuInfo.stuNo,writtenExam,labExam
from stuInfo INNER JOIN stuMarks
on writtenExam<@writtenPass
or labExam<@labPass
select @notpassSum=count(stuNo)
FROM stuMarks where writtenExam<@writtenPass or labExam<@labPass
go
调用带输出参数的存储过程
declare @sum int
exec proc_stu @sum output,64 #调用时必须带output关键词，返回结构存放在变量@sum中
print’-----------------------------------------------’
if @sum>=3
print’未通过人数’+convert(varchar(5),@sum)+’人’,’超过60%，及格分数线还应该下调’
else
print’未通过人数:’+convert(varchar(5),@sum)+’人’,’已控制在60%以下，及格分数线适中’
go
处理存储过程中的错误
1，可以使用print语句显示错误信息，但这些信息是临时的，只能显示给用户
2，可以使用raiserror显示用户定义的错误信息时
a) 可以指定严重级别
b) 设置系统变量@@error
c) 记录所反生的错误
raiserror用法
raiserror(msg_id|msg_str,severity,state with option[,…n]])
说明：
msg_id:在sysmessages系统表中指定用户定义的错误信息
msg_str:用户定义的特定信息，最长255个字符
severity:定义严重性级别，用户可以使用的级别为0-18级
state：表示错误状态，1-127之间的值
option：指示是否将错误记录到服务器错误日志中
例子：完善上述例子，当用户调用存储过程时，传入的及格参数不在0-100之间时，将弹出错误警告，终止存储过程的执行。
create procedure proc_stu
@notpassSum int output,
@writtenPass int=60,
@labPass int=60
as
if(not @writtenPass between 0 and 100) or(not @labPass between 0 and 100)
begin
raiserror(‘及格线错误,请指定0-100之间的分数,统计中断退出’,16,1)
return –立即返回，退出存储过程
end
…… --其它语句
go
declare @sum int,@t int
exec proc_stu @sum output,604
set @t=@@ERROR
print ‘错误号：’+convert(varcha(5),@t)
if @t<>0
return
print’--------------------------------------------------------’
if @sum>=3
print ‘未通过人数：’+convert(varchar(5),@sum)+’人’ ,超过60%,及格分数线还应下调'
else
print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
go
存储过程的优点
1．执行速度更快
存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。
存储在数据库服务器，性能高。
2．允许模块化程序设计
只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
3．提高系统安全性
可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。
存储过程的定义文本可以被加密，使用户不能查看其内容
4．减少网络流量
一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。