第13章 存储过程、用户自定义函数与触发器.ppt

1、第13章 存储过程、用户自定义函数与触发器,计算中心,本章内容,13.1 存储过程概述 13.2 通过企业管理器创建、修改和删除存储过程 13.3 存储过程的执行与参数传递 13.4 用户自定义函数 13.5 触发器及其作用、效果演示,13.1 存储过程概述,存储过程（Stored Procedure） 是什么？ 简单的说：存储过程是将常用的或很复杂的工作，预先用T-SQL语句写好并用一个指定的名称存储起来的语句集合。 课本的定义：是SQL Server服务器上一组预编译的T-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。 举例,13.1 存储过程概述,为

2、什么要使用存储过程？ 存储过程在创建时即在服务器上进行编译，所以执行起来比SQL语句快，且能减少网络通信的负担。 可以在单个存储过程中执行一系列SQL语句，完成复杂的操作。 存储过程可以重复使用，减少数据库开发人员的工作量 。 安全性高，可设定只有某些用户才具有对指定存储过程的使用权。,13.1 存储过程概述,存储过程的类型 系统存储过程 例如：EXEC sp_helpdb 用户定义存储过程：由用户创建并能完成某一特定功能的存储过程。 临时存储过程 扩展存储过程 例如：EXEC xp_cmdshell dir d:,13.1 存储过程概述,存储过程的功能 (1)接收输入参数并以输出参数的形式为

3、调用过程或批处理返回多个值。 (2)包含执行数据库操作的编程语句，包括调用其他过程。 (3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。 存储过程特点,13.2 创建、修改和删除存储过程,创建存储过程的指导原则 避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象 每个存储过程完成单个任务 命名本地存储过程的时候，避免使用“sp_”前缀 尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表 不要直接从 syscomments 系统表里删除项,13.2 创建、修改和删除存储过程,创建存储过程 (1) 启动企业管理器，登录到要

4、使用的服务器。 (2) 选择要创建存储过程的数据库，在左窗格中单击“存储过程”文件夹，此时在右窗格中显式该数据库的所有存储过程，如图13-1所示。,13.2 创建、修改和删除存储过程,(3) 右击“存储过程”文件夹，在弹出菜单中选择【新建存储过程】选项，打开创建存储过程对话框，如下图。,图13-2 创建存储过程对话框,13.2 创建、修改和删除存储过程,(4) 在“文本”编辑框中输入存储过程正文。 (5) 单击“检查语法”按钮，检查语法是否正确。 (6) 单击“确定”按钮，保存存储过程。,提示： 新创建存储过程名字包含在CREATE PROCEDURE语句中，不在保存时输入。,13.2 创建、

5、修改和删除存储过程,修改存储过程 (1) 在企业管理器中展开服务器组，再展开服务器。 (2) 展开“数据库”文件夹，再展开要修改存储过程的数据库。 (3) 在要修改的存储过程上右击，并在弹出的快捷菜单中选择【属性】项，或双击该存储过程，弹出“存储过程属性”对话框。,图13-4 控制台目录,13.2 创建、修改和删除存储过程,删除存储过程 类似于删除表操作，在存储过程显示列表中选择要删除的存储过程（可以用ctrl或shift选多个）。 右键单击选中的存储过程，在弹出的快捷菜单中选择【删除】项，打开“除去对象”对话框，如下图，单击【全部除去】按钮，完成删除。,图13-5 “除去对象”对话框,显示与

6、该存储过程相关的对象,13.2 创建、修改和删除存储过程,创建存储过程时，需要确定存储过程的三个组成部分： 参数，所有的输入参数以及传给调用者的输出参数。 过程体，被执行的针对数据库的操作语句，包括调用其它存储过程的语句； 返回状态，返回给调用者的状态值，以指明调用是成功还是失败。,13.2 创建、修改和删除存储过程,不含参数的存储过程 例13-1：查询学生成绩。 CREATE PROCEDURE 查询学生成绩 AS SELECT 学生表.姓名, 课程表.课程名, 选课表.成绩 FROM 选课表 INNER JOIN 学生表 ON 选课表.学号 = 学生表.学号 INNER JOIN 课程表

7、ON 选课表.课程号 = 课程表.课程号 执行：EXEC 查询学生成绩,13.2 创建、修改和删除存储过程,例13-2：查找1100之间的完全平方数。 CREATE PROCEDURE 查找完全平方数 AS declare n int set n=1 while n*n=100 begin print cast(n*n as varchar(5) set n=n+1 end 执行：exec 查找完全平方数,13.3 存储过程的执行与参数传递,参数 存储过程和调用者之间需要通过参数来交换数据，可以按输入的参数执行，也可由参数输出执行结果。 例如：查询学号为s2008001的c01课程的成绩。输入

8、参数：学号（s2008001）和课程号（c01） 输出参数：成绩 SQL Server支持这两类参数。,13.3 存储过程的执行与参数传递,输入参数 输入参数允许调用程序为存储过程传送数据值。 定义存储过程的输入参数 必须在CREATE PROCEDURE语句中声明一个或多个变量及数据类型。 例13-3：创建带参数的存储过程，输入学生学号，返回学生姓名、性别等个人信息。 CREATE PROCEDURE dbo.查询指定学生信息 学号 varchar(10) AS select * from 学生表 where 学号=学号 提示：定义参数的数据类型需和表内字段类型一致。,13.3 存储过程的执

9、行与参数传递,随堂思考： (1) 扩展例13-1，查询指定学生指定课程成绩； (2) 扩展例13-2，增加输入参数，要求显示某整数范围完全平方数。,13.3 存储过程的执行与参数传递,存储过程的执行 语法格式： EXECUTE return_status= procedure_name ;number|procedure_name_var parameter= value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE ,13.3 存储过程的执行与参数传递,执行带输入参数的存储过程 在查询分析器中执行例13-3： exec 查询指定学生信息 s200800

10、1 exec执行（execute） 查询指定学生信息存储过程名 s2008001输入参数 运行结果： 提示： 需要根据输入参数的数据类型加定界符。,13.3 存储过程的执行与参数传递,执行时，参数可以由位置标识，也可以由名字标识。 例如，定义一个具有3个参数的存储过程： CREATE PROC myproc val1 int, val2 int, val3 int AS . 参数以位置传递： EXEC myproc 10,20,15 参数以名字传递，每个值由对应的参数名引导： EXEC myproc val2=20,val1=10,val3=15 按名字传递参数比按位置传递参数具有更大的灵活性

11、。但是，按位置传递参数却具有更快的速度。,13.3 存储过程的执行与参数传递,输出参数 输出参数允许存储过程将数据值传回调用程序。 用OUTPUT关键字指出能返回到调用它的存储过程。 例13-4：创建存储过程，查询指定学生的某门课程成绩。 CREATE PROCEDURE dbo.查询成绩 学号 varchar(10), 课程号 varchar(10), score int output AS select score=成绩 from 选课表 where 学号=学号 and 课程号=课程号,13.3 存储过程的执行与参数传递,执行带输出参数的存储过程 在查询分析器中执行例13-4： decla

12、re 学生成绩 int exec 查询成绩 s2008001,c01,学生成绩 output select 学生成绩 运行结果： 提示： 输出参数（返回值）需要提前声明，数据类型应同输出参数的数据类型相匹配。 EXEC语句需要关键字OUTPUT以允许参数值返回变量。,13.3 存储过程的执行与参数传递,例13-5：编写存储过程“学生成绩分析”，根据输入参数“学号”指定的学生，计算该生的平均成绩，若平均成绩在85分及以上为优秀，85分以下60分及以上为一般，60分以下为差，将内容填入“学生考试评价”表中。 附：“学生考试评价”表结构： 学号 char(10) 平均成绩int 考试评价varcha

13、r(10) 【学号】列是该表主键,CREATE PROCEDURE 学生成绩分析 学号 char(10) AS declare 平均分 as int,评价 as varchar(10) select 平均分=avg(成绩) from 选课表 where 学号=学号 if 平均分=85 set 评价=优秀 else begin if 平均分=60 set 评价=一般 else set 评价=差 end insert into 学生考试评价(学号,平均成绩,考试评价) values(学号,平均分,评价),13.3 存储过程的执行与参数传递,返回存储过程的状态 用RETURN语句定义返回值 存储过程

14、可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。 如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为 0，表示成功执行；若返回-1-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99的整数来定义自己的返回状态值，以表示不同的执行结果。,13.3 存储过程的执行与参数传递,例13-6：创建存储过程，输入课程号，返回课程名称。在存储过程中，用值15表示用户没有提供参数；值-101表示没有输入课程号；值0表示过程运行没有出错。 CREATE PROCEDURE dbo.查询课程名 课程号 as varchar(10)=null AS

15、if 课程号=null return 15 if not exists(select * from 课程表 where 课程号=课程号) return -101 select 课程名 from 课程表 where 课程号=课程号,13.3 存储过程的执行与参数传递,捕获返回状态值 在执行过程时，要正确接收返回的状态值，必须使用语句：EXECUTE status_var=procedure_name 参数 例13-6的存储过程查询课程名执行时使用以下语句： DECLARE return_status int EXEC return_status=查询课程名 c01 IF return_statu

16、s=15 SELECT 语法错误，未输入参数! ELSE IF return_status=-101 SELECT 没有找到该课程号. 执行时，对不同的输入值返回不同的状态值。,13.4 用户自定义函数,用户自定义函数概述 内置函数不能满足用户（应用程序）需求，创建的自己定义的函数 参数：零个、一个或 多个 返回值：一个 单个数值 一个表 根据函数返回值形式的不同将用户定义函数分为3种类型,13.4 用户自定义函数,(1) 标量函数 标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、timestamp、tabl

17、e类型外）。函数体语句定义在BEGINEND语句内。 (2) 内嵌表值函数 内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGINEND语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。 (3) 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体用BEGINEND语句定义，返回值的表中的数据由函数体中的语句插入。,13.4 用户自定义函数,创建用户自定义函数,图13-6 创建用户自定义函数对话框,13.4 用户自定义函数,例13-7：创建标量函数DatetoQuarter，将输入的日

18、期数据转换为该日期对应的季度值。如输入2006-8-5，返回3Q2006，表示2006年3季度。 CREATE FUNCTION DatetoQuarter(dqdate datetime) RETURNS char(6) AS BEGIN RETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate) END,例13-8：创建标量函数NumToStr，输入阿拉伯数字09，输出对应的中文大写。 CREATE FUNCTION NumToStr(num as int) RETURNS char(2) AS BEGIN declare ChineseCap as

19、 char(2) set ChineseCap=(case num when 0 then 零 when 1 then 壹 when 2 then 贰 when 3 then 叁 when 4 then 肆 when 5 then 伍 when 6 then 陆 when 7 then 柒 when 8 then 捌 when 9 then 玖 end) return ChineseCap END,13.4 用户自定义函数,例13-9：通过自定义函数根据输入课程号，返回对应的课程名。 CREATE FUNCTION F课程名 (courseID char(10) RETURNS char(20

20、) AS BEGIN declare courseName char(20) select courseName=课程名 from 课程表 where 课程号=courseID return courseName END,13.4 用户自定义函数,例13-10：创建一个内嵌表值函数stuinfo，输入学生学号，返回学生姓名及各科成绩。 CREATE FUNCTION stuinfo (xh varchar(10) RETURNS table AS return(select 姓名,课程号,成绩 from 学生表 inner join 选课表 on 学生表.学号=选课表.学号 where 选课表

21、.学号=xh),例13-11：创建多语句表值函数Stu_Info，根据输入的学号、课程号，返回对应的姓名、课程名和成绩。 CREATE FUNCTION Stu_Info(学号 varchar(10),课程号 varchar(10) RETURNS stu_info table(stuName varchar(8), courseName varchar(20), score smallint) AS BEGIN declare stuName as varchar(8),courseName as varchar(20) declare score as smallint select st

22、uName=姓名 from 学生表 where 学号=学号 select courseName=课程名 from 课程表 where 课程号=课程号 select score=成绩 from 选课表 where 学号=学号 and 课程号=课程号 insert into stu_info values(stuName,courseName,score) return END,13.4 用户自定义函数,修改和删除用户自定义函数 用企业管理器修改用户定义函数，选择要修改函数，双击或单击右键，从快捷菜单中选择“属性”选项，打开图13-6所示的“用户定义函数属性”对话框。在该对话框中可以修改用户定义函

23、数的函数体、参数等。 从快捷菜单中选择“删除”选项，打开“除去对象”对话框，则可删除用户自定义函数。,13.4 用户自定义函数,用户自定义函数的使用 当调用标量值函数时，必须加上“所有者”，通常是dbo（但不是绝对，可以在企业管理器中的“用户定义函数”中查看所有者） 当调用表值函数时，可以只使用函数名。,13.4 用户自定义函数,例13-12：调用例13-7函数，返回当前日期对应的季度值。 select dbo.DatetoQuarter(getdate() 例13-13：调用例13-9函数，返回c02对应的课程名。 select dbo.F课程名(c02) 例13-14：调用例13-10函数，返回学号为s2008001的学生姓名和各科成绩。 select * from stuinfo(s2008001) 例13-15：调用例13-11，返回学号为 s2007031课程号为c01的学生姓名、课程名和成绩。 select * from stu_info(s2007031,c01),例13-16：使用例13-8函数，完成099的数字大写转换。 declare num as i