第8章 视图和游标

第8章视图和游标本章导读：视图是从一个或多个基本表或其它视图导出的虚拟表，其数据会伴随引用的基本表或其它视图数据变化而变化。游标是一种数据访问机制，允许用户访问单独的数据行，而非整个数据行集，用户也可以通过游标进行查询、插入、更改和删除基表中的数据。知识要点：视图游标8.1视图8.2游标8.1视图视图不是真实存在的基表，而是从基表或其它视图导出的虚拟表。视图中并不存放数据，只存放对基表或其它视图的查询定义，因此，对视图的操作归根究底都是对基表的操作。8.1.1视图的概念8.1.2创建视图8.1.3修改视图8.1.4使用视图8.1.5查看视图8.1.6删除视图8.1.1视图的概念视图保存了对基表或其它视图的查询定义，其运行结果是一种来源于对基表的查询数据集，用户可以像对基本表一样对待视图。和真实的表一样，视图也包含一系列带有行和列的数据，但是这些数据并不真实地存储在视图中，而仍然存储在视图所引用的基表中。视图兼有表和查询的特点：与表相似的是，视图可以更新其中的数据，并将结果永久地存储在磁盘上；与查询相似的是，视图可以从一个或多个相关联的表或视图中提取数据。使用视图有很多优点，主要有以下4个方面。8.1.1视图的概念使用视图有很多优点，主要有以下4个方面。（1）简化数据操作：用户可以将频繁使用的复杂查询定义为视图，从而简化查询。（2）定制数据：通过视图，可以屏蔽数据的数据复杂性，用户不必了解数据库的全部数据结构，就可以操作和管理数据库中的同一数据。（3）分割数据：使用视图，可以逻辑上重构数据结构，但不破坏基表的原有结构，从而使原有的应用程序仍然可以通过视图来重载数据，而不需要做任何修改。（4）提高安全性：用户只能看到视图中的数据，不能看到基表中的数据，使用withcheckoption选项，可以确保用户只能查询和修改满足条件的数据，从而提高数据的安全性。8.1.2创建视图在创建视图时，需注意以下几点：（1）要创建视图，用户必须获取数据库所有者授权使用createview语句，并具有与定义视图有关的表或视图的相应权限。（2）只能在当前数据库中创建视图，但可以引用其它数据库中的表和视图，甚至可以是其它服务器上的表和视图。（3）一个视图最多可以引用1024个列，且这些列可以来自不同的表或视图。（4）在用select语句定义的视图中，如果在视图的基表中加入新列，则新列不会在视图中出现，除非先删除视图再重建它。（5）如果视图中的某一列是函数、数学表达式、常量或来自多表的同名列时，则必须为此列定义一个不同的名称。（6）即使删除了一个视图所依赖的表或视图，该视图的定义仍然保留在数据库中。8.1.2创建视图1．用视图设计器创建视图在SQLServer2005中，视图设计器的界面从上到下分为表区（又称关系图窗格）、列区、代码区和数据结果区4个区。初始状态时，表区中没有表。【例8-1】利用SSMS创建一视图，数据来源于“学生”表和“选修”表中学号、姓名、性别、课程号、成绩，且课程号限定为“01”。操作步骤如下所示：（1）启动SSMS，在“对象资源管理器”窗格中展开要创建视图的数据库（jxgl）节点，右击“视图”，弹出快捷菜单，选择“新建视图”命令；单击释放后，会打开“视图设计器”对话框和“添加表”对话框，如图8-1所示。8.1.2创建视图图8-1“视图设计器”和“添加表”对话框8.1.2创建视图（2）选择要添加的表、视图、函数或同义词，单击“添加”按钮，将其添加到“视图设计器”的“表区”中。这里添加“选修”表和“学生”表，然后单击“关闭”按钮，返回“视图设计器”。首先将学生.学号与选修.学号进行连接；其次在“表区”中选择需要的数据列，并设置相应的输出、排序类型、排序顺序和筛选器；最后单击“运行”按钮，将包含在视图中的数据行输出到“数据结果区”，如图8-2所示。注意：1）如需添加新的表、视图或函数，可以右击“表区”的空白区域，在弹出快捷菜单中选择“添加表”命令，弹出如图8-1所示的“添加表”对话框，继续添加表、视图或函数；2）如需移除已经添加的表、视图或函数，可以在“表区”中右击需要移除的表、视图或函数，在弹出快捷菜单中选择“移除”命令；3）如果多表之间没有建立关系连接，视图查询结果默认是交叉连接查询结果。8.1.2创建视图图8-2“视图设计器”对话框28.1.2创建视图（3）单击“保存”按钮，弹出“选择名称”对话框，输入视图名“View”，如图8-3所示，然后单击“关闭”按钮，完成视图创建。图8-3“视图设计器”对话框48.1.2创建视图2．用T-SQL语句创建视图用T-SQL语句创建视图的语法格式如下：createview[数据库拥有者.]视图名[(列名1,列名2[,…n])][with{encryption|schemabinging|view_metadata}]assql-select语句[withcheckoption]说明：（1）withencryption：在系统表syscomments中对createview语句进行加密；（2）withschemabinging：将视图与其所依赖的表或视图结构关联；（3）withview_metadata：指定引用视图的查询请求浏览模式的元数据时，向DBLIB、ODBC或OLEDBAPI返回有关视图的元数据信息，而不是返回给基表或其它表；（4）withcheckoption：限制在视图上的修改都要符合SQL语句中设置的条件；（5）sql-select语句中不能包含compute子句、computeby子句或into子句；（6）sql-select语句中不能包含orderby子句，除非另外指定top子句或forxml；（7）当存在计算列表达式、同名列、列别名时，必须指定视图中每列的名称。8.1.2创建视图【例8-2】创建一个视图，数据来源于“学生”表的学号、姓名、性别、籍贯、总分列数据，且学号前6位为“080201”。usejxglgocreateviewInformasselect学号,姓名,性别,籍贯,总分from学生whereleft(学号,6)='080201'8.1.2创建视图【例8-3】创建一个视图，其包含成绩不及格的课程名称及学生学号和姓名等信息。usejxglgocreateviewv_不及格asselect学生.学号,姓名,课程名称,成绩from学生,选修,课程where学生.学号=选修.学号and选修.课程号=课程.课程号and成绩608.1.3修改视图修改视图有两种方式：一是使用T-SQL语句，二是使用SSMS管理工具。1．使用T-SQL语句修改视图用T-SQL语句修改视图的语法格式如下：alterview[数据库拥有者.]视图名[with{encryption|schemabinging|view_metadata}]assql语句[withcheckoption]功能：对指定的视图进行修改。说明：（1）不论视图是否加密，均可修改；（2）各子句与创建视图的子句含义一样。8.1.3修改视图2．使用SSMS修改视图使用SSMS修改视图的步骤如下：（1）启动SSMS，在“对象资源管理器”窗格中依次展开SQLServer→“数据库”→“jxgl”→“视图”，右击需要修改的视图，在弹出快捷菜单中选择“设计”命令，单击释放后，会打开如图8-2所示的视图设计器。（2）在视图设计器中，用户可以对视图信息进行修改（修改视图方法与设计视图一样）。修改完成后，单击工具栏上的“保存”按钮即可。8.1.4使用视图视图是一个虚拟表，视图在使用上类似于表。利用视图不仅可以查询数据，还可以修改基表中的数据。要通过视图更新基表中的数据，必须保证视图是可更新视图。可更新视图必须满足以下条件：（1）视图中没有聚合函数，且没有top、groupby、union子句及distinct关键字；（2）视图的select语句不含从基本表列通过计算所得列；（3）在一个基表上建立的视图，只有包含基本表的主码才是可更新视图；（4）通过insteadof触发器创建的可更新视图；（5）利用视图修改数据时，一条语句不能影响到多个基表中的数据，可以通过多条语句多次修改的方式涉及多个表；（6）利用视图修改列值时，必须符合基表对列值的约束条件；（7）创建的视图的select语句的from子句至少包含一个基本表。（8）若视图定义中使用了withcheckoption子句，则对视图所执行的数据修改都必须符合视图中select语句所设定的条件；8.1.4使用视图1．查询视图视图的查询和表的查询一样，使用视图查询时，若其关联的基本表添加了新字段，则必须重新创建视图才能查询到新字段。【例8-4】查询视图Inform中的所有记录。select*fromInform2．插入数据当视图引用多个基表时，则向视图插入数据只能指定其中一个表的列。【例8-5】向视图Inform插入一个新的学生记录。记录数据如下：学号：'08020104'，姓名：'马后炮'，性别：'男'insertintoinform(学号,姓名,性别)values('08020104','马后炮','男')运行后，表“学生”和视图“inform”都会新增这条记录。8.1.4使用视图3．更新数据若一个视图（非分区视图）依赖多个基本表，则修改一次视图只能变动一个基本表的数据。而对于可更新的分区视图，则修改一次视图可以变动其依赖多个基本表。【例8-6】对视图Inform的新增的记录的性别修改为“女”。updateinformset性别='女'where学号='08020104'4．删除数据对于依赖多个基表的视图（不包括分区视图），不能使用delete语句。【例8-7】将视图新增的记录的记录删除。deleteinformwhere学号='08020104'8.1.5查看视图查看视图同样有种方法：一是利用SSMS，二是利用T-SQL语句。利用T-SQL语句查看视图命令主要有3个命令。（1）sp_depends：查看视图对象的参照对象和字段信息。（2）sp_help：查看数据库对象的数据类型信息。（3）sp_helptext：查看视图的详细定义文本信息。8.1.6删除视图删除视图同样有种方法：一是利用SSMS管理工具，二是利用T-SQL语句。利用T-SQL语句删除的视图语法格式如下：格式：dropview{视图名}说明：删除指定名称的视图。。8.2游标在数据库应用程序中，对数据行的操作有两种方式：一种是基于数据行集合的整体处理方式，由用户直接使用insert、update、delete等SQL语句操作符合条件的数据行；另一种是逐行处理数据行的方式，而游标就是这种数据访问机制，允许用户访问单独的数据行，而非整个数据行集。另外，用户也可以使用游标进行查询、更改和删除基表中的数据。游标在使用时共有5种状态，分别对应使用游标的5个步骤：即声明游标→打开游标→读取数据→关闭游标→删除游标。8.2.1游标的概念8.2.2游标的分类8.2.3声明游标8.2.4打开游标8.2.5读取游标8.2.6关闭游标8.2.7删除游标8.2.8更新和删除游标数据8.2.1游标的概念SQLSever通过游标实现了对一个结果集进行逐行处理的方式，游标可以被看作是一种与查询结果集相关联的特殊指针，指向单个行的位置实体，以便对指定位置的数据行进行处理，使用游标可以在查询数据的同时对数据进行处理。游标的完整结果集在游标打开时，临时存储在tempdb数据库中。游标主要功能如下：（1）允许定位在结果集的特定行；（2）从结果集的当前位置检索一行或多行；（3）支持对结果集中当前位置的行进行数据更新和删除；（4）如果其它用户需要对显示在结果集中的数据库数据进行修改，游标可以提供不同级别的可见性支持；（5）为脚本、存储过程和触发器提供访问游标结果集的Transact-SQL语句。8.2.2游标的分类游标分类方法很多，根据游标结果集是否允许被修改，游标分为只读游标和可写游标；根据游标在结果集中的移动方