1、存储过程和触发器第9章 存储过程和触发器教学目标:掌握存储过程和触发器的基本概念,学会编写简单的存储过程和触发器,对存储过程和触发器的实际应用有较好的理解。9.1 存储过程9.1.1 存储过程的基本知识1. 概念存储过程(Stored Procedure)是一组编译好存储在服务器上的完成特定功能T-SQL代码,是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行存储过程。2. 优点使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括:(1)允许标准组件式编程,增强重用性和共享性(2)能够实现较快的执行速度(3)能够减少网络流量
2、(4)可被作为一种安全机制来充分利用3. 分类在SQL Server 2005中存储过程分为三类:系统提供的存储过程、用户自定义存储过程和扩展存储过程。系统:系统提供的存储过程,sp_*,例如:sp_rename扩展:SQL Server环境之外的动态链接库DLL,xp_远程:远程服务器上的存储过程用户:创建在用户数据库中的存储过程临时:属于用户存储过程,#开头(局部:一个用户会话),#(全局:所有用户会话)9.1.2 创建用户存储过程1. 使用存储过程模板创建存储过程在【对象资源管理器】窗口中,展开“数据库”节点,再展开所选择的具体数据库节点,再展开选择“可编程性”节点,右击“存储过程”,选
3、择“新建存储过程”命令,如图所示:在右侧查询编辑器中出现存储过程的模板,用户可以在此基础上编辑存储过程,单击“执行”按钮,即可创建该存储过程。例9-1:创建一个简单的存储过程。USE LibraryGOCREATE PROCEDURE borrowed_numASSELECT Rname,LendnumFROM ReaderWHERE Rname=赵良宇 存储过程建好了,什么时候,怎么用呢?执行存储过程:borrowed_num 或EXEC borrowed_num执行结果:2. 使用T-SQL语句创建表格式:CREATE PROC 过程名形参名 类型变参名 类型 OUTPUTAS SQL语句
4、例9-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 存储过程的参数1. 输入参数(值参)例9-3:输入参数为某人的名字。USE Libra
5、ryGOCREATE 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
6、 temp1 -实参表执行结果:例9-4:使用默认参数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 r
7、eader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k.BID WHERE Rname=nameGO执行存储过程:EXEC borrowed_book32. 输出参数(变参)例9-5:利用输出参数计算阶乘。USE LibraryIF EXISTS(SELECT name FROM sysobjects WHERE name=factorial AND type=P) DROP PROCEDURE factorialGOCREATE PROCEDURE factorial in float, -输入形式参数
8、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执行结果:过期-157 天(1 行受影响)例9-9:对Library库中Reader表的 DELETE操作定义触发器。USE LibraryGOIF EXISTS(
9、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 LibraryGODELET
10、E Reader WHERE RID=2005216119执行结果:该读者不能删除!还有4 本书没还。9.2.3 创建DDL触发器DDL 触发器会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务,例如审核和控制数据库操作。语法形式:CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.nAS sql_statement; .n|EXTERNAL NAME ; 其中::
11、=ENCRYPTION EXECUTE AS Clause := assembly_name.class_name.method_name 例9-10:使用DDL触发器来防止数据库中的任一表被修改或删除。CREATE TRIGGER safetyON DATABASE FOR DROP_TABLE, ALTER_TABLEASPRINT You must disable Trigger safety to drop or alter tables!ROLLBACK 例9-11:使用 DDL 触发器来防止在数据库中创建表。CREATE TRIGGER safetyON DATABASEFOR C
12、REATE_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 触发器9.2.5 删除触发器DROP TRIGGER 触发器9.2.6 查看触发器sp_helptext trigger_namesp_helptrigger table_name9.3 小结本章介绍了存储过程和触发器的概念和创建、调用方法。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1