《数据库第10章视图和用户定义函数课件》由会员分享，可在线阅读，更多相关《数据库第10章视图和用户定义函数课件（36页珍藏版）》请在金锄头文库上搜索。
1、第第10章章 视图和用户定义函数视图和用户定义函数数据库的基本表，是数据库设计人员按照专业观点设计的，但并不一定符合普通用户的需求。SQL Server 2012可以根据用户需求重新定义表的逻辑结构，这就是视图。SQL Server 2012还提供了用户定义函数，用于补充系统函数所没有的功能。本章主要介绍视图和用户定义函数的概念及使用。10.1 视图视图视图也是数据库的基本概念，几乎所有的数据库管理系统都引入了视图。在SQL Server 2012中，视图是一种数据库对象。10.1.1 视图概述视图概述1视图的优点视图的优点使用视图有很多优点，主要表现在：1）为用户集中数据，简化用户的数据查询
2、和处理。视图可以使用户只关心他感兴趣的某些特定数据，使得分散在多个表中的数据，通过视图定义在一起。2）简化操作，屏蔽了数据库的复杂性。3）重新定制数据，使得数据便于共享。3）合并分割数据，有利于数据输出到应用程序中。4）简化了用户权限的管理，增加了安全性。10.1.1 视图概述视图概述2视图的使用范围视图的使用范围视图通常用来集中、简化和自定义每个用户对数据库的不同认识。通常在以下情况下使用视图：1）着重于特定数据。视图使用户能够着重于他们感兴趣的特定数据和所负责的特定任务。不必要的数据或敏感数据可以不出现在视图中。2）简化数据操作。视图可以简化用户处理数据的方式。可以将常用连接、投影、UNI
3、ON查询和SELECT查询定义为视图，用户不必在每次对该数据执行附加操作时指定所有条件和条件限定。3）自定义数据。视图允许用户以不同方式查看数据，即使在他们同时使用相同的数据时也是如此。4）数据的导入与导出。可使用视图将数据导出到其他应用程序。5）跨服务器组合分区数据库。10.1.2 视图的类型视图的类型在SQL Server 2012中，视图可以分为标准视图、索引视图、分区视图和系统视图。1标准视图标准视图标准视图组合了一个或多个表中的数据，用户可以使用标准视图对数据库进行查询、修改、删除等基本操作，是用户使用频率最高的一种视图。标准视图可以获得使用视图的大多数优点。2索引视图索引视图索引视
4、图是被具体化了的视图，即它已经过计算并存储。可以为视图创建索引，即对视图创建一个唯一的聚集索引。索引视图可以显著提高某些类型查询的性能。索引视图尤其适于聚合许多行的查询。但它们不太适于经常更新的基本数据集。3分区视图分区视图分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。这样，数据看上去如同来自于一个表。连接同一个SQL Server实例中的成员表的视图是一个本地分区视图。如果视图在服务器间连接表中的数据，则它是分布式分区视图。4系统视图系统视图系统视图公开了目录元数据。用户可以使用系统视图返回与SQL Server实例或在该实例中定义的对象有关的信息。SQL Server提供的公
5、开元数据的系统视图集合包括目录视图、兼容性视图、信息架构视图、兼容性视图、复制视图等。在这4种视图中，标准视图是最常用的，而且使用范围也最广。10.1.3 创建视图准则创建视图准则在创建视图前应考虑如下准则：1）只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库，甚至其他服务器中。2）视图名称必须遵循标识符的规则，且在每个数据库中都必须惟一。此外，该名称不得与该当前数据库中任何表的名称相同。3）用户可以对其他视图创建视图。SQL Server 2012允许嵌套视图。但嵌套不得超过32层。根据视图的复杂性及可用内存，视图嵌套的实际限制可能低
6、于该值。4）不能将规则或默认约束与视图相关联。5）不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联。6）定义视图的查询不能包含COMPUTE子句、COMPUTE BY子句或INTO关键字。7）定义视图的查询不能包含ORDER BY子句，除非在SELECT语句的选择列表中还有一个TOP子句。10.1.3 创建视图准则创建视图准则8）定义视图的查询不能包含指定查询提示的OPTION子句。9）定义视图的查询不能包含TABLESAMPLE子句。10）不能为视图定义全文索引定义。11）不能创建临时视图，也不能对临时表创建视图。12）不能删除参与到使用SCHEMABINDI
7、NG子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行ALTER TABLE语句，而这些语句又会影响该视图的定义，则这些语句将会失败。13）下列情况下必须指定视图中每列的名称： 视图中的任何列都是从算术表达式、内置函数或常量派生而来。 视图中有两列或多列源应具有相同名称。 希望为视图中的列指定一个与其源列不同的名称。10.1.4 创建视图创建视图在SQL Server 2012中，创建标准视图就如同创建表。1管理工具界面方式创建视图管理工具界面方式创建视图使用创建视图向导创建视图是一种最直观、最方便快捷的方式。展开COLLE
8、GE数据库，单击“视图”选项，选择右键菜单“新建视图”选项。在弹出的“添加表”对话框，用户可以选择需要添加的表、视图、函数和同义词。10.1.4 创建视图创建视图添加完毕，进入“视图设计器”窗口。该窗口又分为多个子窗口。通常，最上边部分是关系图子窗口，如同数据库的关系图，显示所有添加表的结构及它们之间的关系。中间部分是条件子窗口，用户可以选择视图操作涉及的列的列名、别名、表名、顺序类型等。下边部分是SQL语句子窗口，显示用户的设置相应的T-SQL语句代码。当执行视图时，最下边的查询结果子窗口显示视图的查询结果。10.1.4 创建视图创建视图例如，现在创建一个查询所有学生选修课程成绩信息的视图，
9、包含Name、CourseName、Score列。用户在关系图子窗口中选择Student表的Name列、Course表的CourseName列、Mark表的Score列，被选中的列名左边显示对勾。在关系图子窗口中选择操作的同时，视图设计器会自动在条件子窗口中设置对应的选择，SQL语句子窗口会自动生成对应的T-SQL语句。当然用户也可以自行设置条件子窗口中的选项，可以选择排序类型等。用户也可以在SQL语句子窗口中修改SQL语句子窗口中的T-SQL语句。操作完毕，点击运行按钮，即可在查询结果子窗口看到视图的结果显示。视图创建完毕，给视图命名“View_Score”，并存盘退出。用户可以在“视图”选
10、项中查看该视图对象。10.1.4 创建视图创建视图2命令行方式创建视图命令行方式创建视图T-SQL提供了CREATE VIEW语句创建一个视图。其语法格式如下：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION CREATE VIEW语句语法说明：1）schema_name用于指定视图的所有者，包括数据库名、所有者名、视图名。2）view_name用于指定视图的名字。3）column用于指定视图中包括的基本表的列。4）WITH 用于指定视图的属性。视
11、图属性包括ENCRYPTION（存储视图语句时是否加密）、SCHEMABINDING（是否显示视图关联）、VIEW_METADATA（指定返回结果是否是元数据）。5）AS select_statement就是用于创建视图的SELECT语句。10.1.4 创建视图创建视图【例10-1】 在COLLEGE数据库中创建视图，查询计算机学院学生们的选修课程成绩信息。USE COLLEGEGOCREATE VIEW VIEW_Score_COMPUTER(学号,姓名,学院名,课程名,分数)ASSELECT Student.StudentID,Student.Name,School.SchoolName,
12、Course.CourseName,Mark.ScoreFROM Student,School,Course,MarkWHERE Student.StudentID=Mark.StudentIDAND Course.CourseID=Mark.CourseIDAND Student.SchoolID=School.SchoolIDAND SchoolName=计算机学院GO10.1.4 创建视图创建视图【例10-2】 在COLLEGE数据库中创建视图，查询所有选修有课程的学生们的总分和平均分。USE COLLEGEGOCREATE VIEW VIEW_Score_AVG_SUM(姓名,平均分,
13、总分)ASSELECT Student.Name,AVG(Score),SUM(Score)FROM Student,Course,MarkWHERE Student.StudentID=Mark.StudentIDAND Course.CourseID=Mark.CourseIDGROUP BY Student.NameGO10.1.5 查询视图查询视图视图创建完毕，就可以如同查询基本表一样查询视图了。展开COLLEGE数据库的“视图”选项，单击查询的视图，选择右键菜单“编辑前200行”选项就可以查询视图。10.1.5 查询视图查询视图也可以执行T-SQL语句查询视图。如果某个视图依赖于已删
14、除的表（或视图），则当用户试图使用该视图时，数据库引擎将产生错误消息。如果创建了新表或视图（该表的结构与以前的基表没有不同之处）以替换删除的表或视图，则视图将再次可用。如果新表或视图的结构发生更改，则必须删除并重新创建该视图。【例10-3】 查询VIEW_Score_COMPUTER视图。USE COLLEGEGOSELECT *FROM VIEW_Score_COMPUTERWHERE 分数=90GO10.1.6 可更新视图可更新视图只要满足下列条件，即可通过视图修改基本表的数据：1）任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基本表的列。2）在视图中修改的列必
15、须直接引用表列中的基础数据。它们不能通过其他方式派生，例如通过聚合函数（AVG、COUNT、SUM、MIN、MAX、GROUPING等）计算，不能通过表达式并使用列计算出其他列。使用集合运算符（UNION、UNION ALL、CROSSJOIN、EXCEPT和INTERSECT）形成的列得出的计算结果不可更新。3）被修改的列不受GROUP BY、TOP、HAVING或DISTINCT子句的影响。上述限制适用于视图的FROM子句中的任何子查询，就像其应用于视图本身一样。能够修改表数据的视图称为可更新视图。例如，【例10-1】创建的视图是可更新的视图，而【例10-2】创建的视图不是可更新的视图。1
16、0.1.6 可更新视图可更新视图若要对SQL Server 2012的视图进行表数据更新，可以在视图中直接操作，就像在基本表中操作一样。即使是可更新视图，也不能随意更新数据，一定要符合更新规则，否则系统提示出错。如果视图所依赖的基本表有多个时，不能向该视图添加数据，因为这将影响多个基本表。修改数据时，若视图依赖于多个基本表，那么一次只能修改一个基本表中的数据。删除数据时，若视图依赖于多个基本表，就不能通过视图删除数据。就算更新时不会提示出错，也有可能会造成逻辑错误。所以，如果用户一定要通过视图更新基本表数据，必须谨慎。也可以通过执行T-SQL语句更新视图。【例10-4】 通过VIEW_Scor
17、e_COMPUTER视图，修改刘雨航大学物理的分数。USE COLLEGEGOUPDATE VIEW_Score_COMPUTERSET 分数=分数+2WHERE 姓名=刘雨航 AND 课程名=大学物理GO10.1.6 可更新视图可更新视图【例10-5】 通过VIEW_Score_AVG_SUM视图，修改刘雨航的总分和平均分。USE COLLEGEGOUPDATE VIEW_Score_AVG_SUMSET 平均分=平均分+2WHERE 姓名=刘雨航GO10.1.7 修改视图定义修改视图定义虽然视图和表从查询结果看差不多，但修改视图定义与修改基本表结构不一样。修改视图定义是指修改视图的指定列的
18、列名、别名、表名、是否输出、顺序类型等属性。修改视图定义时，可以通过“视图设计器”窗口修改。单击要修改的视图，选择右键菜单“设计”选项。进入“视图设计器”窗口后修改。修改视图操作和创建视图操作相同。10.1.7 修改视图定义修改视图定义也可以通过T-SQL语句修改视图定义。T-SQL提供了视图修改语句ALTER VIEW。其语法格式如下：ALTER VIEW schema_name . view_name ( column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION 【例10-6】 修改VIEW_Score_COMPUTER视图
19、，不再是查询计算机学院的学生成绩信息，而是物理学院学生的成绩信息。USE COLLEGEGOALTER VIEW VIEW_Score_COMPUTER(学号,姓名,学院名,课程名,分数)ASSELECT Student.StudentID,Student.Name,School.SchoolName,Course.CourseName,Mark.ScoreFROM Student,School,Course,MarkWHERE Student.StudentID=Mark.StudentIDAND Course.CourseID=Mark.CourseIDAND Student.School
20、ID=School.SchoolIDAND SchoolName=物理学院GO10.1.8 删除视图删除视图如果不需要视图，则可以将视图删除。单击要删除的视图，选择右键菜单“删除”选项，即可删除视图。也可以通过T-SQL语句删除视图。T-SQL提供了视图删除语句DROP VIEW。其语法格式如下：DROP VIEW view_name【例10-7】 删除VIEW_Score_AVG_SUM视图。DROP VIEW VIEW_Score_AVG_SUMGO删除视图不会影响基本表结构和数据，也不会影响对的表的其它操作。10.2 用户定义函数用户定义函数创建用户定义函数，T-SQL提供了CREATE
21、 FUNCTION语句创建。也可以在“对象资源管理器窗口”中，选择相应的函数创建。选择创建后，打开一个“查询编辑器”窗口，里面已经存在有一个函数模板程序。但最终还是要在“查询编辑器”窗口中使用T-SQL语句来详细创建。10.2.1 标量值函数标量值函数在SQL Server 2012中，T-SQL提供了CREATE FUNCTION语句创建标量值函数。其语法格式如下：CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default READ
22、ONLY ,.n )RETURNS return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expression END10.2.1 标量值函数标量值函数CREATE FUNCTION语句创建标量值函数语法说明：1）function_name是所创建标量值函数的函数名。2）parameter_name是用户定义函数中的参数。可声明一个或多个参数。3）default是参数的默认值。4）READONLY指示不能在函数定义中更新或修改参数。如果参数类型为用户定义的表类型，则应指定READONLY。5）RETURNS是返回值的数据类
23、型。6）AS后面是函数主体语句。7）RETURN是函数本身返回值。10.2.1 标量值函数标量值函数【例10-8】 创建标量值函数FUN_SUM，函数返回两个数的和。USE COLLEGEGOCREATE FUNCTION FUN_SUM(i INT,j INT)RETURNS INTASBEGIN DECLARE s INT SET s=i+j RETURN sENDGO用户可以在该数据库的“可编程性”选项的“函数”子选项中的“标量值函数”子选项中查看到。该函数名前系统自动加上“dbo.”作为该函数名的前缀，即该函数的所有者是当前数据库。10.2.1 标量值函数标量值函数【例10-9】 调用
24、FUN_SUM函数，求两个数的和。USE COLLEGEGODECLARE m INTDECLARE n INTSET m=10SET n=20SELECT dbo.FUN_SUM(m,n)GO10.2.1 标量值函数标量值函数【例10-10】 创建标量值函数FUN_AVGScore，代入某课程号，函数返回某门课程的平均分。USE COLLEGEGOCREATE FUNCTION FUN_AVGScore(CID INT)RETURNS FLOATASBEGIN DECLARE avgscore FLOAT SELECT avgscore=AVG(Score) FROM Mark WHERE
25、CourseID=CID GROUP BY CourseID RETURN avgscoreENDGO创建完成后，调用该函数。USE COLLEGEGOSELECT dbo.FUN_AVGScore(021)GO10.2.2 内嵌表值函数内嵌表值函数在SQL Server 2012中，T-SQL提供了CREATE FUNCTION语句创建内嵌表值函数。其语法格式如下：CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default READ
26、ONLY ,.n )RETURNS TABLE WITH ,.n AS RETURN ( select_stmt ) CREATE FUNCTION语句创建内嵌表值函数语法说明：1）RETURNS返回的数据类型是TABLE类型。2）RETURN后是SELECT语句。select_stmt定义内联表值函数返回值的单个SELECT语句。3）TABLE指定表值函数的返回值为表。只有常量和局部变量可以传递到表值函数。在内嵌表值函数中，TABLE返回值是通过单个SELECT语句定义的。内嵌函数没有关联的返回变量。10.2.2 内嵌表值函数内嵌表值函数【例10-11】 创建内嵌表值函数，代入某图书类型号编
27、号，函数返回该类图书信息。USE PUBLISHGOCREATE FUNCTION FUN_Book(Bid INT)RETURNS TABLEASRETURN( SELECT BookID,BookName FROM Book WHERE TypeID=Bid)GO创建完成后，调用该函数。USE PUBLISHGOSELECT * FROM dbo.FUN_Book (1)GO结果显示类型编号为“1”号的图书名称。10.2.3 多语句表值函数多语句表值函数在SQL Server 2012中，T-SQL提供了CREATE FUNCTION语句创建多语句表值函数。其语法格式如下：CREATE F
28、UNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default READONLY ,.n )RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURN ENDCREATE FUNCTION语句创建多语句表值函数语法说明：1）return_variable是TABLE变量，用于存储和累积应作为函数值返回的行。只能将return_variable指定用于T-SQL函数。2）RETU
29、RN后没有语句。10.2.3 多语句表值函数多语句表值函数【例10-12】 创建多语句表值函数USE COLLEGEGOCREATE FUNCTION FUN_Sch(schname NCHAR(20)RETURNS stuSch TABLE( stuid INT PRIMARY KEY NOT NULL, stuName NCHAR(10) NOT NULL, stuSex NCHAR(2) NOT NULL)ASBEGIN INSERT stuSch SELECT StudentID,Name,Sex FROM Student WHERE SchoolID= ( SELECT School
30、ID FROM School WHERE SchoolName=schname ) RETURN END GO10.2.3 多语句表值函数多语句表值函数调用该函数。USE COLLEGEGOSELECT *FROM dbo.FUN_Sch(计算机学院)GO结果显示计算机学院学生的信息。10.2.4 修改和重命名用户定义函数修改和重命名用户定义函数与系统函数不同，用户定义函数可以随时修改和重命名。修改和重命名可以使用管理工具界面方式，也可使用命令行方式。1. 管理工具界面方式管理工具界面方式单击需要修改的用户定义函数，选择右键菜单“重命名”选项，即可重命名函数。选择右键菜单“修改”选项。即可进入
31、“查询编辑器”窗口修改函数。10.2.4 修改和重命名用户定义函数修改和重命名用户定义函数2. 命令行方式命令行方式T-SQL提供了ALTER FUNCTION语句修改用户定义函数。ALTER FUNCTION语句的使用方法与CREATE FUNCTION语句相似。T-SQL还提供了sp_rename系统存储过程重命名函数。例如重命名dbo.FUN_Sch函数：EXEC sp_rename dbo.FUN_Sch,dbo.FUN_Sch110.2.5 删除用户定义函数删除用户定义函数与系统函数不同，用户定义函数还可以在不需要时删除。删除也可以使用管理工具界面方式和命令行方式。单击需要删除的用户定义函数，选择右键菜单“删除”选项即可。也可使用T-SQL提供的DROP FUNCTION语句删除用户定义函数。例如删除dbo.FUN_Sch函数：DROP FUNCTION dbo.FUN_Sch
