IT认证Oracle10g基础教程课件

1、第7章 SQL高级查询技术本章概述本章要点教学过程1第1页，共40页。本章概述除了前一章介绍的SQL基本功能之外，SQL还可以用来完成其他一些重要且复杂的操作。对于全世界来说，不同的地点有不同的时间，不同地点的人可能操纵相同数据库中的数据。数据库中需要存储准确的数据，这些数据离不开时间。在Oracle Database 10g系统中，可以灵活地支持有关日期和时间的操作。在许多实际应用中，数据之间的关系是层次关系。例如，由零部件组成的汽车结构、组织结构和家族谱系等都是典型的层次结构关系。Oracle Database 10g系统，提供了层次查询的功能。现在，数据库系统越来越强调对业务数据的分析，

2、强调业务数据如何为决策人员提供帮助，数据仓库技术就是用来解决这种数据分析问题的。从Oracle Database 10g系统提供的分析查询技术可以看到，数据库技术和数据仓库技术由分离走向融合的趋势。本章将对有关日期和时间、层次查询、分析查询等内容进行详细分析。 2第2页，共40页。本章要点日期的默认格式日期和时间函数层次查询技术情景查询技术翻译查询技术分析查询技术3第3页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询 4第4页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查

3、询 7.5 分析查询 5第5页，共40页。7.1 日期和时间地球是一个广袤的空间，不同的地点有不同的时间，不同地点的人可能操纵相同数据库中的数据。数据库中需要存储准确的数据，这些数据离不开时间。那么如何在这种分布式数据库环境中准确地存储和使用时间呢？Oracle Database 10g系统提供了有关日期和时间数据的完整解决方案。 6第6页，共40页。设置默认的日期格式 在默认情况下，日期数据的格式是dd-mon-yy。其中，dd表示两位数字的日，例如10日。mon表示3位字符的月份，在英文版的Oracle系统中，月份分别是相应英文月份名称的简写形式，例如Jun；而在中文版中，月份的表示形式是

4、“x月”或“xx月”，例如“8月”。yy表示两位数字年，例如90年和06年。在插入数据时，默认情况下，应该按照dd-mon-yy格式插入数据。系统会自动将两位数字年识别成4位数字年。在中文版的Oracle Database 10g系统中，应该按照“x月”或“xx月”格式输入月份。 在插入数据时，如果使用date关键字，那么可以采用yyyy-mm-dd的格式输入日期数据。其中，yyyy表示4位数字年，mm表示两位数字月，即112月，dd表示两位数字日。 7第7页，共40页。日期数据格式 日期数据格式由nls_date_format系统参数来设置。系统参数存储在init.ora文件和spfile.

5、ora文件中。可以使用show parameters命令查看这些系统参数的值。如果需要改变这些系统参数的值，可以通过执行alter system命令或alter session命令来实现。alter system命令表示修改了系统参数的文件，这种修改设置在今后的数据库操作中一直起作用。而alter session命令的设置只在当前的会话中起作用，该会话结束之后，其设置就会失效。使用alter session命令设置日期的语法格式如下：alter session set nls_date_format date_format;在上面的语法格式中，date_format参数既可以采用month-d

6、d-yyyy格式，也可以采用yyyy-month-dd格式，还可以采用yyyy-dd-month格式。8第8页，共40页。转换日期数据 在Oracle Database 10g系统中，既可以把字符数据转变成日期数据，也可以把日期数据转变成字符数据。可以使用to_char()函数to_date()函数完成日期和字符数据之间转换操作。下面介绍这两个转换函数的使用方法。9第9页，共40页。to_char()函数 使用to_char()函数可以完成从日期数据到字符串数据的转换过程。to_char()函数的语法格式如下：to_char(x, format)在上面的语法格式中，主要参数的意义如下：x参数表

7、示将要转换的源日期表达式类型。format参数表示将要转变的源日期表达式的格式类型。格式类型由许多参数组成，这些参数如表7-1所示。10第10页，共40页。to_date()函数 使用to_date()函数可以把指定的字符串转换成一个日期和时间。to_date()函数的语法格式如下：to_date(x, format)在上面的语法格式中，该函数将字符串表达式x按照由format指定的格式转变成日期和时间。如果没有使用format日期格式参数，则按照默认的日期格式进行转换。11第11页，共40页。两位数字年和四位数字年 从本质上看，千年虫问题就是两位数字年的问题。 在前面的表7-1中讨提到可以使

8、用yy和rr表示两位数字的年。实际上，这两种不同形式的年的表示方法存在着很大的差别。如果使用yy形式表示两位年，那么这种年的世纪与当前系统的日期世纪是相同的，并不会因为当前系统年的变化而变化。但是，如果使用rr形式表示两位年，那么这种年的世纪不仅取决于当前系统的日期世纪，而且还取决于当前系统的日期年。12第12页，共40页。日期和时间函数 Oracle Database 10g系统提供了许多用来处理日期和时间的函数，这些函数的类型和功能如表7-2所示。 13第13页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询 14第14

9、页，共40页。7.2 层次查询在许多实际的应用中，数据之间的关系是层次关系。例如，图书编码、由零部件组成的汽车结构、组织结构和家族谱系等都是典型的层次结构关系。在各种的ERP应用中，产品结构都是由一种树状的层次结构。Oracle Database 10g系统提供了层次查询的功能。用户利用这些功能可以很方便地查询各种层次结构的数据。本节将详细介绍Oracle Database 10g系统提供的层次查询功能。15第15页，共40页。自行车结构示例 16第16页，共40页。bicycle表结构 17第17页，共40页。bicycle表数据18第18页，共40页。层次查询的基本语法 为了执行层次查询，

10、需要在select语句中添加start with子句和connect by子句。添加这些子句后的select语句的语法格式如下：select level, column_name, expression, from table_namewhere where_conditionstart with start_conditionconnect by prior prior_condition;19第19页，共40页。层次查询示例 首先执行最基本的层次查询，不显示树状结构的层次。在如图7-14所示的层次查询示例中，由于没有使用level列，该层次查询的结果与没有使用层次查询的普通查询的结果类似。

11、练习在层次查询中使用level伪列，该伪列表示树状结构的层次。执行层次查询，并且使用level伪列，其中order by子句用于对level列的数据进行排序。该层次查询的执行结果如图7-15所示。从该图可知，该树状结构的最高层次是5。为了使显示的结果更加直观，可以对层次查询的查询结果进行格式化。使用column命令设置partName列为35位长的字符，设置mp_cost列按照99999.99的格式显示数字数据。在层次查询中，使用lpad()函数为执行的数据增加空格，使得这些数据按照不同的层次进行缩进。该层次查询的执行结果如图7-16所示。从结果中可以看出，由于采用字符缩进，所以这种树状结构更

12、加直观。20第20页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询 21第21页，共40页。7.3 情景查询在执行select操作过程中，经常需要对不同的数据显示不同的结果。也就是说，在不同的情景下，需要显示不同的结果。这种情景查询非常类似于ifthenelse程序流程控制语句。在Oracle Database 10g系统中，可以使用decode()函数和case表达式完成情景查询。22第22页，共40页。decode()函数 在Oracle Database 10g系统中，可以使用decode()函数执行类似于ifthe

13、nelse程序流程控制语句的操作。decode()函数的语法格式如下：decode(value_expression, search_value, result_value, default_value)23第23页，共40页。case表达式 除了使用decode()函数执行情景查询之外，还可以使用case表达式执行情景查询。与decode()函数相比，case表达式的功能更加强大，使用更加灵活。在Oracle Database 10g系统中，有两种类型的case表达式，即简单case表达式和搜索case表达式。24第24页，共40页。简单case表达式case search_expressi

14、onwhen expression_1 then display_result_1when expression_2 then display_result_2when expression_n then display_result_nelse default_display_resultend25第25页，共40页。搜索case表达式 case when expression_1 then display_result_1when expression_2 then display_result_2when expression_n then display_result_nelse de

15、fault_display_resultend26第26页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询 27第27页，共40页。7.4 使用翻译函数查询除了前面介绍的查询之外，Oracle Database 10g系统还提供了一个有意义的翻译函数translate()。顾名思义，可以使用translate()函数把指定的表达式翻译成另一个表达式。该函数类似于加密函数，可以使用一种密钥加密表达式，并且可以使用同样的密钥解密。因此，可以说translate()函数是一种对称加密方式。translate()函数的语法格式如下：

16、transalate(message_expression, source_encode_key, destination_encode_key)28第28页，共40页。教学过程7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询 29第29页，共40页。7.5 分析查询本节首先介绍分析查询的基本概念接着介绍分析函数的重要性和分类最后介绍一个将采用分析查询的实例30第30页，共40页。分析查询和分析函数 在执行分析查询过程中需要借助于分析函数。Oracle Database 10g系统提供了许多可以用于分析查询的分析函数。可以把这些分析函数分成以下

17、几种类型。窗口函数：允许用户在指定的窗口中计算累加值、移动值等运算；制表函数：允许计算诸如市场份额、销售总计和销售比率等数据；第一个值和最后一个值函数：可以在一组数据中得到第一个值和最后一个值；落后位置和提前位置函数：可以使得行中的某一个列的数据位置落后或提前指定的距离；等级函数：可以计算一组数据的等级、百分比等；线性回归函数：可以计算最小方框回归线。31第31页，共40页。数据仓库在数据仓库的设计中，经常采用事实表和维度表组成的星型结构。在这种结构中，事实表是数据仓库中最关键的数据表，该表存储了业务领域的主要数据。维度表存储了用于分析事实表的维度。维度表中的粒度与对事实表分析的精细程度紧密相

18、关。星型结构示意图如图7-28所示。 32第32页，共40页。sales_fact_2006事实表的结构33第33页，共40页。sales_fact_2006事实表中的数据 34第34页，共40页。窗口函数 窗口函数是分析函数中最基本的一类函数，也是使用得最为广泛的分析函数。下面，首先介绍窗口函数的作用、类型和特点，然后详细介绍窗口函数的常见操作方法。使用窗口函数可以在不同的分组上计算各种累计值、移动值等。本书前面介绍过的合计函数，例如sum()、avg()、max()、min()、count()和variance()等函数，以及first_value()和last_value()函数都可以用

19、在窗口函数中。窗口函数的基本语法格式如下：function_name (argument_list)over( )35第35页，共40页。制表函数 制表函数用于计算各种合计值和比率值。下面首先介绍制表函数的特点，然后通过具体示例介绍如何使用制表函数。制表函数与窗口函数非常类似。事实上，这些制表函数的名称大多数也相同，例如sum()、min()和max()等。不同之处在于，窗口函数用于数据的一个窗口上，而制表函数则用于一个分区或分组上。在over子句中，窗口函数必须有order by子句，而制表函数没有order by子句，因为制表函数应用于分区或分组中的所有数据上。制表函数的基本语法格式如下：function_name (argument_list)over()36第36页，共40页。检索当前行的周围行 如果需要检索的行不是当前行，而是当前行之前或之后的行