《数据库系统概念6版高级SQL.ppt》由会员分享，可在线阅读，更多相关《数据库系统概念6版高级SQL.ppt（73页珍藏版）》请在三一办公上搜索。
1、DATABASE SYSTEM CONCEPTS,第5章 高级SQL,4.4 嵌入式SQL,为什么使用嵌入式SQL？嵌入式SQL执行过程需要解决的几个问题,为什么使用嵌入式SQL？,有些操作对于交互式SQL是不可能的任务SQL的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用SQL无法完成。一方面，SQL在逐渐增强自己的表达能力（参见习题4.10），另一方面，太多的扩展会导致优化能力及执行效率的降低非声明性动作实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现,嵌入式SQL执行过程,需要解决的几个问题,区分SQL语句与C语
2、言语句嵌入的SQL语句以EXEC SQL开始，以分号(;)或END_EXEC结束EXEC SQL delete from PROF where DNO=10；,需要解决的几个问题,嵌入SQL语句与C语言之间的数据传递宿主变量C变量，既可以用在C语句中，也可用在SQL语句中，用来在两者之间传递数据宿主变量的声明声明为通常的C变量，并将其放在下列标识语句之间EXEC SQL BEGIN DECLARE SECTIONEXEC SQL END DECLARE SECTION,需要解决的几个问题,EXEC SQL BEGIN DECLARE SECTIONint prof_no;char prof_n
3、ame30;int salary;EXEC SQL END DECLARE SECTION,需要解决的几个问题,注：宿主变量出现于SQL语句中时，前面加（:）以区别列名 宿主变量可出现的地方：SQL的数据操纵语句中可出现常数的任何地方，select，fetch等语句的into子句中示例：EXEC SQL select PNAME,SAL into:prof_name,:salary from PROF where PNO=:prof_no;,需要解决的几个问题,SQL与主语言之间操作方式的协调执行方式的差别SQL：一次一集合C语言：一次一记录游标在查询结果的记录集合中移动的指针若一个SQL语句
4、返回单个元组，则不用游标若一个SQL语句返回多个元组，则使用游标,需要解决的几个问题,不需要游标的数据操作结果是一个元组的select语句EXEC SQL select PNAME,SALinto:prof_name,:salary from PROFwhere PNO=:prof_no;,需要解决的几个问题,insert语句 EXEC SQL insert into PROF values(:prof_no,:prof_name,:salary,:dept_no,:salary);delete语句 EXEC SQL delete from PROF where PNO:prof_no;upd
5、ate语句 EXEC SQL update PROF set SAL=:salary where PNO=:prof_no;,需要解决的几个问题,需要游标的数据操作当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组活动集：select语句返回的元组的集合当前行：活动集中当前处理的那一行。游标即是指向当前行的指针,需要解决的几个问题,定义与使用游标的语句declare定义一个游标，使之对应一个select语句 declare 游标名 cursor forselect语句for update of列表名for update：该游标可用于对当前行的修改删除open打开游标，执行对
6、应的查询，结果集为该游标的活动集open 游标名,需要解决的几个问题,fetch在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中fetch 游标名 into 宿主变量表close关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句close 游标名,需要解决的几个问题,推进游标指针EXEC SQL FETCH INTO，.FETCH语句通常用在一个循环结构中，逐条取出结果集中的记录进行处理。许多关系数据库管理系统对FETCH语句做了扩充，允许向任意方向以任意步长移动游标指针。关闭游标EXEC SQL CLOSE释放缓冲区及其他资源。,需要解决的几个问题,说明游
7、标EXEC SQL DECLARE CURSOR FOR 其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。打开游标EXEC SQL OPEN执行相应的SELECT语句，把查询记录取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中第一条记录。,需要解决的几个问题,SQL语句执行信息反馈良好的应用程序必须提供对错误的处理,应用程序需要知道SQL语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息SQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行情况在其执行完成后写入SQLCA结构中的各变量中,根据SQLCA中的内容可以获得每一嵌入SQL语句执行
8、后的信息,应用程序就可以做相应的处理在PB中，一个SQL语句执行结束后，SQLCA.SQLCODE=0，100，-1分别表示执行成功，未查找到符合条件的元组，执行出错,PB中的游标,Declare cur_a cursor for Select a,b,c from;Open cur_a;If sqlca.sqlcode0 then returnDo while true Fetch cur_a into:va,:vb,:vc;If sqlca.sqlcode=100 then exit If sqlca.sqlcode=-1 then return LoopClose cur_a;If sq
9、lca.sqlcode0 then return,嵌入式 SQL 编程,嵌入式SQL语句与主语言之间的通信过程1数据库工作单元与源程序工作单元之间通信主要包括：向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程。主语言向SQL语句提供参数。将SQL语句查询数据库的结果交主语言进一步处理。,嵌入式 SQL 编程,2嵌入式SQL与主语言之间通信的实现SQL语句通过SQL通信区SQLCA(SQL communiction area)向主语言传递SQL执行状态信息。主语言通过自己的主变量(host vaiable)向SQL语句输入数据参数。两者数据传送通过主变量和游标(crusor)实
10、现。,嵌入式 SQL 编程,3SQL通信区SQL语句执行后，系统将当前工作状态送到SQL通信区SQLCA中。应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句。主语言程序中用EXEC SQL INCLUDE SQLCA定义通信区。SQLCA中有一个返回代码的变量SQLCODE。执行完一条SQL语句后可以测试一下SQLCODE的值，如果是SUCCESS，则表示SQL语句成功，否则表示错误。,嵌入式 SQL 编程,4主变量在SQL语句中使用的主语言程序的变量称为主变量。输入主变量由主语言程序对其赋值，SQL语句引用。输出主变量由SQL语句对其赋值，返回给主语言程序。一个主变量附带一个
11、任选的指示变量,整型变量，指示主变量的值或条件。输入主变量可以利用指示变量赋空值，输出主变量可以利用指示变量检测出是否空值，值是否被截断。,嵌入式 SQL 编程举例,例 查询某个系全体学生的信息。系名在主程序中指定。EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION CHAR deptname(20)CHAR HSno(6)CHAR HSnmae(8)CHAR HSsex(2),嵌入式 SQL 编程举例,CHAR HSdept(20)INT HSageEXEC SQL END DECLARE SECTION;main()WHILE(get
12、s(deptname!=NULL),嵌入式 SQL 编程举例,EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Sdept=:deptname EXEC SQL OPEN SX WHILE(1)EXEC SQL FETCH SX INTO,嵌入式 SQL 编程举例,:HSno,:HSname,:HSsex,:Hsage,:HSdept if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsa
13、ge,HSdept);,嵌入式 SQL 编程举例,EXEC SQL CLOSE SX;End;例 修改学生的年龄。.main()Char yn(1);Int Newage;,嵌入式 SQL 编程举例,gets(deptname);EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Sdept=:deptname FOR UPDATE OF Sage;EXEC SQL OPEN SX;WHILE(1),嵌入式 SQL 编程举例,EXEC SQL FETCH SX INTO:HSno,
14、:HSname,:HSsex,:Hsage,:HSdept if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsage,HSdept);printf(“修改该学生的年龄？”);,嵌入式 SQL 编程举例,scanf(“%c”,嵌入式 SQL 编程举例,;EXEC SQL CLOSE SX;End;UPDATE语句分为CURRENT形式和非CURRENT形式，非CURRENT形式是集合操作，一次修改所有满足条件的记录。CURRENT形式只修改其中某个记录。,嵌入式 SQL 编程举例,例 删除某些学生的记录
15、。.EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage FROM Student WHERE SDept=:deptname;EXEC SQL OPEN SX WHILE(1),嵌入式 SQL 编程举例,EXEC SQL FETCH SX INTO:HSno,:HSname,:HSsex,:HSage，:HSdept;if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsage,HSdept)printf(删除该记录?);,嵌入式 SQL 编
16、程举例,scanf(%c,.,5.3 触发器,触发器：触发器是一条语句，当对数据库做修改时，它自动被系统执行触发器的定义指明什么条件下触发器被执行指明触发器执行的动作是什么触发器的作用示警满足特定条件时自动执行某项任务触发器事件Insert、delete、update,触发器定义,触发器举例,EMP(ENO,ENAME,SAL,JOB)职工工资增幅不得超过10%create trigger RAISE_LIMIT after update of SAL on EMPreferencing new row as nrow old row as orowfor each rowwhen(nrow.
17、SAL 1.1*orow.SAL)begin atomicsignal SQLSTATE(7500,Salary increase 10%)end,触发器举例,当帐户透支时，将帐户余额设为0，并建一笔贷款，其金额为透支额create trigger overdraft-trigger after update on accountreferencing new row as nrow for each rowwhen nrow.balance 0begin atomic insert into borrower(select customer-name,account-number from
18、depositor where nrow.account-number=depositor.account-number);insert into loan values(nrow.account-number,nrow.branch-name,nrow.balance);update account set balance=0where account.account-number=nrow.account-numberend,5.6 OLAP,数据仓库的主要任务是OLAP（联机分析处理）OLTP面向客户、办事员、操作员的事务和查询处理OLAP面向经理、主管、分析人员的数据分析OLTP处理的
19、数据是基本表中的元组数据OLAP处理的数据是不同粒度的汇总数据,OLAP综述,OLTP采用E-R模型和面向应用的数据库设计 OLAP采用星型或雪花模型和面向主题的多维数据立方体设计 OLTP主要功能是DBMSOLAP主要功能是检索查询工具，多维数据分析工具、统计分析及数据挖掘工具,多维数据模型,OLAP基于多维数据模型-数据立方体,三季度,一季度,二季度,宁波,四季度,杭州,济南,莱芜,计算机,电话,电视,音响,605,825,872,970,多维数据模型,数据立方体允许以多维对数据建模和观察，由维表和事实表组成例：商品销售立方体维表：时间维表、商品维表、商店维表、地区维表事实表：各维表的码和
20、度量（销售金额、销售量）,多维数据模型,多维数据模型分为星型模型和雪花模型,商店维,时间维,销售事实表,地区维,商品维,多维数据模型,商店维,时间维,销售事实表,地区维,商品维,城市键城市省国家,供应键供应商类型,多维数据模型,常用的多维数据模型，见曹译本产品缺陷与质量分析商标管理-促销分析咨询服务-费用分析咨询服务-使用分析金融-帐目分析金融-信贷分析,多维数据模型,保健服务使用人力资源雇员停工期和调动人力资源-工资津贴分析生产-循环时间生产-库存事务处理分析生产-劳动时间分析生产-定期发货分析,多维数据模型,生产-产品成本分析生产-供应能力分析产品管理-市场分析销售-顾客人口统计分析销售-
21、商品供应线管理分析销售-销售分析销售-软件产品销售分析,多维数据模型,销售-电信产品销售分析服务中心-寻呼分析,多维数据模型,数据挖掘查询语言DWQL定义 define cube 销售事实表 时间键，商品键，商店键，地区键:销售金额,销售量 define dimension 时间 as(时间键,天,星期，,月,季,年)define dimension 商店 as(商店键，商店名，商店类别),多维数据模型,define dimension 地区 as(地区键,街道,城市,省,国家)define dimension 商品 as(商品键，商品名，商品类别，商品大类别，供应商类型)维表、事实表三种存放
22、形式1）关系OLAP（ROLAP）服务器 使用关系表存放2）多维OLAP（MOLAP）服务器,多维数据模型,基于数组的多维存储引擎3）混合OLAP（HOLAP）服务器详细数据保存在ROLAP，聚集数据保存在MOLAP产生事实表中的数据按关系OLAP（ROLAP）服务器的关系表存放维表、事实表的关系模式如下：,多维数据模型,日常销售=（时间键，商品键，商店键，地区键，销售量，价格）时间=(时间键,天,星期,月,季,年)商店=(商店键，商店名，商店类别)地区=(地区键,街道,城市,省,国家)商品=(商品键，商品名，商品类别，商品大类别，供应商类型),多维数据模型,销售事实表=（时间键，商品键，商店
23、键，地区键，销售金额，销售量）形成销售事实表的数据 Insert into 销售事实表 asselect s.时间键，s.商品键，s.商店键，s.地区键,sum(s.销售量*s.价格,),sum(s.销售量),多维数据模型,From 时间 as t，商品 as i，商店 as b，地区as l,日常销售 as sWhere s.时间键=t.时间键 and s.商品键=i.商店键 and s.地区键=b.商品键 and s.商店键=l.地区键Group by s.时间键，s.商品键，s.商店键，s.地区键,多维数据模型OLAP操作,以此立方体为例,三季度,一季度,二季度,宁波,四季度,杭州,济南
24、,莱芜,计算机,电话,电视,音响,605,825,872,970,672,634,619,623,812,845,867,多维数据模型OLAP操作,维表的概念分层例如：all、国家、省、城市、街道 分层的目的是为形成不同粒度的立方体事实表中保存的数据是最基本的方格（日，商品名，街道），其中的数据是最小粒度的数据取维表不同层的值形成不同立方体的和值，代表着OLAP的典型操作。,多维数据模型OLAP操作,上卷（roll up)操作沿维的概念分层向上攀升，然后在数据立方体上进行的聚集。例如：如上图，对于时间维：all、国家、省、城市、街道，维层次为城市时，（一季度，济南，音响）=605，（一季度，莱
25、芜，音响）=672，维层次为省时，（一季度，山东，音响）=1544,多维数据模型OLAP操作,下钻（drill down)操作沿维的概念分层向下延伸，然后在大的数据立方体上进行小立方体上值的细化。例如：如上图，对于时间维：all、年,季,月，星期,天 维层次为季度时，（一季度，宁波，计算机）=605维层次为月时，（一月，宁波，计算机）=200，（二月，宁波，计算机）=203，（三月，宁波，计算机）=202,多维数据模型OLAP操作,切片（slice)操作在数据立方体的一个维上选择。例如：如上图，对于商品维上确定为计算机，可以切出一个片的立方体数据（16个格）切块（dice)操作在数据立方体的两
26、个或多个维上选择。例如：如上图，对于商品维上确定为计算机，电话。时间维上确定为一季度、二季度，可以切出一个小立方体数据（16格）。若再选地区维的宁波、杭州，切出立方体（8个格）,多维数据模型OLAP操作,转轴（pivot)操作 转动数据的视角，一种目视操作例如：如上图，对于商品维上确定为计算机，可以切出一个片的立方体数据，视角是纵轴是时间，横轴是城市，转轴操作后，纵轴是城市，横轴是时间转轴操作也可以在三维上进行,多维数据模型OLAP操作,数据立方体的预计算物化以此立方体为例,三季度,一季度,二季度,宁波,四季度,杭州,济南,莱芜,计算机,电话,电视,音响,605,825,872,970,672
27、,634,619,623,812,845,867,多维数据模型OLAP操作,三维：是格，（商品，季度，城市），保存最基本的数据（64个格）二维：是条求和，（商品，季度）有16个条，（商品，城市）有16个条，（季度，城市）有16个条，（64条）一维：是面求和，（商品）有4个面，（季度）有4个面，（城市）有4个面，零维：记为 all,是整个立方体，1个,多维数据模型OLAP操作,查询 例：找出2000年各月的销售总额和销售总量Select 月，商品名，商店名，城市，SUM(R：销售金额)，SUM(R:销售量)from 销售事实表，时间，商品，地区，商店where 年=“2000”Cube by（月
28、，商品名，商店名，城市:R）,多维数据模型的OLAP操作,说明：Cube by子句对后面的属性月，商品名，商店名，城市，所有可能的集合计算聚集例：找出各季度的销售总额和销售总量Select 商品键，商店键，季度，SUM(销售金额)，SUM(销售量)from 销售事实表 as A，时间 as BWhere A.时间键=B.时间键Cube by（商品键，商店键，季度）,多维数据模型的OLAP操作,同样，聚集的方式有：Cube by（“电视”，“济南”，“银座”,“一季度”）Cube by（all,“济南”，“银座”,“一季度”）Cube by（all,all,“银座”，“一季度”）Cube by（
29、all,all,all，all）,多维数据模型的OLAP操作,同样，聚集的方式有：Cube by（“电视”，“济南”，“银座”,“一季度”）+Cube by（“音响”,“济南”，“银座”,“一季度”）Cube by（all,all,“银座”，“一季度”）/Cube by（all,all,all，all）*100%,多维数据模型的OLAP操作,上卷：Select 商品键，商店键，日期键，SUM(销售金额)，SUM(销售量)from 销售事实表Where A.时间键=B.时间键Cube by 商品键，商店键，日期键 ROLLUP,多维数据模型的OLAP操作,说明：Cube by 商品键，商店键，日
30、期键 ROLLUP依次生成零维、一维、二维、三维的各个立方体的值。例：找出2007-2008每个城市各季度各商品的销售总额和销售总量,多维数据模型的OLAP操作,Select 商品键，城市，季度，SUM(销售金额)，SUM(销售量)from 销售事实表 as A，日期 as B,商店 as CWhere A.时间键=B.时间键 and A.商店键=C.商店键 and 年 between 2007 AND 2008Cube by（商品键，城市，季度）,多维数据模型的OLAP操作,例：找出2007-2008每个城市按月、季度、年的销售总额和销售总量Select 商品键，商店键，日期键，SUM(销售
31、金额)，SUM(销售量)from 销售事实表 as A，日期 as B,Where A.时间键=B.时间键 and A.商店键=C.商店键 and 年 between 2007 AND 2008Cube by（商品键，商店键，日期键）ROLLUP 日，月，季度，年,SQL server的多维数据模型,安装SQL server中的组件插入 SQL server 2000 光盘选“安装 SQL server 2000 组件”选“安装 Analysisi service”进入“选择组件”对话框，全选完成,SQL server的多维数据模型,进入 Analysisi Manager建立维度表创建多维数据集在Analysisi Manager，进入多维数据集浏览集可以完成：1.维度的替换,SQL server的多维数据模型,2.维度添加3.上卷与下钻4.切片与切块,
