数据库原理及应用存储过程和触发器.pptx
数据库系统概论An Introduction to Database System第9章存储过程和触发器第1页/共28页教学目标：掌握存储过程和触发器的基本概念，学会编写简单的存储过程和触发器，对存储过程和触发器的实际应用有较好的理解。第2页/共28页第9章 存储过程和触发器9.1存储过程9.2触发器第3页/共28页9.1存储过程存储过程的基本知识创建用户存储过程存储过程的参数第4页/共28页存储过程的基本知识存储过程（Stored Procedure）是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。Sp（x,y）客户：sp（1，2）第5页/共28页存储过程的基本知识 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括：允许标准组件式编程，增强重用性和共享性能够实现较快的执行速度能够减少网络流量可被作为一种安全机制来充分利用第6页/共28页存储过程的基本知识在SQL Server 2005中存储过程分为5类：系统：系统提供的存储过程，sp_*，例如：sp_rename扩展：SQL Server环境之外的动态链接库DLL，xp_远程：远程服务器上的存储过程用户：创建在用户数据库中的存储过程临时：属于用户存储过程，#开头（局部：一个用户会话）,#（全局：所有用户会话）第7页/共28页创建用户存储过程格式：CREATE PROC 过程名形参名 类型变参名 类型 OUTPUTAS SQL语句第8页/共28页创建用户存储过程例：创建一个多表查询的存储过程。USE LibraryGOCREATE PROCEDURE borrowed_book1ASSELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDateFROM reader r INNER JOIN borrow bON r.RID=b.RID INNER JOIN book kON b.BID=k.BIDWHERE Rname=程鹏执行存储过程：borrowed_book1 或EXEC borrowed_book1第9页/共28页存储过程的参数例：输入参数为某人的名字。USE LibraryGOCREATE PROCEDURE borrowed_book2name varchar(10)-形式参数AsSELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDateFROM reader r INNER JOIN borrow bON r.RID=b.RID INNER JOIN book kON b.BID=k.BIDWHERE Rname=nameGO直接传值：EXEC borrowed_book2 程鹏 -实参表变量传值：DECLARE temp1 char(20)SET temp1=杨树华EXEC borrowed_book2 temp1 -实参表第10页/共28页存储过程的参数例：使用默认参数USE LibraryGOCREATE PROCEDURE borrowed_book3name varchar(10)=NULL -默认参数ASIF name IS NULL SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k.BIDELSE SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k.BID WHERE Rname=nameGO执行存储过程：EXEC borrowed_book3第11页/共28页存储过程的参数例：利用输出参数计算阶乘。USE LibraryIF EXISTS(SELECT name FROM sysobjects WHERE name=factorial AND type=P)DROP PROCEDURE factorialGOCREATE PROCEDURE factorial in float,-输入形式参数 out float OUTPUT -输出形式参数ASDECLARE i intDECLARE s floatSET i=1SET s=1WHILE i0 PRINT 没有过期！ELSE PRINT 过期+convert(char(6),days)+天GO应用：USE LibraryUPDATE borrow SET ReturnDate=2007-12-12WHERE RID=2000186010 and BID=TP85-08GO第20页/共28页创建DML触发器例：对Library库中Reader表的 DELETE操作定义触发器。USE LibraryGOIF EXISTS(SELECT name FROM sysobjects WHERE name=reader_d AND type=TR)DROP TRIGGER reader_dGOCREATE TRIGGER reader_dON ReaderFOR DELETEASDECLARE data_yj intSELECT data_yj=LendnumFROM deletedIF data_yj0 BEGIN PRINT 该读者不能删除！还有+convert(char(2),data_yj)+本书没还。ROLLBACK ENDELSE PRINT 该读者已被删除！GO应用：USE LibraryGODELETE Reader WHERE RID=2005216119 第21页/共28页创建DDL触发器语法形式：CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.nAS sql_statement;.n|EXTERNAL NAME;第22页/共28页创建DDL触发器例：使用DDL触发器来防止数据库中的任一表被修改或删除。CREATE TRIGGER safetyON DATABASE FOR DROP_TABLE,ALTER_TABLEASPRINT You must disable Trigger safety to drop or alter tables!ROLLBACK 第23页/共28页创建DDL触发器例：使用 DDL 触发器来防止在数据库中创建表。CREATE TRIGGER safetyON DATABASEFOR CREATE_TABLEASPRINT CREATE TABLE Issued.SELECTEVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max)RAISERROR(New tables cannot be created in this database.,16,1)ROLLBACK 第24页/共28页修改触发器ALTER TRIGGER trigger_name第25页/共28页删除触发器DROP TRIGGER trigger_name第26页/共28页查看触发器sp_helptext trigger_namesp_helptrigger table_name第27页/共28页感谢您的观看！第28页/共28页
