从零开始学SQL Server――第12章 存储过程与自定义函数

第12章存储过程与自定义函数存储过程是一组预先编译好的T-SQL代码，即在执行时就不用再次进行编译，这样会提高程序的运行效率。它类似于其他高级语言里的过程，是一种能够完成特定功能的函数。存储过程可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。本章内容主要包括：存储过程的概念；创建和执行存储过程；存储过程的操作；系统存储过程；自定义函数。12.1存储过程存储过程是T-SQL语句和流程控制语句的集合，在大型数据库系统的开发和应用中，具有很重要的作用。12.1.1存储过程的概念运行在客户端的应用程序对服务器端的数据库进行访问的一般流程如下：（1）T-SQL语句发送到服务器。（2）数据库服务器编译T-SQL语句。（3）产生查询执行计划。（4）执行查询计划。（5）将执行结果返回客户端。在这个过程中，每一条SQL语句是单独的、一句一句的从客户端向服务器发送请求，然后数据库服务器再将结果一个一个地返回给应用程序。有一些应用程序，涉及的服务器端数据库处理活动很多，而与用户的交互活动较少，在这种情况下，将有关数据库的处理活动以储存过程的形式放在数据库服务器上完成，则可以大大减轻网上传输流量，提高应用程序性能。12.1.2存储过程的优缺点存储过程能够封装一些常用的复杂操作以提高复用性，并且保证数据库的安全性。然而在享有存储过程带来便利的同时，仍然需要考虑系统的应用需求以确定是否选用存储过程。1．存储过程的优点2．存储过程的缺点12.1.3存储过程的类型在SQLServer2005中，存储过程可以分为两类，分别是T-SQL存储过程和CLR存储过程。T-SQL存储过程是指用T-SQL语句编写的存储过程，是一组完成特定功能的T-SQL语句集合；CLR存储过程是指引用.NETFRAMEWORK公共语言时方法的存储过程。它在.NETFRAMEWORK程序集中是以类的公共静态方法实现的。按照存储过程的应用目和来源的不同，可以将存储过程分为系统存储过程、用户存储过程和扩展存储过程。1．系统存储过程2．用户存储过程3．扩展存储过程12.1.4存储过程与视图的比较存储过程与视图的区别如下：视图是从一个或者多个表或视图中导出来的表，其结构和数据是建立在对表的查询基础上的，但其并不存在物理上的结构；而存储过程类似于程序，一个用SQL语句写的程序，在其内可以定义变量。视图在每次查询时，都需要重新生成执行计划；而存储过程第一次编译完成后，以后就不需要再次编译，执行速度比视图快。可以在单个存储过程中执行一系列T-SQL语句，存储过程可以包含程序流，逻辑以及对数据库查询的T-SQL语句，而视图中只能是SELECT语句。视图的查询条件是固定的，而存储过程可以通过给里面的参数赋值达到查询条件灵活变化的效果。12.2创建和执行存储过程对于存储过程的概念和优缺点有了充分的认识之后，下面将详细讲解存储过程的核心内容，即如何创建和执行存储过程。12.2.1创建存储过程存储过程是一组实现特定功能的T-SQL语句集，使用存储过程不仅能够极大的提升执行效率，而且可以作为一种安全机制，实现用户不同级别权限的控制。1．创建存储过程的语法2．存储过程的结构3．创建存储过程的规则4．使用T-SQL创建存储过程5．使用SSMS创建存储过程12.2.1创建存储过程12.2.1创建存储过程12.2.2执行存储过程为了便于演示，此处存储过程的执行都是放在SSMS查询编辑器窗口中进行的。系统开发过程中，不同的编程语言有不同的执行存储过程语法。1．执行存储过程的语法2．执行存储过程12.2.2执行存储过程12.2.3带输入参数的存储过程系统开发过程中，有时需要根据用户的输入信息产生不同的查询结果，即把用户的输入信息作为参数传递给存储过程，这就要用到带输入参数的存储过程。1．创建带输入参数的存储过程2．执行带输入参数的存储过程12.2.3带输入参数的存储过程12.2.4带输出参数的存储过程系统开发过程中，有时需要对查询结果进行一定形式的操作，并接结果返回给用户，需要用到带输出参数的存储过程。1．创建带输出参数的存储过程2．执行带输入参数的存储过程12.2.4带输出参数的存储过程12.2.4带输出参数的存储过程12.2.5综合实例本小节将展示一个综合程度较高的存储过程实例，该实例以带输入参数的存储过程为主要结构，结合常用的T-SQL语法结构，其功能是实现大批量数据的分页输出。1．存储过程代码2．关键环节解析12.2.5综合实例12.3存储过程的有关操作除了可以创建和执行存储过程外，还可以对存储过程执行修改、查看、重命名和删除等操作，实现较丰富的功能。12.3.1修改存储过程已有的存储过程不完善，或者产生新的特定业务需求时，需要对原有的存储过程进行修改。修改之后的存储过程与原存储过程名称相同。1．修改存储过程基本语法2．使用视图设计器对存储过程进行修改12.3.1修改存储过程12.3.1修改存储过程12.3.1修改存储过程12.3.2查看存储过程使用存储过程时，难以根据存储过程的名称直接判断其功能，需要对存储过程进行查看操作才能获得有关该存储过程的详细信息。查看存储过程的方法有两种，一种是使用SSMS查看，另一种是使用T-SQL查看。1．使用T-SQL语句查看存储过程2．使用SSMS查看存储过程的定义12.3.2查看存储过程12.3.2查看存储过程12.3.3重命名存储过程创建存储过程时定义的名称有时不能准确的反映该存储过程的功能，或者很多存储过程需要按照一定的规则统一命名，这就需要对原有存储过程进行重命名。12.3.3重命名存储过程12.3.4删除存储过程不需要的存储过程可以删除掉，然而删除的时候要详细分析是否存在关联到该存储过程的其他存储过程。如果存在，则调用该存储过程时会产生错误信息。删除存储过程同样有两种方法，一种是使用SSMS，另一种是使用T-SQL语句进行删除。1．使用T-SQL语句删除存储过程2．使用SSMS删除存储过程12.3.4删除存储过程12.4系统存储过程与扩展存储过程前文所涉及的存储过程均为自定义存储过程，即为实现某种特定的业务需求，由用户编写的T-SQL语句集。除此之外，SQLServer2005还提供了系统存储过程和扩展存储过程。12.4.1系统存储过程系统存储过程是SQLSerer预先定义好的，其功能主要定位于为用户提供对象属性、系统性能等信息的查看，避免用户直接访问系统表。1．系统存储过程分类2．系统存储过程的应用12.4.1系统存储过程12.4.2扩展存储过程扩展存储过程提供从SQLServer到外部程序的接口，以便进行各种维护活动，实质上是SQLServer可以动态装载并执行的动态链接库。下面列出一些常用的扩展存储过程。xp_availablemedia：xp_dirtree：xp_enumdsn：xp_enumgroups：xp_getfiledetails：xp_makecab：xp_ntsec_enumdomains：xp_servicecontrol：xp_subdirs：12.5用户自定义函数SQLServer2005不仅提供了丰富的系统内置函数，同时还允许用户自定义函数，满足个性化的业务需求。用户自定义函数是SQLServer的数据库对象，它不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等程序段中使用，也可以像存储过程一样通过EXECUTE命令来执行。1．用户自定义函数概述2．创建用户自定义函数12.5用户自定义函数12.6小结SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。它是一组为了完成特定功能的T-SQL语句的预编译集合，经过编译后存储在数据库中，前台或者后台程序可以通过存储过程的名字调用执行该存储过程。