第11章存储过程触发器游标

SQLServer实用教程Page12020年1月18日星期六存储过程、触发器、游标、事务、并发控制存储过程触发器游标事务并发控制SQLServer实用教程Page22020年1月18日星期六存储过程、触发器、游标、事务教学要求：通过本章学习，读者应掌握以下内容：存储过程和触发器的作用存储过程的创建、修改和删除存储过程的灵活运用触发器的创建、修改和删除SQLServer实用教程Page32020年1月18日星期六存储过程的概念存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给SQLServer数据库服务器完成，以实现某个任务。存储过程的优点（1）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。（2）防止数据库中表的细节暴露给用户。（3）提供安全机制。（4）改进性能。（5）减少网络流量。SQLServer实用教程Page42020年1月18日星期六存储过程的类型（1）系统存储过程。Sp_help（2）本地存储过程。学习对象（3）临时存储过程。不永久保存（4）远程存储过程。（5）扩展存储过程。C语言等SQLServer实用教程Page52020年1月18日星期六建立和执行存储过程简单存储过程类似于将一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。CREATEPROCEDURE存储过程名[WITHENCRYPTION]--对存储过程进行加密[WITHRECOMPILE]--对存储过程重新编译ASSQL语句SQLServer实用教程Page62020年1月18日星期六【例】从S_C数据库的三个表中查询，返回学生学号、姓名、课程名、成绩。该存储过程实际上只返回一个查询信息。CREATEPROCEDUREstu_cjASSELECTstudent.sno,sname,cname,gradeFROMstudentINNERJOINscONstudent.sno=sc.snoINNERJOINcourseONsc.cno=course.cnoexecstu_cjSQLServer实用教程Page72020年1月18日星期六execute除了可以执行存储过程，还可以执行sql语句。execute(‘select*fromstudent’)DECLARE@tab_namevarchar(20)SET@tab_name='student'EXECUTE('SELECT*FROM'+@tab_name)SQLServer实用教程Page82020年1月18日星期六带输入参数的存储过程一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。SQLServer实用教程Page92020年1月18日星期六【例10.3】从S_C数据库的三个表中查询某人指定课程的成绩。CREATEPROCEDUREstu_cj1@namechar(10),@cnamechar(16)ASSELECTstudent.sno,sname,cname,gradeFROMstudentINNERJOINscONstudent.sno=sc.snoINNERJOINcourseONsc.cno=course.cnoWHEREstudent.sname=@nameANDcourse.cname=@cnameSQLServer实用教程Page102020年1月18日星期六2．调用存储过程在执行存储过程的语句中，有两种方式传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。按参数位置传递参数值:execstu_cj1'王勇','数据库管理系统'参数名传递参数值:execstu_cj1@cname='数据库管理系统',@name='王勇'SQLServer实用教程Page112020年1月18日星期六使用参数名传递参数值，当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值.按参数位置传递参数值，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时使用关键字“DEFAULT”作为参数值的占位。SQLServer实用教程Page122020年1月18日星期六如果不确定是否已经存在stu_cj1,则可以在创建之前执行以下代码：IFEXISTS(SELECTnameFROMSYSOBJECTSWHEREname='stu_cj1'ANDtype='P')DROPPROCEDUREstu_cj1SQLServer实用教程Page132020年1月18日星期六【例】从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设置的默认值。CREATEPROCEDUREstu_cj2@namechar(10)='王%'ASSELECTstudent.sno,sname,cname,gradeFROMstudentINNERJOINscONstudent.sno=sc.snoINNERJOINcourseONsc.cno=course.cnoWHEREsnameLIKE@nameSQLServer实用教程Page142020年1月18日星期六上面存储过程有多种执行形式，下面列了一部分：EXECUTEstu_cj2/*参数使用默认值*/EXECUTEstu_cj2defaultEXECUTEstu_cj2'刘%‘SQLServer实用教程Page152020年1月18日星期六带输出参数的存储过程如果我们需要从存储过程中返回一个或多个值，可使用OUTPUT关键字定义输出参数来实现。CREATEPROCEDUREstu_sum@namechar(10),@totalintOUTPUTASSELECT@total=SUM(grade)FROMstudent,scWHEREsname=@nameANDstudent.sno=sc.snoGROUPBYstudent.snoSQLServer实用教程Page162020年1月18日星期六DECLARE@totalintEXECUTEstu_sum'王勇',@totalOUTPUTSELECT'王勇',@total注意：要定义变量来接收output类型变量传出来的参数值，存储过程参数名和调用时的变量名不一定相同，不过数据类型和参数的位置必须匹配。SQLServer实用教程Page172020年1月18日星期六存储过程的管理与维护查看存储过程的定义信息在SQLServerManagementStudio的“对象资源管理器”中，可以在要查看信息的存储过程上单击鼠标右键，在快捷菜单中选择“属性”，弹出“存储过程属性”窗口.EXECUTEsp_helptextstu_sumEXECUTEsp_helpstu_sumEXECUTEsp_dependsstu_sum运行后得到存储过程的定义、参数和依赖信息。。SQLServer实用教程Page182020年1月18日星期六存储过程的重编译存储过程所采用的执行计划，只在编译时优化生成，以后便驻留在高速缓存中。当用户对数据库新增了索引或其他影响数据库逻辑结构的更改后，已编译的存储过程执行计划可能会失去效率。通过对存储过程进行重新编译，可以重新优化存储过程的执行计划。SQLServer为用户提供了3种重新编译的方法。SQLServer实用教程Page192020年1月18日星期六1．在创建存储过程时设定在创建存储过程时，使用WITHRECOMPILE子句时SQLServer不将该存储过程的查询计划保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。2．在执行存储过程时设定通过在执行存储过程时设定重新编译，可以让SQLServer在执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被保存在缓存中。这样用户就可以根据需要进行重新编译。EXECUTEstu_cj1WITHRECOMPILE3．通过系统存储过程设定重编译通过系统存储过程sp_recompile设定重新编译标记，使存储过程在下次运行时重新编译。其语法格式如下：EXECUTEsp_recompile数据库对象SQLServer实用教程Page202020年1月18日星期六修改和删除存储过程1．修改存储过程存储过程的修改是由ALTER语句来完成的，基本语法如下：ALTERPROCEDURE存储过程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL语句SQLServer实用教程Page212020年1月18日星期六2．删除存储过程在SQLServerManagementStudio的“对象资源管理器”中可以进行删除。命令方式DROPPROCEDUREstu_sumSQLServer实用教程Page222020年1月18日星期六触发器的概念触发器是一类特殊的存储过程，它是在执行某些特定的T-SQL语句时可以自动执行的一种存储过程。触发器的功能SQLServer2005提供了两种方法来保证数据的有效性和完整性：约束和触发器。触发器的常用功能如下。（1）完成更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束。（2）检查SQL所做的操作是否允许：触发器可以检查SQL所做的操作是否被允许。SQLServer实用教程Page232020年1月18日星期六（3）修改其他数据表里的数据：当一个SQL语句对数据表进行操作的时候，触发器可以根据SQL语句的操作情况来对另一个数据表进行操作。（4）调用更多的存储过程：约束是不能调用存储过程的，但触发器本身就是一种存储过程，而存储过程是可以嵌套调用的，所以触发器也可能调用一个或多个存储过程。（5）返回自定义的错误信息：约束只能通过标准的系统错误信息来传递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。（6）防止数据表结构更改或数据表被删除：为了保护已经建立好的数据表，触发器可以在接收到以DROP或ALTER开头的语句后，不对数据表的结构做任何操作。SQLServer实用教程Page242020年1月18日星期六触发器的类型1.DML触发器DML触发器是当数据库服务器中发生数据操纵语言DML事件时执行的特殊存储过程，如INSERT、UPDATE，DELETE。2.DDL触发器DDL触发器是当数据库服务器中发生数据定义语言DDL事件时执行的特殊存储过程，如CREATE、ALTER，DROP等。SQLServer实用教程Page252020年1月18日星期六DML触发器的类型触发事件：INSERT、UPDATE、DELETE。触发类型：for/after、insteadofFOR与AFTER同义，指定触发器只有在触发器SQL语句中指定的所有操作都已成功后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。INSTEADOF。指定执行触发器而不执行造成触发的SQL语句，从而替代造成触发的语句。在表或视图上，每个INSERT、UPDATE或DELETE语句只能定义一个INSTEADOF触发器，即替代触发。SQLServer实用教程Page262020年1月18日星期六创建和应用DML触发器1．AFTER触发器这类触发器是在记录已经改变之后，才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用ROLLBACKTRANSACTION语句来回滚本次的操作。SQLServer实用教程Page272020年1月18日星期六1．DELETE触发器【例】当从student表中删除一个学生的记录时，相应的应从sc表中删除该学生对应的所有记录。C