数据库原理及应用存储过程和触发器.pptx

数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用An Introduction to Database System 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用数据库系统概论数据库系统概论An Introduction to Database System第第9章章 存储过程和触发器存储过程和触发器 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用教学目标：教学目标：掌握存储过程和触发器的基本概念，掌握存储过程和触发器的基本概念，学会编写简单的存储过程和触发器，学会编写简单的存储过程和触发器，对存储过程和触发器的实际应用有较好的对存储过程和触发器的实际应用有较好的理解。理解。数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用第9章 存储过程和触发器9.1存储过程9.2触发器 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1存储过程9.1.1存储过程的基本知识9.1.2创建用户存储过程9.1.3存储过程的参数 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.1存储过程的基本知识存储过程（Stored Procedure）是一组编译编译好好存储在服务器服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。Sp（x,y）客户：客户：sp（1，2）数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.1存储过程的基本知识 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括：允许标准组件式编程，增强重用性和共享性能够实现较快的执行速度能够减少网络流量可被作为一种安全机制来充分利用 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.1存储过程的基本知识在SQL Server 2005中存储过程分为5类：系统：系统提供的存储过程，sp_*，例如：，例如：sp_rename扩展：SQL Server环境之外的动态链接库DLL，xp_远程：远程服务器上的存储过程用户：创建在用户数据库中的存储过程临时：属于用户存储过程，#开头（局部：一个用户会话）,#（全局：所有用户会话）数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.2创建用户存储过程格式：CREATE PROC 过程名形参名形参名 类型类型变参名变参名 类型类型 OUTPUTAS SQL语句 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.2创建用户存储过程例例：创建一个多表查询的存储过程。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.1.3存储过程的参数例例：输入参数为某人的名字。USE LibraryGOCREATE PROCEDURE borrowed_book2name varchar10)-形式参数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 -实参表实参表 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用91.3存储过程的参数例例：使用默认参数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 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.1.3存储过程的参数例例：利用输出参数计算阶乘。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 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.2创建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 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.3创建DDL触发器语法形式：CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.nAS sql_statement;.n|EXTERNAL NAME;数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.3创建DDL触发器例例：使用DDL触发器来防止数据库中的任一表被修改或删除。CREATE TRIGGER safetyON DATABASE FOR DROP_TABLE,ALTER_TABLEASPRINT You must disable Trigger safety to drop or alter tables!ROLLBACK 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.3创建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 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.4修改触发器ALTER TRIGGER trigger_name 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.5删除触发器DROP TRIGGER trigger_name 数据库原理及应用数据库原理及应用数据库原理及应用数据库原理及应用9.2.6查看触发器sp_helptext trigger_namesp_helptrigger table_name