存储过程（Procedure）

1.定义（Definition）：

存储过程类似C#或C++中的方法，用来执行管理任务或应用复杂的业务逻辑，通过一次编译后，只需带参调用即可。

注：

创建储存过程时，该储存过程就进行了编译，并直接保存到可编程性--储存过程中，后期直接调用即可，减少网络流量，减轻网络负担。

2.组成：

储存过程名储存过程参数数据操纵语句、变量语句、逻辑控制语句等可以有返回值返回单个或者多个结果集

3.优点：

提升系统运行速度。（直接调用 ）简化操作流程。（封装复杂或重复的数据库操作）允许模块化程序设计，提升程序可维护性和可移植性。（统一的数据库访问接口）提高系统的安全性。（对储存过程设置访问权限，可作为一种安全机制）减轻网络流量。（通信只需传输调用语句，减轻网络负载）

4.缺点：

数据库移植不方便。（代码封装，移植时不一定通用）不支持面向对象设计。（结构化查询语言，擅长查询，无法通过面向对象思想大量封装业务逻辑，因此无法形成通用的可支持复用的业务逻辑框架。）调试复杂。（原因：集成设备电路IDE（Integrated Device Electronics）的问题）代码可读性差，不支持群集，数据库服务器无法水平扩展。无法适应数据库的切割。没办法应用缓存。（若缓存并发严重，严重影响效率）对象关系映射ORM（Object Relational Mapping）竞争出现。Teacher基表创建存储过程(Create Produce)

语法：

--*****选出性别为男或者薪资为8000的所有老师信息*********create procedure USP_GetAllInfo --创建 存储过程 存储过程名称@Gender bit , --形参只能是声明变量，非定义不能用declare。@Salary moneyas --执行语句select * from Teacher where Gender = @Gender or Salary = @Salary --操纵或控制语句。go --批处理语句。

if exists (select * from sysobjects where name = 'USP_GetAllInfo') --sysobjects：系统表，保存了当前数据库的所有对象，都存在于sys.sysobjects视图对象中。drop procedure USP_GetAllInfo ----删除存储过程go

法一：不用dbo架构，在调用寻找储存过程名时，盲目寻找，遍历，效率低。

--**调用Exec USP_GetAllInfo false , @Salary = 5900 --调用参数时，第一个参数可省略，第二个及以后，必须指定赋值。go

法二：储存过程一般存在dbo架构下，调用dbo架构，提高定位精度，提高效率。

--**调用dbo架构（系统管理员架构）中的储存过程Exec dbo.USP_GetAllInfo false , @Salary = 5900 --调用参数时，第一个参数可省略，第二个及以后，必须指定赋值。go

结果：

删除储存过程（drop procedure）

drop procedure USP_GetAllInfo 

 --修改存储过程 alter procedure USP_GetAllCName asupdate Teacher set Age = 15 where Name ='bb' go

1.系统存储过程 (System Procedure)

定义：系统存储过程是SQL Server系统本身预定义的存储过程。常以前缀"sp_ "[system procedure]开头。

位置：创建并存放在系统数据库master中。

作用：从sysbojects系统表中获取信息，完成数据库服务器的管理工作。

权限：部分只能系统管理员使用，部分可开通授权，被用户使用。

常见的系统存储过程有：

sp_article_validation 启动对指定项目的数据验证请求。sp_databases 列出服务器上的所有数据库信息,包括数据库名称和数据库大小。sp_helpdb 报告有关指定数据库或所有数据库的信息。sp_renamedb ['',''] 更改数据库的名称。sp_help 查看某个数据库对象的信息,如列名,主键,约束,外键,索引。sp_helpindex [TableName ] 查看某个表的索引。sp_helpconstraint [TableName ] 查看某个表的约束。sp_stored_procedures 显示储存过程的列表。sp_password ['',''] 修改密码 。sp_tables 返回当前环境下可查询的表或视图的信息。sp_columns [ Table or View] 返回某个表或视图的列信息,包括列的类型和长度。sp_helptext 显示默认值,未加密的存储过程,用户定义的存储过程,触发器或视图的实际文本还可以查看该存储过程所在的DLL，比如xp_availablemedia所在的DLL为xpstar.dll。

2.自定义存储过程(Self Definition Procedure)

定义：用户创建的存储过程。

位置：存放在dbo架构下的用户数据库中。

作用：为了实现某一特定业务需求。

特点：

可以接受输入参数向客户端返回结果和信息返回输出参数

分类：

T-SQL存储过程：值保存的T_SQL语句集合，可以接受和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据CLR存储过程：指引用Microsoft.NET Framework公共语言的方法存储过程，可以接受和返回用户提供的参数，它们在.NET Framework程序集是作为类的公共静态方法实现的。

权限：

1.全局的临时储存过程

定义：在创建的储存过程名称前加两个“##”。

例如：

create procedure ##USP_GetAllInfo asselect * from Teacher go 

作用域：整个过程全局可用。

2.局部的临时储存过程：

定义：在创建的储存过程名称前加一个“#”。

例如：

create procedure #USP_GetAllInfo asselect * from Teacher go 

作用域：只能在创建它的回话中使用，会话结束时，将被删除。

自定义存储过程如：

3.扩展储存过程 (Extend Procedure)

定义： 扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的存储过程。常以前缀"xp_ "[extend procedure]开头。

扩展存储过程的工作流程：

当客户端执行扩展存储的过程时，以表格格式数据流 (TDS) 或从客户端应用程序的简单对象访问协议 (SOAP) 格式传输请求 Microsoft SQL Server。SQL Server 搜索与扩展存储过程关联的 DLL 并加载此 DLL（如果尚未加载）。SQL Server 调用请求的扩展存储过程（在 DLL 内部作为函数实现）。扩展存储过程通过扩展存储过程 API 传递结果集，并将参数返回给服务器。

作用：

可以加载到SQL SERVER实例运行的地址空间中执行。还可以用SQL SERVER扩展存储过程API编程。

常用的扩展储存过程：

xp_availablemedia 查看系统上可用的磁盘驱动器的空间信息。xp_cmdshell 执行DOS各种命令，结果以文本行返回。xp_dirtree 查看某个目录下所有子目录的结构xp_loginconfig 报告SQL Server 实例在Windows 上运行时的登录安全配置xp_enumgroups 查看系统上的组信息xp_getfiledetails 获取某个文件的属性xp_makecab 将目标多个文件压缩到某个目标档案之内。所有要压缩的档案都可以接在参数列的最后方。如：xp_makecab 'd:/TestSchool.zip', 'mssqlzip',1,'d:/1.jpg','d:2.txt'xp_ntsec_enumdomains 列出服务器的机器名极其所在的NT域的名称。xp_servicecontrol 停掉或启动某个服务 ，对window服务进行操作。xp_subdirs 只列某个目录下的第一层子目录的信息

本文部分内容参考至网络，如有错误，敬请指正，如有侵权，请联系修改，谢谢。