java web项目开发案例精粹-补技术sql server2000数据库chap

8提高数据库性句和流程控制语句的集合。就本质而言，触发器也是一种过程。过程在运算时生成执行方式，所以，之后对其再运算时其执行速度变快。SQLServer2000中提供了许多可作为工具使用的系统过程。本章将学习SQLServer2000中过触发器的使用。重点讲解过触发器的创建、过程的参数使用。通过本章学习，读者可掌握过触发器的创建，掌握过触发器的的管理，并能熟练的使用它们。为Student_info表创建过程Student_info_pro，该过程中包Student_id、Name、Address l段为Student_info表创建 过程Student_info_pro1，该过程根据传为Student_info表创建一带默认值的过程Student_info_pro2，用来查询是20岁的同学信息。为Student_info表创建过程Student_info_pro3，用来返回学生的为Student_info表创建过程Student_info_pro4，该过程根据查1个应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的程度上发挥出SQL的潜能。它支持用户提供的变量，可以返回、修改值，也可以将用户8.1过程概Sybase公司80年代末期在它的SQLServer产品中首创了过程(StoredProcedures)使用过程还可以提高性能。过程开始执行后，其中包含的SQL语句在数据库服。而且过程实际上只是在第一次使用时被编译，编译后的映像和过程在，安全性高。通过对执行某一过程的权限进行限制，从而实现对相应的数据访问权限的限制，避免非用户对数据的，保证数据的安全。 。当使用的过程的数量非常多时，每个 在 8.1.2过程的类系统过程就是系统创建的过程，目的在于能够方便地从系统表中查询信息或缀，在master数据库中，为检索系统表的信息提供快捷方式。尽管这些过程前加上数据库名。而且在创建一个新的数据库时，一些系统过程会在新数据库中自动通过程，没有前缀“sp_”。种过程称为局部临时过程，只能在一个用户会话中使用；如果在本地过程前过程指服务器上的过程，也就是非本地服务器上的过程。8.2过程的创在一个过程中，可以使用任何SQL语句，但是不包括下面的语句：CREATE使用Transact-SQLCREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...nprocedure_name表示新过程的名称。过程名必须符合标识符规则，且对于数(##)。完整的名称（包括#或##）过128个字符。指定过程所有者的名称orders的应用程序使用的过程可@parameter是过程中的参数。可以一个或多个参数。用户必须在执行过程时data_type是参数的数据类型。所有数据类型均可以用作过程的参数。不过，cursorOUTPUTcursor，也必须同时指定VARYING和OUTPUT关键字。VARYING认值必须是常量或NULL。OUTPUT表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用sql_statementSQL语句。n表示此过程可以包含任意数目和类型的Transact-SQL语句。 l这段【任务分析】使用CREATEPROC语句创建此过程，其两个参数的值分别为 lUSEStudentCREATEPROCEDUREStudent_info_proSELECTstudent_id,name,Address, FROMStudent_info图8-1使用Transact-SQL语句创建过程后在企业管理器中的显在企业管理器其中用鼠标右键单击要创建过程的数据库下的“过程”选项，弹出快捷菜单，如图8-2所示。8-2弹出快在弹出的快捷菜单中单击“新建过程”命令，打开“过程属性”如果要检查用于创建过程的语法，可以单击“检查语法”按钮。若脚本中没有语法错误，将会弹出提示“语法检查成功”的框，单击“确定”按钮，保存\administrator，PROCEDURENAME=Student_info_pro。过程的执行语句为select lfromStudent_info。在企业管理器其中用鼠标右键单击Student数据库下的“过程”选项，在弹出快捷菜单，单击“新建过程”命令，打开“过程属性”框。在“文本”列的语句，如图8-3。图8-3“过程属性”查成功”的框，如图8-4所示。单击“确定”按钮，保存过程定义。图8-4“语法检查成功”的框，在该框中选中“数据库”下的“创建过程向导”选项。单击“确定”按单击“下一步”按钮，打开“正在完成创建过程向导”框，该框中用于确认过程信息。在该框中选中其中一个过程，单击“编辑”按钮，打开“编辑过程属性”框，在该框中的“名称”文本框中修改该过程的名单击“编辑SQL”按钮，弹出“编辑过程SQL”框，用户可以在已Student_info，操作可何一项或全部选择，在“编辑过程SQL”框中将PROCEDURENAME改为Student_info_pro，将操作改为查询 l和Address段的值。框，在该框中选中“数据库”下的“创建过程向导”选项，如图8-5所示。图8-5“选择向导” 图8-6“欢迎使用创建过程向导”称”下拉列表中选择Student数据库，如图8-7所示。”以及该表后的插入、删除和更新复选框，如图8-8所示。”图8-7“选择数据库” 图8-8“创建过程向导”框中选中insert_Student_info_1过程，如图8-9所示。单击“编辑”按钮，打开“编辑过程属性”框，在该框中的“名 图8-9“正在完成创建过程向导” 图8-10“编辑过程属性”如图8-11：8-完成对过程的创建。如图8-11。8.3执行过过程可以由应用程序通过一个调用来执行，而且允许用户变量和有条件执可用EETE句来执这个过程。EXECUTE语句的语法格式如下：[[[EXEC[UTE]{[[@return_status={procedure_name[;number]|}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][ PILEEXECUTE语句中各参数的含义如下：@return_status是一个可选的整型变量，保存 于EXECUTE语句前，必须在批处理、 加上符号(@)。在以“@parameter_namevalue”格式使用时，参数名称和常量不一定按照CREATEPROCEDURE语句中定义的顺序出现。value是过程中参数的值。如果参数名称没有指定，参数值必须以CREATEPROCEDURE语句中定义的顺序给出。OUTPUT指定过程必须返回一个参数。该过程的匹配参数也必须由关键字OUTPUT创建。 PILE强制编译新的计划。该选项不能用于扩展过程。建议尽【任务8-4】通过执行名为Student_info_pro的过程，查看Student_info表中【任务分析】使用EXECUTE语句来执行这个过程，其参数的值USEUSEStudentEXECUTE图8-12执行过8.4过程的参从过程中返回。输入参数必须以@提示符开始，而且这些参数必须是Transcat-SQL的合法数据类型。输出参数也必须以@提示符开始。另外，OUTPUT关键字必须紧跟着输出【任务8-5】前面创建的过程是显示表中的所有记录，下面创建另一个过【任务分析】使用CREATEPROC语句创建此过程，其各参数的值分别为：SELECTStudent_id,Name,Address,lFROMStudent_infoWHERESex=@Sex_pro。USEUSEStudentCREATEPROCEDUREStudent_info_pro1@Sex_prochar(2)SELECTStudent_id,Name,Address, FROMStudent_infoWHERESexEXECUTE'男8-13使用参数传有默认参数的过程，并使用一个默认值来执行过程。【任务8-6】创建一个带有默认值参数的过程Student_info_pro2，用来查询Student_info表中字段为20的记录。【任务分析】使用CREATEPROC语句创建此过程，其各参数的值分别为=CREATECREATEPROCEDUREStudent_info_pro2(@ageInt=20)WHEREAge=@ageexec8-量。下面将创建带OUTPUT的返回参数的过程。【任务分析】任务8-7涉及到CREATEPRODE语句的应用，过程（procedure_name）为Student_info_pro2；过程参数@id用来传入学生编号；过程参数@addressOUTPUT类型，用以返回学生地址信息；sql语句为根据输入的参数查询学生USEUSEStudentCREATEPROCEDUREStudent_info_pro3@idInt,@addressVARCHAR(50)OUTPUTSELECT@address=addressFROMStudent_infoWHEREDECLAREDECLARE@address1EXECUTE1002,@address=@address1OUTPUTPRINT@address18-15使用OUTPUT的返回参RETURN0~-14OUTPUT参数面将创建带RETURN的过程。【任务8-8】创建过程Student_info_pro4，该过程为带有RETURN语句和参且这一字段值所对应记录的Address字段值为“唐山”，该过程的返回值为error。Student_info_pro4，过程参数（@parameter）为@Student_id，用来传入学生编号信息；RETURN语句根据查询情况返回执行状态。USEUSEStudentCREATEPROCEDUREStudent_info_pro4@Student_idint(4)(SELECTAddress(SELECTAddressWHEREStudent_id@Student_id)=’唐山RETURN@@ERROR8-16RETURN语8.5过程的查看、修改和删在数据库建立一个过程后，可以查看该过程的定义和相关性等。如果对已存在的过程不满意，可以对该过程进行修改。而对于不再需要的过程，可以将8.5.1过程的查创建过程后，可以用企业管理器或有关的系统过程来查看该过程的信在企业管理器中，选择相应数据库下的“过程”选项，在详细信息列表框中右键单击要查看的过程，在弹出的快捷菜单中单击“属性”命令，打开“过程属性”框，在该框中列出了过程的定义信息。这里可以通过单击“权限”按在企业管理器中，选择相应数据库下的“过程”选项，在详细信息列表框Student_infoStudent_info_pro过程，在弹出的快捷菜单中单击“属性”命令，打开“过程属性”框，在该框中列出了过程的定义信息，如图8-17所示。在企业管理器中，用鼠标右键单击Student数据库下Student_info过程，在弹出的快捷菜单中依次选择“所有任务”|“显示相关性”选项，打开“相关性”框，在该框可以查看依附于该过程的对象和该过程依附的对象，如图8-18所示。图8-17“过程属性” 图8-18“相关性”[EXECUTE]sp_help[EXECUTE]sp_help【任务分析】要查看的过程维Student_info_pro，使用sp_helptext查看其定义，使用sp_help查看其参数，使用sp_depends查看其相关性，这三个系统过程的参数都是USEUSEsp_helptextStudent_info_prosp_helpStudent_info_pro图8-19查看过程的定义、参数和相关8.5.2过程的修在创建过程后，用户可能需要对过程进行修改。修改过程通常指编辑它的参数和SQL语句。在企业管理器中，选择相应数据库下的“过程”选项，在详细信息列表框中右键单击要修改的过程，然后选择“属性”选项，打开“过程属性”框。在“文本”编辑框中编辑过程的参数和SQL语句但是不能修改CREATEPROCEDURE如果要设置过程的权限，可通过单击“权限”按钮，打开“对象属性”对话框，如图8-20所示。在此可指定过程的权限。图8-20“对象属性”l l字段去掉。在企业管理器中，选择Student数据库下的“过程”选项，在详细信息列表框中右键单击Student_info_pro过程，然后选择“属性”选项，打开“过程属性”框。在“文本”编辑框中的SELECT后的 l字段去掉，如图8-21所示。（与图8-17进行对比）。单击“确定”按钮，Student_info_pro过程修改成功。图8-21修改过除了使用企业管理器外，还可以使用ALTERPROCEDURE语句修改过程，但该语句不会更改权限，也不影响相关的过程。其语法格式与创建过程的语法格式相ALTERALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE| PILE,}][FORREPLICATION]sql_statement[...nVARYING为(procedure_name)Student_info_pro，过程要执行的sql语句为查询Student_info表中的Student_id，Name和Address三个字段。USEUSEStudentALTERPROCEDUREStudent_info_proFROMStudent_info8.5.3过程的删 DROPPROCEDURE{procedure}[,...nDROPPROCEDURE{procedure}[,...nDROPPROCEDURE语句中参数的procedure是要删除的过程或过程组的名称。过程名称必须符合标识符规触发器的CHECK约束更为复杂的触发器可以通过数据库中的相关表进行级联修改。例如，可以在pubs表的id列上作为唯一键，在title、sales及emp表中对各匹配行进行定位。CHECKDROPLOADDISKDISKLOAD触发器的CREATETRIGGER语句来完成。触发器定义语句的INSERT、DELETE、UPDATE语Student，数据表为Student_infoStudent_info_tri，执行语句为输出字符串“操作已执行”。Student_info表，在弹出的快捷菜单中选择“所有任务”|“管理触发器”命令，TRIGGERNAMEStudent_info_triAS后输入“PRINT'数据插入'”，如图8-23所示。图8-23“触发器属性”CREATECREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION][{IFUPDATE(column[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask{comparison_operator}column_bitmask[...n}sql_statement[...n}}AFTERSQL语句中指定的所有操作都已成功执行后才激UPDATE指明是UPDATE触发器，每UPDATE语句修改OF子句指定的列值DBMS都将激发触发器。WITHAPPEND指定应该添加现有类型的其它触发器。WITHAPPEND不能与NOTFORREPLICATION表示当进程更改触发器所涉及的表时，不应执行该sql_statement是触发器的条件和操作。触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。于DELETE操作。可以指定多列。【任务分析】任务8-14涉及到CREATETRIGGER的应用，其参数reae=efriae=osql_statement=PRINT‘操作已执行’。。USEUSEStudentCREATETRIGGERStudent_info_triONStudent_infoAFTERINSERT,UPDATE,DELETE只能在当前的数据库中创建，不过触发器可以当前数据库的外部对象。而CREATETRIGGER语句中，可以为多种用户操作(INSERT和触发器的inserteddeleted每个触发器有两个特殊的插入表（inserted表）和删除表（deleted表，这两个表是逻辑表，由系统管理，在内存中，因此不允许用户直接对其进行修改，当触发器工作完inserted表和deleted表只能被创建它们的触发器即它们的范围仅限于该触发器，6-1说明了在使用何种触发器时，触发6-1触发器创建的INSERT是否DELETE触发否是是是inserted表，该表映射了与该触发器相连接的表的列的结构。例如当用户在A表中插入一行时，A表的触发器使用A表的insertedAinserted表中也包含该行。删除的每一行都包含在删除触发器内的deleted表中。inserted表和deleted表，这两个表和它们相连接的表有相同的列结构。inserteddeleted表分别包含相连接表中数据的前后映射。例如，如果Ainserteddeleted表中所改变的每一行数据都在inserted表中行的数据值是执行更新语句之后的A表中行的数据值。Inserted表中的新行，确定是否应该执行以及如何执行该插入【任务8-15】创建触发器Student_info_ins，该触发器能够保证在学生信息表中添加新记USEUSEStudentCREATETRIGGERStudent_info_insONstudent_infoAFTERINSERTIFEXISTS(SELECT*FROMINSERTEDWHEREClass_idIN(SELECTClass_idROLLBACKTRANSACTIONINSERT ,20,'女运行后的结果如图8-24所示。该图中的字符串“数据插入”是进行INSERT操作时触发了Student_info_tri触发器产生的字符串数据已入”是进行INSERT操作时触发了student_info_ins触发器产生的字符串。8-24使用INSERT语句添加记据并且删除一条旧的数据。UPDATEDeleted表中，将一个新行插入InsertedDeletedInserted表和被修改的表来【任务8-16】创建触Student_info_upd，只有当对Student_info表中的数据执行更【任务分析】使用CREATETRIGGER语句创建此过程，其参数信息如下：USEUSEStudentCREATETRIGGERStudent_info_updONStudent_infoAFTERUPDATEUpdateStudent_infoset ’where8-25创建和使用IPDATE触发到Deleted表中。并检查Deleted表中的行，以确定是否需要执行触发器操作或应如何执行触据变被在Deleted表中。【任务8-17】创建触发器Student_info_del，当学生信息表中删除某一条记录时，留言【任务分析】任务817涉及到CREATETRIGGER语句的应用，=Student_info_del，table=Student_info，触发事件为DELETE，执