第十二章存储过程与自定义函数.pptVIP

第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 存储过程的类型 在SQL Server 2005中，存储过程可以分为两类，分别是T-SQL存储过程和CLR存储过程。T-SQL存储过程是指用T-SQL语句编写的存储过程，是一组完成特定功能的T-SQL语句集合；CLR存储过程是指引用.NET FRAMEWORK公共语言时方法的存储过程。它在.NET FRAMEWORK程序集中是以类的公共静态方法实现的。 按照存储过程的应用目和来源的不同，可以将存储过程分为系统存储过程、用户存储过程和扩展存储过程。 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 存储过程的有关操作 除了可以创建和执行存储过程