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代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。