第12章存储过程与自定义函数省名师优质课赛课获奖课件市赛课一等奖课件

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