• 1. 数据库实用技术 SQL Server 2008第十章 存储过程、触发器 和自定义函数
  • 2. 第十章 存储过程、触发器、自定义函数存储过程1触发器2用户自定义函数3SQL Server 2008实训:T-SQL编程的高级应用4
  • 3. 存储过程存储过程概述 T-SQL语句进行编程有两种方法: 一种是把T-SQL语句全部写在应用程序中,并存储在本地; 另一种是把部分T-SQL语句编写的程序作为存储过程存储在 SQL Server中,只在本地的应用程序调用存储过程。大多数程序员偏向使用后者。 存储过程的概念: 存储过程(Stored Procedure)是一组编译好的、存储在服务器上的、能完成特定功能的 T-SQL语句集合,是数据库的一种对象。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 4. 存储过程存储过程概述 使用存储过程的优点: 存储过程只需编译一次,以后即可多次执行,因此可以提高应用程序的性能。 存储过程一经被创建,可以在程序中被多次调用;并且修改存储过程不会影响应用程序源代码,因此极大地提高了程序的重用性、可维护性、共享性和可移植性。 存储过程存储在服务中,能够减少网络流量。 存储过程可被作为一种安全机制来充分利用。 存储过程的分类: 系统存储过程。 扩展存储过程。 用户定义的存储过程。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 5. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: ⑴ 依次选择“数据库”→“BillingSys”→“可编程性”→“存储过程”节点,右击选择“新建存储过程”命令,打开“查询编辑器”,在“查询编辑器”中出现存储过程的编程模板。如左图所示。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 6. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: ⑵ 修改存储过程的编程模板,插入需要的T-SQL代码。 ① 存储过程编程模板。 …… CREATE PROCEDURE -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> = , <@Param2, sysname, @p2> = AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 7. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: ⑵ 修改存储过程的编程模板,插入需要的T-SQL代码。 ① 存储过程编程模板。 说明: 参数按以下格式包括在尖括号(< >)中:。其中尖括号内是参数的三个元素:参数的名称、该参数的数据类型以及该参数的默认值。 parameter_name:列出存储过程或函数中的参数。此字段是只读的。 data_type:模板中参数的数据类型。此字段是只读的。若要更改数据类型,请更改模板中的参数。 default_value:为所选参数指定值。默认值。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 8. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: ⑵ 修改存储过程的编程模板,插入需要的T-SQL代码。 ② 在存储过程中编写代码。 在上述模板代码中: 在”CREATE PROCEDURE…”行命令中,用户必须自己定义一个存储过程名称,来替代参数部分,即“< >”部分。 “<@Param1,…”、“<@Param2,…”行命令用来指定参数项,如果用户需要为该存储过程指定参数,则按照提示指定参数,例如:@Cust_name varchar(20);如果不需要参数,则删除这两条命令。 “SELECT <@Param1,…”行命令是为参数赋值。如果没有参数,则删除此条命令。 用户从模板的第33行之后(即“-- Insert statements for procedure here”之后),插入所要编写的存储过程代码。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 9. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: 【例10-1】为计费系统创建一个实现查询单位客户及其联系人的存储过程。 …… CREATE PROCEDURE mypro1 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select cid,cname,rname,rtelephone from Customer inner join Relationer on Customer.RID=Relationer.RID where CType='商业' or CType='公众' or CType='大户' END GO 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 10. 存储过程使用SSMS创建与管理存储过程 使用存储过程模板创建存储过程: ⑶ 单击工具栏上的“执行”按钮,即可创建该存储过程。 此时,刷新对象资源管理器,重新展开“存储过程”节点,可以看到刚建立的存储过程。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 11. 存储过程使用SSMS创建与管理存储过程 利用对象资源管理器修改存储过程: ⑴ 在对象资源管理器窗口中,找到需要修改的存储过程节点,在其上右击选择“修改”命令,打开“查询编辑器”,其中出现要修改的存储过程代码。 ⑵ 对现有的存储过程进行修改。 ⑶ 修改完成后,单击工具栏上的“执行”按钮,即可完成存储过程的修改。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 12. 存储过程使用SSMS创建与管理存储过程 利用对象资源管理器删除存储过程: 在对象资源管理器窗口中,找到需要删除的存储过程节点,右击选择“删除”命令。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 13. 存储过程使用T-SQL语句创建与管理存储过程 创建存储过程: CREATE PROCEDUR语句的语法格式如下: CREATE PROCEDURE|PROC <存储过程名>[;n] [@形参名 数据类型,…][,] [@变参名 数据类型 OUTPUT,…] [WITH ENCRYPTION|RECOMPILE] [FOR REPLICATION] AS 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 14. 存储过程使用T-SQL语句创建与管理存储过程 创建存储过程: CREATE PROCEDUR语句的参数说明: n:是可选整数,用于对同名的过程分组。 @形参名…:指定过程中的参数。每个参数仅作用于该过程本身,是局部的。 @变参名…:指定作为输出参数支持的结果集。该参数由存储过程动态构造,其内容可能发生改变。仅适用于CURSOR参数。 WITH ENCRYPTION:指示SQL Server将CREATE PROCEDURE语句的原始文本转换为模糊格式。 WITH RECOMPILE:指示数据库引擎不缓存该过程的计划,该过程在运行时编译。如果指定了FOR REPLICATION,则不能使用此选项。 FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION选项创建的存储过程可用作存储过程筛选器,且只能在复制过程中执行。如果指定了FOR REPLICATION,则无法声明参数。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 15. 存储过程使用T-SQL语句创建与管理存储过程 查看存储过程的信息: sp_help '存储过程名称':用于查看存储过程的一般信息,如存储过程的名称、属性、类型和创建时间。 sp_helptext '存储过程名称':用于查看存储过程的正文信息。 sp_depends '存储过程名称'|'表名':用于查看指定存储过程所引用的表或者指定的表涉及到的所有存储过程。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 16. 存储过程使用T-SQL语句创建与管理存储过程 修改存储过程: ALTER PROCEDURE语句的语法格式如下: ALTER PROCEDURE|PROC <存储过程名> [@形参名 数据类型,…][,] [@变参名 数据类型 OUTPUT,…] [WITH ENCRYPTION|RECOMPILE] [FOR REPLICATION] AS 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 17. 存储过程使用T-SQL语句创建与管理存储过程 删除存储过程: DROP PROCEDURE语句的语法格式如下: DROP PROCEDURE|PROC <存储过程名>[,…,n] 例如,删除数据库BillingSys中的存储过程“mypro2”,其代码为: USE BillingSys DROP PROCEDURE mypro2第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 18. 存储过程使用T-SQL语句创建与管理存储过程 调用存储过程: EXECUTE语句的语法格式如下: Exec | Execute { [@整型变量=] 存储过程名[,n] |@存储过程变量名 [[@过程参数=]参数值|@可变参数名 [OUTPUT]|[DEFAULT]] [,…,n] [WITH RECOMPILE] } [;]第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 19. 存储过程使用T-SQL语句创建与管理存储过程 调用存储过程: EXECUTE语句的参数说明: @整型变量:是可选项,代表存储过程的返回状态,该变量在用于EXECUTE语句前,必须已经声明过。 n:可选整数,用于对同名的过程分组。 @过程参数…:为存储过程的参数赋值。其中参数名必须和存储过程定义中的相同。 OUTPUT:指定该参数为输出参数,该参数在存储过程中定义时也必须使用OUTPUT选项声明。 DEFAULT:指明该参数使用默认值。如果该参数定义时没有指定默认值,则不能使用DEFAULT选项。 WITH RECOMPILE:使用WITH RECOMPILE,强制在执行存储过程时重新对其进行编译。而一般情况下,存储过程只有在第一次执行时,系统对其进行编译,并将存储起来,以后执行时直接取出执行计划执行,不再编译。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 20. 存储过程使用T-SQL语句创建与管理存储过程 带参数传递的存储过程举例: 【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联系人的存储过程。 创建该存储过程Query_Relationer代码如下: …… CREATE PROCEDURE Query_Relationer @QueryCID int, -- 输入的形参 @QueryRName varchar(20) OUTPUT -- 输出的形参 AS BEGIN SELECT @QueryRName=RName FROM Relationer WHERE RID=(SELECT RID FROM Customer WHERE CID=@QueryCID AND CStatus=1 ) END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 21. 存储过程使用T-SQL语句创建与管理存储过程 带参数传递的存储过程举例: 【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联系人的存储过程。 如果要查询客户ID为20103530的联系人,调用该存储过程Query_Relationer,代码如下: DECLARE @Relationer_name varchar(20),@Cust_ID int SET @Cust_ID=20103530 EXECUTE Query_Relationer @Cust_ID,@Relationer_name OUTPUT PRINT '客户ID为'+convert(char(8),@Cust_ID)+'的联系人是: '+@Relationer_name 如果要查询客户ID为20103531的联系人。只须在下面代码的第二行,将变量@Cust_ID的值改为20103531。 调用该存储过程,结果没有任何信息返回。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 22. 存储过程使用T-SQL语句创建与管理存储过程 带参数传递的存储过程举例: 【例10-2】为计费系统创建一个实现输入一个有效客户ID,查询其联系人的存储过程。 改进:前面设计的存储过程Query_Relationer还有待改进。对不存在的客户,提示“不存在”信息。因此,需要对给定的客户ID是否在表中存在给出判断。代码如下: ALTER PROCEDURE Query_Relationer @QueryCID int, -- 输入的形参 @QueryRName varchar(20) OUTPUT -- 输出的形参 AS BEGIN -- 判断用户输入的CID值是否存在,如果不存在,返回“不存在” IF EXISTS(SELECT CID FROM Customer WHERE CID=@QueryCID) SELECT @QueryRName=RName FROM Relationer WHERE RID=(SELECT RID FROM Customer WHERE CID=@QueryCID AND CStatus=1 ) ELSE SET @QueryRName='不存在' END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 23. 存储过程使用T-SQL语句创建与管理存储过程 带参数传递的存储过程举例: 【例10-3】利用存储过程计算阶乘。 代码如下: CREATE PROCEDURE factorial --创建存储过程factorial @in_num int, @out_num float OUTPUT AS BEGIN DECLARE @i int,@f float SET @i=1 SET @f=1 WHILE @i<=@in_num BEGIN SET @f=@f*@i SET @i=@i+1 END SET @out_num=@f END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 24. 存储过程使用T-SQL语句创建与管理存储过程 带参数传递的存储过程举例: 【例10-3】利用存储过程计算阶乘。 求9的阶乘,调用存储过程factorial,代码如下: DECLARE @factor float EXECUTE factorial @out_num=@factor OUTPUT,@in_num=9 PRINT @factor第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 25. 触发器触发器概念 触发器(Trigger)是一种特殊的存储过程,它不允许带参数,不能由用户直接通过名称调用,而是由用户的某一动作自动触发。 SQL Server 2008提供三类触发器: DML触发器: 在数据库中发生数据操作(如:INSERT、UPDATE、DELETE)事件时自动执行。 DDL触发器: 在服务器或数据库中发生数据定义(如:CREATE、ALTER、DROP)事件时自动执行。 登录触发器: 在与SQL Server实例建立用户会话时自动执行,主要用来审核和控制服务器会话。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 26. 触发器创建触发器 DML触发器 DML触发器在用户对表中的数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE)时自动运行。 使用触发器模板创建触发器: 在展开SQL Server实例中,依次展开“数据库”→“BillingSys”→“表”,继续展开要创建触发器的具体表节点,选择“触发器”,右击选择“新建触发器”命令,打开“查询编辑器”,在“查询编辑器”中出现触发器的编程模板。 当模板修改完成后,单击窗口工具栏中的“执行”按钮,创建该触发器。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 27. 触发器创建触发器 DML触发器 DML触发器在用户对表中的数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE)时自动运行。 使用触发器模板创建触发器: 比如选择Customer表,打开触发器模板,其模板代码如下: …… CREATE TRIGGER . ON AFTER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 28. 触发器创建触发器 DML触发器 使用CREATE TRIGGER创建 DML触发器: 语法格式如下: CREATE TRIGGER <触发器名> ON <表名|视图名> [WITH ENCRYPTION] FOR|AFTER|INSTEAD OF [INSERT][,][UPDATE][,][DELETE] AS T-SQL语句或语句块 [;]第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 29. 触发器创建触发器 DML触发器 使用CREATE TRIGGER创建 DML触发器: 参数说明如下: 触发器名:触发器名称,必须遵守标识符命名规则,不能以#或##开头。 WITH ENCRYPTION:指定对触发器进行加密处理。 FOR|AFTER:指定触发器中在相应的DML操作(INSERT、UPDATE、DELETE)成功执行后才触发。视图上不能定义FOR和AFTER触发器,只能定义INSTEAD OF触发器。 INSTEAD OF:指定执行DML触发器用于“代替”引发触发器执行的INSERT、UPDATE或DELETE语句。在表或视图上,每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。 [INSERT][,][UPDATE][,][DELETE]:指定能够激活触发器的操作,必须至少指定一个操作。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 30. 触发器创建触发器 DML触发器 【例10-4】在联系人表Relationer中修改联系人ID(RID)后,同步修改客户表Customer中的联系人ID(RID),保证修改记录满足参照完整性。 这是一个UPDATE触发器,其代码如下: CREATE TRIGGER Update_RID ON Relationer AFTER UPDATE AS BEGIN SET NOCOUNT ON UPDATE Customer SET RID=(SELECT RID FROM INSERTED) END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 31. 触发器创建触发器 DML触发器 【例10-4】在联系人表Relationer中修改联系人ID(RID)后,同步修改客户表Customer中的联系人ID(RID),保证修改记录满足参照完整性。 在对Relationer表中RID值进行修改时,需要执行UPDATE命令。当执行UPDATE命令时,会激发Relationer表中的触发器Update_RID,同步对Customer表中的RID列值进行修改。由于Customer表中的RID列存在外键约束fk_Customer,因此,在执行UPDATE命令前,需要对外键约束进行禁用设置,执行完UPDATE命令后,再启用外键约束。具体代码如下: --禁用外键约束 ALTER TABLE Customer NOCHECK CONSTRAINTfk_Customer --更新纪录 UPDATE Relationer SET RID=20106609 where RID=20106605 --启用约束 ALTER TABLE Customer CHECK CONSTRAINT fk_Customer第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 32. 触发器创建触发器 DDL触发器 使用CREATE TRIGGER创建 DDL触发器: 语法格式如下: CREATE TRIGGER <触发器名> ON ALL SERVER|DATABASE [WITH ENCRYPTION] FOR|AFTER <事件类型或事件组>[,…,n] AS [;]第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 33. 触发器创建触发器 DDL触发器 使用CREATE TRIGGER创建 DDL触发器: 参数说明如下: ALL SERVER:指定 DDL触发器的作用域为当前服务器。如果指定了此参数,则只要当前服务器中的任何位置上出现命令中指定的事件类型或事件组,就会激发该触发器。 DATABASE:指定DDL触发器的作用域为当前数据库。如果指定了此参数,则只要当前数据库中出现命令中指定的事件类型或事件组,就会激发该触发器。 WITH ENCRYPTION:对 CREATE TAIGGER语句的文本进行加密处理。 FOR|AFTER:指定DDL触发器仅在命令中指定事件类型或事件组的所有操作都已成功执行时才被触发。 事件类型:将激活DDL 触发器的T-SQL语言事件的名称。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE等操作。 事件组:预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后,都将激发DDL触发器。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 34. 触发器创建触发器 DDL触发器 【例10-5】设计 DDL触发器,禁止修改和删除当前数据库中的任何表。 代码如下: CREATE TRIGGER safe_database ON DATABASE FOR ALTER_TABLE,DROP_TABLE AS BEGIN PRINT '不能修改或删除表!如果必须要完成此操作,请先禁用触发器 safe_database。' ROLLBACK END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 35. 触发器管理触发器 查看触发器信息 因为触发器是特殊的存储过程,所以查看触发器也是使用系统存储过程实现。如: sp_help '触发器名称'。 sp_helptext '触发器名称'。 sp_depends '触发器名称'|'表名'。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 36. 触发器管理触发器 修改触发器 修改DML触发器 语法格式如下: ALTER TRIGGER <触发器名> ON <表名|视图名> [WITH ENCRYPTION] FOR|AFTER|INSTEAD OF [INSERT][,][UPDATE][,][DELETE] AS [;]第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 37. 触发器管理触发器 修改触发器 修改DML触发器,其语法格式如下: ALTER TRIGGER <触发器名> ON <表名|视图名> [WITH ENCRYPTION] FOR|AFTER|INSTEAD OF [INSERT][,][UPDATE][,][DELETE] AS [;] 修改DDL触发器,其语法格式如下: ALTER TRIGGER <触发器名> ON ALL SERVER|DATABASE [WITH ENCRYPTION] FOR|AFTER <事件类型或事件组>[,…,n] AS [;]第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 38. 触发器管理触发器 删除触发器 如果确认触发器已经不再需要,可以使用DROP TRIGGER命令将其删除。 其语法格式如下: DROP TRIGGER <触发器名> 可以使用对象资源管理器来完成触发器的管理。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 39. 触发器管理触发器 禁用与启用触发器 当暂时不需要某个触发器时,可将其禁用。 禁用触发器的语法格式如下: DISABLE TRIGGER <触发器名> ON 对象名|DATABASE|ALL SERVER 已禁用的触发器可以被重新启用。 启用触发器的语法格式如下: ENABLE TRIGGER <触发器名> ON 对象名|DATABASE|ALL SERVER 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 40. 用户自定义函数自定义函数的基本概念 用户自定义函数是为了实现某些功能,用户对多个T-SQL语句进行封装,并返回结果。 用户自定义函数与存储过程的比较:第十章 存储过程、触发器、自定义函数SQL Server 2008比较项用户自定义函数存储过程参数允许有0到多个输入参数,不允许有输出参数允许有多个输入/输出参数返回值有且只有一个返回值可以没有返回值调用在表达式或赋值语句中引用使用EXECUTE调用
  • 41. 用户自定义函数自定义函数的基本概念 使用用户定义函数的优点如下: 模块化程序设计: 将特定的功能封闭在一个用户定义函数中,并存储在数据库中。这个函数只需创建一次,以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。 执行速度快: 与存储过程相似,用户定义函数实施缓存计划。即用户定义函数只需编译一次,以后可以多次重用,从而降低了T-SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化,从而缩短了执行时间。 减少网络流量: 和存储过程一样可以减少网络通信的流量。此外,用户定义函数还可以用在WHERE子句中,在服务器端过滤数据,以减少发送至客户端的数字或行数。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 42. 用户自定义函数自定义函数的基本概念 定义函数的分类: 标量值函数: 标量值函数的返回值是返回子句(RETURNS子句)中定义的类型的单个数据值,不能返回多个值。 内嵌表值函数: 内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集(表)。在内嵌表值函数中,RETUAN子句在括号中含有一条单独的SELECT查询语句,该语句的结果构成了内嵌表值函数所返回的表。 多语句表值函数: 与内嵌表值函数一样,多语句表值函数返回的是由选择结果构成的数据行集(表)。与内联表值函数不同的是,多语句表值函数在返回语句之前还有其他的T-SQL语句,并且RETURNS子句指定的表带有列及其数据类型。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 43. 用户自定义函数创建自定义函数 创建标量值函数: 标量值函数的函数体由一条或多条T-SQL语句组成,写在BEGIN与END之间。 其语法格式如下: CREATE FUNCTION <函数名> ([@形参名 数据类型[,…,n]]) RETURNS 返回值数据类型 [WITH ENCRYPTION] [AS] BEGIN RETURN 返回表达式 END 参数说明如下: 返回值的数据类型不能是text、ntext、image和timestamp类型。 在BEGIN…END之间,必须有一条RETURN语句,用于指定返回表达式,即函数的值。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 44. 用户自定义函数创建自定义函数 创建标量值函数: 【例10-6】定义函数EProduct_Number,当给出一个客户ID,返回该客户所拥有的产品数量。 【分析】 该自定义函数接收的参数是客户ID,数据类型应与EProduct中的CID一致;返回的值是产品数,该数据量一般不大,可以设为smallint类型。 代码如下: CREATE FUNCTION EProduct_Number (@EP_CID int) RETURNS smallint AS BEGIN DECLARE @epnum smallint SELECT @epnum =count(*) FROM EProduct WHERE CID=@EP_CID RETURN @epnum END GO第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 45. 用户自定义函数创建自定义函数 创建内联表值函数: 语法格式如下: CREATE FUNCTION <函数名> ([@形参名 数据类型[,…,n]]) RETURNS Table [WITH ENCRYPTION] [AS] RETURN(SELECT查询语句) 参数说明如下: 内联表值函数没有函数体。 RETURNS Table子句说明返回值是一个表。 RETURN子句中的SELECT语句是返回表中的数据。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 46. 用户自定义函数创建自定义函数 创建内联表值函数: 【例10-7】定义函数EProduct_Table,当给出一个客户ID,返回该客户所拥有的产品号码。 代码如下: CREATE FUNCTION EProduct_Table (@EP_CID int) RETURNS Table AS RETURN (SELECT ENO,EName FROM EProduct WHERE cid=@EP_CID第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 47. 用户自定义函数创建自定义函数 创建多语句表值函数: 语法格式如下: CREATE FUNCTION <函数名> ([@形参名 数据类型[,…,n]]) RETURNS @返回变量 Table (表结构定义) [WITH ENCRYPTION] [AS] BEGIN RETURN END 参数说明如下: RETURNS @返回变量子句指明该函数的返回局部变量,该变量的数据类型是Table,而且在该子句中还需要对返回的表进行表结构的定义。 在BEGIN…END之间的语句是函数体,函数体中必须包括一条不带参数的RETURN语句用于返回表。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 48. 用户自定义函数创建自定义函数 创建多语句表值函数: 【例10-8】定义多语句表值函数EProduct_Table_1,完成上例功能。 代码如下: CREATE FUNCTION EProduct_Table_1 (@EP_CID int) RETURNS @tb Table --定义@tb表变量的结构,其中的列名可以和原数据表名不一样,但数据类型要一致。 ( tb_Eno char(11), tb_EName varchar(50), tb_EJoinData DateTime ) AS BEGIN INSERT INTO @tb SELECT ENO,EName,EJoinDate FROM EProduct WHERE CID=@EP_CID RETURN END第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 49. 用户自定义函数创建自定义函数 创建多语句表值函数: 【例10-8】定义多语句表值函数EProduct_Table_1,完成上例功能。 查看多语句表值函数: 多语句表值函数创建后,可以在SQL Server实例中,依次展开 “数据库”→“BillingSys”→“可编程性”→“函数”→“表值函数”节点,即可看到dbo.EProduct_Table_1函数。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 50. 用户自定义函数创建自定义函数 使用菜单命令创建函数: 在“对象资源管理器”中也可以完成创建函数的操作: 新建标量值函数 新建表值函数 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 51. 用户自定义函数修改和删除自定义函数 查看用户自定义函数: sp_help ’函数名称’:用于查看函数的一般信息,如函数的名称及相关参数。 sp_helptext ’函数名称’:用于查看函数的正文信息。 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 52. 用户自定义函数修改和删除自定义函数 修改用户自定义函数: 语法格式如下: ALTER FUNCTION <函数名> ([@形参名 数据类型[,…,n]]) RETURNS 返回值数据类型 [WITH ENCRYPTION] [AS] BEGIN RETURN 返回表达式 END 删除用户自定义函数: 语法格式如下: DROP FUNCTION <函数名> 使用菜单命令管理自定义函数: 在“对象资源管理器”中选择需要修改或删除的自定义函数,右击,选择相应的菜单命令执行操作即可。 第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 53. 实训:T-SQL编程的高级应用存储过程应用 编写一个存储过程,实现由产品表(EProduct)中的单价(EUnivalence)列按月统计产品的通信费用数据,并为帐单信息表(Bills)添加一数据行。 触发器应用 一般情况下产品表EProduct中的客户ID(CID)是相对不变的,只有一种情况会发生变化,即原来客户已不再使用该产品了,相隔一段时间后有新的客户重新使用它,因此发生CID的变更。 创建一个触发器,在产品表EProduct中修改客户ID(CID)后,如果在开通服务表户表StartAdditionalService有相应的产品,同步删除包含该产品号码的数据行,保证EProduct表中修改记录满足参照完整性。 自定义函数应用 定义函数EProduct_AddNumber,当给出一个产品号码,返回该产品所绑定的附加服务数量。第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 54. 小结存储过程 基本概念。 创建及管理存储过程(创建、修改、删除) 调用存储过程。 触发器 基本概念。 创建触发器(创建DML,DDL) 管理触发器(查看、修改、删除、禁用、启用) 用户自定义函数 基本概念。 创建自定义函数(创建标量值、内联表值、多语句表值函数) 管理自定义函数(查看、修改、删除等)第十章 存储过程、触发器、自定义函数SQL Server 2008
  • 55. Thank You !