SQL补充：触发器、临时表与游标.ppt

SQL补充 : 触发器、临时表与游标 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 回顾 1 存储过程是什么？ 使用存储过程有什么好处？ 存储过程的参数和局部变量有什么不同？ 请写出以下存储过程的调用命令，要求 num1使用默认 值， num2使用 65： create procedure proc_sample (num1 int =60, num2 int =70 ) as Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 回顾 2 代码阅读，下列系统存储过程的功能是： EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_renamedb stuDB,studentDB EXEC xp_cmdshell mkdir d:project 根据您的理解， print语句和 RAISERROR语句有什么区别 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 回顾 3 代码阅读 :指出下列语句的错误 CREATE PROCEDURE proc_stuInfo -定义存储过程 stuName VARCHAR(10), m INT OUTPUT, stuAge=18 INT AS .代码略 GO -调用存储过程 DECLARE s INT EXEC proc_stuInfo 张三 ,s,30 EXEC proc_stuInfo 张三 ,s EXEC proc_stuInfo stuAge=22,stuName=李四 ,m=s OUTPUT stuAge INT=18 EXEC proc_stuInfo 张三 ,s OUTPUT,30 EXEC proc_stuInfo 张三 ,s OUTPUT 正确 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 本章目标 掌握触发器的用法 理解触发器的使用场合 掌握创建 Insert/update/delete触发器的语法 掌握创建列触发器的语法 掌握临时表的用法 全局临时表和本地临时表的用法 全局临时表和本地临时表的区别 掌握游标的用法 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 为什么需要触发器 (TRIGGER)呢 ？ 典型的应用就是银行的 取款机系统 一、触发器 演示： 为什么需要触发器 .sql 帐户信息表 bank 交易信息表 transInfo 张三取钱 200 问题： 没有 自动修改 张三的余额 最优的解决方案就是采用 触发器： 它是一种特殊的存储过程 也具备事务的功能 它能在多表之间执行特殊的业务规则 张三开户 1000元 , 李四开户 1元 为什么需要触发器 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 张三 李四 王五 赵二 王三 宋二 刘五 插入 什么是触发器 3-1 删除 触发器触发 赵二退休 赵二 员工表 退休员工表 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 触发器是在对表进行插入 、 更新或删除操作时 自 动执行的存储过程 触发器通常用于强制业务规则 触发器是一种 高级约束 ， 可以定义比用 CHECK 约束更为复杂的约束 可执行复杂的 SQL语句 （ if/while/case） 可引用其它表中的列 什么是触发器 3-2 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 触发器定义在特定的表上 ， 与表相关 自动触发执行 不能直接调用 是一个事务 （ 可回滚 ） 什么是触发器 3-3 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 触发器的类型 DELETE 触发器 INSERT 触发器 UPDATE 触发器 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 触发器触发时： 系统自动在内存中创建 deleted表或 inserted表 只读，不允许修改；触发器执行完成后，自动删除 inserted 表 临时保存了插入或更新后的记录行 可以从 inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作 deleted 表 临时保存了删除或更新前的记录行 可以从 deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作 inserted 和 deleted 表 2-1 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. inserted 和 deleted 表 2-2 修改操作 inserted表 deleted表 增加 (INSERT)记录 存放新增的记录 - 删除 (DELETE)记录 - 存放被删除的记录 修改 (UPDATE)记录 存放更新后的记录 存放更新前的记录 inserted表和 deleted表存放的信息 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 创建触发器的语法： 如何创建触发器 CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL语句 GO WITH ENCRYPTION表示加密触发器定义的 SQL文本 DELETE, INSERT, UPDATE指定触发器的类型 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. INSERT触发器 transInfo cardID transType transMoney 1001 0002 1001 0002 存入 300 存入 500 inserted cardID transType transMoney 1001 0001 支取 200 1001 0002 存入 存入 500 1001 0001 支取 200 插入记录行 触发 insert触发器。 向 inserted表中插 入新行的副本 触发器检查 inserted表中插入的新行数据， 确定是否需要回滚或执行其他操作 INSERT触发器的工作原理： Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. INSERT 触发器示例 3-1 问题： 解决上述的银行取款问题：当向交易信息表 (transInfo)中插 入一条交易信息时，我们应自动更新对应帐户的余额 。 分析： 在交易信息表上创建 INSERT触发器 从 inserted临时表中获取插入的数据行 根据交易类型 (transType)字段的值是存入 /支取， 增加 /减少对应帐户的余额。 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. -关键代码 - CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney， myCardID=cardID FROM inserted IF (type=支取 ) UPDATE bank SET currentMoney=currentMoney-outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID . GO INSERT 触发器示例 3-2 从 inserted表中获取交 易类型、教员金额等 根据交易类型，减少或增加对应卡号的余额 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. INSERT 触发器示例 3-3 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. transInfo cardID transType transMoney 1001 0002 1001 0002 存入 300 存入 500 1001 0001 支取 200 DELETE触发器 transInfo 1001 0002 存入 存入 500 deleted cardID transType transMoney 1001 0001 支取 200 删除记录行 触发 delete触发器 向 deleted表中插入 被删除的副本 触发器检查 deleted表中被删除的数据， 决定是否需要回滚或执行其他操作 DELETE触发器的工作原理： Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 问题： 当删除交易信息表时，要求自动备份被删除的数据到表 backupTable中 。 分析： 在交易信息表上创建 DELETE触发器 被删除的数据可以从 deleted表中获取 DELETE触发器示例 3-1 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. -关键代码 - CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print 开始备份数据，请稍后 . IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 备份数据成功，备份表中的数据为 : SELECT * FROM backupTable GO 从 deleted表中获取 被删除的交易记录 DELETE触发器示例 3-2 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. DELETE触发器示例 3-3 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. Deleted（更新前的数据） customerName cardID currentMoney 李四 1000 0002 1 bank customerName cardID currentMoney 张三 10010001 1000 李四 10000002 1 UPDATE触发器 删除记录行 向 deleted表中插入 被删除的副本 检查 deleted和 inserted表中的数据， 确定是否需要回滚或执行其他操作 UPDATE触发器的工作原理： 李四 10000002 20001 向 inserted表中插 入被添加的副本 Inserted（更新后的数据） customerName cardID currentMoney 李四 1000 0002 20001 插入记录行 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 问题： 跟踪用户的交易，交易金额超过 20000元，则取消交易，并 给出错误提示。 分析： 在 bank表上创建 UPDATE触发器 修改前的数据可以从 deleted表中获取 修改后的数据可以从 inserted表中获取 UPDATE触发器示例 3-1 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. -关键代码 - CREATE TRIGGER trig_update_bank ON bank FOR UPDATE AS DECLARE beforeMoney MONEY,afterMoney MONEY SELECT beforeMoney=currentMoney FROM deleted SELECT afterMoney=currentMoney FROM inserted IF ABS(afterMoney-beforeMoney)20000 BEGIN print 交易金额 :+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR (每笔交易不能超过 2万元，交易失败 ,16,1) ROLLBACK TRANSACTION END GO 从 deleted表中获取交易 前的余额，从 inserted表 中获取交易后的余额 UPDATE触发器 3-2 交易金额是否 2万 回滚事务，撤销交易 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. UPDATE触发器 3-3 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 列级 UPDATE 触发器 3-1 UPDATE触发器除了跟踪数据的变化（修改）外， 还可以检查是否修改了某列的数据 使用 UPDATE（列）函数检测是否修改了某列 问题： 交易日期一般由系统自动产生，默认为当前日期。为了安全 起见，一般禁止修改，以防舞弊。 分析： UPDATE（列名）函数可以检测是否修改了某列 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. -关键代码 - CREATE TRIGGER trig_update_transInfo ON transInfo FOR UPDATE AS IF UPDATE(transDate) BEGIN print 交易失败 . RAISERROR (安全警告：交易日期不能修改， 由系统自动产生 ,16,1) ROLLBACK TRANSACTION END GO 检查是否修改了交易日 期列 transDate 回滚事务，撤销交易 列级 UPDATE 触发器 3-2 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 列级 UPDATE 触发器 3-3 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 触发器是在对表进行插入 、 更新或删除操作时自动执行的 存储过程 ， 触发器通常用于强制业务规则 触发器还是一个特殊的事务单元 ， 当出现错误时 ， 可以执 行 ROLLBACK TRANSACTION回滚撤销操作 触发器一般都需要使用临时表： deleted表和 inserted表 ， 它们存放了被删除或插入的记录行副本 触发器类型： INSERT触发器 UPDATE触发器 DELETE触发器 小结 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 临时表工作于内存中，临时存储数据行 具有普通表的结构和访问特点 访问结束后，临时表将会被自动释放 SQL中没有数组的概念，临时表可用作替代，但功能更强 大，结构可以更复杂 二、临时表 什么是临时表 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 临时表的分类及区别 本地临时表 表名以 “ #”开头 在与首次创建或引用表时相同的 SQL Server 实例连接期间 ， 本 地临时表只对于创建者是可见的 当用户与 SQL Server 实例断开连接后 ， 将删除本地临时表 只能在一个存储过程内部起作用 。 全局临时表 表名以 “ #”开头 在创建后对任何用户和任何连接都是可见的 当引用该表的所有用户都与 SQL Server 实例断开连接后 ， 将删 除全局临时表 。 可以在相互调用的存储过程间起传递数据的作用 。 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. create table #stuInfo ( stuNo char(4) primary key, name nchar(4) , sex bit ) go 创建临时表 创建本地临时表 create table #stuInfo ( stuNo char(4) primary key, name nchar(4) , sex bit ) go 创建全局临时表 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. select * from #stuInfo 访问临时表 使用本地临时表 select * from #stuInfo 使用全局临时表 If (object_id(tempdb.dbo.#stuInfo) 检查临时表是否存在 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 临时表用于什么场合？ 临时表和普通表有什么异同点？ 全局临时表和本地临时表有什么联系和区别？ 小结 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 什么是游标？ 游标相当于一个当前记录指针 三、游标 游标有什么作用？ 允许将查询所得的结果集进行逐行处理 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 案例 请查询输出不及格的同学名单，若是少数民族，则加 10 分并输出其各项信息。否则只加分，不输出。 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 创建游标 declare cursor_stuNo cursor for select stuNo from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo where score60 or (score is null) Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 打开游标 open cursor_stuNo 取出游标指向的当前行的值 declare stuNo char(4) fetch next from cursor_stuNo into stuNo Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 判断取出的游标是否有效 while(fetch_status=0) 关闭游标 close cursor_stuNo 释放游标 deallocate cursor_stuNo Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 什么情况下使用游标？ 游标取值后，使用前，如何判断是否有效？ 游标一般会与哪种流程控制结构一起使用？ 小结 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.