SQL——存储过程

9/1/2015来源:SQL技巧人气:1346

SQL——存储过程

1. 为什么使用存储过程

应用程序通过T-SQL语句到服务器的过程是不安全的。

1) 数据不安全

2)每次提交SQL代码都要经过语法编译后在执行,影响应用程序的运行性能

3) 网络流量大

2. 什么是存储过程

存储过程是SQL语句和控制语句的预编译集合,保存在数据库里,可由应用程序调用执行,而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。保存在SQLServer中,通过名称和参数执行,也可一返回结果。对于存储过程我更倾向于把他理解成方法。它里面可以只有一条查询语句,也可以包含一系列使用控制流的SQL语句。

3. 存储过程的优点

1) 模块化呈现设计

2) 执行速度快,效率高

3) 减少网络流量

4) 具有良好的安全性

4. 存储过程的分类

1)系统存储过程

2)扩展存储过程(属于系统存储过程的一种)

3)用户自定义存储过程

5. 系统存储过程

它一般以"sp_"开头,是由SQL Server创建、管理和使用,它存放在Resource数据库中。类似C#语言类库中的方法,暂时先不考虑它是如何编写的,先了解常用的系统存储过程及调用方法。

常见的系统存储过程,见下一篇文章

调用方法:exec[ute] 存储过程名 [参数值]

6. 常用的扩展存储过程 xp_cmdshell

xp_cmdshell 它可以完成DOS命令下的一些操作。

exec xp_cmdshell DOS命令 [no_output]

说明 no_output是可选参数,表示设置执行DOS命令后是否输出返回信息。

示例: exec xp_cmdshell 'mkdir D:\newdir' output

强调: 因为用户可以通过xp_cmdshell对操作系统做一些操作,如果该存储过程被黑客使用对操作系统做操作就麻烦了,所以通常会把xp_cmdshell 关闭掉:

方法一:

SQL Server 2008版本及以上, 通过数据库右击 选择“方面” ,在下拉列表中选择 “服务器安全‘ , 下面的列表项中可以看到xmcmdshellEnable 设置。

SQL Server2005版本及以下,通过开始- SQLServer- 外围设备查找

方法二:

关闭xp_cmdshell

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

开启xp_cmdshell

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 0;

RECONFIGURE;

7. 用户自定义存储过程

语法:

create PRoc[edure] 存储过程名

@参数1 数据类型 = 默认值 output,

……

@参数n 数据类型 = 默认值 output

as

<SQL 语句>

go

一个完成的存储过程包含以下3部分:

1) 输入参数、输出参数

2) 在存储过程中执行的T-SQL语句

3) 存储过程的返回值

其中输入参数允许有默认值。

删除存储过程

drop proc 存储过程名

if exists (select * from sysobject where name = 存储过程名)

drop proc 存储过程名

go

8. 注意事项

存储过程的声明: 输入参数可以有默认值,输出参数也可以有默认值

create proc usp_name

@age int = 5,

      @name varchar(10)

as

……

go

执行语句:

exec pr_name 18 , 'zm'

exec default , 'zm'

exec @name = 'zm'

说明: 为了调用方便,最好将有默认值的存储过程参数列表放到最后。

带输出参数的存储过程

create proc usp_name

@num1 int,

@sum int output

as

<SQL语句>

go

调用存储过程

declare @sum int

exec usp_name 5, @sum output

注意, 调用带有输出参数的存储过程参数后面必须带output关键字

9. 处理存储过程中的错误

raiserror ( {msg_id | msg_str} {, serverity, state } [with option [,……]])

其中:

msg_id: 在sysmessage系统表中指定用户定义错误信息

msg_str: 用户定义的特定信息,最长为255个字符

serverity: 与特定信息相关联,表示用户定义的严重性级别。用户可选用的级别是0~18。数字越大,表示越严重。

state : 表示错误的状态, 1~255中的值

option: 错误的自定义选项,可以使一下任意一值

    LOG: 在Microsoft SQl Server 数据库引擎示例的错误日志和应用程序日志中记录错误

NOWAIT:将消息立即发送给客户端

   SETERROR:将@@error值和 ERROR_NUMBER 值设置为msg_id 或5000, 不用考虑严重级别。

例如: raiserror ('错误信息', 16,1)