存储过程触发器及自定义函数讲稿.ppt

存储过程触发器及自存储过程触发器及自定义函数定义函数第一页，讲稿共五十五页哦第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数存储过程存储过程1触发器触发器2用户自定义函数用户自定义函数3SQL Server 2008实训：实训：T-SQL编程的高级应用编程的高级应用4第二页，讲稿共五十五页哦存储过程存储过程v 存储过程概述 lT-SQL语句进行编程有两种方法：u一种是把T-SQL语句全部写在应用程序中，并存储在本地；u另一种是把部分T-SQL语句编写的程序作为存储过程存储在 SQL Server中，只在本地的应用程序调用存储过程。大多数程序员偏向使用后者。l 存储过程的概念：u存储过程（Stored Procedure）是一组编译好的、存储在服务器上的、能完成特定功能的 T-SQL语句集合，是数据库的一种对象。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三页，讲稿共五十五页哦存储过程存储过程v 存储过程概述 l 使用存储过程的优点：u存储过程只需编译一次，以后即可多次执行，因此可以提高应用程序的性能。u存储过程一经被创建，可以在程序中被多次调用；并且修改存储过程不会影响应用程序源代码，因此极大地提高了程序的重用性、可维护性、共享性和可移植性。u存储过程存储在服务中，能够减少网络流量。u存储过程可被作为一种安全机制来充分利用。l 存储过程的分类：u系统存储过程。u扩展存储过程。u用户定义的存储过程。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l使用存储过程模板创建存储过程：u 依次选择“数据库”“BillingSys”“可编程性”“存储过程”节点，右击选择“新建存储过程”命令，打开“查询编辑器”，在“查询编辑器”中出现存储过程的编程模板。如左图所示。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第五页，讲稿共五十五页哦存储过程存储过程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 2008第六页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l使用存储过程模板创建存储过程：u 修改存储过程的编程模板，插入需要的T-SQL代码。存储过程编程模板。说明：参数按以下格式包括在尖括号（）中：。其中尖括号内是参数的三个元素：参数的名称、该参数的数据类型以及该参数的默认值。parameter_name：列出存储过程或函数中的参数。此字段是只读的。data_type：模板中参数的数据类型。此字段是只读的。若要更改数据类型，请更改模板中的参数。default_value：为所选参数指定值。默认值。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第七页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l使用存储过程模板创建存储过程：u 修改存储过程的编程模板，插入需要的T-SQL代码。在存储过程中编写代码。在上述模板代码中：在”CREATE PROCEDURE”行命令中，用户必须自己定义一个存储过程名称，来替代参数部分，即“”部分。“Param1,”、“Param2,”行命令用来指定参数项，如果用户需要为该存储过程指定参数，则按照提示指定参数，例如：Cust_name varchar(20)；如果不需要参数，则删除这两条命令。“SELECT Param1,”行命令是为参数赋值。如果没有参数，则删除此条命令。用户从模板的第33行之后（即“-Insert statements for procedure here”之后），插入所要编写的存储过程代码。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第八页，讲稿共五十五页哦存储过程存储过程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 2008第九页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l 使用存储过程模板创建存储过程：u 单击工具栏上的“执行”按钮，即可创建该存储过程。u此时，刷新对象资源管理器，重新展开“存储过程”节点，可以看到刚建立的存储过程。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l 利用对象资源管理器修改存储过程：u 在对象资源管理器窗口中，找到需要修改的存储过程节点，在其上右击选择“修改”命令，打开“查询编辑器”，其中出现要修改的存储过程代码。u 对现有的存储过程进行修改。u 修改完成后，单击工具栏上的“执行”按钮，即可完成存储过程的修改。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十一页，讲稿共五十五页哦存储过程存储过程v 使用SSMS创建与管理存储过程l 利用对象资源管理器删除存储过程：u在对象资源管理器窗口中，找到需要删除的存储过程节点，右击选择“删除”命令。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十二页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l创建存储过程：uCREATE PROCEDUR语句的语法格式如下：CREATE PROCEDURE|PROC;n形参名 数据类型,变参名 数据类型 OUTPUT,WITH ENCRYPTION|RECOMPILEFOR REPLICATION AS第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十三页，讲稿共五十五页哦存储过程存储过程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 2008第十四页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l查看存储过程的信息：usp_help 存储过程名称：用于查看存储过程的一般信息，如存储过程的名称、属性、类型和创建时间。usp_helptext 存储过程名称：用于查看存储过程的正文信息。usp_depends 存储过程名称|表名：用于查看指定存储过程所引用的表或者指定的表涉及到的所有存储过程。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十五页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l修改存储过程：uALTER PROCEDURE语句的语法格式如下：ALTER PROCEDURE|PROC 形参名 数据类型,变参名 数据类型 OUTPUT,WITH ENCRYPTION|RECOMPILEFOR REPLICATION AS第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十六页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l删除存储过程：uDROP PROCEDURE语句的语法格式如下：DROP PROCEDURE|PROC,n例如，删除数据库BillingSys中的存储过程“mypro2”,其代码为：USE BillingSysDROP PROCEDURE mypro2第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十七页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l 调用存储过程：uEXECUTE语句的语法格式如下：Exec|Execute整型变量=存储过程名,n|存储过程变量名过程参数=参数值|可变参数名 OUTPUT|DEFAULT,nWITH RECOMPILE;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十八页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l 调用存储过程：uEXECUTE语句的参数说明：整型变量：是可选项，代表存储过程的返回状态，该变量在用于EXECUTE语句前，必须已经声明过。n：可选整数，用于对同名的过程分组。过程参数：为存储过程的参数赋值。其中参数名必须和存储过程定义中的相同。OUTPUT：指定该参数为输出参数，该参数在存储过程中定义时也必须使用OUTPUT选项声明。DEFAULT：指明该参数使用默认值。如果该参数定义时没有指定默认值，则不能使用DEFAULT选项。WITH RECOMPILE：使用WITH RECOMPILE，强制在执行存储过程时重新对其进行编译。而一般情况下，存储过程只有在第一次执行时，系统对其进行编译，并将存储起来，以后执行时直接取出执行计划执行，不再编译。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第十九页，讲稿共五十五页哦存储过程存储过程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 2008第二十页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l带参数传递的存储过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程。如果要查询客户ID为20103530的联系人，调用该存储过程Query_Relationer，代码如下：DECLARE Relationer_name varchar(20),Cust_ID intSET Cust_ID=20103530EXECUTE Query_Relationer Cust_ID,Relationer_name OUTPUTPRINT 客户ID为+convert(char(8),Cust_ID)+的联系人是：+Relationer_nameu如果要查询客户ID为20103531的联系人。只须在下面代码的第二行，将变量Cust_ID的值改为20103531。u调用该存储过程，结果没有任何信息返回。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第二十一页，讲稿共五十五页哦存储过程存储过程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 2008第二十二页，讲稿共五十五页哦存储过程存储过程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 2008第二十三页，讲稿共五十五页哦存储过程存储过程v 使用T-SQL语句创建与管理存储过程l 带参数传递的存储过程举例：【例10-3】利用存储过程计算阶乘。求9的阶乘，调用存储过程factorial，代码如下：DECLARE factor floatEXECUTE factorial out_num=factor OUTPUT,in_num=9PRINT factor第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第二十四页，讲稿共五十五页哦触发器触发器v 触发器概念l触发器（Trigger）是一种特殊的存储过程，它不允许带参数，不能由用户直接通过名称调用，而是由用户的某一动作自动触发。lSQL Server 2008提供三类触发器：uDML触发器：在数据库中发生数据操作（如：INSERT、UPDATE、DELETE）事件时自动执行。uDDL触发器：在服务器或数据库中发生数据定义（如：CREATE、ALTER、DROP）事件时自动执行。u登录触发器：在与SQL Server实例建立用户会话时自动执行，主要用来审核和控制服务器会话。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第二十五页，讲稿共五十五页哦触发器触发器v 创建触发器lDML触发器uDML触发器在用户对表中的数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。u使用触发器模板创建触发器：在展开SQL Server实例中，依次展开“数据库”“BillingSys”“表”，继续展开要创建触发器的具体表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器的编程模板。当模板修改完成后，单击窗口工具栏中的“执行”按钮，创建该触发器。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第二十六页，讲稿共五十五页哦触发器触发器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 2008第二十七页，讲稿共五十五页哦触发器触发器v 创建触发器l DML触发器u使用CREATE TRIGGER创建 DML触发器：语法格式如下：CREATE TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAST-SQL语句或语句块;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第二十八页，讲稿共五十五页哦触发器触发器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 2008第二十九页，讲稿共五十五页哦触发器触发器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 2008第三十页，讲稿共五十五页哦触发器触发器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=20106609 where RID=20106605-启用约束ALTER TABLE Customer CHECK CONSTRAINT fk_Customer第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十一页，讲稿共五十五页哦触发器触发器v 创建触发器l DDL触发器u使用CREATE TRIGGER创建 DDL触发器：语法格式如下：CREATE TRIGGER ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER ,nAS ;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十二页，讲稿共五十五页哦触发器触发器v 创建触发器l DDL触发器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 2008第三十三页，讲稿共五十五页哦触发器触发器v 创建触发器l DDL触发器【例10-5】设计 DDL触发器，禁止修改和删除当前数据库中的任何表。代码如下：CREATE TRIGGER safe_databaseON DATABASEFOR ALTER_TABLE,DROP_TABLEASBEGIN PRINT 不能修改或删除表！如果必须要完成此操作，请先禁用触发器 safe_database。ROLLBACKENDGO第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十四页，讲稿共五十五页哦触发器触发器v 管理触发器l 查看触发器信息u因为触发器是特殊的存储过程，所以查看触发器也是使用系统存储过程实现。如：sp_help 触发器名称。sp_helptext 触发器名称。sp_depends 触发器名称|表名。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十五页，讲稿共五十五页哦触发器触发器v 管理触发器l修改触发器u修改DML触发器 语法格式如下：ALTER TRIGGER ON WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS ;第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十六页，讲稿共五十五页哦触发器触发器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 2008第三十七页，讲稿共五十五页哦触发器触发器v 管理触发器l删除触发器u如果确认触发器已经不再需要，可以使用DROP TRIGGER命令将其删除。u其语法格式如下：DROP TRIGGER u可以使用对象资源管理器来完成触发器的管理。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十八页，讲稿共五十五页哦触发器触发器v 管理触发器l禁用与启用触发器u当暂时不需要某个触发器时，可将其禁用。u禁用触发器的语法格式如下：DISABLE TRIGGER ON 对象名|DATABASE|ALL SERVERu已禁用的触发器可以被重新启用。u启用触发器的语法格式如下：ENABLE TRIGGER ON 对象名|DATABASE|ALL SERVER第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第三十九页，讲稿共五十五页哦用户自定义函数用户自定义函数v 自定义函数的基本概念l 用户自定义函数是为了实现某些功能，用户对多个T-SQL语句进行封装，并返回结果。l 用户自定义函数与存储过程的比较：第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008比较项比较项用户自定义函数用户自定义函数存储过程存储过程参数允许有0到多个输入参数，不允许有输出参数允许有多个输入/输出参数返回值有且只有一个返回值可以没有返回值调用在表达式或赋值语句中引用使用EXECUTE调用第四十页，讲稿共五十五页哦用户自定义函数用户自定义函数v 自定义函数的基本概念l 使用用户定义函数的优点如下：u模块化程序设计：将特定的功能封闭在一个用户定义函数中，并存储在数据库中。这个函数只需创建一次，以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。u执行速度快：与存储过程相似，用户定义函数实施缓存计划。即用户定义函数只需编译一次，以后可以多次重用，从而降低了T-SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。u减少网络流量：和存储过程一样可以减少网络通信的流量。此外，用户定义函数还可以用在WHERE子句中,在服务器端过滤数据，以减少发送至客户端的数字或行数。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十一页，讲稿共五十五页哦用户自定义函数用户自定义函数v 自定义函数的基本概念l定义函数的分类：u标量值函数：标量值函数的返回值是返回子句（RETURNS子句）中定义的类型的单个数据值，不能返回多个值。u内嵌表值函数：内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集（表）。在内嵌表值函数中，RETUAN子句在括号中含有一条单独的SELECT查询语句，该语句的结果构成了内嵌表值函数所返回的表。u多语句表值函数：与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集（表）。与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的TSQL语句，并且RETURNS子句指定的表带有列及其数据类型。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十二页，讲稿共五十五页哦用户自定义函数用户自定义函数v 创建自定义函数l 创建标量值函数：u标量值函数的函数体由一条或多条TSQL语句组成，写在BEGIN与END之间。u其语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表达式ENDu参数说明如下：返回值的数据类型不能是text、ntext、image和timestamp类型。在BEGINEND之间，必须有一条RETURN语句，用于指定返回表达式，即函数的值。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十三页，讲稿共五十五页哦用户自定义函数用户自定义函数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 2008第四十四页，讲稿共五十五页哦用户自定义函数用户自定义函数v 创建自定义函数l创建内联表值函数：u语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS TableWITH ENCRYPTIONAS RETURN(SELECT查询语句)u参数说明如下：内联表值函数没有函数体。RETURNS Table子句说明返回值是一个表。RETURN子句中的SELECT语句是返回表中的数据。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十五页，讲稿共五十五页哦用户自定义函数用户自定义函数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 2008第四十六页，讲稿共五十五页哦用户自定义函数用户自定义函数v 创建自定义函数l创建多语句表值函数：u语法格式如下：CREATE FUNCTION(形参名 数据类型,n)RETURNS 返回变量 Table(表结构定义)WITH ENCRYPTIONASBEGIN RETURNENDu参数说明如下：RETURNS 返回变量子句指明该函数的返回局部变量，该变量的数据类型是Table，而且在该子句中还需要对返回的表进行表结构的定义。在BEGINEND之间的语句是函数体，函数体中必须包括一条不带参数的RETURN语句用于返回表。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十七页，讲稿共五十五页哦用户自定义函数用户自定义函数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 2008第四十八页，讲稿共五十五页哦用户自定义函数用户自定义函数v 创建自定义函数l 创建多语句表值函数：【例10-8】定义多语句表值函数EProduct_Table_1，完成上例功能。查看多语句表值函数：多语句表值函数创建后，可以在SQL Server实例中，依次展开“数据库”“BillingSys”“可编程性”“函数”“表值函数”节点，即可看到dbo.EProduct_Table_1函数。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第四十九页，讲稿共五十五页哦用户自定义函数用户自定义函数v 创建自定义函数l 使用菜单命令创建函数：u在“对象资源管理器”中也可以完成创建函数的操作：新建标量值函数 新建表值函数 第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第五十页，讲稿共五十五页哦用户自定义函数用户自定义函数v 修改和删除自定义函数l 查看用户自定义函数：usp_help 函数名称：用于查看函数的一般信息，如函数的名称及相关参数。usp_helptext 函数名称：用于查看函数的正文信息。第十章第十章 存储过程、触发器、自定义函数存储过程、触发器、自定义函数SQL Server 2008第五十一页，讲稿共五十五页哦用户自定义函数用户自定义函数v 修改和删除自定义函数l修改用户自定义函数：u语法格式如下：ALTER FUNCTION(形参名 数据类型,n)RETURNS 返回值数据类型WITH ENCRYPTIONASBEGIN RETURN 返回表达式ENDl删除用户自定义函数:u语法格式如下：DROP FUNCTION l使用菜单命令管理自定义函数：u在“对象资源管理器”中选择需要修改或删除的自定义函数，右击，选择相应的菜单命令执行操作即可。第十章第十章 存储过程、触发器、自定义函数存储过程、触