存储过程和触发器

第6章存储过程、触发器和数据完整性6.1存储过程6.2触发器6.3数据完整性6.1存储过程客户/服务器数据库与传统的数据库结构的一个很重要的区别是，在传统的数据库中只存放数据，所有的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起；而在客户/服务器结构的数据库中，在数据库中还可以存放程序，即存储过程。基本概念存储过程是事先编好的、存储在数据库中的程序，这些程序用来完成对数据库的指定操作。系统存储过程SQLServer本身提供了一些存储过程，用于管理SQLServer和显示有关数据库和用户的信息，我们称之为系统存储过程。用户存储过程用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。客户端应用（不使用存储过程）客户端应用（使用存储过程）.Starttransaction.INSERTdataUPDATEdataDELETEdataEndtransaction..Starttransaction.CallStoredprocedureEndtransaction.DBMSServerDBMSServerProcedure:INSERTdataUPDATEdataDELETEdata(a)(b)不使用存储过程时，所有的数据处理都在客户端完成；而使用存储过程时，可以使数据处理在服务器端完成。创建存储过程CREATEPROC[edure]procedure_name[;number][@parameterdata_type[=default],…]ASsql_statementsprocedure_name：给出存储过程名；snumber：对同名的存储过程指定一个序号；s@parameter：给出参数名；sdata_type：指出参数的数据类型；s=default：给出参数的默认值；ssql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。例：创建一个最简单的存储过程：CREATEPROCeduresp_getemp;1ASSELECT*FROM职工例：带参数的存储过程：CREATEPROCeduresp_getemp;2(@salaryint)ASSELECT*FROM职工WHERE工资@salary存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句：·CREATETABLE·CREATEVIEW·CREATEDEFAULT·CREATERULE·CREATETRIGGER·CREATEPROCEDURE执行存储过程[EXECute][@返回状态码=]存储过程名[[@参数=]{值|@变量}…]例：执行带参数的sp_getemp;2存储过程executesp_getemp;21240存储过程的返回值和状态信息无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。CREATEPROCeduresp_getemp;3(@salaryint=NULL)ASIF@salaryISNULLBEGINPRINT'必须提供一个数值作参数！'RETURN13ENDIFNOTEXISTS(SELECT*FROM职工WHERE工资@salary)BEGINPRINT'没有满足条件的记录！'RETURN-103ENDSELECT*FROM职工WHERE工资@salaryRETURN0例：带参数和返回状态值的存储过程。例：执行以上存储过程。DECLARE@statusintEXECUTE@status=sp_getemp;31200print@status存储过程的修改和删除修改存储过程的语句是（一般格式）：ALTERPROC[edure]procedure_name[;number][@parameterdata_type[=default],…]ASsql_statement删除存储过程的语句是：DROPPROC[edure]procedure_name注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。要用好存储过程存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。6.2触发器触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。触发器的用途1.触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。2.触发器可以禁止或撤消违反参照完整性的修改。3.触发器可以强制比用CHECK约束定义更加复杂的限制。触发器是依附于表的数据库对象一个触发器和三部分内容有关：定义触发器的表激活触发器的数据操作语句触发器要采取的动作建立触发器的语句CREATETRIGGERtrigger_nameONtableFOR{INSERT|UPDATE|DELETE}AS[IFUPDATE(column)[{AND|OR}UPDATE(column)…]]sql_statementstrigger_name：给出了触发器的名称；stable：说明了定义触发器的表或视图；sFOR{INSERT|UPDATE|DELETE}：说明了激活触发器的数据操作语句；sIFUPDATE(column)：对应于UPDATE类触发器，说明如果更新某（些）列则做如何处理；ssql_statement：触发器所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。例：建立一个简单的触发器。CREATETRIGGERwh_triggerON仓库FORINSERTASPRINT'插入了一个仓库元组'插入视图和删除视图为触发器运行而自动派生的两个视图：inserted——存放刚插入的新记录deleted——存放刚删除的旧记录删除类触发器删除类触发器就是当表上发生删除操作时所触发执行的程序。例：定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL：CREATETRIGGERw_del_triggerON仓库FORDELETEASUPDATE职工SET仓库号=NULLWHERE仓库号=(SELECT仓库号FROMdeleted)插入类触发器插入类触发器就是当表上发生插入操作时所触发执行的程序。例：对职工表的插入操作定义一个触发器，使得当插入职工记录时，检查相应的仓库元组是否存在，如果不存在则撤消所做的插入操作。CREATETRIGGERe_ins_triggerON职工FORINSERTASIF(SELECTCOUNT(*)FROM仓库w,insertediWHEREw.仓库号=i.仓库号)=0BEGINRAISERROR('非法仓库号！',1,1)ROLLBACKTRANSACTIONEND更新类触发器更新类触发器就是当表上发生更新操作时所触发执行的程序。例：对职工表的更新操作定义一个触发器，使得当职工变换所属仓库时，检查相应的仓库元组是否存在，如果不存在则撤消所做的更新操作，如果新的仓库号是WH2则将工资提高10%。CREATETRIGGERe_upd_triggerON职工FORUPDATEASDECLARE@wh_noCHAR(4)IFUPDATE(仓库号)BEGINIF(SELECTCOUNT(*)FROM仓库w,insertediWHEREw.仓库号=i.仓库号)=0BEGINRAISERROR('非法仓库号！',16,1)ROLLBACKTRANSACTIONENDELSEBEGINSELECT@wh_no=仓库号FROMinsertedIF@wh_no='WH2'UPDATE职工SET工资=工资*1.10WHERE职工号=(SELECT职工号FROMinserted)ENDEND6.3数据完整性在第3章已经介绍了在关系数据模型上数据完整性的概念和规则；在第5章介绍了CREATETABLE语句中可以实现的一些完整性约束。这里介绍与数据完整性有关的其他一些内容。.6.3.1规则在CREATETABLE语句中可以使用CHECK子句实现一些用户定义完整性或域完整性约束。另外还可以通过“规则”（RULE）来实现用户定义完整性或域完整性。CHECK约束固定在一个表的一个列上，它只在指定的列上起作用。如果在不同的列上有相同的约束条件，则可以使用规则，一个规则可以绑定在多个列上。规则是一种独立的数据库对象，它可以绑定到一个列上来约束该列的取值范围等。建立规则的命令是：CREATERULEruleAScondition_expressionrule：给出新建规则的名称；condition_expression：定义规则的条件，可以是任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的元素。注意：规则不能引用列或其它数据库对象，规则可以包含不引用数据库对象的内置函数；condition_expression需要包含一个变量，变量的前面有一个@前缀；该表达式引用通过UPDATE或INSERT语句输入或传递的字段值。例如，规定某类数值对象的取值范围是1000～3000，则可以定义规则：CREATERULErange_ruleAS@range=1000AND@range=3000规则的绑定规则是独立的数据库对象，要通过系统存储过程sp_bindrule把规则绑定到数据列上，该系统存储过程的格式是：sp_bindrulerulename,objname[,futureonly]rulename是用CREATERULE命令建立的规则名；objname指出要绑定的表和列或用户定义的数据类型；futureonly，当绑定规则到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的规则。例如，将规则range_rule绑定到职工表的工资列上sp_bindrulerange_rule,‘职工.工资’使用CREATERULE命令创建的规则对象，可以绑定到多个数据列上，即一个规则可以反复使用。绑定的消除和规则的删除绑定到数据列上的规则可以去除，相应的系统存储过程是sp_unbindrule。例如，取消绑定在职工表工资列上的规则可以使用如下语句：sp_unbindrule'职工.工资'规则可以删除，删除规则的命令是DROPRULE，但是删除规则之前，必须首先解除所有的绑定。6.3.2默认值在CREATETABLE命令中可以使用DEFAULT约束为数据列定义默认值。这里介绍另外一种方法：使用CREATEDEFAULT命令创建默认值对象。CREATEDEFAULT命令的格式CREATEDEFAULTdefaultASconstant_expressiondefault：是建立的默认值对象名；constant_expression：定义默认值的常量表达式。例如，定义一个值为“北京”的默认值对象val_bjCREATEDEFAULTval_bjAS'北京'绑定默认值默认值是独立的数据库对象，它要作用于某个数据对象，则也和绑定规则一样，需要用类似的系统存储过程把默认值绑定到列，绑定默认值的系统存储过程是sp_bindefault，具体格式是：sp_bindefaultdefname,objname[,futureonly]defname：是用CREATEDEFAUL