存储过程触发器及自定义函数省公共课一等奖全国赛课获奖课件.pptx

数据库实用技术SQL Server 第十章 存放过程、触发器 和自定义函数第1页数据库实用技术第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数存放过程存放过程1触发器触发器2用户自定义函数用户自定义函数3SQL Server 实训：实训：T-SQL编程高级应用编程高级应用4第2页数据库实用技术存放过程存放过程v存放过程概述 lT-SQL语句进行编程有两种方法：u一个是把T-SQL语句全部写在应用程序中，并存放在当地；u另一个是把部分T-SQL语句编写程序作为存放过程存放在 SQL Server中，只在当地应用程序调用存放过程。大多数程序员偏向使用后者。l存放过程概念：u存放过程（Stored Procedure）是一组编译好、存放在服务器上、能完成特定功效 T-SQL语句集合，是数据库一个对象。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第3页数据库实用技术存放过程存放过程v存放过程概述 l使用存放过程优点：u存放过程只需编译一次，以后即可屡次执行，所以能够提升应用程序性能。u存放过程一经被创建，能够在程序中被屡次调用；而且修改存放过程不会影响应用程序源代码，所以极大地提升了程序重用性、可维护性、共享性和可移植性。u存放过程存放在服务中，能够降低网络流量。u存放过程可被作为一个安全机制来充分利用。l存放过程分类：u系统存放过程。u扩展存放过程。u用户定义存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第4页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：u 依次选择“数据库”“BillingSys”“可编程性”“存放过程”节点，右击选择“新建存放过程”命令，打开“查询编辑器”，在“查询编辑器”中出现存放过程编程模板。如左图所表示。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第5页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：u 修改存放过程编程模板，插入需要T-SQL代码。存放过程编程模板。CREATE PROCEDURE -Add the parameters for the stored procedure here =,=ASBEGIN-SET NOCOUNT ON added to prevent extra result sets from-interfering with SELECT statements.SET NOCOUNT ON;-Insert statements for procedure hereSELECT,ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第6页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：u 修改存放过程编程模板，插入需要T-SQL代码。存放过程编程模板。说明：参数按以下格式包含在尖括号（）中：。其中尖括号内是参数三个元素：参数名称、该参数数据类型以及该参数默认值。parameter_name：列出存放过程或函数中参数。此字段是只读。data_type：模板中参数数据类型。此字段是只读。若要更改数据类型，请更改模板中参数。default_value：为所选参数指定值。默认值。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第7页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：u 修改存放过程编程模板，插入需要T-SQL代码。在存放过程中编写代码。在上述模板代码中：在”CREATE PROCEDURE”行命令中，用户必须自己定义一个存放过程名称，来替换参数部分，即“”部分。“Param1,”、“Param2,”行命令用来指定参数项，假如用户需要为该存放过程指定参数，则按照提醒指定参数，比如：Cust_name varchar(20)；假如不需要参数，则删除这两条命令。“SELECT Param1,”行命令是为参数赋值。假如没有参数，则删除此条命令。用户从模板第33行之后（即“-Insert statements for procedure here”之后），插入所要编写存放过程代码。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第8页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：【例10-1】为计费系统创建一个实现查询单位客户及其联络人存放过程。CREATE PROCEDURE mypro1 ASBEGIN-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=大户ENDGO 第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第9页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l使用存放过程模板创建存放过程：u 单击工具栏上“执行”按钮，即可创建该存放过程。u此时，刷新对象资源管理器，重新展开“存放过程”节点，能够看到刚建立存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第10页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l利用对象资源管理器修改存放过程：u 在对象资源管理器窗口中，找到需要修改存放过程节点，在其上右击选择“修改”命令，打开“查询编辑器”，其中出现要修改存放过程代码。u 对现有存放过程进行修改。u 修改完成后，单击工具栏上“执行”按钮，即可完成存放过程修改。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第11页数据库实用技术存放过程存放过程v使用SSMS创建与管理存放过程l利用对象资源管理器删除存放过程：u在对象资源管理器窗口中，找到需要删除存放过程节点，右击选择“删除”命令。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第12页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l创建存放过程：uCREATE PROCEDUR语句语法格式以下：CREATE PROCEDURE|PROC;n形参名 数据类型,变参名 数据类型 OUTPUT,WITH ENCRYPTION|RECOMPILEFOR REPLICATION AS第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第13页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l创建存放过程：uCREATE PROCEDUR语句参数说明：n：是可选整数，用于对同名过程分组。形参名：指定过程中参数。每个参数仅作用于该过程本身，是局部。变参名：指定作为输出参数支持结果集。该参数由存放过程动态结构，其内容可能发生改变。仅适合用于CURSOR参数。WITH ENCRYPTION：指示SQL Server将CREATE PROCEDURE语句原始文本转换为含糊格式。WITH RECOMPILE：指示数据库引擎不缓存该过程计划，该过程在运行时编译。假如指定了FOR REPLICATION，则不能使用此选项。FOR REPLICATION：指定不能在订阅服务器上执行为复制创建存放过程。使用 FOR REPLICATION选项创建存放过程可用作存放过程筛选器，且只能在复制过程中执行。假如指定了FOR REPLICATION，则无法申明参数。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第14页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l查看存放过程信息：usp_help 存放过程名称：用于查看存放过程普通信息，如存放过程名称、属性、类型和创建时间。usp_helptext 存放过程名称：用于查看存放过程正文信息。usp_depends 存放过程名称|表名：用于查看指定存放过程所引用表或者指定表包括到全部存放过程。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第15页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l修改存放过程：uALTER PROCEDURE语句语法格式以下：ALTER PROCEDURE|PROC 形参名 数据类型,变参名 数据类型 OUTPUT,WITH ENCRYPTION|RECOMPILEFOR REPLICATION AS第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第16页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l删除存放过程：uDROP PROCEDURE语句语法格式以下：DROP PROCEDURE|PROC,n比如，删除数据库BillingSys中存放过程“mypro2”,其代码为：USE BillingSysDROP PROCEDURE mypro2第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第17页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l调用存放过程：uEXECUTE语句语法格式以下：Exec|Execute整型变量=存放过程名,n|存放过程变量名过程参数=参数值|可变参数名 OUTPUT|DEFAULT,nWITH RECOMPILE;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第18页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l调用存放过程：uEXECUTE语句参数说明：整型变量：是可选项，代表存放过程返回状态，该变量在用于EXECUTE语句前，必须已经申明过。n：可选整数，用于对同名过程分组。过程参数：为存放过程参数赋值。其中参数名必须和存放过程定义中相同。OUTPUT：指定该参数为输出参数，该参数在存放过程中定义时也必须使用OUTPUT选项申明。DEFAULT：指明该参数使用默认值。假如该参数定义时没有指定默认值，则不能使用DEFAULT选项。WITH RECOMPILE：使用WITH RECOMPILE，强制在执行存放过程时重新对其进行编译。而普通情况下，存放过程只有在第一次执行时，系统对其进行编译，并将存放起来，以后执行时直接取出执行计划执行，不再编译。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第19页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联络人存放过程。创建该存放过程Query_Relationer代码以下：CREATE PROCEDURE Query_Relationer QueryCID int,-输入形参 QueryRName varchar(20)OUTPUT-输出形参ASBEGINSELECT QueryRName=RName FROM Relationer WHERE RID=(SELECT RID FROM Customer WHERE CID=QueryCID AND CStatus=1)ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第20页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联络人存放过程。假如要查询客户ID为3530联络人，调用该存放过程Query_Relationer，代码以下：DECLARE Relationer_name varchar(20),Cust_ID intSET Cust_ID=3530EXECUTE Query_Relationer Cust_ID,Relationer_name OUTPUTPRINT 客户ID为+convert(char(8),Cust_ID)+联络人是：+Relationer_nameu假如要查询客户ID为3531联络人。只须在下面代码第二行，将变量Cust_ID值改为3531。u调用该存放过程，结果没有任何信息返回。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第21页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联络人存放过程。改进：前面设计存放过程Query_Relationer还有待改进。对不存在客户，提醒“不存在”信息。所以，需要对给定客户ID是否在表中存在给出判断。代码以下：ALTER PROCEDURE Query_Relationer QueryCID int,-输入形参 QueryRName varchar(20)OUTPUT-输出形参ASBEGIN -判断用户输入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=不存在ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第22页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例：【例10-3】利用存放过程计算阶乘。代码以下：CREATE PROCEDURE factorial -创建存放过程factorial in_num int,out_num float OUTPUTASBEGIN 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=fENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第23页数据库实用技术存放过程存放过程v使用T-SQL语句创建与管理存放过程l带参数传递存放过程举例：【例10-3】利用存放过程计算阶乘。求9阶乘，调用存放过程factorial，代码以下：DECLARE factor floatEXECUTE factorial out_num=factor OUTPUT,in_num=9PRINT factor第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第24页数据库实用技术触发器触发器v触发器概念l触发器（Trigger）是一个特殊存放过程，它不允许带参数，不能由用户直接经过名称调用，而是由用户某一动作自动触发。lSQL Server 提供三类触发器：uDML触发器：在数据库中发生数据操作（如：INSERT、UPDATE、DELETE）事件时自动执行。uDDL触发器：在服务器或数据库中发生数据定义（如：CREATE、ALTER、DROP）事件时自动执行。u登录触发器：在与SQL Server实例建立用户会话时自动执行，主要用来审核和控制服务器会话。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第25页数据库实用技术触发器触发器v创建触发器lDML触发器uDML触发器在用户对表中数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。u使用触发器模板创建触发器：在展开SQL Server实例中，依次展开“数据库”“BillingSys”“表”，继续展开要创建触发器详细表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器编程模板。当模板修改完成后，单击窗口工具栏中“执行”按钮，创建该触发器。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第26页数据库实用技术触发器触发器v创建触发器lDML触发器uDML触发器在用户对表中数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。u使用触发器模板创建触发器：比如选择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 hereENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第27页数据库实用技术触发器触发器v创建触发器lDML触发器u使用CREATE TRIGGER创建 DML触发器：语法格式以下：CREATE TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAST-SQL语句或语句块;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第28页数据库实用技术触发器触发器v创建触发器lDML触发器u使用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 第29页数据库实用技术触发器触发器v创建触发器lDML触发器【例10-4】在联络人表Relationer中修改联络人ID（RID）后，同时修改客户表Customer中联络人ID（RID），确保修改统计满足参考完整性。这是一个UPDATE触发器，其代码以下：CREATE TRIGGER Update_RIDON RelationerAFTER UPDATEAS BEGIN SET NOCOUNT ON UPDATE Customer SET RID=(SELECT RID FROM INSERTED)ENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第30页数据库实用技术触发器触发器v创建触发器lDML触发器【例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=6609 where RID=6605-启用约束ALTER TABLE Customer CHECK CONSTRAINT fk_Customer第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第31页数据库实用技术触发器触发器v创建触发器lDDL触发器u使用CREATE TRIGGER创建 DDL触发器：语法格式以下：CREATE TRIGGER ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER ,nAS ;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第32页数据库实用技术触发器触发器v创建触发器lDDL触发器u使用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 第33页数据库实用技术触发器触发器v创建触发器lDDL触发器【例10-5】设计 DDL触发器，禁止修改和删除当前数据库中任何表。代码以下：CREATE TRIGGER safe_databaseON DATABASEFOR ALTER_TABLE,DROP_TABLEASBEGIN PRINT 不能修改或删除表！假如必须要完成此操作，请先禁用触发器 safe_database。ROLLBACKENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第34页数据库实用技术触发器触发器v管理触发器l查看触发器信息u因为触发器是特殊存放过程，所以查看触发器也是使用系统存放过程实现。如：sp_help 触发器名称。sp_helptext 触发器名称。sp_depends 触发器名称|表名。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第35页数据库实用技术触发器触发器v管理触发器l修改触发器u修改DML触发器语法格式以下：ALTER TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS ;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第36页数据库实用技术触发器触发器v管理触发器l修改触发器u修改DML触发器，其语法格式以下：ALTER TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS ;u修改DDL触发器，其语法格式以下：ALTER TRIGGER ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER ,nAS ;第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第37页数据库实用技术触发器触发器v管理触发器l删除触发器u假如确认触发器已经不再需要，能够使用DROP TRIGGER命令将其删除。u其语法格式以下：DROP TRIGGER u能够使用对象资源管理器来完成触发器管理。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第38页数据库实用技术触发器触发器v管理触发器l禁用与启用触发器u当暂时不需要某个触发器时，可将其禁用。u禁用触发器语法格式以下：DISABLE TRIGGER ON 对象名|DATABASE|ALL SERVERu已禁用触发器能够被重新启用。u启用触发器语法格式以下：ENABLE TRIGGER ON 对象名|DATABASE|ALL SERVER第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第39页数据库实用技术用户自定义函数用户自定义函数v自定义函数基本概念l用户自定义函数是为了实现一些功效，用户对多个T-SQL语句进行封装，并返回结果。l用户自定义函数与存放过程比较：第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 比较项比较项用户自定义函数用户自定义函数存放过程参数允许有0到多个输入参数，不允许有输出参数允许有多个输入/输出参数返回值有且只有一个返回值能够没有返回值调用在表示式或赋值语句中引用使用EXECUTE调用第40页数据库实用技术用户自定义函数用户自定义函数v自定义函数基本概念l使用用户定义函数优点以下：u模块化程序设计：将特定功效封闭在一个用户定义函数中，并存放在数据库中。这个函数只需创建一次，以后便能够在程序中屡次调用。而且用户定义函数能够独立于程序源代码进行修改。u执行速度快：与存放过程相同，用户定义函数实施缓存计划。即用户定义函数只需编译一次，以后能够屡次重用，从而降低了T-SQL代码编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。u降低网络流量：和存放过程一样能够降低网络通信流量。另外，用户定义函数还能够用在WHERE子句中,在服务器端过滤数据，以降低发送至客户端数字或行数。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第41页数据库实用技术用户自定义函数用户自定义函数v自定义函数基本概念l定义函数分类：u标量值函数：标量值函数返回值是返回子句（RETURNS子句）中定义类型单个数据值，不能返回多个值。u内嵌表值函数：内嵌表值函数返回是在RETURNS子句中指定“table”类型数据行集（表）。在内嵌表值函数中，RETUAN子句在括号中含有一条单独SELECT查询语句，该语句结果组成了内嵌表值函数所返回表。u多语句表值函数：与内嵌表值函数一样，多语句表值函数返回是由选择结果组成数据行集（表）。与内联表值函数不一样是，多语句表值函数在返回语句之前还有其它TSQL语句，而且RETURNS子句指定表带有列及其数据类型。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第42页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建标量值函数：u标量值函数函数体由一条或多条TSQL语句组成，写在BEGIN与END之间。u其语法格式以下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表示式ENDu参数说明以下：返回值数据类型不能是text、ntext、image和timestamp类型。在BEGINEND之间，必须有一条RETURN语句，用于指定返回表示式，即函数值。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第43页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建标量值函数：【例10-6】定义函数EProduct_Number，当给出一个客户ID，返回该客户所拥有产品数量。【分析】u该自定义函数接收参数是客户ID，数据类型应与EProduct中CID一致；返回值是产品数，该数据量普通不大，能够设为smallint类型。u代码以下：CREATE FUNCTION EProduct_Number(EP_CID int)RETURNS smallintASBEGIN DECLARE epnum smallint SELECT epnum=count(*)FROM EProduct WHERE CID=EP_CID RETURN epnumENDGO第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第44页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建内联表值函数：u语法格式以下：CREATE FUNCTION(形参名 数据类型,n)RETURNS TableWITH ENCRYPTIONAS RETURN(SELECT查询语句)u参数说明以下：内联表值函数没有函数体。RETURNS Table子句说明返回值是一个表。RETURN子句中SELECT语句是返回表中数据。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第45页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建内联表值函数：【例10-7】定义函数EProduct_Table，当给出一个客户ID，返回该客户所拥有产品号码。代码以下：CREATE FUNCTION EProduct_Table(EP_CID int)RETURNS TableAS RETURN(SELECT ENO,EName FROM EProduct WHERE cid=EP_CID第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第46页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建多语句表值函数：u语法格式以下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回变量 Table(表结构定义)WITH ENCRYPTIONASBEGIN RETURNENDu参数说明以下：RETURNS 返回变量子句指明该函数返回局部变量，该变量数据类型是Table，而且在该子句中还需要对返回表进行表结构定义。在BEGINEND之间语句是函数体，函数体中必须包含一条不带参数RETURN语句用于返回表。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第47页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建多语句表值函数：【例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)ASBEGIN INSERT INTO tb SELECT ENO,EName,EJoinDate FROM EProduct WHERE CID=EP_CID RETURNEND第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第48页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l创建多语句表值函数：【例10-8】定义多语句表值函数EProduct_Table_1，完成上例功效。查看多语句表值函数：多语句表值函数创建后，能够在SQL Server实例中，依次展开“数据库”“BillingSys”“可编程性”“函数”“表值函数”节点，即可看到dbo.EProduct_Table_1函数。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第49页数据库实用技术用户自定义函数用户自定义函数v创建自定义函数l使用菜单命令创建函数：u在“对象资源管理器”中也能够完成创建函数操作：新建标量值函数 新建表值函数 第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第50页数据库实用技术用户自定义函数用户自定义函数v修改和删除自定义函数l查看用户自定义函数：usp_help 函数名称：用于查看函数普通信息，如函数名称及相关参数。usp_helptext 函数名称：用于查看函数正文信息。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第51页数据库实用技术用户自定义函数用户自定义函数v修改和删除自定义函数l修改用户自定义函数：u语法格式以下：ALTER FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表示式ENDl删除用户自定义函数:u语法格式以下：DROP FUNCTION l使用菜单命令管理自定义函数：u在“对象资源管理器”中选择需要修改或删除自定义函数，右击，选择对应菜单命令执行操作即可。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第52页数据库实用技术实训：实训：T-SQL编程高级应用编程高级应用v存放过程应用编写一个存放过程，实现由产品表（EProduct）中单价（EUnivalence）列按月统计产品通信费用数据，并为帐单信息表（Bills）添加一数据行。v触发器应用普通情况下产品表EProduct中客户ID（CID）是相对不变，只有一个情况会发生改变，即原来客户已不再使用该产品了，相隔一段时间后有新客户重新使用它，所以发生CID变更。创建一个触发器，在产品表EProduct中修改客户ID（CID）后，假如在开通服务表户表StartAdditionalService有对应产品，同时删除包含该产品号码数据行，确保EProduct表中修改统计满足参考完整性。v自定义函数应用定义函数EProduct_AddNumber，当给出一个产品号码，返回该产品所绑定附加服务数量。第十章第十章 存放过程、触发器、自定义函数存放过程、触发器、自定义函数SQL Server 第53页数据库实用技术小结小结v存放过程l基本概念。l创建及管理存放过程（创建、修改、删除）l调用存放过程。v触发器l基本概念。l创建触发器（创建DML，DDL）l管理触发器（查看、修改、删除、禁用、启用）v用户自定义函数l基本概念。l创建自定义函数（创建标量值、内联表值、多语句表值函数）l管理自定义函数