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

1、数据库系统原理及应用,SQLServer2000编程-存储过程,2,存储过程,第一步,第二步,第三步,存储过程 的 基本知识,创建用户 存储过程,存储过程 的 参数,3,1 存储过程的基本知识,1. 概念 存储过程（Stored Procedure） 是存储在服务器上的 Transact-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性 2.功能 存储过程与其他编程语言中的过程类似，它可以 包含执行数据库操作（包括调用其他过程）的编程语句 接受输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因） 以输出参数的形式将多个值返回

2、至调用过程或批处理,4,1 存储过程的基本知识,3. 优点 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括： 与其他应用程序共享应用逻辑，确保一致的数据访问和修改。存储过程封装了商务逻辑。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用 能够实现较快的执行速度预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分 能够减少网络流量客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包 提供了安全性机制。屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内

3、的对象。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限,5,1存储过程的基本知识,4 分类 SQL Server 支持五种类型的存储过程 系统存储过程（sp_）：存储在 master 数据库内，以“sp_”前缀标识 本地存储过程：本地存储过程在单独的用户数据库内创建 临时存储过程：临时存储过程可能是局部的，名称以“#”开头；也可能是全局的，名称以“#”开头 远程存储过程：远程存储过程是 SQL Server 早期版本的特性，分布式查询支持这项功能 扩展存储过程（xp_）：扩展存储过程以动态链接库（DLL）的形式实现，在 SQL Server 环境外执行,6,存储过程

4、的初始处理,存储过程的处理 包括创建，以及初次执行时将执行计划放入过程缓存 过程缓存是一个包含所有当前正在执行的Transact-SQL 语句的执行计划的内存池，其大小动态变化 过程缓存在内存池内。内存池是 SQL Server 内存的主要单元，它包含了 SQL Server 中大部分使用内存的数据结构,7,存储过程的初始处理,存储过程的创建 创建存储过程时，先分析该过程中的语句以检查语法的准确性。然后 SQL Server 将存储过程的名字存入当前数据库的 sysobjects 系统表中，存储过程的文本存入当前数据库的 syscomments 系统表中 延迟名称解析：存储过程引用的对象不需要

5、在创建该存储过程时就存在，而只需在执行该存储过程时存在 存储过程的执行（初次或重新编译时） 存储过程初次执行或者重新编译后，查询处理器读入存储过程的处理过程称为解析 数据库的某些变化会使得执行计划低效或失效，SQL Server 检测这些变化并自动重新编译执行计划,8,存储过程的初始处理,优化 如果过程执行顺利通过解析阶段，则查询优化器将分析该存储过程中的 Transact-SQL 语句，并创建一个执行计划，描述执行存储过程的最快方法 编译 编译指的是分析存储过程，创建执行计划并将之放入过程缓存的过程 过程缓存包含了最有价值的存储过程执行计划。增加执行计划价值的因素包括：重新编译需要的时间（高

6、的编译代价）和频繁被使用,执行计划所依据的信息包括： 表中的数据量； 表中是否存在索引及索引的属性以及数据在索引列中的分布； WHERE 子句条件所用的比较运算符和比较值； 是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。,9,存储过程的初始处理,项存入 sysobjects和 syscomments 表,编译过的执行计划放入过程缓存,编译,优化,创建,执行（初次或重新编译）,解析,10,存储过程的后续处理,若符合下列条件，则 SQL Server 使用在内存中的计划来执行随后的查询 当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境 存储过程

7、引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析 SQL Server 的执行计划有两个主要部分 查询计划：执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用，这称为查询计划。查询计划中不存储用户环境 执行环境：每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构，称为执行环境,11,存储过程的后续处理,在缓存中，对于每个存储过程和环境的组合最多只有一个编译过的计划。对于一个存储过程的多个不同环境，可以有多个计划 形成不同环境并影响编译选择的因素 并行和串行编译计划 隐含的对象拥有 不同的 SET 选项 一个执行计划产生后，驻留在过程

8、缓存中。仅当需要空间时，SQL Server 将老的、没用的计划移出缓存,12,存储过程的后续处理,13,3 创建存储过程,既可创建一个存储过程供永久使用，也可创建一个存储过程在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。语法格式为： Create Procedure 存储过程名 参数名 数据类型Varying=默认值 Output , n WITH Recompile |Encryption| Recompile , Encryption AS SQL语句 n ,14,说明：,存储过程名称局部临时过程，前面加编号符（#）；全局临时过程，前面加两个编号符（#）；

9、不能超过128个字符。 整数用来对同名过程进行分组。以便用一个Drop Procedure语句即可将同组的过程一起删去。 参数名指定过程中的参数。在该语句中可以声明一个或多个参数。用户必须在执行过程时提供每个声明参数的值。使用符号作为第一个字符来指定参数名称。 数据类型所有数据类型都可以用作存储过程的参数。 Varying指定作为输出参数支持的结果集(由存储过程动态构造，内容可变化)。仅适用游标参数。 Recompile该过程将在运行时重新编译。 Encryption 加密存储过程的定义 SQL语句 n 过程中包含的任意数目和类型的T-SQL语句，但有一些限制。,15,创建存储过程,创建存储过

10、程时，需要确定存储过程的三个组成部分： 存储过程名称，包括所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句以及调用其他存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,16,3 创建存储过程,创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失,USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM d

11、bo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null GO,17,创建存储过程,在单个批处理中，CREATE PROCEDURE 语句不能与其他 Transact-SQL 语句组合使用 CREATE PROCEDURE 定义可以包括任何数目和类型的Transact-SQL语句，但不包括下列对象创建语句：CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 执行 CREATE PROCEDURE 语句的用户必须是 sysadmin、d

12、b_owner 或 db_ddladmin角色的成员，或必须拥有 CREATE PROCEDURE 权限 依赖于可用内存，存储过程的最大大小为128 MB,18,创建存储过程,嵌套存储过程：一个存储过程调用另一个 存储过程可以嵌套32层。若试图超过32层嵌套，则整个存储过程调用链失败 当前的嵌套层数存储在系统函数 nestlevel 中 若一个存储过程调用了第二个存储过程，那么第二个存储过程可以访问第一个存储过程创建的所有对象，包括临时表 嵌套的存储过程可以递归调用。例如，存储过程X调用了存储过程Y，当存储过程Y运行的时候它可以调用存储过程X,19,通过系统存储过程查看存储过程,可供使用的系统

13、存储过程及其语法形式如下： sp_help：用于显示存储过程的参数及其数据类型 sp_help objname= name 参数name为要查看的存储过程的名称。 sp_helptext：用于显示存储过程的源代码 sp_helptext objname= name 参数name为要查看的存储过程的名称。 sp_depends：用于显示和存储过程相关的数据库对象 sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。 sp_stored_procedures：用于返回当前数据库中的存储过程列表,20,执行存储过程,可单独执行存储过程或作为 INSE

14、RT 语句的一部分执行存储过程 必须在存储过程上拥有 EXECUTE 权限 单独执行存储过程 语法：EXECUTE返回状态=存储过程名 ;编号|存储过程名称变量参数= 值|变量OUTPUT|DEFAULT,.n WITH RECOMPILE 在 INSERT 语句内执行存储过程 语法：INSERT INTO 表名 EXECUTE 将本地或远程存储过程返回的结果集插入本地表中 在 INSERT 语句内执行的存储过程必须返回关系结果集,21,修改和删除存储过程,修改存储过程 用 ALTER PROCEDURE 中的定义取代现有存储过程原先的定义，但保留权限分配,USE Northwind GO A

15、LTER PROC dbo.OverdueOrders AS SELECT CONVERT(char(8),RequiredDate,1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate GO,22,修改和删除存储过程,修改存储过程的注意事项 若想修改带选项创建的存储过程，例如 WITH ENCR

16、YPTION 选项，则必须在 ALTER PROCEDURE 语句中包括那些选项，以保留选项的功能 ALTER PROCEDURE 语句只更改单个过程，不影响嵌套的存储过程 ALTER PROCEDURE 权限默认授予 sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员和过程的所有者且不可转让,23,修改和删除存储过程,删除存储过程 语法：DROP PROCEDURE 存储过程名 ,.n 用 DROP PROCEDURE 语句从当前数据库中移除用户定义存储过程 删除存储过程的注意事项 在删除存储过程之前，执行系统存储过程 sp_depends

17、 检查是否有对象依赖于此存储过程,24,在存储过程中使用参数,使用输入参数 使用输入参数执行存储过程 使用输出参数返回值 显式地重新编译存储过程,25,使用输入参数,输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 参数名 数据类型 =默认值 指定参数的依据和指导原则 所有的输入参数值都应该在存储过程开始的时候进行检查，以尽早捕获缺失值和非法值的情况 应该为参数提供合适的默认值。若定义了默认值，用户可以在未指定参数值的基础上执行存储过程 一个存储过程最多可以有1 024个参数 存储过程内局部变量的数目没有限制，只和可用内存有关 参数对存储过程而言是局部的。在不同存

18、储过程中可以使用相同名字的参数,26,使用输入参数执行存储过程,通过参数名传递值 在 EXECUTE 语句中以“参数名=值”的格式指定参数称为通过参数名传递 当通过参数名传递值时，可以以任何顺序指定参数值，并且可以省略允许空值或具有默认值的参数 若在存储过程中定义了参数的默认值，则在下列情况使用：当调用存储过程的时候，参数未指定值或者参数的值指定为 DEFAULT 关键字 通过位置传递参数 只传递值（而没有对被传值参数的引用）称为通过位置传递 参数值必须以参数在 CREATE PROCEDURE 语句中的定义顺序列出 可以忽略有默认值的参数，但不能中断次序,27,创建一个带输入参数的存储过程,

19、查询某一学生选课的详细信息，参数为学生名,create procedure p1 pname char(20) as select student.sno,sname,sdept,o,cname,grade from student left join sc on student.sno=sc.sno left join course on o=o where sname=pname go,调用： exec p1 李勇 exec p1 panme=李勇,28,创建一个带输入参数的存储过程,查询某一年龄范围内的学生信息,create procedure p2 bage int=18, eage

20、int=20 as Select * from student where sage between bage and eage go,调用： exec p2 exec p2 18 exec p2 18,20 exec p2 bage=18,eage=20,29,使用输出参数返回值,输出参数：以 OUTPUT 关键字指定的变量 存储过程通过输出参数向调用它的存储过程或客户端返回信息 通过输出参数，存储过程的运行结果可以得到保留，即使存储过程运行结束 输出参数的特性 调用语句必须包含一个变量名，以接受返回值。不能传递常数 可以在随后的 Transact-SQL 语句中使用返回变量 输出参数可以是

21、任何类型，除了 text 或 image 输出参数可以是游标占位符,30,创建一个带输出参数的存储过程,计算某一个工程所使用的零件总数，输入工程号，输出数量,create procedure p3 pjno char(8),pnum int output as Select pnum=sum(qty) from spj where jno=pjno go,调用： declare num int exec p3 J1,num output if num0 print num else print 无该工程使用零件信息,31,显式地重新编译存储过程,存储过程可以显式地重新编译，但应尽量少做，仅当

22、参数值传递给返回大量变化结果集的存储过程时 为底层表增加了存储过程可能从中受益的索引时 提供的参数值非典型时,32,显式地重新编译存储过程,三种显式重新编译存储过程的方法 CREATE PROCEDURE WITH RECOMPILE 创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存，该存储过程将在每次执行时都重新编译 EXECUTE WITH RECOMPILE 在执行存储过程时指定 WITH RECOMPILE 选项，可强制对存储过程进行重新编译 sp_recompile sp_recompile 系统存储过程强

23、制在下次运行存储过程或触发器时进行重新编译。若 objname 参数指定的是表或视图，那么所有使用指定对象的存储过程在下次执行时都会重新编译,33,调用成功与否处理,为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败） 可以在错误处理逻辑中检查下列错误：返回码、SQL Server 错误、用户定义的错误信息 RETURN 语句 从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码） 返回码为0表示成功。0至-14的返回码已被系统使用，-15至-99的返回码被系统保留作将来扩展。若用户不提供返回码，则返回 SQL Server 的返回码。用户定义的返回码优先级高于系统

24、提供的返回码,34,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,35,推荐操作,36,存储过程（procedure）类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也

25、可以返回结果,int sum(int a,int b) int s; s =a+b; return s ; ,存储过程相当于C语言中的函数,什么是存储过程,37,存储过程 - - -,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包含,什么是存储过程,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,38,存储过程的优点,执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量,39,存储过程的分类,系统存储过程 由系统定义，存放在master数据库中 类似C语言中的系统函数 系统存储过程的名称都以“sp_”开头或”xp_”开头 用户自定义存储

26、过程 由用户在自己的数据库中创建的存储过程 类似C语言中的用户自定义函数,40,常用的系统存储过程,41,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_proced

27、ures,常用的系统存储过程,修改数据库的名称(单用户访问),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表stuInfo的信息,查看表stuInfo的约束,查看表stuMarks的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,42,常用的系统存储过程,常用的扩展存储过程：xp_cmdshell 可以执行DOS命令下的一些的操作 以文本行方式返回任何输出 调用语法： EXEC xp_cmdshell DOS命令 NO_OUTPUT,43,常用的系统存储过程,USE master GO EXEC xp_cmdshell mkdir

28、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,44,如何创建存储过程,定义存储过程的语法 CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPU

29、T AS SQL语句 GO 和C语言的函数一样，参数可选 参数分为输入参数、输出参数 输入参数允许有默认值,45,创建不带参数的存储过程,问题： 请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,46,创建不带参数的存储过程,CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 笔试平均分：+convert(varchar(5),writtenAvg) p

30、rint 机试平均分：+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为存储过程的

31、名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,47,调用存储过程,EXECUTE（执行）语句用来调用存储过程 调用的语法 EXEC 过程名 参数,EXEC proc_stu,48,存储过程的参数分两种： 输入参数 输出参数 输入参数： 用于向存储过程传入值，类似C语言的按值传递; 输出参数： 用于在调用存储过程后， 返回结果，类似C语言的 按引用传递;,int sum (int a, int b) int s; s=a+b; return s; ,c=sum（5, 8）,传入参数值,返回结果,49,带输入参数的存储过程,问题： 修改上例：由于每次考试的难易程度不一样，

32、每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。,分析： 在述存储过程添加2个输入参数： writtenPass 笔试及格线 labPass 机试及格线,50,带输入参数的存储过程,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=stu

33、Marks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,51,带输入参数的存储过程,EXEC proc_stu 60,55,调用带参数的存储过程 假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分,-或这样调用： EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，李斯文（59分）成为“漏网之鱼”了,52,输入参数的默认值,带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及

34、格线,问题： 如果试卷的难易程度合适，则调用者还是必须 如此调用： EXEC proc_stu 60,60，比较麻烦 这样调用就比较合理： EXEC proc_stu 55 EXEC proc_stu,笔试及格线55分，机试及格线默认为60分,笔试和机试及格线都默认为标准的60分,53,CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo

35、INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,笔试及格线：默认为60分,机试及格线：默认为60分,查询没有通过考试的学员,输入参数的默认值,54,输入参数的默认值,EXEC proc_stu -都采用默认值 EXEC proc_stu 64 -机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值,调用带参数默认值的存储过程,-错误的调用方式：希望笔试采用默认值，机试及格线55分 EXEC proc_stu ,55,-正

36、确的调用方式： EXEC proc_stu labPass=55,55,带输出参数的存储过程,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了,问题： 修改上例，返回未通过考试的学员人数。,56,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.stuN

37、o=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenExamwrittenPass OR labExamlabPass GO,输出（返回）参数：表示没有通过的人数,推荐将默认参数放后,带输出参数的存储过程,统计并返回没有通过考试的学员人数,57,/*-调用存储过程-*/ DECLARE sum int EXEC proc_stu sum OUTPUT ,64 print - IF sum=3 print 未通过人数：+convert(varchar(5),sum)+ 人, 超过60%,及格分数线还应下调 ELSE print 未通过人数：+convert(varchar(5),sum)+ 人, 已控制在60%以下，及格分数线适中 GO,调用带输出参数的存储过程,带输出参数的存储过程,调用时必须带OUTPUT关键字 ，返回结果将存放在变量sum中,后续语句引用返回结果,58,处理存储过程中的错误,可以使用PRINT语句显示错误信息，但这 些信息是临时的，