7.5存储过程触发器2012

11第七章采用SQLServer2000管理数据库7.1SQLServer数据库结构特点7.2T-SQL编程基础7.3系统函数7.4约束、默认和规则7.5存储过程和触发器7.6事务7.7锁7.8查询优化227.5存储过程和触发器7.5.1存储过程7.5.2创建自己的存储过程7.5.3触发器337.5存储过程和触发器存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。447.5存储过程和触发器存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。存储过程就是由过程化语言和SQL语句组成的函数。是存储在数据库中的一段T－SQL程序，可以由客户端直接调用，也可以从另一过程或从触发器调用。557.5存储过程和触发器存储过程有以下一些优势：存储过程是一个可重用的代码模块，可以高效率地完成指定的操作,提高执行效率可以从SQLServer数据库返回有用的信息。可以在本地或远程机器中执行存储过程减少在服务器和客户之间的信息交换在MicrosoftSQLServer2008系统中，可以使用Transact-SQL语言编写存储过程，也可以使用CLR方式编写存储过程。使用CLR编写存储过程是MicrosoftSQLServer2008系统与.NET框架紧密集成的一种表现形式。667.5.1存储过程的结构存储过程分为：系统存储过程用户定义的存储过程777.5.1存储过程的结构系统存储过程分为：管理、辅助、配置和监控管理存储过程，如：sp_create_removable（用此命令建数据库，用户可自行拆卸）sp_certify_removablesp_attachsp_dboption887.5.1存储过程的结构使用存储过程收集信息，如：sp_helpsp_helpdb使用存储过程进行配置和协调，如：sp_addtypesp_config使用存储过程监控系统，如：sp_monitor:997.5存储过程和触发器7.5.1存储过程7.5.2创建自己的存储过程7.5.3触发器10107.5.2创建自己的存储过程组成：头部：定义了存储过程的名称、输入和输出参数以及其他的过程选项主体：包含了一条或者多条待执行的TSQL语句11117.5.2创建自己的存储过程1.创建存储过程语法CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]12127.5.2创建自己的存储过程procedure_name:新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时过程，可以在procedure_name前面加一个编号符(#procedure_name)要创建全局临时过程，可以在procedure_name前面加两个编号符(##procedure_name)完整的名称（包括#或##）不能超过128个字符13137.5.2创建自己的存储过程[;number]:是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。14147.5.2创建自己的存储过程@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。存储过程最多可以有2.100个参数。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。15157.5.2创建自己的存储过程data_type：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。16167.5.2创建自己的存储过程VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。Default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。17177.5.2创建自己的存储过程OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。Text、ntext和image参数可用作OUTPUT参数。n：表示最多可以指定2.100个参数的占位符。18187.5.2创建自己的存储过程RECOMPILE:SQLServer不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION:对系统表中的文本加密；使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。19197.5.2创建自己的存储过程FORREPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。使用FORREPLICATION选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。20207.5.2创建自己的存储过程存储过程的最大为128MB。用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb中创建）。在单个批处理中，CREATEPROCEDURE语句不能与其它Transact-SQL语句组合使用。21创建一个简单的存储过程2020年1月18日第21页22创建一个带参数的存储过程2020年1月18日第22页23创建带有OUTPUT类型参数的存储过程2020年1月18日第23页24247.5.2创建自己的存储过程2.存储过程调用[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,…n][WITHRECOMPILE]25257.5.2创建自己的存储过程参数传递方式：1):@parameter=value的形式，那么可以按任何顺序提供参数。还可以省略那些已提供默认值的参数。2)按照CREATEPROCEDURE语句中给出的顺序提供参数。26执行简单的存储过程2020年1月18日第26页27为存储过程直接提供参数值2020年1月18日第27页28为存储过程间接提供参数值2020年1月18日第28页29使用OUTPUT参数执行存储过程2020年1月18日第29页30307.5.2创建自己的存储过程执行存储过程在查询分析器中执行存储过程利用图形工具执行存储过程自动执行存储过程3131在查询分析器中执行存储过程3232利用图形工具执行存储过程3333自动执行存储过程如果将存储过程标记为自动执行，则每次启动Microsoft®SQLServer™2000时都会执行这些存储过程。如果有需要定期执行的操作，或者有作为后台进程运行的存储过程，并希望该存储过程在所有时间都处于运行状态，此种方法非常有用。34347.5.2创建自己的存储过程3.存储过程应用举例例.根据参数，显示指定类型的图书。CREATEPROCEDUREpublisher_proc2(@booktypechar(12))ASSELECTPUB_NAME,TITLEFROMtitles,publishersWHEREtitles.pub_id=publishers.pub_idANDtypeLIKE@booktype35357.5.2创建自己的存储过程执行DECLARE@tCHAR(12)SET@t=‘business’EXECpublisher_proc2@t36367.5存储过程和触发器7.5.1存储过程7.5.2创建自己的存储过程7.5.3触发器37377.5.3触发器触发器是SQLSERVER为应用程序开发人员和数据库分析人员提供的一种保证数据完整性的方法，它是一种特殊类型的存储过程：它不允许使用参数不能被直接调用只能由系统自动激活38387.5.3触发器优点:1.无论什么原因对触发器表中数据进行修改时,即无论是操作人员录入数据,还是其他应用程序的修改都能自动激活触发器,从而对这些数据实施完整性检查.2.触发器能实施比Foreignkey约束、CHECK约束、规则等更为复杂的检查和操作.（但是外键约束效率高）397.5.3触发器MicrosoftSQLServer2008系统提供的触发器分成两种类型DML触发器当数据库中发生数据操纵语言(DataManipulationLanguage，简称为DML)事件时将调用DML触发器DDL触发器40407.5.3触发器当表中数据被修改时SQLServer自动执行触发器.建立在表一级，它与指定的数据修改事件相对应MicrosoftSQLServer2008系统提供的DML触发器分成3种类型,这也是DML触发器的基本类型:INSERT类型UPDATE类型DELETE类型。41417.5.3触发器一个触发器逻辑上包括：头部：触发器名字与触发器关联的表的名字对触发器进行初始化的更改语句（即事件）主体：包括运行时的所有TSQL语句42427.5.3触发器1建立CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]加密存储{{FOR{[INSERT][[,]DELETE][[,]UPDATE]}}[WITHAPPEND][NOTFORREPLICATION]ASsql_statement[…n]}43437.5.3触发器2.应用例:订书表(书店号,订单号,定日,数量,付款期限,图书标识)sales(stor_id,ord_num,ord_date,qty,payterms,title_id)销量表titles(title_id,…ytd_sales(当年销量),…)需求：每订一本书，当年销售量自动增加解决：建触发器每当向表sales插入新行时,触发器自动将图书的每次销量qty自动合计到该书的当年总销量ytd_sales44447.5.3触发器USEpubsGOCREATETRIGGERins_triggerONsalesFORINSERTASUPDATEtitlesSETytd_sales=ytd_sales+(SELECTSUM(qty)FROMinsertedGROUPBYinserted.title_idHAVINGtitles.title_id=inserted.title_id)45457.5.3触发器触发器执行时，根据触发事件的操作类型的不同，将创建一个或者两个临时表：inserteddeleted46467.5.3触发器inserted表当一个记录插入表中时，相应的插入触发器创建一个inserted表；其结构映射了与该触发器相连接的表的列结构；deleted表当表删除一个记录时，相应的插入触发器创建一个deleted表；其结构映射了与该触发器相连接的表的列结构；47477.5.3触发器Insert语句当一个记录插入表中时，相应的插入触