第7章视图索引游标事务

1、第7章视图、索引、游标及事务,逯燕玲戴红李志明主编,2,第7章视图、索引、游标及事务,7.1视图7.2索引7.3游标7.4事务的使用,3,7.1视图,7.1.1视图概述视图是从一个表、多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。视图不是真实存在的基础表，而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。,4,7.1.2创建视图的方法,1.使用企业管理器向导创建视图,【例7-1】在学籍管理数据库（students）中创建一个仅包含基础课的视图。启动SQLServer企业管理器，在企业管理器窗口中展开MicrosoftSQLServ

2、ers和SQLServer组，选择SQLServer服务器。在“工具”菜单中选择“向导”菜单项，选择工具菜单中的“创建视图向导”，在出现的“创建视图向导”界面中单击“下一步”。,5,输入数据库名“学生学籍数据库”，单击“下一步”；输入所选数据库的选择对象对话框名“课程基本信息表”，单击“下一步”。为对象中的列选择视图所显示的字段，单击“下一步”。用“where课程类型=基础”语句来定义所限制对话框，单击“下一步”。将对话框命名为“课程基本信息表_VIEW”，单击“下一步”。再选择“完成”，即可成功创建视图。,6,7.1.2创建视图的方法,2.用T-SQL创建视图语法：CREATEVIEWvie

3、w_name(column,n)WITH,nASsellect_statement,7,【例7-2】在学籍管理数据库（students）中创建一个视图，其中内容为一门课程的成绩单，包含学生姓名。在”工具”菜单中选择”向导”命令CREATEVIEWSCCourseGWITH,nASsellectSC.CNoFROMSC,CoursesWHERESC.CNo=Courses.CNoANDCourses.Cname=网络数据库）；,8,【例7-3】假设在学籍管理数据库（students）中有三个结构相同的学生基本信息表：S1、S2、S3，分别存放“计算机科学与技术”、“电子信息科学与技术”和“电子信

4、息工程”专业的学生基本信息。创建一个视图将这三个表合并在一起。USEstudentsCREATEVIEWALL_StudentsASSELECT*FROMS1UNIONSELECT*FROMS2UNIONSELECT*FROMS3,9,7.1.3视图信息的查看和修改,1.使用企业管理器查看和修改视图,启动SQLServer企业管理器，登录到指定的服务器。打开要创建视图的数据库文件夹，选中view图标，此时在右面的窗格中显示当前数据库的所有视图。右键单击要查看的视图，在弹出的快捷菜单中选择“属性”，打开ViewProperties对话框。在该对话框内可浏览到该视图的正文，也可对该视图进行修改，然

5、后单击“CheckSyntax”按钮对语句合法性进行检查，若对视图的访问权限进行设置，单击“Permissions”按钮。,10,2使用T-SQL查看和修改视图【例7-4】查看在学籍管理数据库（students）中创建的视图DB_S_Grades。SELECTFROMDB_S_Grades,11,当需要在视图中对数据进行插入、修改和删除等更新操作时，可与操作表数据窗口相似，同样可以对数据进行插入、修改和删除等更新操作。还可以使用INSERT、UPDATE和DELETE语句对视图数据进行更新。,7.1.4视图数据的更新,12,在企业管理器窗口中单击“视图”选项，打开“视图列表”。在视图列表中找到

6、要删除的视图，右键单击该视图打开其快捷菜单，选择“删除”。在出现的“除去对象”对话框中，选择“全部除去”即可完成。还可以使用T-SQL删除视图，基本语法格式为：DROPVIEWview_name,n,7.1.5删除视图,13,7.2索引,7.2.1索引的分类聚簇索引（ClusteredIndex）：行的物理存储顺序与索引顺序完全相同，所以每个表只能有一个聚簇索引，且在表的主键列上自动创建聚簇索引。非聚簇索引：逻辑顺序不等于物理顺序，仅仅记录指针，通过这些指针可以在表中快速准确的定位数据。唯一性索引（UniqueIndex）：能够保证在创建索引的列或多列的组合上不包括重复的数据，聚簇或非聚簇索引

7、都可以是唯一性索引。,14,创建索引前考虑的问题,只有表的拥有者才能在表上创建索引每一个表上只能创建一个聚簇索引每一个表上最多能创建249个非聚簇索引一个索引最多包含16个列，最大为900字节,15,7.2.2创建索引,1.使用企业管理器直接创建用鼠标右键单击要创建索引的表，选择”管理索引”菜单项。2.使用向导创建索引在”工具”菜单中选择”向导”命令3.使用T-SQL创建索引CREATEUNIQUECLUSTEREDNONCLUSTEREDINDEXindex_nameONtable|view(columnASCDESC,n)WITH参数,16,7.2.3创建索引视图,在视图上创建唯一聚集索引

8、可以提高性能，执行该视图结果集在数据库中的存储方式与带聚集索引的表的存储方式相同。在视图上创建索引的另一个好处是：查询优化器开始在查询中使用视图索引，而不是直接在子句中命名视图。这样可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。,17,7.2.4删除索引,1.使用企业管理器直接删除索引在要删除索引的表上单击右键,选择”所有任务”中的”管理索引”命令。2.用T-SQL删除索引语法：DROPINDEXtable.index|view.index,n例如：DROPINDEXStudent.DEPT,18,用企业管理器查看修改索引选择要查看的数据库表上单击右键，从快捷菜单中选择

9、”所有任务中的“管理索引”项，出现“索引管理”对话框。选择要查看或修改的索引，单击“编辑”按钮，出现“修改索引”对话框。可修改索引的大部分设置，还可单击“编辑SQL”按钮，在显示的SQL脚本编辑框直接修改其SQL脚本，但不能修改索引名，程序方式的索引名修改需要使用系统存储过程Sp_rename。要在企业管理器中修改索引名、改变其所属文件组等其它信息则需要在表的属性对话框中进行，该属性对话框是从”设计表结构“对话框中调用，而不是直接通过快捷菜单的“属性”菜单项调用。,7.2.5查看与修改索引,19,7.3游标,游标是系统为用户在内存中开辟的一个数据缓冲区，用于存放SQL语句的查询结果，每个游标都

10、有一个名字，通过宿主语言的循环使SQL逐一从游标中读取记录，赋给主变量，然后由宿主语言作进一步的处理。游标的操作一般分为如下几个步骤：定义游标EXECSQLDECLARECURSORFOR打开游标EXECSQLOPEN推进游标EXECSQLFETCHINTO关闭游标EXECSQLCLOSE删除游标DEALLOCATE,20,【例7-5】查询各种职称的教师的名单。,EXECSQLBEGINDECLARESECTION；charxm8；charzc6；EXECSQLENDDECLARESECTION；printf（”Enter职称：”）；scanf（“%s”，zc）；EXECSQLDECLAREz

11、c_curCURSORFORSELECTTname，TtitleFROMTEACHERWHERETtitle=：zc；EXECSQLOPENzc_curWhile(1)EXECSQLFETCHzc_curINTO：xm，：zc；if（sqlca.sqlcode0）break；EXECSQLCLOSEzc_cur；,21,7.4事务的使用,7.4.1事务概述所谓事务，实际上就是对于一个不可分割的操作序列，控制它要么全部执行，要么都不执行。在每个事务结束时，数据库中的数据应该是一致的。设计事务程序时应保证事务具有如下特性：原子性（Atomicity）一致性（Consistency）独立性（Isol

12、ation）持久性（Durability）,22,三种执行事务的模式,显式事务：每个事务均以BEGINTRANSACTION语句显式开始，而以COMMITTRAN或ROLLBACKTRAN等语句显式结束。隐性事务：在T-SQL脚本中，执行“SETIMPLICIT_TRANSACTIONSON”语句后，系统即进入隐性事务模式。自动提交事务：如果一个T-SQL语句成功地完成，则自动提交该语句；如果遇到错误，则自动回滚该语句。,23,开始事务语句BEGINTRANSACTIONTRANSACTION_NAMEWITHMARKdescription提交事务语句COMMITTRANSACTIONTRAN

13、SACTION_NAME回滚事务语句ROLLBACKTRANSACTIONtransaction_name|savepoint_name保存事务断点语句SAVETRANSACTIONSAVEPOINT_NAME,7.4.2事务处理语句,24,【例7-6】给教工“高山”增加工资100元。BEGINTRANSACTIONUPDATE教工登记表SET基本工资=基本工资+100WHERE姓名=高山UPDATE教工工资表SET基本工资=基本工资+100WHERE姓名=高山COMMIT,25,【例7-7】在“中高级职称名册”中修改一条记录，并插入一条记录。BEGINTRANSACTIONUPDATE中高级

14、职称名册SET职称=副教授WHERE姓名=王冠INSERT中高级职称名册VALUES(SYS010,高山,男,45,1,教授,4800,计算机系)SELECT*FROM中高级职称名册COMMIT,26,【例7-8】将当前连接设成隐性事务状态，并进行还书。假如在还书时必须完成修改“租借信息”表和“图书信息”表两个操作，可编写如下脚本。,DECLARE借书证号varchar(5),图书编号varchar(6)SET借书证号=00006SET图书编号=100006SETIMPLICIT_TRANSACTIONSONUPDATE租借信息SET还书日期=getdate()WHERE图书编号=图书编号AN

15、D借书证号=借书证号IFERROR!=0ANDROWCOUNT=0BEGINROLLBACKTRANRETURNEND,UPDATE图书信息SET状态=0WHERE图书编号=图书编号IFERROR!=0ANDROWCOUNT=0BEGINROLLBACKTRANRETURNENDCOMMITPRINT恭喜你还书成功！SETIMPLICIT_TRANSACTIONSOFF,27,【例7-9】某一新生来借书，假如要完成办借书证和借书两个操作。办借书证操作可理解为向“学生信息”表中插入一条办证记录；借书操作为了说明方便简化为只向“租借信息”表中插入一条借书记录。编写脚本如下。,DECLARE学号va

16、rchar(6),姓名varchar(10),班级varchar(10)DECLARE借书证号char(5),图书编号varchar(6),借阅号intSET图书编号=100002SELECT学号=20030104,姓名=李小军,班级=2003-01BEGINTRANSELECT借书证号=借书证号FROM学生信息ORDERBY借书证号SET借书证号=convert(varchar,convert(bigint,借书证号)+1)INSERT学生信息(借书证号,学号,姓名,班级)VALUES(借书证号,学号,姓名,班级)IFerror!=0BEGINROLLBACKTRANPRINT办证失败，不能

17、借书RETURNEND,PRINT办证成功，借书证号是：+借书证号SAVETRANAAIFNOTEXISTS(SELECT*FROM图书信息WHERE图书编号=图书编号AND状态=0)PRINT该书已经借出，借书失败ELSEBEGINSELECT借阅号=借阅号FROM租借信息ORDERBY借阅号INSERT租借信息(借阅号,借书证号,图书编号,借书日期)VALUES(借阅号+1,借书证号,图书编号,getdate()IFerror!=0BEGINROLLBACKTRANaaPRINT借书失败ENDELSEPRINT恭喜您借书成功ENDCOMMITTRAN,28,7.4.3分布式事务,如果要在事

18、务中存取多个数据库服务器中的数据，那么就必须使用“分布式事务”，将工作分散到多个服务器中进行。执行分布式事务很简单，只要将BEGINTRANSACTION换成BEGINDISTRIBUTEDTRANSACTION即可，其他部分都不必改动。【例7-10】假如向“服务器1.数据库1.表1.”插入记录操作和向“服务器2.数据库2.所有者名.表2.”中插入记录操作构成一个事务，那么这个事务就是分布式事务。,29,【例7-10】假如向“服务器1.数据库1.表1.”插入记录操作和向“服务器2.数据库2.所有者名.表2.”中插入记录操作构成一个事务，那么这个事务就是分布式事务。主要脚本如下：,BEGINDISTRIBUTEDTRANINSERT服务器1.数据库1.表1(a1,a2)VALUES(a1,a2)IFerror!=0BEGINROLLBACKTRANRETURNENDINSERT服务器2.数据库2.所有者名.表2(b1,b2)