自定义函数、存储过程和触发器.ppt

第9章自定义函数、存储过程和触发器,教学提示：在数据库实际应用中，存在有带变量数据处理需求，如某班学生信息表、某老师带过的学生、某班某门课不及格学生等。自定义函数、存储过程、触发器是由一系列的T-SQL 语句组成的子程序，用来满足更高的应用需求，可以说是SQL程序设计的灵魂，掌握和使用好它们对数据库的开发与应用非常重要。教学要求：自定义函数、存储过程、触发器的概念、用途、创建方法。编写简单的自定义函数、存储过程、触发器。,第9章自定义函数、存储过程和触发器,自定义函数的概念创建自定义函数 查看自定义函数信息 调用自定义函数 调用自定义函数 删除自定义函数,存储过程的概念 创建存储过程 查看存储过程信息 执行存储过程 修改存储过程 删除存储过程 常用系统存储过程,触发器的概念 创建触发器触发器使用限制修改触发器删除触发器 使用触发器的优点,9.1 自定义函数 9.2 存储过程9.3 触发器,1 自定义函数,第9章自定义函数、存储过程和触发器,1.1 自定义函数的概念1.2 创建自定义函数 1.3 查看自定义函数信息 1.4 调用自定义函数 1.5 调用自定义函数 1.6 删除自定义函数,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.1 自定义函数的概念,概念,在SQL Server中，除了系统内置的函数外，用户还可以自己定义函数，来补充和扩展系统支持的内置函数。自定义函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重复使用。,标量函数、内嵌表值函数、多语句表值函数,种类,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.2 创建自定义函数,1.用CREATE FUNCTION 语句创建,标量函数返回RETURNS子句中定义的类型、RETURN子句指定数值的单个数据值，如同使用系统内置的函数一样。语法格式：,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,CREATE FUNCTION 所有者.自定义函数名(参数n)RETURNS 返回参数的类型 AS BEGIN 函数体 RETURN 函数返回的标量值END,【例9.1】创建一个自定义函数：is中文字符串，判断自变量是否是纯中文字符串，返回：是或否,函数体中可使用的有效语句类型：DECLARE语句，声明局部变量和游标。SET语句，局部变量赋值。游标操作，包括声明、打开、关闭和释放游标。不允许使用fetch语句将数据返回到客户端，仅允许使用fetch语句通过into子句给局部变量赋值。控制流语句。select 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。insert、update和delete语句，这些语句修改函数的局部table变量。execute语句，该语句调用扩展存储过程。,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.2 创建自定义函数,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.2 创建自定义函数,内嵌表值函数是返回一个Select语句查询结果的表。,CREATE FUNCTION 所有者.自定义函数名(参数n RETURNS TABLE AS RETURN(SELECT查询语句),【例9.2】创建一个自定义函数：某班学生信息表输入参数班级名称，返回：表。,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.2 创建自定义函数,操作步骤：(1)展开【服务器组】、服务器。(2)展开【数据库】，展开数据库，单击【用户定义函数】，在详细信息窗口中显示已有的自定义函数。(3)右击【用户定义函数】选择【新建用户定义的函数】弹出【用户定义函数属性】对话框，并给出一个通用模板。如图9.1所示。(4)在【文本】输入框中，把OWNER.FUNCTION NAME改为某用户和要创建的函数名，在FUNCTION BODY处输入自定义函数正文。(5)单击【检查语法】，检查语法是否正确。(6)单击【确定】完成。,2.使用企业管理器创建自定义函数,1.3 查看自定义函数信息,使用企业管理器查看自定义函数,使用查询分析器查看自定义函数,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,(1)展开【服务器组】、服务器。(2)展开【数据库】，展开数据库，单击【用户定义函数】，在详细信息窗口中显示已有的自定义函数。(3)在详细信息窗口中右击要查看的用户定义函数单击【属性】菜单弹出【用户定义函数属性】对话框，可查看和编辑自定义函数的代码等。,(1)打开【查询分析器】，登录到要使用的服务器。(2)在【对象浏览器】中，展开所要的数据库、【函数】文件夹。显示已有的自定义函数和参数及相关性。(3)右击已有的用户定义函数单击【编辑】弹出代码窗口，在此窗口中查看和编辑自定义函数的代码。,1.4 调用自定义函数,调用时指明函数的拥有者和函数名称：标量函数在表达式中；内嵌表值在select的from子句中。,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,【例9.3】在select子句表达式中调用已定义的is中文字符串函数。,【例9.4】使用已定义的is中文字符串函数，可在创建表时定义约束。,【例9.5】创建一个自定义函数：某班某课不及格表输入参数班级名称和课程名称，并查询。,执行结果如图9.2所示。,1.5 修改自定义函数,自定义函数的修改，用【企业管理器】或T-SQL 语句进行。其中：使用【企业管理器】修改自定义函数与查看自定义函数等同，而用T-SQL 语句修改自定义函数的语法格式类同CREATE FUNCTION，即CREATE换成ALTER。,【例9.6】修改is中文字符串函数：自变量的长度由255改为1000。,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,1.6 删除自定义函数,用DROP FUNCTION 语句删除自定义函数,DROP FUNCTION 所有者.自定义函数名,.,用【企业管理器】删除自定义函数,第9章自定义函数、存储过程和触发器,1自定义函数 概念 创建 查看 调用 修改 删除2存储过程 3触发器 实训小结,(1)展开【服务器组】、展开 服务器。(2)展开【数据库】，展开数据库，单击【用户定义函数】，在详细信息窗口中显示已有的自定义函数。(3)右击要删除的用户定义函数单击【删除】弹出【去除对象】对话框，单击【全部去除】完成。,【例9.7】删除is中文字符串函数。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,2 存储过程,2.1 存储过程的概念 2.2 创建存储过程 2.3 查看存储过程信息 2.4 执行存储过程 2.5 修改存储过程 2.6 删除存储过程 2.7 使用存储过程的优点,用户定义函数采用零个或最多可以有 1024 个输入参数并返回单个标量值或单个表(记录集)。但对于返回多个（或零个）标量值或多个（或零个）表(记录集)问题，SQL Server如何解决呢？,第9章自定义函数、存储过程和触发器,2.1 存储过程的概念,概念,存储过程是SQL Server应用最广泛、最灵活的技术。存储过程是已经存储在SQL Server服务器中的一组预编译过的Transact-SQL语句，存储过程可以接受参数，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程既不能在被调用的位置上返回数据，也不能被引用在语句当中。,分类,系统存储过程和用户定义存储过程。系统存储过程主要存储在master 数据库中并以sp_为前缀，在任何数据库中都可以调用，在调用时不必在存储过程前加上数据库名。用户自定义存储过程是由用户创建的，是用来完成某项任务的存储过程。存储在创建时的数据库中。,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,第9章自定义函数、存储过程和触发器,2.2 创建存储过程,提示,当创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数及执行的输出结果；被执行的针对数据库的操作语句，包括调用其他存储过程的语句；返回给调用者的状态值，以指明调用是否成功.,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,第9章自定义函数、存储过程和触发器,1 用CREATE PROCEDURE命令,2.2 创建存储过程,主要语法格式：CREATE PROCDURE 存储过程名 参数 参数的数据类型 OUTPUT,.n AS 任意数量的Transct-SQL 语句 OUTPUT选项可将参数的值返回给调用语句。,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,【例9.8】设计某班某门课程成绩表，按学号排序。,2.2 创建存储过程,2 用【企业管理器】创建存储过程,(1)展开【服务器组】、展开 服务器。(2)展开【数据库】，展开数据库，单击【存储过程】，在详细信息窗口中显示已有的存储过程。如图9.3所示。其中，类型列显示系统为系统存储过程，显示用户为用户创建的存储过程。(3)右击【存储过程】，在快捷菜单中选择【新建存储过程】，弹出【存储过程属性新建存储过程】对话框，并给出一个通用模板。如图9.3所示。(4)把OWNER.PROCEDURE NAME改为所有者和要创建的存储过程名，并在AS之后输入存储过程的正文。(5)单击【检查语法】，检查语法。(6)单击【确定】完成。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,使用企业管理器查看存储过程,展开【服务器组】，展开服务器。展开【数据库】，展开数据库，单击【存储过程】，在详细信息窗口中显示已有的存储过程。右击查看、修改存储过程，单击【属性】按钮，此时弹出【存储过程属性】对话框，便可查看或编辑存储过程的代码。,使用查询分析器查看存储过程,打开【查询分析器】，登录到要使用的服务器。展开所要的数据库，展开【存储过程】文件夹。右击要查看、修改的用户存储过程，单击【编辑】菜单。在弹出的编辑窗口可查看和编辑用户存储过程的代码等。,2.3 查看存储过程信息,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,2.4 执行存储过程,语法：execute 存储过程名 参数1，参数n,【例9.9】执行【例9.8】创建的存储过程 执行结果如图9.4所示。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,2.5 修改存储过程,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,对存储过程的修改，可以用【企业管理器】或T-SQL 语句进行。其中：使用【企业管理器】修改存储过程与查看存储过程等同，而用T-SQL 语句修改存储过程的语法格式类同CREATE PROC，即CREATE换成ALTER。,【例9.10】修改【例9.8】某班某门课程成绩表存储过程，按分数从大到小排序。,2.6 删除存储过程,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,1 用DROP PROC 语句删除存储过程,DROP PROCEDURE 所有者.存储过程,.,2 用【企业管理器】删除自定义函数,(1)展开【服务器组】、展开 服务器。(2)展开【数据库】，展开数据库，单击【存储过程】，在详细信息窗口中显示已有的存储过程。(3)右击要删除的用户存储过程单击【删除】弹出【去除对象】对话框，单击【全部去除】完成。,【例9.11】删除某班某门课程成绩表存储过程。,2.7 使用存储过程的优点,模块化的程序设计。执行速度快。减少网络通信量。保证系统的安全性。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 概念 创建 查看 执行 修改 删除 优点3触发器 实训小结,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,3 触发器,3.1 触发器的概念 3.2 创建触发器3.3 触发器使用限制3.4 修改触发器3.5 删除触发器 3.6 使用触发器的优点,引言：当表或视图中的某些重要数据发生变化 时，为了保持数据的一致性和完整性，可以自动执行某段程序保证相关联其他数据也跟着进行相应的变化。能完成这种功能的程序就是触发器。,3.1 触发器的概念,触发器是特殊类型的存储过程，它能在任何试图改变表或视图中由触发器保护的数据时执行。触发器主要通过操作事件(INSERT、UPDATE、DELETE)进行触发而被自动执行，不能直接调用执行，也不能被传送和接受参数。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,分类：据触发语句分为:INSERT、UPDATE和DELETE触发器；据触发时刻分为:AFTER(之后)触发器和INSTEAD(替代)触发器。,触发器与表或视图是不能分开的，触发器定义在一个表或视图中，当在表或视图中执行插入（INSERT）、修改（UPDATE）、删除（DELETE）操作时触发器被触发自动执行。当表或视图被删除时与它关联的触发器也一同被删除。,3.1 触发器的概念,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,AFTER(之后)触发器和INSTEAD(替代)触发器：AFTER触发器是在执行触发操作（INSERT、UPDATE 或 DELETE）和处理完约束之后激发，INSTEAD触发器是由触发器的程序代替代替INSERT、UPDATE 或 DELETE语句执行，在处理约束之前激发。所以，若执行INSERT、UPDATE 或 DELETE语句违犯约束条件时，将不执行AFTER触发器；而在定义INSTEAD OF触发器的表或视图上执行INSERT、UPDATE 或 DELETE语句时，会激发触发器而不执行这些数据操作语句本身。,3.2 创建触发器,1 用CREATE TRIGGER命令创建触发器,CREATE TRIGGER 触发器名ON 表名或视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS IF UPDATE(列名1)AND|OR UPDATE(列名2).n SQL语句,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,触发器名称：触发器名。何处触发：表名或视图名。何时激发：FOR|AFTER指定为AFTER触发器，INSTEAD OF指定为INSTEAD触发器。,3.2 创建触发器,【例9.12】在学院信息表中创建一个名为T修改学院信息表编号的触发器。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,何种数据修改语句触发：INSERT指定为INSERT触发器；UPDATE 指定为UPDATE触发器；DELETE指定为DELETE触发器。何列数据修改时触发：可选项IF UPDATE(列名1)AND|OR UPDATE(列名2).n 用于指定如果测试到在列名1且或列名2上进行的 INSERT 或 UPDATE 操作时触发。不能用于 DELETE 语句触发器。如何触发：SQL语句指定触发器触发时所作的操作。,3.2 创建触发器,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,触发器运行时SQL Server会在内存中自动创建和管理deleted 表和 inserted 表，用于在触发器内部测试某些数据修改的效果及设置触发器操作的条件，用户不能直接对表中的数据进行更改。DELETE触发器会将删除旧行的内容保存在deleted表中，INSERT触发器会将添加新行的内容保存在inserted表中，而UPDATE触发器将替换旧行的内容保存在deleted表中、替换的新行内容保存inserted表中 语句中。,【例9.13】在学院信息表中创建一个名为T删除学院信息表记录的触发器。,3.2 创建触发器,2 使用企业管理器创建触发器,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,(1)展开【服务器组】、展开要操作服务器。(2)展开【数据库】，展开创建触发器的表所在的数据库，单击【表】，在详细信息窗口中显示已有的数据表。(3)右击创建触发器的表选择【所有任务】、【管理触发器】菜单，弹出【触发器属性】对话框。(4)在【触发器属性】对话框【名称】下拉列表框中选择新建，在【文本】编辑框中把OWNER.PROCEDURE NAME改为所有者与要创建的触发器名，在AS之后输入触发器正文。(5)单击【检查语法】，检查语法是否正确。(6)单击【确定】保存。,3.3 触发器使用限制,create trigger 语句只能作为批处理的第一条语句。在表中如果既有约束又有触发器，则在执行中约束优先于触发器。而且如果在操作中触发器与约束发生冲突，触发器将不执行。触发器中不允许包含以下SQL语句：ALTER DATABASE、CREATE DATABAS、DROP DATABASE、RESTORE DATABASE、RESTORE LOG等。不能在视图或临时表上建立触发器，但是在触发器定义中可以引用视图或临时表。当触发器引用视图或临时表，并产生两个特殊的表：deleted表和 inserted 表。这两个表由系统进行创建和管理，用户不能直接修改其中的内容，其结构与触发表相同，可以用于触发器的条件测试。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,3.4 修改触发器,1用【企业管理器】修改触发器正文,2 系统过程 sp_rename 修改触发器的名字,3 用Alert trigger 命令修改触发器正文,用【企业管理器】修改触发器正文的操作步骤与创建触发器相似，只需在名称框中选择要修改的触发器即可。,格式为：sp_rename 旧的触发器名 新的触发器名,ALTER TRIGGER的语法格式类与CREATE TRIGGER，只需将CREATE换成ALTER。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,3.5 删除触发器,用户可以删除不再需要的触发器，此时原来的触发表以及表中的数据不受影响。如果删除表，则表中所有的触发器将被自动删除。,使用DROP TRIGGER,DROP TRIGGER 触发器名,使用【企业管理器】,用【企业管理器】删除触发器的操作步骤与修改触发器相似，在名称框中选择要删除的触发器后，点击删除按钮，即可删除该触发器。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,3.6 使用触发器的优点,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 概念 创建 限制 修改 删除 优点实训小结,引用完整性(外键)的级联更新、级联删除用来实现主键与引用键之间的级联，而触发器可实现数据库中的表间记录数据的级联更改和级联删除。如【例9.12】所示。触发器可以强制比引用完整性(外键)、CHECK约束更为复杂的约束。如【例9.13】所示。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。,本章实训,实训目的,练习用企业管理器和查询分析器创建用户自定义函数、存储过程、触发器，并掌握创建和调用它们的方法。,实训内容,通过【企业管理器】或【查询分析器】创建用户自定义函数、存储过程、触发器1创建一个用户自定义函数：查询宿舍函数，通过输入学生姓名，查询学生的姓名、性别、宿舍编号、宿舍电话号码。并调用该函数查询。2创建一个带参数的存储过程：查询宿舍过程，输入宿舍编号，查询某宿舍同学的存储过程。并带参数，执行该存储过程。3在宿舍表上创建一个触发器：修改宿舍编号，当修改该表中的宿舍编号时，同时修改同学表中的宿舍编号。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 实训小结,内容与过程,本章小结,自定义函数、存储过程和触发器，他们是一组SQL语句集，自定义函数是用来补充和扩展系统内置函数的。存储过程可以由用户直接调用执行，用户能够使用相同的存储过程来保证数据的一致性。触发器是一种特殊的存储过程，但触发器不能直接调用，只能通过某些操作触发。存储过程和触发器在数据库开发过程中，在对数据库的维护和管理等任务中，特别是在维护数据完整性等方面具有不可替代的作用。,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 实训小结,本章小结,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 实训小结,本章小结,第9章自定义函数、存储过程和触发器,1自定义函数 2存储过程 3触发器 实训小结,第9章自定义函数、存储过程和触发器,谢谢！,再见！,第9章自定义函数、存储过程和触发器,谢谢！,再见！,