第6章 存储过程、触发器

.,第6章存储过程、触发器,6.1存储过程,6.2触发器,.,6.1.1存储过程的类型,(1)系统存储过程系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，例如常用的显示系统对象信息的sp_help存储过程，它们为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。常用的系统存储过程，请见附录。(2)本地存储过程本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3)临时存储过程临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。如果本地存储过程的名称前有两个“#”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。(4)远程存储过程远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程在SQLServer2000环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer2000系统中，并且按照使用存储过程的方法执行。,.,6.1.2用户存储过程的创建与执行,用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或SQLServer的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。1创建存储过程语法格式:CREATEPROCEDUREprocedure_name;number/*定义过程名*/parameterdata_type/*定义参数的类型*/VARYING=defaultOUTPUT/*定义参数的属性*/,.n1WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION/*定义存储过程的处理方式*/FORREPLICATIONASsql_statement.n2/*执行的操作*/说明：参数procedure_name用于指定存储过程名，必须符合标识符规则，且对于数据库及其所有者必须唯一；创建局部临时过程，可以在procedure_name前面加一个“#”；创建全局临时过程，可以在procedure_name前加“#”。参数number为可选的整数，用于区分同名的存储过程，以便用一条DROPPROCEDURE语句删除一组存储过程；,.,6.1.2用户存储过程的创建与执行,FORREPLICATION用于说明不能在订阅服务器上执行为复制创建的存储过程，该选项不能和WITHRECOMPILE一起使用。参数sql_statement代表过程体包含的T-SQL语句，参数n2说明一个存储过程可以包含多条T-SQL语句。对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb中创建）。(2)成功执行CREATEPROCEDURE语句后，过程名称存储在sysobjects系统表中，而CREATEPROCEDURE语句的文本存储在syscomments中。(3)自动执行存储过程QLServer启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在master数据库中创建，并在sysadmin固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。(4)sql_statement的限制除了SETSHOWPLAN_TEXT和SETSHOWPLAN_ALL外，其它SET语句均可在存储过程内使用。,.,6.1.2用户存储过程的创建与执行,如下语句必须使用对象所有者名对数据库对象进行限定：CREATETABLE、ALTERTABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS及DBCC语句。权限。CREATEPROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员。sysadmin固定服务器角色成员和db_owner固定数据库角色成员可以将CREATEPROCEDURE权限转让给其他用户。注意：存储过程的定义不能跨越批处理。2存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,.,6.1.2用户存储过程的创建与执行,说明：参数return_status为可选的整型变量，保存存储过程的返回状态，EXECUTE语句使用该变量前，必须对其定义。参数procedure_name和number用于调用已定义的一组存储过程中的某一个，procedure_name代表了存储过程的组名，number用于指定组中的存储过程。定义存储过程组的目的是以便用一条DROPPROCEDURE语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数procedure_name_var代表存储过程名。parameter为CREATEPROCEDURE语句中定义的参数名；value为存储过程的实参；variable为变量，用于保存OUTPUT参数返回的值。DEFAULT关键字表示不提供实参，而是使用对应的默认值。n：表示实参可有多个。关键字WITHRECOMPILE指定强制编译。存储过程的执行要注意下列几点：(1)如果存储过程名的前三个字符为sp_，SQLServer会在Master数据库中寻找该过程。如果没能找到合法的过程名，SQLServer会寻找所有者名称为dbo的过程。(2)参数可以通过value或parameter_name=value提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE关键字。,.,6.1.2用户存储过程的创建与执行,3存储过程的几种情况(1)不使用任何参数的存储过程【例6.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学分。该存储过程不使用任何参数。/*创建存储过程*/CREATEPROCEDUREstudent_infoASSELECTa.学号,姓名,课程名,成绩,t.学分FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号student_info存储过程可以通过以下方法执行：EXECUTEstudent_info或者EXECstudent_info如果该过程是批处理中的第一条语句，则可使用：student_info,.,6.1.2用户存储过程的创建与执行,(2)使用带参数的存储过程【例6.2】从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。CREATEPROCEDUREstudent_info1namechar(8),cnamechar(16)ASSELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHEREa.姓名=nameandt.课程名=cnamestudent_info1存储过程有多种执行方式，下面列出了一部分：EXECUTEstudent_info1王林,计算机基础或者EXECUTEstudent_info1name=王林,cname=计算机基础或者EXECUTEstudent_info1cname=计算机基础,name=王林或者EXECstudent_info1王林,计算机基础或者EXECau_infocname=计算机基础,name=王林,.,6.1.2用户存储过程的创建与执行,(3)使用带有通配符参数的存储过程【例6.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。CREATEPROCEDUREst_infonamevarchar(30)=刘%ASSELECTa.学号,a.姓名,c.课程名,b.成绩FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCcONc.课程号=b.课程号WHERE姓名LIKEnamest_info存储过程可以有多种执行形式，下面列出了一部分：EXECUTEst_info/*参数使用默认值*/或者EXECUTEst_info王%/*传递给name的实参为王%*/或者EXECUTEst_info王张%(4)使用带OUTPUT参数的存储过程,.,6.1.2用户存储过程的创建与执行,【例6.4】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。CREATEPROCEDUREtotalcreditnamevarchar(40),totalintOUTPUTASSELECTtotal=SUM(KC.学分)FROMXS,XS_KC,KCWHERE姓名=nameANDXS.学号=XS_KC.学号andXS_KC.课程号=KC.课程号GROUPBYXS.学号注意：OUTPUT变量必须在创建表和使用该变量时都进行定义。定义时的参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。DECLAREt_creditchar(20),totalintEXECUTEtotalcredit王林,totalOUTPUTSELECT王林,total(5)使用OUTPUT游标参数的存储过程OUTPUT游标参数用于返回存储过程的局部游标。【例6.5】在XSCJ数据库的XS表上声明并打开一个游标。CREATEPROCEDUREst_cursorst_cursorCURSORVARYINGOUTPUTASSETst_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMXSOPENst_cursor,.,6.1.2用户存储过程的创建与执行,在如下的批处理中，声明一局部游标变量，执行上述存储过程过程并将游标赋值给局部游标变量，然后通过该游标变量读取记录。DECLAREMyCursorCURSOREXECst_cursorst_cursor=MyCursorOUTPUTWHILE(FETCH_STATUS=0)BEGINFETCHNEXTFROMMyCursorENDCLOSEMyCursorDEALLOCATEMyCursor(6)使用WITHENCRYPTION选项WITHENCRYPTION子句对用户隐藏存储过程的文本。【例6.6】创建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。CREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMXS,.,6.1.2用户存储过程的创建与执行,通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。执行如下语句：EXECsp_helptextencrypt_this结果集为提示信息：对象备注已加密。(7)创建用户定义的系统存储过程【例6.7】创建一个过程，显示表名以xs开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引。USEmasterGOCREATEPROCEDUREsp_showtableTABLEvarchar(30)=kc%ASSELECTASTABLE_NAME,ASINDEX_NAME,indidASINDEX_IDFROMsysindexesinxINNERJOINsysobjectstabONtab.id=inx.idWHERELIKETABLEGOUSEXSCJEXECsp_showtablexs%GO,.,6.1.2用户存储过程的创建与执行,4利用企业管理器编辑用户存储过程如果要通过企业管理器界面定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，步骤如下：第1步在SQLServer企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图6.1所示的快捷菜单。第2步选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过程，如图6.2所示，然后选择“确定”按钮。,图6.1存储过程的快捷菜单图6.2编辑存储过程的属性窗口,.,6.1.3用户存储过程的修改,使用ALTERPROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。语法格式：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING0=defaultOUTPUT,.n1WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n2说明：各参数含义与CREATEPROCEDURE相同。如果原来的过程定义是用WITHENCRYPTION或WITHRECOMPILE创建的，那么只有在ALTERPROCEDURE中也包含这些选项时，这些选项才有效。ALTERPROCEDURE权限默认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员和过程的所有者且不可转让。,.,6.1.3用户存储过程的修改,用ALTERPROCEDURE更改后，过程的权限和启动属性保持不变。【例6.8】对存储过程student_info1进行修改。USEXSCJGOALTERPROCEDUREstudent_info1namechar(8),cnamechar(16)ASSELECTa.学号,姓名,课程名,成绩,t.学分FROMXSaINNERjoinXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHEREa.姓名=nameandt.课程名=cnameGO【例6.9】创建名为select_students的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。当该过程需更改为能检索计算机专业的学生信息时，用ALTERPROCEDURE重新定义该存储过程。,.,6.1.3用户存储过程的修改,USEXSCJGOCREATEPROCEDUREselect_students/*创建存储过程*/ASSELECT*FROMXSORDERBY学号GO修改存储过程select_studentsALTERPROCEDUREselect_studentsWITHENCRYPTIONASSELECT*FROMXSWHERE专业名=计算机ORDERBY学号GO,.,6.1.4用户存储过程的删除,当不再使用一个存储过程时，就要把它从数据库中删除。使用DROPPROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROPPROCEDUREprocedure,.n说明：procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。默认情况下，将DROPPROCEDURE权限授予过程所有者，该权限不可转让。db_owner和db_ddladmin固定数据库角色成员和sysadmin固定服务器角色成员可以通过在DROPPROCEDURE内指定所有者删除任何对象。若要查看过程名列表，可使用sp_help系统存储过程。若要显示过程定义（存储在syscomments系统表内），可使用sp_helptext。【例6.10】删除XSCJ数据库中的student_info1存储过程。USEXSCJGODROPPROCEDUREstudent_info1,.,6.2.1利用SQL命令创建触发器,SQL命令创建触发器语法格式：CREATETRIGGERtrigger_nameONtable|view/*指定操作对象*/WITHENCRYPTION/*说明是否采用加密方式*/FOR|AFTER|INSTEADOFINSERT,UPDATEWITHAPPENDNOTFORREPLICATION/*说明该触发器不用于复制*/ASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n/*两个IF子句用于说明触发器执行的条件*/sql_statement.n/*一条或若干条SQL语句*/,.,6.2.1利用SQL命令创建触发器,说明：参数trigger_name用于指定触发器名。触发器名必须符合标识符规则，并且在数据库中必须唯一，可以包含触发器所有者名。Table|view指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以包含表或视图的所有者名。使用关键字WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。bitwise_operator为用于比较运算的位运算符。updated_bitmask为整型的位屏蔽码，与实际更新或插入的列对应，例如：表t包含列C0、C1、C2、C3和C4。假定该表上有UPDATE触发器，若要检查列C0、C2、C4是否都有更新，可指定updated_bitmask的值为00010101=0 x15；若要检查是否只有列C1有更新，可指定updated_bitmask的值为000000102。comparison_operator为比较运算符；column_bitmask为列屏蔽码，用来检查是否已更新或插入了对应列。参数sql_statement为触发器的T-SQL语句，当执行DELETE、INSERT或UPDATE操作时，对应的触发器操作将生效。n表示触发器中可以包含多条T-SQL语句。2触发器中使用的特殊表执行触发器时，系统创建了两个特殊的逻辑表inserted表和deleted表，下面介绍一下这两个表的内容。inserted逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。,.,6.2.1利用SQL命令创建触发器,deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。修改一条记录等于插入一新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。触发器可检查deleted表、inserted表及被修改的表。例如，若要检索deleted、inserted表中的所有记录，可使用如下语句：SELECT*FROMdeletedSELECT*FROMinserteddeleted、inserted逻辑表的查询方法与数据库表的查询方法相同。3使用触发器的限制使用触发器有下列限制：(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。,.,6.2.1利用SQL命令创建触发器,(5)如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。(7)在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。(8)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(9)触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SETNOCOUNT语句以避免返回任何结果集。4权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。,.,6.2.1利用SQL命令创建触发器,【例6.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/*使用带有提示消息的触发器*/USEXSCJGOCREATETRIGGERreminderONXSFORINSERT,UPDATEASRAISERROR(4008,16,10)GO消息4008是sysmessages中的用户定义消息。有关创建用户定义消息的方法请参考附录的sp_addmessage存储过程。,.,6.2.1利用SQL命令创建触发器,【例6.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USEXSCJGOCREATETRIGGERcheck_trigONXS_KCFORINSERTASSELECT*FROMinsertedaWHEREa.学号NOTIN(SELECTb.学号FROMXSb)ORa.课程号NOTIN(SELECTc.课程号FROMKCc)BEGINRAISERROR(违背数据的一致性.,16,1)ROLLBACKTRANSACTIONEND,.,6.2.1利用SQL命令创建触发器,【例6.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。通过调用COLUMNS_UPDATED()函数，可快速测试对学号列和课程号列修改所做的更改。USEXSCJGOCREATETRIGGERupdate_trigONXS_KCFORupdateAS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF(COLUMNS_UPDATED()&3)0BEGINRAISERROR(违背数据的一致性.,16,1)ROLLBACKTRANSACTIONENDGO,.,6.2.1利用SQL命令创建触发器,5.INSTEADOF触发器的设计如果视图的数据来自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。例如，若在一个多表视图上定义了INSTEADOFINSERT触发器，视图各列的值可能允许为空也可能不允许为空，若视图某列的值不允许为空，则INSERT语句必须为该列提供相应的值。如果视图的列为以下几种情况之一：（1）基表中的计算列。（2）IDENTITYINSERT为OFF的基表中的标识列。（3）具有timestamp数据类型的基表列。该视图的INSERT语句必须为这些列指定值，INSTEADOF触发器在构成将值插入基表的INSERT语句时会忽略指定的值。【例6.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEADOFINSERT触发器的使用。,.,6.2.1利用SQL命令创建触发器,直接引用books表的INSERT语句不能为BookKey字段和.ComputedCol.字段提供值，例如：-正确的INSERT语句INSERTINTObooks(BookName,Color,Pages)VALUES(计算机辅助设计,红色,100)-查看INSERT语句的执行结果:SELECT*FROMbooks-不正确的INSERT语句INSERTINTObooksVALUES(2,计算机辅助设计,红色,绿色,100)但对于引用View2视图的INSERT语句视图的每一列都指定值，例如：-对于视图View2，正确的INSERT语句INSERTINTOView2(BookKey,BookName,Color,ComputedCol,Pages)VALUES(4,计算机辅助设计,红色,绿色,100)-查看INSERT语句的执行结果SELECT*FROMView2在执行视图的插入语句时，虽然将BookKey和ComputedCol字段的值传递到了InsteadTrig触发器，但触发器中的INSERT语句没有选择inserted