SQL Server存储过程
创建于2016-12-24 16:12:19
存储过程
概念：
1.存储过程是在数据库管理系统中保存的、预先编译的、能实现某种功能的SQL程序，它是数据库应用中运用比较广泛的
一种数据对象。
2.存储过程是SQL语句和控制语句的预编译集合，保存在数据库里可由应用程序调用执行，而且允许用户声明变量、逻辑
控制语句及其他强大的编程功能。
3.存储过程可包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集及返回值。
4.存储过程可以只包含一条select语句，也可以包含一系列使用控制流的SQL语句。存储过程可以包含个别或全部的控制
流语句。
特点：
1.模块化程序设计：
只需创建一次存储过程并将其存储在数据库中，以后即可在程序中反复调用该存储过程。
2.执行速度快，效率高：
如果某操作需要大量的T-SQL代码或需要重复执行，则存储过程将比T-SQL批处理代码的执行速度更快。因为存储
过程只在创建的时候编译一次，而批处理代码每次执行的时候都需要编译。
3.减少网络流量：
使用存储过程后，一个需要数百行T-SQL代码的操作，由一条执行过程代码的单独语句即可实现，而不需要再网络
中发送数百行代码。
4.具有良好的安全性：
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。不同的用户使用不同
的存储过程。
5.存储过程分为两类：
系统存储过程和用户自定义的存储过程
系统存储过程
概括：SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制，并
充当从系统表中检索信息的快捷方式。
常用的系统存储过程：
SQL Server的系统存储过程的名称以“sp_”开头，并存放在Resource数据库中。下面列出一些常用的系统存储过程↓
| 系统存储过程 | 说明 |
| sp_databases | 列出服务器上的所有数据库信息，包括数据库名称和数据库大小 |
| sp_ helpdb | 报告有关指定数据库或所有数据库的信息 |
| sp_renamedb | 更改数据库的名称 |
| sp_ tables | 返回当前环境下可查询的表或视图的信息 |
| sp_ columns | 返回某个表或视图的列信息，包括列的数据类型和长度等 |
| sp_help | 查看某个数据库对象的信息，如列名、主键、约束、外键、索引等 |
| sp_helpconstraint | 查看某个表的约束 |
| sp_helpindex | 查看某个表的索引 |
| sp_stored_procedures | 显示存储过程的列表 |
| sp_password | 添加或修改登录账户的密码 |
| sp_helptext | 显示默认值、未加密的存储过程、用户定义的存储过程、 触发器和视图的实际文本 |
调用执行存储过程：
语法： exec[ute] [返回变量=] 存储过程名
[@参数1=]参数值1 [output] | [default],
......,
[@参数n=]参数值n [output] | [default]
其中，exec是execute的简写
如果执行存储过程的语句时批处理中的第一个语句，则可以省略execute关键字。
如果有返回值的话，可以用一个变量接收。但是在存储过程内return只能返回数值类型。
output表明参数是输出参数，default表示参数的默认值。
如果不按照参数顺序传递参数值，则要指定参数名。
一旦某个参数按照"@参数名=参数值"格式传递数据，那么该参数之后的其他参数都必须以同样的格式传递参数
值。
常用的扩展存储过程：
扩展存储过程是SQL Server提供的各类系统存储过程中的一类，允许使用其他编程语言创建外部存储过程，为数据库用
户提供从SQL Server实例到外部程序的接口，以便进行各种维护活动。通常以"xp_"作为前缀，以DLL形式单独存在。
语法： exec xp_cmdshell DOS命令 [no_output]
其中，exec表示调用存储过程，no_output为可选参数，设置执行DOS命令后是否输出返回信息。
用户自定义的存储过程
组成：
1.输入参数和输出参数。
2.在存储过程中执行的T-SQL语句。
3.存储过程的返回值。
语法：
1.创建语法：
create proc[edure] 存储过程名
[{@参数1 数据类型} [=默认值] [output],
......,
{@参数n 数据类型} [=默认值] [output]
]
as
SQL语句
2.删除语法：
drop proc[edure] 存储过程名
3.判断存储过程是否存在，如果存在，则删除
if exists(select * from sysobjects where name = 存储过程名)
drop proc[edure] 存储过程名
go
语法释义：
1.创建：
proc是procedure的简写，参数的默认值可选，不写则没有默认值，调用执行时必须带有此参数。output
表示是输出参数，即执行存储过程后把返回值存放在输出参数中。就像C#中的引用传递一样。输出参数也
可以设置默认值。不写output则为输入参数，就像C#中的值传递一样。
另外需注意，存储过程中参数的默认值不能是系统函数。比如参数是时间类型，默认值不能是
GETDATE()
处理错误信息
概念：在存储过程中，可以使用print语句显式用户定义的错误信息，但这些是临时的，并不保存错误信息。使用raiserror语句
可以指定严重级别并设置系统变量记录所发生的错误。
raiserror语句：
语法：
raiserror ({msg_id | msg_str} {, severity, state} [with option [...n]])
语法释义：
msg_id： 在sysmessages系统表中指定的用户定义错误信息。
msg_str： 用户定义的特定信息，最长为255个字符。
severity： 与特定信息关联，表示用户定义的严重性级别。用户可使用的级别为0~18级；19~25级是
为sysadmin固定角色的成员预留的，并且需要指定with log选项；20~25级被认为是致命
错误。
state： 表示错误的状态，是1~255中的值。
option： 错误的自定义选项，可以是下列任一值
a. log： 在Microsoft SQL Server数据库引擎实例的错误日志和应用程序日志中记
录错误。
b. nowait： 将消息立即发送给客户端。
c. seterror： 将@@error值和error_nummber值设置为msg_id或50000，不用考虑
严重级别。
