第十章_存储过程触发器及自定义函数.ppt

数据库实用技术SQLServer2008,第十章存储过程、触发器和自定义函数,SQLServer2008,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,存储过程概述T-SQL语句进行编程有两种方法：一种是把T-SQL语句全部写在应用程序中，并存储在本地；另一种是把部分T-SQL语句编写的程序作为存储过程存储在SQLServer中，只在本地的应用程序调用存储过程。大多数程序员偏向使用后者。存储过程的概念：存储过程（StoredProcedure）是一组编译好的、存储在服务器上的、能完成特定功能的T-SQL语句集合，是数据库的一种对象。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,存储过程概述使用存储过程的优点：存储过程只需编译一次，以后即可多次执行，因此可以提高应用程序的性能。存储过程一经被创建，可以在程序中被多次调用；并且修改存储过程不会影响应用程序源代码，因此极大地提高了程序的重用性、可维护性、共享性和可移植性。存储过程存储在服务中，能够减少网络流量。存储过程可被作为一种安全机制来充分利用。存储过程的分类：系统存储过程。扩展存储过程。用户定义的存储过程。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：依次选择“数据库”“BillingSys”“可编程性”“存储过程”节点，右击选择“新建存储过程”命令，打开“查询编辑器”，在“查询编辑器”中出现存储过程的编程模板。如左图所示。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：修改存储过程的编程模板，插入需要的T-SQL代码。存储过程编程模板。CREATEPROCEDURE-Addtheparametersforthestoredprocedurehere=,=ASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;-InsertstatementsforprocedurehereSELECT,ENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：修改存储过程的编程模板，插入需要的T-SQL代码。存储过程编程模板。说明：参数按以下格式包括在尖括号（）中：。其中尖括号内是参数的三个元素：参数的名称、该参数的数据类型以及该参数的默认值。parameter_name：列出存储过程或函数中的参数。此字段是只读的。data_type：模板中参数的数据类型。此字段是只读的。若要更改数据类型，请更改模板中的参数。default_value：为所选参数指定值。默认值。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：修改存储过程的编程模板，插入需要的T-SQL代码。在存储过程中编写代码。在上述模板代码中：在”CREATEPROCEDURE”行命令中，用户必须自己定义一个存储过程名称，来替代参数部分，即“”部分。“Param1,”、“Param2,”行命令用来指定参数项，如果用户需要为该存储过程指定参数，则按照提示指定参数，例如：Cust_namevarchar(20)；如果不需要参数，则删除这两条命令。“SELECTParam1,”行命令是为参数赋值。如果没有参数，则删除此条命令。用户从模板的第33行之后（即“-Insertstatementsforprocedurehere”之后），插入所要编写的存储过程代码。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：【例10-1】为计费系统创建一个实现查询单位客户及其联系人的存储过程。CREATEPROCEDUREmypro1ASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;selectcid,cname,rname,rtelephonefromCustomerinnerjoinRelationeronCustomer.RID=Relationer.RIDwhereCType=商业orCType=公众orCType=大户ENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程使用存储过程模板创建存储过程：单击工具栏上的“执行”按钮，即可创建该存储过程。此时，刷新对象资源管理器，重新展开“存储过程”节点，可以看到刚建立的存储过程。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程利用对象资源管理器修改存储过程：在对象资源管理器窗口中，找到需要修改的存储过程节点，在其上右击选择“修改”命令，打开“查询编辑器”，其中出现要修改的存储过程代码。对现有的存储过程进行修改。修改完成后，单击工具栏上的“执行”按钮，即可完成存储过程的修改。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用SSMS创建与管理存储过程利用对象资源管理器删除存储过程：在对象资源管理器窗口中，找到需要删除的存储过程节点，右击选择“删除”命令。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程创建存储过程：CREATEPROCEDUR语句的语法格式如下：CREATEPROCEDURE|PROC;n形参名数据类型,变参名数据类型OUTPUT,WITHENCRYPTION|RECOMPILEFORREPLICATIONAS,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程创建存储过程：CREATEPROCEDUR语句的参数说明：n：是可选整数，用于对同名的过程分组。形参名：指定过程中的参数。每个参数仅作用于该过程本身，是局部的。变参名：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适用于CURSOR参数。WITHENCRYPTION：指示SQLServer将CREATEPROCEDURE语句的原始文本转换为模糊格式。WITHRECOMPILE：指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了FORREPLICATION，则不能使用此选项。FORREPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FORREPLICATION选项创建的存储过程可用作存储过程筛选器，且只能在复制过程中执行。如果指定了FORREPLICATION，则无法声明参数。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程查看存储过程的信息：sp_help存储过程名称：用于查看存储过程的一般信息，如存储过程的名称、属性、类型和创建时间。sp_helptext存储过程名称：用于查看存储过程的正文信息。sp_depends存储过程名称|表名：用于查看指定存储过程所引用的表或者指定的表涉及到的所有存储过程。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程修改存储过程：ALTERPROCEDURE语句的语法格式如下：ALTERPROCEDURE|PROC形参名数据类型,变参名数据类型OUTPUT,WITHENCRYPTION|RECOMPILEFORREPLICATIONAS,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程删除存储过程：DROPPROCEDURE语句的语法格式如下：DROPPROCEDURE|PROC,n例如，删除数据库BillingSys中的存储过程“mypro2”,其代码为：USEBillingSysDROPPROCEDUREmypro2,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程调用存储过程：EXECUTE语句的语法格式如下：Exec|Execute整型变量=存储过程名,n|存储过程变量名过程参数=参数值|可变参数名OUTPUT|DEFAULT,nWITHRECOMPILE;,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程调用存储过程：EXECUTE语句的参数说明：整型变量：是可选项，代表存储过程的返回状态，该变量在用于EXECUTE语句前，必须已经声明过。n：可选整数，用于对同名的过程分组。过程参数：为存储过程的参数赋值。其中参数名必须和存储过程定义中的相同。OUTPUT：指定该参数为输出参数，该参数在存储过程中定义时也必须使用OUTPUT选项声明。DEFAULT：指明该参数使用默认值。如果该参数定义时没有指定默认值，则不能使用DEFAULT选项。WITHRECOMPILE：使用WITHRECOMPILE，强制在执行存储过程时重新对其进行编译。而一般情况下，存储过程只有在第一次执行时，系统对其进行编译，并将存储起来，以后执行时直接取出执行计划执行，不再编译。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程带参数传递的存储过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程。创建该存储过程Query_Relationer代码如下：CREATEPROCEDUREQuery_RelationerQueryCIDint,-输入的形参QueryRNamevarchar(20)OUTPUT-输出的形参ASBEGINSELECTQueryRName=RNameFROMRelationerWHERERID=(SELECTRIDFROMCustomerWHERECID=QueryCIDANDCStatus=1)ENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程带参数传递的存储过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程。如果要查询客户ID为20103530的联系人，调用该存储过程Query_Relationer，代码如下：DECLARERelationer_namevarchar(20),Cust_IDintSETCust_ID=20103530EXECUTEQuery_RelationerCust_ID,Relationer_nameOUTPUTPRINT客户ID为+convert(char(8),Cust_ID)+的联系人是：+Relationer_name如果要查询客户ID为20103531的联系人。只须在下面代码的第二行，将变量Cust_ID的值改为20103531。调用该存储过程，结果没有任何信息返回。,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程带参数传递的存储过程举例：【例10-2】为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程。改进：前面设计的存储过程Query_Relationer还有待改进。对不存在的客户，提示“不存在”信息。因此，需要对给定的客户ID是否在表中存在给出判断。代码如下：ALTERPROCEDUREQuery_RelationerQueryCIDint,-输入的形参QueryRNamevarchar(20)OUTPUT-输出的形参ASBEGIN-判断用户输入的CID值是否存在，如果不存在，返回“不存在”IFEXISTS(SELECTCIDFROMCustomerWHERECID=QueryCID)SELECTQueryRName=RNameFROMRelationerWHERERID=(SELECTRIDFROMCustomerWHERECID=QueryCIDANDCStatus=1)ELSESETQueryRName=不存在ENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程带参数传递的存储过程举例：【例10-3】利用存储过程计算阶乘。代码如下：CREATEPROCEDUREfactorial-创建存储过程factorialin_numint,out_numfloatOUTPUTASBEGINDECLAREiint,ffloatSETi=1SETf=1WHILEi=in_numBEGINSETf=f*iSETi=i+1ENDSETout_num=fENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,存储过程,使用T-SQL语句创建与管理存储过程带参数传递的存储过程举例：【例10-3】利用存储过程计算阶乘。求9的阶乘，调用存储过程factorial，代码如下：DECLAREfactorfloatEXECUTEfactorialout_num=factorOUTPUT,in_num=9PRINTfactor,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,触发器概念触发器（Trigger）是一种特殊的存储过程，它不允许带参数，不能由用户直接通过名称调用，而是由用户的某一动作自动触发。SQLServer2008提供三类触发器：DML触发器：在数据库中发生数据操作（如：INSERT、UPDATE、DELETE）事件时自动执行。DDL触发器：在服务器或数据库中发生数据定义（如：CREATE、ALTER、DROP）事件时自动执行。登录触发器：在与SQLServer实例建立用户会话时自动执行，主要用来审核和控制服务器会话。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器DML触发器在用户对表中的数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。使用触发器模板创建触发器：在展开SQLServer实例中，依次展开“数据库”“BillingSys”“表”，继续展开要创建触发器的具体表节点，选择“触发器”，右击选择“新建触发器”命令，打开“查询编辑器”，在“查询编辑器”中出现触发器的编程模板。当模板修改完成后，单击窗口工具栏中的“执行”按钮，创建该触发器。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器DML触发器在用户对表中的数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE）时自动运行。使用触发器模板创建触发器：比如选择Customer表，打开触发器模板，其模板代码如下：CREATETRIGGER.ONAFTERASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;-InsertstatementsfortriggerhereENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器使用CREATETRIGGER创建DML触发器：语法格式如下：CREATETRIGGERONWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEAST-SQL语句或语句块;,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器使用CREATETRIGGER创建DML触发器：参数说明如下：触发器名：触发器名称，必须遵守标识符命名规则，不能以#或#开头。WITHENCRYPTION：指定对触发器进行加密处理。FOR|AFTER：指定触发器中在相应的DML操作（INSERT、UPDATE、DELETE）成功执行后才触发。视图上不能定义FOR和AFTER触发器，只能定义INSTEADOF触发器。INSTEADOF：指定执行DML触发器用于“代替”引发触发器执行的INSERT、UPDATE或DELETE语句。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEADOF触发器。INSERT,UPDATE,DELETE：指定能够激活触发器的操作，必须至少指定一个操作。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器【例10-4】在联系人表Relationer中修改联系人ID（RID）后，同步修改客户表Customer中的联系人ID（RID），保证修改记录满足参照完整性。这是一个UPDATE触发器，其代码如下：CREATETRIGGERUpdate_RIDONRelationerAFTERUPDATEASBEGINSETNOCOUNTONUPDATECustomerSETRID=(SELECTRIDFROMINSERTED)ENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DML触发器【例10-4】在联系人表Relationer中修改联系人ID（RID）后，同步修改客户表Customer中的联系人ID（RID），保证修改记录满足参照完整性。在对Relationer表中RID值进行修改时，需要执行UPDATE命令。当执行UPDATE命令时，会激发Relationer表中的触发器Update_RID，同步对Customer表中的RID列值进行修改。由于Customer表中的RID列存在外键约束fk_Customer，因此，在执行UPDATE命令前，需要对外键约束进行禁用设置，执行完UPDATE命令后，再启用外键约束。具体代码如下：-禁用外键约束ALTERTABLECustomerNOCHECKCONSTRAINTfk_Customer-更新纪录UPDATERelationerSETRID=20106609whereRID=20106605-启用约束ALTERTABLECustomerCHECKCONSTRAINTfk_Customer,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DDL触发器使用CREATETRIGGER创建DDL触发器：语法格式如下：CREATETRIGGERONALLSERVER|DATABASEWITHENCRYPTIONFOR|AFTER,nAS;,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DDL触发器使用CREATETRIGGER创建DDL触发器：参数说明如下：ALLSERVER：指定DDL触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现命令中指定的事件类型或事件组，就会激发该触发器。DATABASE：指定DDL触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中出现命令中指定的事件类型或事件组，就会激发该触发器。WITHENCRYPTION：对CREATETAIGGER语句的文本进行加密处理。FOR|AFTER：指定DDL触发器仅在命令中指定事件类型或事件组的所有操作都已成功执行时才被触发。事件类型：将激活DDL触发器的T-SQL语言事件的名称。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE等操作。事件组：预定义的T-SQL语句事件分组的名称。执行任何属于事件组的T-SQL语句事件之后，都将激发DDL触发器。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,创建触发器DDL触发器【例10-5】设计DDL触发器，禁止修改和删除当前数据库中的任何表。代码如下：CREATETRIGGERsafe_databaseONDATABASEFORALTER_TABLE,DROP_TABLEASBEGINPRINT不能修改或删除表！如果必须要完成此操作，请先禁用触发器safe_database。ROLLBACKENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,管理触发器查看触发器信息因为触发器是特殊的存储过程，所以查看触发器也是使用系统存储过程实现。如：sp_help触发器名称。sp_helptext触发器名称。sp_depends触发器名称|表名。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,管理触发器修改触发器修改DML触发器语法格式如下：ALTERTRIGGERONWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEAS;,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,管理触发器修改触发器修改DML触发器，其语法格式如下：ALTERTRIGGERONWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEAS;修改DDL触发器，其语法格式如下：ALTERTRIGGERONALLSERVER|DATABASEWITHENCRYPTIONFOR|AFTER,nAS;,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,管理触发器删除触发器如果确认触发器已经不再需要，可以使用DROPTRIGGER命令将其删除。其语法格式如下：DROPTRIGGER可以使用对象资源管理器来完成触发器的管理。,第十章存储过程、触发器、自定义函数,SQLServer2008,触发器,管理触发器禁用与启用触发器当暂时不需要某个触发器时，可将其禁用。禁用触发器的语法格式如下：DISABLETRIGGERON对象名|DATABASE|ALLSERVER已禁用的触发器可以被重新启用。启用触发器的语法格式如下：ENABLETRIGGERON对象名|DATABASE|ALLSERVER,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,自定义函数的基本概念用户自定义函数是为了实现某些功能，用户对多个T-SQL语句进行封装，并返回结果。用户自定义函数与存储过程的比较：,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,自定义函数的基本概念使用用户定义函数的优点如下：模块化程序设计：将特定的功能封闭在一个用户定义函数中，并存储在数据库中。这个函数只需创建一次，以后便可以在程序中多次调用。并且用户定义函数可以独立于程序源代码进行修改。执行速度快：与存储过程相似，用户定义函数实施缓存计划。即用户定义函数只需编译一次，以后可以多次重用，从而降低了T-SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。减少网络流量：和存储过程一样可以减少网络通信的流量。此外，用户定义函数还可以用在WHERE子句中,在服务器端过滤数据，以减少发送至客户端的数字或行数。,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,自定义函数的基本概念定义函数的分类：标量值函数：标量值函数的返回值是返回子句（RETURNS子句）中定义的类型的单个数据值，不能返回多个值。内嵌表值函数：内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集（表）。在内嵌表值函数中，RETUAN子句在括号中含有一条单独的SELECT查询语句，该语句的结果构成了内嵌表值函数所返回的表。多语句表值函数：与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集（表）。与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的TSQL语句，并且RETURNS子句指定的表带有列及其数据类型。,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建标量值函数：标量值函数的函数体由一条或多条TSQL语句组成，写在BEGIN与END之间。其语法格式如下：CREATEFUNCTION(形参名数据类型,n)RETURNS返回值数据类型WITHENCRYPTIONASBEGINRETURN返回表达式END参数说明如下：返回值的数据类型不能是text、ntext、image和timestamp类型。在BEGINEND之间，必须有一条RETURN语句，用于指定返回表达式，即函数的值。,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建标量值函数：【例10-6】定义函数EProduct_Number，当给出一个客户ID，返回该客户所拥有的产品数量。【分析】该自定义函数接收的参数是客户ID，数据类型应与EProduct中的CID一致；返回的值是产品数，该数据量一般不大，可以设为smallint类型。代码如下：CREATEFUNCTIONEProduct_Number(EP_CIDint)RETURNSsmallintASBEGINDECLAREepnumsmallintSELECTepnum=count(*)FROMEProductWHERECID=EP_CIDRETURNepnumENDGO,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建内联表值函数：语法格式如下：CREATEFUNCTION(形参名数据类型,n)RETURNSTableWITHENCRYPTIONASRETURN(SELECT查询语句)参数说明如下：内联表值函数没有函数体。RETURNSTable子句说明返回值是一个表。RETURN子句中的SELECT语句是返回表中的数据。,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建内联表值函数：【例10-7】定义函数EProduct_Table，当给出一个客户ID，返回该客户所拥有的产品号码。代码如下：CREATEFUNCTIONEProduct_Table(EP_CIDint)RETURNSTableASRETURN(SELECTENO,ENameFROMEProductWHEREcid=EP_CID,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建多语句表值函数：语法格式如下：CREATEFUNCTION(形参名数据类型,n)RETURNS返回变量Table(表结构定义)WITHENCRYPTIONASBEGINRETURNEND参数说明如下：RETURNS返回变量子句指明该函数的返回局部变量，该变量的数据类型是Table，而且在该子句中还需要对返回的表进行表结构的定义。在BEGINEND之间的语句是函数体，函数体中必须包括一条不带参数的RETURN语句用于返回表。,第十章存储过程、触发器、自定义函数,SQLServer2008,用户自定义函数,创建自定义函数创建多语句表值函数：【例10-8】定义多语句表值函数EProduct_Table_1，完成上例功能。代码如下：CREATEFUNCTIONEProduct_Table_1(EP_CIDint)RETURNStbTable-定义tb表变量的结构，其中的列名可以和原数据表名不一样，但数据类型要一致。(tb_Enochar(11),tb_ENamevarchar(50),tb_EJoinDataDateTime)ASBEGININSERTINTOtbSELECTE