第11节存储过程触发器游标.pptVIP

存储过程、触发器、游标、事务、并发控制;存储过程、触发器、游标、事务; 存储过程的概念;; 建立和执行存储过程;【例】从S_C数据库的三个表中查询，返回学生学号、姓名、课程名、成绩。该存储过程实际上只返回一个查询信息。 CREATE PROCEDURE stu_cj AS SELECT student.sno,sname,cname,grade FROM student INNER JOIN sc ON student.sno=sc.sno INNER JOIN course ON sc.cno=course.cno exec stu_cj;; 带输入参数的存储过程 一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。;【例10.3】从S_C数据库的三个表中查询某人指定课程的成绩。;2．调用存储过程 在执行存储过程的语句中，有两种方式传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。 按参数位置传递参数值: exec stu_cj1 王勇,数据库管理系统 参数名传递参数值: exec stu_cj1 @cname=数据库管理系统,@name=王勇;; 如果不确定是否已经存在stu_cj1,则可以在创建之前执行以下代码： IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name=stu_cj1 AND type=P) DROP PROCEDURE stu_cj1 ;【例】从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设置的默认值。 CREATE PROCEDURE stu_cj2 @name char(10)=王% AS SELECT student.sno,sname,cname,grade FROM student INNER JOIN sc ON student.sno=sc.sno INNER JOIN course ON sc.cno=course.cno WHERE sname LIKE @name ;; 带输出参数的存储过程; DECLARE @total int EXECUTE stu_sum 王勇, @total OUTPUT SELECT 王勇,@total 注意：要定义变量来接收output类型变量传出来的参数值，存储过程参数名和调用时的变量名不一定相同，不过数据类型和参数的位置必须匹配。 ;存储过程的管理与维护; 存储过程的重编译;1．在创建存储过程时设定 在创建存储过程时，使用WITH RECOMPILE子句时SQL Server不将该存储过程的查询计划保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。 2．在执行存储过程时设定 通过在执行存储过程时设定重新编译，可以让SQL Server在执行存储过程时重新编译该存储过程，这一次执行完成后，新的执行计划又被保存在缓存中。这样用户就可以根据需要进行重新编译。 EXECUTE stu_cj1 WITH RECOMPILE 3．通过系统存储过程设定重编译 通过系统存储过程sp_recompile设定重新编译标记，使存储过程在下次运行时重新编译。 其语法格式如下： EXECUTE sp_recompile 数据库对象; 修改和删除存储过程;; 触发器的概念;（3）修改其他数据表里的数据：当一个SQL语句对数据表进行操作的时候，触发器可以根据SQL语句的操作情况来对另一个数据表进行操作。 （4）调用更多的存储过程：约束是不能调用存储过程的，但触发器本身就是一种存储过程，而存储过程是可以嵌套调用的，所以触发器也可能调用一个或多个存储过程。 （5）返回自定义的错误信息：约束只能通过标准的系统错误信息来传递错误信息，如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。 （6）防止数据表结构更改或数据表被删除：为了保护已经建立好的数据表，触发器可以在接收到以DROP或ALTER开头的语句后，不对数据表的结构做任何操作。;触发器的类型;; 创建和应用DML触发器;1．DELETE触发器 【例】当从student表中删除一个学生的记录时，相应的应从sc表中删除该学生对应的所有记录。CREATE TRIGGER delete_trig ON student AFTER DELETE AS DELETE FROM sc WHERE sno=(SELECT sno FROM DELETED);; DML触