第10单元 创建和管理存储过程
学习目标
知识目标:
了解存储过程的作用
掌握存储过程的分类
能力目标:
会创建和管理用户自定义存储过程
会调用存储过程
任务1 存储过程
一、 存储过程概述
在开发SQL Server应用程序过程中，T-SQL语句是应用程序与SQL Server数据库之间使用的主要编译接口。
应用程序与SQL Server数据库交互执行某些操作有两种方法：一种是存储在本地的应用程序中记录操作命令，应用程序向SQL Server发送每一个命令，并对返回的数据进行处理：另一种是在SQL Server中定义某个过程，其中记录了一系列的操作，每次应用程序只需调用该过程就可以完成操作。这种在SQL Server中定义的过程被称为存储过程。
存储过程是一组T-SQL语句的集合，它类似于函数，具有模块化的优点。在T-SQL语句的编写过程中，如果只有几百条语句则很容易查看阅读，可以不用对这些语句进行模块化，但如果有成千上万条语句，如果不采用存储过程，则是非常难以阅读的。例如，有一些算法，如某公司的计算工资的算法，如果不采用存储过程，则每次计算工资时需要重写算法语句，难免不出差错。另外，还有许许多多使用到存储过程的场合，可以说，存储过程是T-SQL语句的骨架。
SQL Server中的存储过程与其他语言中的过程（或函数>类似，它们有一些共同的 特征，如它们都接受输入参数，并向调用程序或语句返回值。它们都包含执行操作或调用其他过程的编程语句。可以向调用程序返回状态值，指示执行过程是否成功。
相对直接使用T-SQL语句来说，存储过程具有如下优点：
存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库服务器中，第一次执行时将被编译，之后调用将不再编译。它有以下优点：
1.允许模块化程序设计。即只需创建一次，以后在程序中就可以反复调用该存储过程。通过嵌套的存储过程的调用，可以建立起程序调用的层次关系，使程序结构更加清晰。
2.提高执行效率。如果某操作需要执行大量SQL语句或重复执行，由于存储过程已经被编译过了，所以它的执行效率比直接运行SQL语句更高。因为存储过程在最初创建时编译，在使用时已经是使用目标代码了。
3.减少网络流量。存储过程是存放在服务器端的，客户端只需发送一条调用命令即可。
4.更好的安全机制，可以用授予用户执行存储过程的权限来代替授予用户数据库对象的权限，从而提高数据的安全性。
二 、存储过程的类型
SQL Server的存储过程主要分为三类：系统存储过程、自定义存储过程和扩展存储过程。
系统存储过程是由SQL Server提供可以作为命令执行的存储过程，通常以“sp_”为前缀。
自定义存储过程是由用户自己用T-SQL语句编写的存储过程，可以完成某个特定业务要求。
“用户自定义存储过程”也称本地存储过程，它是创建在用户自己数据库中的存储过程，这种存储过程是用户创建的普通存储过程，没有前缀sp_。在SQL Server 中，用户存储过程可以使用T-SQL语言编写，也可以使用CLR方式编写。在此主要介绍使用T-SQL语言编写的本地存储过程。
扩展存储过程是通过动态链接库（DLL文件）来实现的，通常以“xp_”为前缀。
例：exec xp_cmdshell 'dir c:'
可能出现出错信息：
SQL Server 阻止了对组件 'xp_cmdshell' 的 过程 'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
备注 解决思路：出现这种错误是因为使用bcp命令的时候，没有权限。SQL Server的用户没有SP_configure的使用权限，需要将对应用户添加"sysadmin"角色。所以，要首先启动该命令。
执行下面命令：
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
再执行：exec xp_cmdshell 'dir c:'
便可成功执行。
系统存储过程可以归纳为若干类。下面是几个常用的类别：
数据库引擎存储过程：用于SQL Server数据库引擎的常规维护。
分布式查询存储过程：用于实现和管理分布式查询。分布式查询可以从多个数据源检索数据。这些数据源可以包含一个或多个SQL Server数据库。
游标存储过程：用于实现游标变量功能。
复制存储过程：用于管理复制，包括复制表的架构和数据。
安全性存储过程：用于管理数据库安全性。
SQL Server代理存储过程：由SQL Server代理用于管理计划的活动和事件驱动活动。
XML存储过程：用于XML文本管理。
常规扩展存储过程：提供从SQL Server实例到外部程序的接口，以便进行各种维 护活。
临时存储过程 是一种特殊的本地存储过程。如果在本地存储过程前面有一个“#”，这种存储过程称为局部临时存储过程，只能在一个用户会话中使用。如果在本地存储过程前面有一个“##”，这种存储过程称为全局临时存储过程，可以在所有用户会话中使用。
远程存储过程 是指保存在远程服务器上的存储过程，也就是非本地服务器上的存储过程。
本单元主要介绍自定义存储过程的创建和管理。
