• 1. Chapter 6 常用存储过程、视图、索引器与触发器
  • 2. 本章目标掌握如何创建视图 掌握如何创建索引 掌握如创创建触发器 掌握常用的存储过程
  • 3. 什么是视图CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName, 学号=stuInfo.stuNo,…基于学员信息表和成绩表创建视图教员需要的视图: 方便查看学员的成绩班主任需要的视图: 方便查看学员的档案
  • 4. 什么是视图视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上 视图中并不存放数据,而是存放在视图所引用的原始表(基表)中 同一张原始表,根据不同用户的不同需求,可以创建不同的视图
  • 5. 什么是视图视图的用途 筛选表中的行 防止未经许可的用户访问敏感数据 降低数据库的复杂程度 将多个物理数据库抽象为一个逻辑数据库
  • 6. 如何创建视图使用T-SQL语句创建视图的语法 CREATE VIEW view_name AS
  • 7. 如何创建视图创建方便教员查看成绩的视图IF EXISTS (SELECT * FROM sysobjects WHERE name = 'view_stuInfo_stuMarks') DROP VIEW view_stuInfo_stuMarks GO CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo, 笔试成绩 =writtenExam, 机试成绩=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo GO SELECT * FROM view_stuInfo_stuMarks检测是否存在创建视图使用视图删除视图
  • 8. 什么是索引汉语字典中的汉字按页存放,一般都有汉语拼音目录(索引)、偏旁部首目录等 我们可以根据拼音或偏旁部首,快速查找某个字词
  • 9. Indexes Use Key Values to Locate Data (根据索引键查找定位数据行) Data Pages(数据页)…Index Pages(索引页)什么是索引
  • 10. 什么是索引SQL Server中的数据也是按页( 8KB )存放 索引:是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。 索引页:数据库中存储索引的数据页;索引页类似于汉语字(词)典中按拼音或笔画排序的目录页。 索引的作用:通过使用索引,可以大大提高数据库的检索速度,改善数据库性能。
  • 11. 索引类型唯一索引:唯一索引不允许两行具有相同的索引值 主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的,并且不能为空 聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个 非聚集索引(Non-clustered):非聚集索引指定表的逻辑顺序。数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于249个
  • 12. 如何创建索引 演示使用企业管理器创建
  • 13. 如何创建索引CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…) [WITH FILLFACTOR=x]UNIQUE表示唯一索引,可选 CLUSTERED、NONCLUSTERED表示聚集索引还是 非聚集索引,可选 FILLFACTOR表示填充因子,指定一个0到100之间的值, 该值指示索引页填满的空间所占的百分比 使用T-SQL语句创建索引的语法:唯一索引聚集索引或非聚集索引填充因子(系数):指定一个0~100之间的值,表示索引页填充的百分比
  • 14. USE stuDB GO IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_writtenExam') DROP INDEX stuMarks.IX_writtenExam /*--笔试列创建非聚集索引:填充因子为30%--*/ CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30 GO /*-----指定按索引 IX_writtenExam 查询----*/ SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90如何创建索引 在stuMarks表的writtenExam列创建索引:检测索引是否存在: 索引存放在系统表sysindexes中
  • 15. 索引的优缺点优点 加快访问速度 加强行的唯一性 缺点 带索引的表在数据库中需要更多的存储空间 操纵数据的命令需要更长的处理时间,因为它们需要对索引进行更新
  • 16. 创建索引的指导原则请按照下列标准选择建立索引的列。 该列用于频繁搜索 该列用于对数据进行排序 请不要使用下面的列创建索引: 列中仅包含几个不同的值。 表中仅包含几行。为小型表创建索引可能不太划算,因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长
  • 17. 小结1视图是一张虚拟表,可以方便不同用户的查询,提高数据的安全性,筛选特定的数据行 建立索引有助于快速检索数据。索引分为唯一索引、主键索引、聚集索引、非聚集索引
  • 18. 触发器是一种特殊类型的存储过程,基于某个表或视图(触发表、触发视图)的一个或多个列创建,用于监控它们的insert、update、delete操作(触发操作),当发生以上操作时自动执行事先定义的步骤,可进行复杂的数据完整性监测和约束
  • 19. 触发器的特点不能直接调用,只有在对触发器表的数据进行更改时,才自动执行 不能传递和接受参数 触发器可以实施更为复杂的数据完整性约束
  • 20. 触发器触发时: 系统自动在内存中创建deleted表或inserted表 只读,不允许修改;触发器执行完成后,自动删除 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足,则向用户报告错误消息,并回滚插入操作 inserted 和deleted 表2-1
  • 21. inserted 和deleted 表2-2修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息
  • 22. 创建触发器的语法: 如何创建触发器CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL语句 GOWITH ENCRYPTION表示加密触发器定义的SQL文本 DELETE, INSERT, UPDATE指定触发器的类型
  • 23. INSERT触发器transInfocardIDtransType transMoney1001 0002 1001 0002存入 300 存入 500 insertedcardIDtransType transMoney 1001 0001 支取 200transInfocardIDtransType transMoney1001 0002 1001 0002存入 300 存入 5001001 0001支取 200插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据,确定是否需要回滚或执行其他操作INSERT触发器的工作原理:
  • 24. INSERT 触发器示例 3-1 问题: 解决上述的银行取款问题:当向交易信息表(transInfo)中插入一条交易信息时,我们应自动更新对应帐户的余额。 分析: 在交易信息表上创建INSERT触发器 从inserted临时表中获取插入的数据行 根据交易类型(transType)字段的值是存入/支取, 增加/减少对应帐户的余额。
  • 25. -------关键代码------ 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表中获取交易类型、教员金额等根据交易类型,减少或增加对应卡号的余额
  • 26. INSERT 触发器示例 3-3
  • 27. transInfocardIDtransType transMoney1001 0002 1001 0002存入    300 存入   500 1001 0001支取 200DELETE触发器transInfocardIDtransType transMoney1001 0002 1001 0002存入   300 存入   500 deletedcardIDtransType transMoney 1001 0001支取 200删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据,决定是否需要回滚或执行其他操作DELETE触发器的工作原理:
  • 28. 问题: 当删除交易信息表时,要求自动备份被删除的数据到表backupTable中 。分析: 在交易信息表上创建DELETE触发器 被删除的数据可以从deleted表中获取DELETE触发器示例 3-1
  • 29. -------关键代码------ 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
  • 30. DELETE触发器示例 3-3
  • 31. Deleted(更新前的数据)customerNamecardID currentMoney 李四1000 0002 1bankcustomerName    cardID currentMoney张三 10010001 1000 李四10000002 1UPDATE触发器删除记录行向deleted表中插入被删除的副本检查deleted和inserted表中的数据,确定是否需要回滚或执行其他操作UPDATE触发器的工作原理:李四 10000002 20001向inserted表中插入被添加的副本Inserted(更新后的数据)customerNamecardID currentMoney 李四1000 0002 20001插入记录行
  • 32. 问题: 跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示。 分析: 在bank表上创建UPDATE触发器 修改前的数据可以从deleted表中获取 修改后的数据可以从inserted表中获取UPDATE触发器示例 3-1
  • 33. -------关键代码------ 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万回滚事务,撤销交易
  • 34. UPDATE触发器 3-3
  • 35. 列级 UPDATE 触发器 3-1UPDATE触发器除了跟踪数据的变化(修改)外,还可以检查是否修改了某列的数据 使用UPDATE(列)函数检测是否修改了某列问题: 交易日期一般由系统自动产生,默认为当前日期。为了安全 起见,一般禁止修改,以防舞弊。 分析: UPDATE(列名)函数可以检测是否修改了某列
  • 36. -------关键代码------ 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
  • 37. 列级 UPDATE 触发器 3-3
  • 38. 查看触发器(1)使用SQL Server管理平台查看触发器信息。 在SQL Server管理平台中,展开服务器和数据库,选择并展开表,然后展开触发器选项,右击需要查看的触发器名称,如图9-4所示,从弹出的快捷菜单中,选择“编写触发器脚本为→create到→新查询编辑器窗口”,则可以看到触发器的源代码。 图9-4 查看触发器
  • 39. 查看触发器(2)使用系统存储过程查看触发器。 系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。 sp_help:用于查看触发器的一般信息,如触发器的名称、属性、类型和创建时间。   sp_help ‘触发器名称’ sp_helptext:用于查看触发器的正文信息。   sp_helptext ‘触发器名称’ sp_depends:用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。   sp_depends ‘触发器名称’   sp_depends ‘表名’
  • 40. 修改触发器通过SQL Server管理平台、存储过程,可以修改触发器的正文和名称。 1.使用SQL Server管理平台修改触发器正文。 在管理平台中,展开指定的表,右击要修改的触发器,从弹出的快捷菜单中选择“修改”选项,则会出现触发器修改窗口,如图9-5所示。在文本框中修改触发器的SQL语句,单击“语法检查”按钮,可以检查语法是否正确,单击“执行”按钮,可以成功修改此触发器。 图9-5 触发器修改窗口
  • 41. 修改触发器修改DML触发器的语法形式如下: ALTER TRIGGER schema_name.trigger_name ON (table|view) [WITH [,...n]] (FOR|AFTER|INSTEAD OF) {[DELETE][,][INSERT][,][UPDATE]} [NOT FOR REPLICATION] AS {sql_statement[;][...n]|EXTERNAL NAME [;]} ::=[ENCRYPTION][&lEXECUTE AS Clause >] ::=assembly_name.class_name.method_name 修改DDL触发器的语法形式如下: ALTER TRIGGER trigger_name ON {DATABASE|ALL SERVER}[WITH [,...n]] {FOR|AFTER}{event_type[,...n]|event_group} AS {sql_statement[;]|EXTERNAL NAME [;]} ::=[ENCRYPTION][&lEXECUTE AS Clause > ] ::=assembly_name.class_name.method_name
  • 42. 修改触发器例9-13 修改触发器。 程序清单如下: CREATE TRIGGER s_reminder ON S WITH ENCRYPTION AFTER INSERT, UPDATE AS RAISERROR ('不能对该表执行添加、更新操作', 16, 10) ROLLBACK GO -- 下面修改触发器. ALTER TRIGGER s_reminder ON S AFTER INSERT AS RAISERROR ('不能对该表执行添加操作', 16, 10) ROLLBACK GO
  • 43. 修改触发器2.使用sp_rename命令修改触发器的名称。 sp_rename命令的语法形式如下:   sp_rename oldname,newname
  • 44. 删除触发器由于某种原因,需要从表中删除触发器或者需要使用新的触发器,这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法: (1)使用系统命令DROP TRIGGER删除指定的触发器。其语法形式如下:    DROP TRIGGER { trigger } [ ,...n ] (2)删除触发器所在的表。删除表时,SQL Server将会自动删除与该表相关的触发器。 (3)在SQL Server管理平台中,展开指定的服务器和数据库,选择并展开指定的表,右击要删除的触发器,从弹出的快捷菜单中选择“删除”选项,即可删除该触发器。
  • 45. 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程,触发器通常用于强制业务规则 触发器还是一个特殊的事务单元,当出现错误时,可以执行ROLLBACK TRANSACTION回滚撤销操作 触发器一般都需要使用临时表:deleted表和inserted表,它们存放了被删除或插入的记录行副本 触发器类型: INSERT触发器 UPDATE触发器 DELETE触发器小结2
  • 46. 常用存储过程我们在项目开发中有一些常用的存储过程 大批量数据的插入 分页
  • 47. 批量插入在项目的开发中,有时会碰到批量数据录入数据库的情况,编写多条插入语句效率太低
  • 48. 批量数据插入1-1Create procedure SplitString (  @string nvarchar(1000),  -- 要分隔的字符串  @splitchar nvarchar(10) = ,, -- 分隔字符  @tablename nvarchar(50),  -- 存入的表名称  @fieldname nvarchar(50) = [id] -- 存入的字段名称 ) as -- 将字符串分隔开放进表中 declare @l int -- 第一个分隔字符的位置 declare @s int -- 第二个分隔字符的位置 set @l = 0 set @s = charindex(@splitchar, @string, @l) while @l <= len(@string) begin  declare @id nvarchar(50)   if @s = 0 set @s = len(@string) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一    set @id = substring(@string, @l, @s - @l) -- 取值  set @l = @s + 1  set @s = charindex(@splitchar, @string, @l)  if ltrim(rtrim(@id)) = continue -- 如果是空字符串就跳过  declare @sql nvarchar(1000)  set @sql = insert into + @tablename + (+ @fieldname +) values( + @id + )  exec sp_executesql @sql end go
  • 49. 批量插入多行数据2-1示例数据库: NorthWind 涉及表: Orders OrderDetails
  • 50. 多行数据批量插入2-2分隔字符串存储过程 CutString create proc CutString (@sourcestring varchar(100) output, @outstring varchar(10) output) as declare @position int set @position = charindex(',',@sourcestring) if (@position = 0) begin set @outstring = @sourcestring set @sourcestring = null end else begin set @outstring = substring(@sourcestring,1,@position-1) set @sourcestring = substring(@sourcestring,@position+1,(len(@sourcestring)-@position)) end
  • 51. 多行数据批量插入2-3Create proc SubmitOrder ( @customerid varchar(5), @employeeid int, @productidlist varchar(100), @unitpricelist varchar(100), @quantitylist varchar(100), @discountlist varchar(100)) as set xact_abort on --开启自动事务会滚,出任何错误都自动回滚 declare @orderid int declare @productid varchar(20) declare @unitprice varchar(20) declare @quantity varchar(20) declare @discount varchar(20) begin transaction --使用事务来执行 insert into Orders(CustomerID,EmployeeID) values(@customerid,@employeeid) --select top 1 OrderID from Orders order by OrderID desc select @orderid = @@identity --@@identity取得刚刚完成插入的全局自动增长列的值 while ( @productidlist is not null ) begin exec CutString @productidlist output,@productid output exec CutString @unitpricelist output,@unitprice output exec CutString @quantitylist output,@quantity output exec CutString @discountlist output,@discount output insert into [Order Details] values( @orderid, convert(int,@productid), convert(money,@unitprice), convert(int,@quantity), convert(real,@discount)) --delete end Commit transaction
  • 52. 分页存储过程数据量很大时必须使用分页 常用的分页方法: 临时表方式 排名函数方式 TOP方式 ……..
  • 53. 使用临时表举例使用临时表实现分页功能create procedure procSplitPage @CurPage int, --当前页 @PageSize int --每页显示的记录数 AS DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound = (@CurPage-1)*@PageSize SET @PageUpperBound = (@CurPage-1)*@PageSize+@PageSize --创建临时表 CREATE TABLE #TempTb ( IndexId int IDENTITY (1, 1) NOT NULL, TID int ) --向临时表中添加数据 INSERT INTO #TempTb (TID) SELECT TOP (@PageUpperBound) TID FROM bbsTopic ORDER BY bbsTopic.TID ASC SELECT bbsTopic.*,#TempTb.IndexID FROM bbsTopic, #TempTb WHERE #TempTb.IndexID > @PageLowerBound AND bbsTopic.TID=#TempTb.TId ORDER BY #TempTb.IndexID GO执行存储过程: EXEC procSplitPage @CurPage=1,@PageSize=2
  • 54. 排名函数方式 CREATE PROCEDURE [dbo].[P_GetBank] @startIndex INT, -- 第几页 @pageSize INT --页显示几条数据 AS with orderlist as( select row_number() over(order by cardId) as rownumber, customerName,cardId,currentMoney from bank) select customerName,cardId,currentMoney from orderlist where rownumber between(@startIndex-1)*@pageSize+1 AND @startIndex*@pageSize
  • 55. 小结1为什么我们一定要编写分页存储过程? 临时表的创建语法是什么? 用以分页的排名函数是什么?