第七讲(第五章视图)

数据库原理与应用 第五章视图 存储过程和用户自定义函数 5 1视图 5 1 1视图的概念虚表 是从一个或几个基本表 或视图 导出的表数据库中只存放视图的定义 不存放视图对应的数据基表中的数据发生变化 从视图中查询出的数据也随之改变 基于视图的操作查询 删除 受限更新 基于视图定义新视图 5 1 2定义视图 语句格式CREATEVIEW AS 注意 子查询通常不包含ORDERBY和DISTINCT子句组成视图的属性列名 全部省略或全部指定 5 1 2定义视图 续 RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典 并不执行其中的SELECT语句 在对视图查询时 按视图的定义从基本表中将数据查出 5 1 2定义视图 续 1 定义单源表视图 例 建立信息系学生的视图 CREATEVIEWIS StudentASSELECTSno Sname SageFROMStudentWHERESdept 信息系 5 1 2定义视图 续 对IS Student视图的更新操作 修改操作 自动加上Sdept 信息系 的条件删除操作 自动加上Sdept 信息系 的条件插入操作 自动检查Sdept属性值是否为 信息系 如果不是 则拒绝该插入操作如果没有提供Sdept属性值 则自动定义Sdept为 信息系 5 1 2定义视图 续 2 定义多源表的视图 例 建立信息系选修了1号课程的学生视图 CREATEVIEWIS S1 Sno Sname Grade ASSELECTS Sno Sname SageFROMStudentSJOINSCONS Sno SC SnoWhereSdept 信息系 AndSC Cno C01 5 1 2定义视图 续 CREATEVIEWIS S1 Sno Sname Grade ASSELECTStudent Sno Sname GradeFROMStudent SCWHERESdept 信息系 ANDStudent Sno SC SnoANDSC Cno 1 5 1 2定义视图 续 3 基于视图的视图 例 建立信息系选修了1号课程且成绩在90分以上的学生的视图 CREATEVIEWIS S2ASSELECTSno Sname GradeFROMIS S1WHEREGrade 90 5 1 2定义视图 续 4 带表达式的视图 例 定义一个反映学生出生年份的视图 CREATEVIEWBT S Sno Sname Sbirth ASSELECTSno Sname 2007 SageFROMStudent 5 1 2定义视图 续 5 含分组统计信息的视图 例 将学生的学号及他的平均成绩定义为一个视图CREATEVIEWS G Sno Gavg ASSELECTSno AVG Grade FROMSCGROUPBYSno 5 1 3删除视图 语句的格式 DROPVIEW 该语句从数据字典中删除指定的视图定义在删除视图时一定要注意是否有引用被删除对象的视图 如果有则应同时删除删除基表时 由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除 5 1 4视图的作用 简化数据查询语句可以使用户将注意力集中在所关心的数据上使用户能从多角度看待同一数据提高了数据的安全性可以定制用户能查看哪些数据并屏蔽掉敏感的数据提供了一定程度的逻辑独立性 5 2存储过程 存储过程是SQL语句和控制流语句的预编译集合 它以一个名称存储并作为一个单元处理 应用程序可以通过调用来执行存储过程 存储过程可以使用户对数据库的管理和操作更容易 效率更高 5 2 1存储过程的概念 SQL语言是应用程序和SQLServer数据库之间的主要编程接口 两种方式 客户端存储代码存储过程存储过程的优点 允许进行模块化程序设计改善性能减少网络流量提供安全机制简化管理和操作 5 2 2创建和执行存储过程 语句格式 CREATEProc edure 存储过程名 参数名数据类型 default output AsSql语句 n 语句格式 exec ute 存储过程名 实参 output n 5 2 2创建和执行存储过程 续 1 创建不带参数的存储过程 例 查询计算机系学生的考试成绩 列出学生的姓名 课程名和成绩 createprocedurestudent grade1asselectsname cname gradefromstudentsinnerjoinscons sno sc snoinnerjoincourseco owheresdept 计算机系 executestudent grade1 5 2 2创建和执行存储过程 续 2 创建带有输入参数的存储过程 例 查询某个指定系学生的考试成绩 列出学生的姓名 所在系 课程名和成绩 createprocedurestudent grade2 sdeptchar 20 asselectsname sdept cname gradefromstudentsinnerjoinscons sno sc snoinnerjoincourseco owheresdept sdeptexecutestudent grade2 信息系 5 2 2创建和执行存储过程 续 3 创建带有多个输入参数的存储过程 例 查询某个学生某门课程的考试成绩 列出学生的姓名 课程名和成绩 createprocedurestudent grade3 snamechar 10 cnamechar 20 asselectsname cname gradefromstudentsinnerjoinscons sno sc snoinnerjoincourseco owheresname snameandcname cnameexecutestudent grade3 刘晨 VB executestudent grade3 sname 刘晨 cname VB 5 2 2创建和执行存储过程 续 4 创建带有多个输入参数并有默认值的存储过程 例 查询某个学生某门课程的考试成绩 若没有指定课程 则默认课程为 数据库基础 createprocedurestudent grade4 snamechar 10 cnamechar 20 数据库基础 asselectsname sdept cname gradefromstudentsinnerjoinscons sno sc snoo owheresname snameandcname cnameexecutestudent grade4 吴宾 数据库基础 5 2 2创建和执行存储过程 续 5 创建带有多个输入参数并均指定默认值的存储过程 例 查询指定系 指定性别的学生中年龄大于等于指定年龄的学生的情况 系的默认值为 计算机系 性别的默认值为 男生 年龄的默认值为20 createprocp student deptchar 20 计算机系 sexchar 2 男 ageint 20asselect fromstudentwheresdept deptandssex sexandsage ageexecutep studentexecutep student 信息系 女 19executep student age 18 5 2 2创建和执行存储过程 续 6 创建带输出参数的存储过程 例 createprocsum var1int var2int var3intoutputasset var3 var1 var2declare resintexecsum10 20 resoutputprint res 5 2 2创建和执行存储过程 续 7 创建带输入参数和一个输出参数的存储过程 例 统计指定课程的平均成绩 并将统计的结果用输出参数返回 createprocavg grade cnochar 20 avg gradeintoutputasselect avg grade avg grade fromsco owherecname cnodeclare resintexecavg grade 数据库基础 resoutputprint res 5 2 2创建和执行存储过程 续 8 创建带删除数据的存储过程 例 删除考试成绩不及格学生的修课记录 createprocp deleteasdeletefromscwheregrade 609 创建带修改数据的存储过程 例 将指定的课程的学分增加2分createprocp update cnchar 20 asupdatecoursesetccredit ccredit 2wherecname cn 5 3用户自定义函数 5 3 1函数的概念函数 由一个或多个SQL语句组成的子程序 可用于封装代码以提高代码共享的功能 函数分类 内置函数 用户自定义函数SQLServer2000支持的三种用户自定义函数 标量函数 内嵌表值函数 多语句表值函数 5 3 2创建和调用标量函数 定义标量函数 返回单个数据值 语法 Createfunction 拥有者名 函数名 参数名 as 标量数据类型 default n Returns返回值类型 as Begin函数体return标量表达式end 5 3 2创建和调用标量函数 续 例 创建求立方体体积的函数createfunctiondbo cubeVolume cubeLengthint cubeWidthint cubeHeightint returnsintasbeginreturn cubeLength cubeWidth cubeHeight endselectdbo cubeVolume 4 6 8 5 3 2创建和调用标量函数 续 例 创建统计指定课程的选课人数的函数createfunctiondbo f count cnamechar 20 returnsintasbegindeclare xintselect x count o owherecname cnamereturn xendselectdbo f count 数据库基础 selectcname dbo f count 数据库基础 as选课人数fromcoursewherecname 数据库基础 5 3 3创建和调用内嵌表值函数 定义内嵌表值函数 返回值是表 语法 Createfunction 拥有者名 函数名 参数名 as 标量数据类型 default n Returnstable as return select语句 Table 指定内嵌表值函数的返回值为表Select语句 定义内嵌表值函数返回值的单个select语句 5 3 3创建和调用内嵌表值函数 续 例 创建查询指定系的学生的姓名 年龄和性别的函数createfunctiondbo f sdept sdeptchar 20 returnstableasreturn selectsname sage ssexfromstudentwheresdept sdept select fromdbo f sdept 信息系 思考 创建查询指定课程中成绩大于指定分数的学生的姓名 所在系和这门课程的考试成绩 5 3 4创建和调用多语句表值函数 定义多语句表值函数 返回值是表 语法 Createfunction 拥有者名 函数名 参数名 as 标量数据类型 default n Returns返回变量table as begin函数体returnEnd 列定义 表约束 n 5 3 4创建和调用多语句表值函数 续 例 创建返回考试成绩为指定等级的学生的学号 姓名 课程名和考试成绩的多语句表值函数createfunctiondbo f gradelevel levelchar 6 returns gradeLeveltable snochar 7 snamechar 8 cnamevarchar 20 gradeint asbeginif level 优秀 insertinto gradeLevelselects sno sname cname gradefromstudentsjoinscon