存储过程与自定义函数.ppt

《存储过程与自定义函数.ppt》由会员分享，可在线阅读，更多相关《存储过程与自定义函数.ppt（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存存储过程与自定程与自定义函函数数现在学习的是第1页，共38页12.1 存存储过程程存储过程是存储过程是T-SQL语句和流程控制语句的集合，在大型语句和流程控制语句的集合，在大型数据库系统的开发和应用中，具有很重要的作用。数据库系统的开发和应用中，具有很重要的作用。现在学习的是第2页，共38页12.1.1 存存储过程的概念程的概念运行在客户端的应用程序对服务器端的数据库进行访问运行在客户端的应用程序对服务器端的数据库进行访问的一般流程如下：的一般流程如下：（1）T-SQL语句发送到服务器。语句发送到服务器。（2）数据库服务器编译）数据库服务器编译T-SQL语句。语句。（3）产生查询执行计划。）

2、产生查询执行计划。（4）执行查询计划。）执行查询计划。（5）将执行结果返回客户端。）将执行结果返回客户端。在这个过程中，每一条在这个过程中，每一条SQL语句是单独的、一句一句的语句是单独的、一句一句的从客户端向服务器发送请求，然后数据库服务器再将结果一从客户端向服务器发送请求，然后数据库服务器再将结果一个一个地返回给应用程序。有一些应用程序，涉及的服务器个一个地返回给应用程序。有一些应用程序，涉及的服务器端数据库处理活动很多，而与用户的交互活动较少，在这种端数据库处理活动很多，而与用户的交互活动较少，在这种情况下，将有关数据库的处理活动以储存过程的形式放在数情况下，将有关数据库的处理活动以储存

3、过程的形式放在数据库服务器上完成，则可以大大减轻网上传输流量，提高应据库服务器上完成，则可以大大减轻网上传输流量，提高应用程序性能。用程序性能。现在学习的是第3页，共38页12.1.2 存存储过程的程的优缺点缺点存储过程能够封装一些常用的复杂操作以提高复用性，并存储过程能够封装一些常用的复杂操作以提高复用性，并且保证数据库的安全性。然而在享有存储过程带来便利的同时，且保证数据库的安全性。然而在享有存储过程带来便利的同时，仍然需要考虑系统的应用需求以确定是否选用存储过程。仍然需要考虑系统的应用需求以确定是否选用存储过程。1存储过程的优点存储过程的优点2存储过程的缺点存储过程的缺点现在学习的是第4

4、页，共38页12.1.3 存存储过程的程的类型型在在SQL Server 2005中，存储过程可以分为两类，分别中，存储过程可以分为两类，分别是是T-SQL存储过程和存储过程和CLR存储过程。存储过程。T-SQL存储过程是指用存储过程是指用T-SQL语句编写的存储过程，是一组完成特定功能的语句编写的存储过程，是一组完成特定功能的T-SQL语句集合；语句集合；CLR存储过程是指引用存储过程是指引用.NET FRAMEWORK公共公共语言时方法的存储过程。它在语言时方法的存储过程。它在.NET FRAMEWORK程序集中程序集中是以类的公共静态方法实现的。是以类的公共静态方法实现的。按照存储过程的

5、应用目和来源的不同，可以将存储过程按照存储过程的应用目和来源的不同，可以将存储过程分为系统存储过程、用户存储过程和扩展存储过程。分为系统存储过程、用户存储过程和扩展存储过程。1系统存储过程系统存储过程2用户存储过程用户存储过程3扩展存储过程扩展存储过程现在学习的是第5页，共38页12.1.4 存存储过程与程与视图的比的比较存储过程与视图的区别如下：存储过程与视图的区别如下：视图是从一个或者多个表或视图中导出来的表，其结构和视图是从一个或者多个表或视图中导出来的表，其结构和数据是建立在对表的查询基础上的，但其并不存在物理上的结数据是建立在对表的查询基础上的，但其并不存在物理上的结构；而存储过程类

6、似于程序，一个用构；而存储过程类似于程序，一个用SQL语句写的程序，在其语句写的程序，在其内可以定义变量。内可以定义变量。视图在每次查询时，都需要重新生成执行计划；而存储过视图在每次查询时，都需要重新生成执行计划；而存储过程第一次编译完成后，以后就不需要再次编译，执行速度比视程第一次编译完成后，以后就不需要再次编译，执行速度比视图快。图快。可以在单个存储过程中执行一系列可以在单个存储过程中执行一系列T-SQL语句，存储过程语句，存储过程可以包含程序流，逻辑以及对数据库查询的可以包含程序流，逻辑以及对数据库查询的T-SQL语句，而视语句，而视图中只能是图中只能是SELECT语句。语句。视图的查询

7、条件是固定的，而存储过程可以通过给里面的视图的查询条件是固定的，而存储过程可以通过给里面的参数赋值达到查询条件灵活变化的效果。参数赋值达到查询条件灵活变化的效果。现在学习的是第6页，共38页12.2 创建和建和执行存行存储过程程对于存储过程的概念和优缺点有了充分的认识之后，下对于存储过程的概念和优缺点有了充分的认识之后，下面将详细讲解存储过程的核心内容，即如何创建和执行存储面将详细讲解存储过程的核心内容，即如何创建和执行存储过程。过程。现在学习的是第7页，共38页12.2.1 创建存建存储过程程存储过程是一组实现特定功能的存储过程是一组实现特定功能的T-SQL语句集，使用存语句集，使用存储过程

8、不仅能够极大的提升执行效率，而且可以作为一种安储过程不仅能够极大的提升执行效率，而且可以作为一种安全机制，实现用户不同级别权限的控制。全机制，实现用户不同级别权限的控制。1创建存储过程的语法创建存储过程的语法2存储过程的结构存储过程的结构3创建存储过程的规则创建存储过程的规则4使用使用T-SQL创建存储过程创建存储过程5使用使用SSMS创建存储过程创建存储过程现在学习的是第8页，共38页12.2.1 创建存建存储过程程现在学习的是第9页，共38页12.2.1 创建存建存储过程程现在学习的是第10页，共38页12.2.2 执行存行存储过程程为了便于演示，此处存储过程的执行都是放在为了便于演示，此

9、处存储过程的执行都是放在SSMS查查询编辑器窗口中进行的。系统开发过程中，不同的编程语言询编辑器窗口中进行的。系统开发过程中，不同的编程语言有不同的执行存储过程语法。有不同的执行存储过程语法。1执行存储过程的语法执行存储过程的语法2执行存储过程执行存储过程现在学习的是第11页，共38页12.2.2 执行存行存储过程程现在学习的是第12页，共38页12.2.3 带输入参数的存入参数的存储过程程系统开发过程中，有时需要根据用户的输入信息产生不系统开发过程中，有时需要根据用户的输入信息产生不同的查询结果，即把用户的输入信息作为参数传递给存储过同的查询结果，即把用户的输入信息作为参数传递给存储过程，这

10、就要用到带输入参数的存储过程。程，这就要用到带输入参数的存储过程。1创建带输入参数的存储过程创建带输入参数的存储过程2执行带输入参数的存储过程执行带输入参数的存储过程现在学习的是第13页，共38页12.2.3 带输入参数的存入参数的存储过程程现在学习的是第14页，共38页12.2.4 带输出参数的存出参数的存储过程程系统开发过程中，有时需要对查询结果进行一定形式的系统开发过程中，有时需要对查询结果进行一定形式的操作，并接结果返回给用户，需要用到带输出参数的存储过操作，并接结果返回给用户，需要用到带输出参数的存储过程。程。1创建带输出参数的存储过程创建带输出参数的存储过程2执行带输入参数的存储过

11、程执行带输入参数的存储过程现在学习的是第15页，共38页12.2.4 带输出参数的存出参数的存储过程程现在学习的是第16页，共38页12.2.4 带输出参数的存出参数的存储过程程现在学习的是第17页，共38页12.2.5 综合合实例例本小节将展示一个综合程度较高的存储过程实例，该实本小节将展示一个综合程度较高的存储过程实例，该实例以带输入参数的存储过程为主要结构，结合常用的例以带输入参数的存储过程为主要结构，结合常用的T-SQL语法结构，其功能是实现大批量数据的分页输出。语法结构，其功能是实现大批量数据的分页输出。1存储过程代码存储过程代码2关键环节解析关键环节解析现在学习的是第18页，共38

12、页12.2.5 综合合实例例现在学习的是第19页，共38页12.3 存存储过程的有关操作程的有关操作除了可以创建和执行存储过程外，还可以对存储过程执除了可以创建和执行存储过程外，还可以对存储过程执行修改、查看、重命名和删除等操作，实现较丰富的功能。行修改、查看、重命名和删除等操作，实现较丰富的功能。现在学习的是第20页，共38页12.3.1 修改存修改存储过程程已有的存储过程不完善，或者产生新的特定业务需求时，已有的存储过程不完善，或者产生新的特定业务需求时，需要对原有的存储过程进行修改。修改之后的存储过程与原需要对原有的存储过程进行修改。修改之后的存储过程与原存储过程名称相同。存储过程名称相

13、同。1修改存储过程基本语法修改存储过程基本语法2使用视图设计器对存储过程进行修改使用视图设计器对存储过程进行修改现在学习的是第21页，共38页12.3.1 修改存修改存储过程程现在学习的是第22页，共38页12.3.1 修改存修改存储过程程现在学习的是第23页，共38页12.3.1 修改存修改存储过程程现在学习的是第24页，共38页12.3.2 查看存看存储过程程使用存储过程时，难以根据存储过程的名称直接判断其使用存储过程时，难以根据存储过程的名称直接判断其功能，需要对存储过程进行查看操作才能获得有关该存储过功能，需要对存储过程进行查看操作才能获得有关该存储过程的详细信息。查看存储过程的方法有

14、两种，一种是使用程的详细信息。查看存储过程的方法有两种，一种是使用SSMS查看，另一种是使用查看，另一种是使用T-SQL查看。查看。1使用使用T-SQL语句查看存储过程语句查看存储过程2使用使用SSMS查看存储过程的定义查看存储过程的定义现在学习的是第25页，共38页12.3.2 查看存看存储过程程现在学习的是第26页，共38页12.3.2 查看存看存储过程程现在学习的是第27页，共38页12.3.3 重命名存重命名存储过程程创建存储过程时定义的名称有时不能准确的反映该存储创建存储过程时定义的名称有时不能准确的反映该存储过程的功能，或者很多存储过程需要按照一定的规则统一命过程的功能，或者很多存

15、储过程需要按照一定的规则统一命名，这就需要对原有存储过程进行重命名。名，这就需要对原有存储过程进行重命名。现在学习的是第28页，共38页12.3.3 重命名存重命名存储过程程现在学习的是第29页，共38页12.3.4 删除存除存储过程程不需要的存储过程可以删除掉，然而删除的时候要详细不需要的存储过程可以删除掉，然而删除的时候要详细分析是否存在关联到该存储过程的其他存储过程。如果存在，分析是否存在关联到该存储过程的其他存储过程。如果存在，则调用该存储过程时会产生错误信息。删除存储过程同样有则调用该存储过程时会产生错误信息。删除存储过程同样有两种方法，一种是使用两种方法，一种是使用SSMS，另一种

16、是使用，另一种是使用T-SQL语句进语句进行删除。行删除。1使用使用T-SQL语句删除存储过程语句删除存储过程2使用使用SSMS删除存储过程删除存储过程现在学习的是第30页，共38页12.3.4 删除存除存储过程程现在学习的是第31页，共38页12.4 系系统存存储过程与程与扩展存展存储过程程前文所涉及的存储过程均为自定义存储过程，即为实现前文所涉及的存储过程均为自定义存储过程，即为实现某种特定的业务需求，由用户编写的某种特定的业务需求，由用户编写的T-SQL语句集。除此之语句集。除此之外，外，SQL Server 2005还提供了系统存储过程和扩展存储过还提供了系统存储过程和扩展存储过程。程

17、。现在学习的是第32页，共38页12.4.1 系系统存存储过程程系统存储过程是系统存储过程是SQL Serer预先定义好的，其功能主要预先定义好的，其功能主要定位于为用户提供对象属性、系统性能等信息的查看，避免定位于为用户提供对象属性、系统性能等信息的查看，避免用户直接访问系统表。用户直接访问系统表。1系统存储过程分类系统存储过程分类2系统存储过程的应用系统存储过程的应用现在学习的是第33页，共38页12.4.1 系系统存存储过程程现在学习的是第34页，共38页12.4.2 扩展存展存储过程程扩展存储过程提供从扩展存储过程提供从SQL Server到外部程序的接口，以到外部程序的接口，以便进行

18、各种维护活动，实质上是便进行各种维护活动，实质上是SQL Server可以动态装载并执可以动态装载并执行的动态链接库。下面列出一些常用的扩展存储过程。行的动态链接库。下面列出一些常用的扩展存储过程。xp_availablemedia：xp_dirtree：xp_enumdsn：xp_enum groups：xp_getfiledetails：xp_makecab：xp_ntsec_enumdomains：xp_servicecontrol：xp_subdirs：现在学习的是第35页，共38页12.5 用用户自定自定义函数函数SQL Server 2005不仅提供了丰富的系统内置函数，同不仅提供

19、了丰富的系统内置函数，同时还允许用户自定义函数，满足个性化的业务需求。用户自时还允许用户自定义函数，满足个性化的业务需求。用户自定义函数是定义函数是SQL Server的数据库对象，它不能用于执行一的数据库对象，它不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等程序段中使用，也可以像存储过程一样通过询或存储过程等程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。命令来执行。1用户自定义函数概述用户自定义函数概述2创建用户自定义函数创建用户自定义函数现在学习的是第36页，共38页12.5 用用户自

20、定自定义函数函数现在学习的是第37页，共38页12.6 小小结SQL Server提供了一种方法，它可以将一些固定的操提供了一种方法，它可以将一些固定的操作集中起来由作集中起来由SQL Server数据库服务器来完成，以实现某个数据库服务器来完成，以实现某个任务，这种方法就是存储过程。它是一组为了完成特定功能任务，这种方法就是存储过程。它是一组为了完成特定功能的的T-SQL语句的预编译集合，经过编译后存储在数据库中，语句的预编译集合，经过编译后存储在数据库中，前台或者后台程序可以通过存储过程的名字调用执行该存储前台或者后台程序可以通过存储过程的名字调用执行该存储过程。过程。现在学习的是第38页，共38页