数据库系统原理及应用-存储过程

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Click to edit Master title style,数据库系统原理及应用,SQLServer2000,编程,-,存储过程,存储过程,第一步,第二步,第三步,存储过程,的,基本知识,创建用户,存储过程,存储过程,的,参数,2,1,存储过程的基本知识,1. 概念,存储过程,（,Stored Procedure）,是存储在服务器上的,Transact-SQL,语句的命名集合,是封装重复性任务的方法,支持用户声明变量、条件执行以及其他强有力的编程特性,2.功能,存储过程与其他编程语言中的过程类似，它可以,包含执行数据库操作（包括调用其他过程）的编程语句,接受输入参数,向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）,以输出参数的形式将多个值返回至调用过程或批处理,3,1,存储过程的基本知识,3.,优点,使用存储过程而不使用存储在客户端计算机本地的,T-SQL,程序的优点包括：,与其他应用程序共享应用逻辑，确保一致的数据访问和修改。,存储过程封装了商务逻辑。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用,能够实现较快的执行速度,预编译的,Transact-SQL,语句，可以根据条件决定执行哪一部分,能够减少网络流量,客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包,提供了安全性机制,。,屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限,4,1,存储过程的基本知识,4,分类,SQL Server,支持五种类型的存储过程,系统存储过程（,sp_）：,存储在,master,数据库内，以,“,sp_”,前缀标识,本地存储过程：,本地存储过程在单独的用户数据库内创建,临时存储过程：,临时存储过程可能是局部的，名称以,“#”,开头；也可能是全局的，名称以,“#”,开头,远程存储过程：,远程存储过程是,SQL Server,早期版本的特性，分布式查询支持这项功能,扩展存储过程（,xp_）：,扩展存储过程以动态链接库（,DLL）,的形式实现，在,SQL Server,环境外执行,5,存储过程的初始处理,存储过程的处理,包括创建，以及初次执行时将执行计划放入过程缓存,过程缓存是一个包含所有当前正在执行的,Transact-SQL,语句的执行计划的内存池，其大小动态变化,过程缓存在内存池内。内存池是,SQL Server,内存的主要单元，它包含了,SQL Server,中大部分使用内存的数据结构,6,存储过程的初始处理,存储过程的创建,创建存储过程时，先分析该过程中的语句以检查语法的准确性。然后,SQL Server,将存储过程的名字存入当前数据库的,sysobjects,系统表中，存储过程的文本存入当前数据库的,syscomments,系统表中,延迟名称解析：存储过程引用的对象不需要在创建该存储过程时就存在，而只需在执行该存储过程时存在,存储过程的执行（初次或重新编译时）,存储过程初次执行或者重新编译后，查询处理器读入存储过程的处理过程称为,解析,数据库的某些变化会使得执行计划低效或失效，,SQL Server,检测这些变化并自动重新编译执行计划,7,存储过程的初始处理,优化,如果过程执行顺利通过解析阶段，则查询优化器将分析该存储过程中的,Transact-SQL,语句，并创建一个执行计划，描述执行存储过程的最快方法,编译,编译,指的是分析存储过程，创建执行计划并将之放入过程缓存的过程,过程缓存包含了最有价值的存储过程执行计划。增加执行计划价值的因素包括：重新编译需要的时间（高的编译代价）和频繁被使用,执行计划所依据的信息包括：,表中的数据量；,表中是否存在索引及索引的属性以及数据在索引列中的分布；,WHERE,子句条件所用的比较运算符和比较值；,是否存在联接以及,UNION、GROUP BY,和,ORDER BY,关键字。,8,存储过程的初始处理,项存入,sysobjects,和,syscomments,表,编译过的执行计划放入过程缓存,编译,优化,创建,执行,（初次或重新编译）,解析,9,存储过程的后续处理,若符合下列条件，则,SQL Server,使用在内存中的计划来执行随后的查询,当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境,存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析,SQL Server,的执行计划有两个主要部分,查询计划：执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用，这称为查询计划。查询计划中不存储用户环境,执行环境：每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构，称为执行环境,10,存储过程的后续处理,在缓存中，对于每个存储过程和环境的组合最多只有一个编译过的计划。对于一个存储过程的多个不同环境，可以有多个计划,形成不同环境并影响编译选择的因素,并行和串行编译计划,隐含的对象拥有,不同的,SET,选项,一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，,SQL Server,将老的、没用的计划移出缓存,11,存储过程的后续处理,检索到的执行计划,未用过的计划过时被清除,执行计划,执行上下文,SELECT *FROM,dbo.,memberWHEREmember_no =,?,连接1,8082,连接2,连接3,24,1003,12,3,创建存储过程,既可创建一个存储过程供永久使用，也可创建一个存储过程在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。语法格式为：,Create Procedure,存储过程名,参数名 数据类型,Varying=,默认值, Output, n ,WITH Recompile |Encryption|,Recompile , Encryption ,AS SQL,语句,n ,13,说明：,存储过程名称,局部临时过程，前面加编号符（,#,）；全局临时过程，前面加两个编号符（,#,）；不能超过,128,个字符。,整数,用来对同名过程进行分组。以便用一个,Drop Procedure,语句即可将同组的过程一起删去。,参数名,指定过程中的参数。在该语句中可以声明一个或多个参数。用户必须在执行过程时提供每个声明参数的值。使用,符号作为第一个字符来指定参数名称。,数据类型,所有数据类型都可以用作存储过程的参数。,Varying,指定作为输出参数支持的结果集,(,由存储过程动态构造，内容可变化,),。仅适用游标参数。,Recompile,该过程将在运行时重新编译。,Encryption ,加密存储过程的定义,SQL,语句,n ,过程中包含的任意数目和类型的,T-SQL,语句，但有一些限制。,14,创建存储过程,创建存储过程时，需要确定存储过程的三个组成部分：,存储过程名称，包括所有的输入参数以及传给调用者的输出参数。,被执行的针对数据库的操作语句以及调用其他存储过程的语句。,返回给调用者的状态值，以指明调用是成功还是失败。,15,3 创建存储过程,创建存储过程,只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在,tempdb,数据库中,存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表,若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失,USE Northwind,GO,CREATE PROC dbo.OverdueOrders,AS,SELECT *,FROM dbo.Orders,WHERE RequiredDate GETDATE(),AND ShippedDate IS Null,GO,16,创建存储过程,在单个批处理中，,CREATE PROCEDURE,语句不能与其他,Transact-SQL,语句组合使用,CREATE PROCEDURE,定义可以包括任何数目和类型的,Transact-SQL,语句，但不包括下列对象创建语句：,CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER,和,CREATE VIEW,执行,CREATE PROCEDURE,语句的用户必须是,sysadmin、db_owner,或,db_ddladmin,角色的成员，或必须拥有,CREATE PROCEDURE,权限,依赖于可用内存，存储过程的最大大小为128,MB,17,创建存储过程,嵌套存储过程：一个存储过程调用另一个,存储过程可以嵌套32层。若试图超过32层嵌套，则整个存储过程调用链失败,当前的嵌套层数存储在系统函数 ,nestlevel,中,若一个存储过程调用了第二个存储过程，那么第二个存储过程可以访问第一个存储过程创建的所有对象，包括临时表,嵌套的存储过程可以递归调用。例如，存储过程,X,调用了存储过程,Y，,当存储过程,Y,运行的时候它可以调用存储过程,X,18,通过系统存储过程查看存储过程,可供使用的系统存储过程及其语法形式如下：,sp_help,：,用于显示存储过程的参数及其数据类型,sp_help objname= name,参数,name,为要查看的存储过程的名称。,sp_helptext,：,用于显示存储过程的源代码,sp_helptext objname= name,参数,name,为要查看的存储过程的名称。,sp_depends,：,用于显示和存储过程相关的数据库对象,sp_depends objname=object,参数,object,为要查看依赖关系的存储过程的名称。,sp_stored_procedures,：,用于返回当前数据库中的存储过程列表,19,执行存储过程,可单独执行存储过程或作为,INSERT,语句的一部分执行存储过程,必须在存储过程上拥有,EXECUTE,权限,单独执行存储过程,语法：,EXECUTE,返回状态,=,存储过程名,;,编号,|,存储过程名称变量,参数,= 值,|,变量,OUTPUT|DEFAULT,.n WITH RECOMPILE,在,INSERT,语句内执行存储过程,语法：,INSERT INTO,表名,EXECUTE ,将本地或远程存储过程返回的结果集插入本地表中,在,INSERT,语句内执行的存储过程必须返回关系结果集,20,修改和删除存储过程,修改存储过程,用,ALTER PROCEDURE,中的定义取代现有存储过程原先的定义，但保留权限分配,USE Northwind,GO,ALTER PROC dbo.OverdueOrders,AS,SELECT CONVERT(char(8),RequiredDate,1) RequiredDate,CONVERT(char(8), OrderDate, 1) OrderDate,OrderID, CustomerID, EmployeeID,FROM Orders,WHERE RequiredDate 0,print num,else,print ,无该工程使用零件信息,30,显式地重新编译存储过程,存储过程可以显式地重新编译，但应尽量少做，仅当,参数值传递给返回大量变化结果集的存储过程时,为底层表增加了存储过程可能从中受益的索引时,提供的参数值非典型时,31,显式地重新编译存储过程,三种显式重新编译存储过程的方法,CREATE PROCEDURE WITH RECOMPILE,创建存储过程时在其定义中指定,WITH RECOMPILE,选项，表明,SQL Server,将不对该存储过程计划进行高速缓存，该存储过程将在每次执行时都重新编译,EXECUTE WITH RECOMPILE,在执行存储过程时指定,WITH RECOMPILE,选项，可强制对存储过程进行重新编译,sp_recompile,sp_recompile,系统存储过程强制在下次运行存储过程或触发器时进行重新编译。若 ,objname,参数指定的是表或视图，那么所有使用指定对象的存储过程在下次执行时都会重新编译,32,调用成功与否处理,为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）,可以在错误处理逻辑中检查下列错误：返回码、,SQL Server,错误、用户定义的错误信息,RETURN,语句,从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）,返回码为0表示成功。0至-14的返回码已被系统使用，-15至-99的返回码被系统保留作将来扩展。若用户不提供返回码，则返回,SQL Server,的返回码。用户定义的返回码优先级高于系统提供的返回码,33,return,的用法,查询某一年龄范围内的学生信息,create procedure p5,bage int=18,eage int=20,as,if bageeage,retrun 1,else,begin,Select *,from student,where sage between bage and eage,return 0,end,go,调用：,declare re int,exec re=p5 17,20,select re,34,推荐操作,每个存储过程完成单个任务,在开始事务前验证数据,校验输入参数,对所有存储过程使用相同的连接设置,使用,WITH ENCRYPTION,选项隐藏存储过程定义,35,存储过程（,procedure,）类似于,C,语言中的函数,用来执行管理任务或应用复杂的业务规则,存储过程可以带参数，也可以返回结果,int sum(int a,int b),int s;,s =a+b;,return s ;,存储过程相当于,C,语言中的函数,什么是存储过程,36,存储过程,-,-,-,单个,SELECT,语句,SELECT,语句块,SELECT,语句与逻辑控制语句,可以包含,什么是存储过程,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,37,存储过程的优点,执行速度更快,允许模块化程序设计,提高系统安全性,减少网络流通量,38,存储过程的分类,系统存储过程,由系统定义，存放在,master,数据库中,类似,C,语言中的系统函数,系统存储过程的名称都以“,sp_”,开头或”,xp_”,开头,用户自定义存储过程,由用户在自己的数据库中创建的存储过程,类似,C,语言中的用户自定义函数,39,常用的系统存储过程,系统存储过程,说明,sp_databases,列出服务器上的所有数据库。,sp_helpdb,报告有关指定数据库或所有数据库的信息,sp_renamedb,更改数据库的名称,sp_tables,返回当前环境下可查询的对象的列表,sp_columns,回某个表列的信息,sp_help,查看某个表的所有信息,sp_helpconstraint,查看某个表的约束,sp_helpindex,查看某个表的索引,sp_stored_procedures,列出当前环境中的所有存储过程。,sp_password,添加或修改登录帐户的密码。,sp_helptext,显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。,40,EXEC sp_databases,EXEC,sp_renamedb,Northwind,Northwind1,USE stuDB,GO,EXEC,sp_tables,EXEC,sp_columns,stuInfo,EXEC,sp_help,stuInfo,EXEC,sp_helpconstraint,stuInfo,EXEC,sp_helpindex,stuMarks,EXEC,sp_helptext,view_stuInfo_stuMarks,EXEC,sp_stored_procedures,常用的系统存储过程,修改数据库的名称,(,单用户访问,),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表,stuInfo,的信息,查看表,stuInfo,的约束,查看表,stuMarks,的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,41,常用的系统存储过程,常用的扩展存储过程：,xp_cmdshell,可以执行,DOS,命令下的一些的操作,以文本行方式返回任何输出,调用语法：,EXEC xp_cmdshell DOS,命令,NO_OUTPUT,42,常用的系统存储过程,USE master,GO,EXEC,xp_cmdshell,mkdir d:bank, NO_OUTPUT,IF,EXISTS,(SELECT * FROM sysdatabases,WHERE name=bankDB),DROP DATABASE bankDB,GO,CREATE DATABASE bankDB,(,),GO,EXEC,xp_cmdshell,dir D:bank -查看文件,创建数据库,bankDB,，要求保存在,D:bank,创建文件夹D:bank,查看,文件夹D:bank,43,如何创建存储过程,定义存储过程的语法,CREATE PROCEDURE,存储过程名,参数,1,数据类型,=,默认值,OUTPUT, ,参数,n,数据类型,=,默认值,OUTPUT,AS,SQL,语句,GO,和,C,语言的函数一样，参数可选,参数分为输入参数、输出参数,输入参数允许有默认值,44,创建不带参数的存储过程,问题：,请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,45,创建不带参数的存储过程,CREATE PROCEDURE,proc_stu,AS,DECLARE writtenAvg float,labAvg float,SELECT writtenAvg=AVG(writtenExam),labAvg=AVG(labExam),FROM stuMarks,print 笔试平均分：+convert(varchar(5),writtenAvg),print 机试平均分：+convert(varchar(5),labAvg),IF (writtenAvg70 AND labAvg70),print 本班考试成绩：优秀,ELSE,print 本班考试成绩：较差,print -,print 参加本次考试没有通过的学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam,FROM stuInfo INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExam60 OR labExam60,GO,proc_stu,为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,46,调用存储过程,EXECUTE,（执行）,语句用来调用存储过程,调用的语法,EXEC,过程名,参数,EXEC proc_stu,47,存储过程的参数分两种：,输入参数,输出参数,输入参数：,用于向存储过程,传入,值，类似,C,语言的按值传递,;,输出参数：,用于在调用存储过程后，,返回,结果，类似,C,语言的,按引用传递,;,int sum (int a, int b),int s;,s=a+b;,return s;,c=sum,（,5, 8,）,传入参数值,返回结果,48,带输入参数的存储过程,问题：,修改上例：由于每次考试的难易程度不一样，每次,笔试和机试的及格线,可能随时变化（不再是,60,分），这导致考试的评判结果也相应变化。,分析：,在述存储过程添加,2,个输入参数：,writtenPass,笔试及格线,labPass,机试及格线,49,带输入参数的存储过程,CREATE PROCEDURE proc_stu,writtenPass int,labPass int,AS,print -,print ,参加本次考试没有通过的学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo,INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExam,writtenPass,OR labExam,labPass,GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,50,带输入参数的存储过程,EXEC proc_stu 60,55,调用带参数的存储过程,假定本次考试机试偏难，机试的及格线定为,55,分，笔试及格线定为,60,分,-,或这样调用：,EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，,李斯文,（,59,分）成为“漏网之鱼”了,51,输入参数的默认值,带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线,问题：,如果试卷的难易程度合适，则调用者还是必须,如此调用：,EXEC proc_stu 60,60,，比较麻烦,这样调用就比较合理：,EXEC proc_stu 55,EXEC proc_stu,笔试及格线,55,分，机试及格线默认为,60,分,笔试和机试及格线都默认为标准的,60,分,52,CREATE PROCEDURE proc_stu,writtenPass int=60,labPass int=60,AS,print -,print ,参加本次考试没有通过的学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo,INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExam,writtenPass,OR labExam,labPass,GO,笔试及格线：默认为,60,分,机试及格线：默认为,60,分,查询没有通过考试的学员,输入参数的默认值,53,输入参数的默认值,EXEC proc_stu -,都采用默认值,EXEC proc_stu 64 -,机试采用默认值,EXEC proc_stu 60,55 -,都不采用默认值,调用带参数默认值的存储过程,-,错误的调用方式：希望笔试采用默认值，机试及格线,55,分,EXEC proc_stu ,55,-,正确的调用方式：,EXEC proc_stu labPass=55,54,带输出参数的存储过程,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（,OUTPUT,）参数了,问题：,修改上例，返回未通过考试的学员人数。,55,CREATE PROCEDURE proc_stu,notpassSum,int,OUTPUT,writtenPass int=60,labPass int=60,AS,SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks,ON stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExamwrittenPass,OR labExamlabPass,SELECT,notpassSum=COUNT(stuNo),FROM stuMarks WHERE writtenExamwrittenPass,OR labExam=3,print ,未通过人数：,+convert(varchar(5),sum)+ ,人,超过,60%,及格分数线还应下调,ELSE,print ,未通过人数：,+convert(varchar(5),sum)+ ,人,已控制在,60%,以下，及格分数线适中,GO,调用带输出参数的存储过程,带输出参数的存储过程,调用时必须带,OUTPUT,关键字 ，返回结果将存放在变量,sum,中,后续语句引用返回结果,57,处理存储过程中的错误,可以使用,PRINT,语句显示错误信息，但这 些信息是临时的，只能显示给用户,RAISERROR,显示用户定义的错误信息时,可指定严重级别，,设置系统变量,ERROR,记录所发生的错误等,58,使用RAISERROR 语句,RAISERROR (msg_id | msg_str,severity,state WITH option,.n,),RAISERROR,语句的用法如下,：,msg_id,：在,sysmessages,系统表中指定用户定义错误信息,msg_str,：用户定义的特定信息，最长,255,个字符,severity,：定义严重性级别。用户可使用的级别为,018,级,state,：表示错误的状态，,1,至,127,之间的值,option,：指示是否将错误记录到服务器错误日志中,59,问题：,完善上例，当用户调用存储过程时，传入的及格线参数不,在,0,100,之间时，将弹出错误警告，终止存储过程的执行。,使用RAISERROR 语句,60,CREATE PROCEDURE proc_stu,notpassSum int OUTPUT, -,输出参数,writtenPass int=60, -,默认参数放后,labPass int=60 -,默认参数放后,AS,IF (NOT writtenPass BETWEEN 0 AND 100),OR (NOT labPass BETWEEN 0 AND 100),BEGIN,RAISERROR,(,及格线错误，请指定,0,100,之间的分,数，统计中断退出,16,1),RETURN -,立即返回，退出存储过程,END,.,其他语句同上例，略,GO,错误处理,引发系统错误，指定错误的严重级别,16,，调用状态为,1,（默认）,并影响,ERROR,系统变量的值,使用RAISERROR 语句,61,使用RAISERROR 语句,/*-,调用存储过程，测试,RAISERROR,语句,-*/,DECLARE sum int, t int,EXEC proc_stu sum OUTPUT ,604,SET t=ERROR,print ,错误号：,+convert(varchar(5),t ),IF t0,RETURN -,退出批处理，后续语句不再执行,print -,IF sum=3,print ,未通过人数：,+convert(varchar(5),sum)+ ,人,超过,60%,及格分数线还应下调,ELSE,print ,未通过人数：,+convert(varchar(5),sum)+ ,人,已控制在,60%,以下，及格分数线适中,GO,笔试及格线误输入,604,分,如果执行了,RAISERROR,语句，系统全局,ERROR,将不等于,0,，表示出现了错误,62,总结,存储过程是一组预编译的,SQL,语句，它可以包含数据操纵语句、变量、逻辑控制语句等,存储过程允许带参数，参数分为：,输入参数,输出参数,其中，输入参数可以有默认值。,输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值,输出参数从存储过程中返回（输出）值，后面跟随,OUTPUT,关键字,RAISERROR,语句用来向用户报告错误,63,