第6章查询中函数的使用_Oracle PL/SQL宝典-QQ阅读男生玄幻网

书名：Oracle PL/SQL宝典
作者名：赵越编著
本章字数：197字
更新时间：2020-08-28 06:11:41

第6章查询中函数的使用

在Oracle数据库中，提供了大量的系统函数用于对数据库的值、对象和设置进行操作并返回有关信息。此外，还提供了一些处理列值的聚合函数（也称统计函数），对表中的数据进行统计分析。在SQL查询语句中，可以使用分组命令（GROUP BY）将列中的数据按照一定的条件进行分组。一般情况下，聚合函数与分组命令同时运用，即只将聚合函数作用到满足条件的分组上。本章将详细介绍在Oracle数据库中单行函数及聚合函数的应用。

6.1 常用的单行函数

在Oracle中，所谓的单行函数就是返回单一的结果行，查询对象可以是表或视图等，它们可以用在查询列表中、WHERE子句中或者HAVING子句中。这些单行函数非常丰富，主要包括对数值、字符、日期以及NULL字段的处理。它们非常重要，可以帮助开发人员快速地得到预期的结果。下面将对这些常用的单行函数进行讲解。

6.1.1 字符处理函数

字符类型的函数可以对字符类型的数据进行相关处理，它们可以接收字符或字符串类型的参数，返回字符串或数字类型的处理结果。这些函数和其他的高级编程语言中的函数类似，例如可以截取字符串、搜索字符串等。本小节将对该类型函数进行讲解。

1.获取字符串长度函数

利用LENGTH函数可以得到指定字符串的长度，它返回数字类型，包含1个字符类型的参数。语法结构如下：

      LENGTH(char)

【实例6-1】LENGTH函数的用法。

要求得到EMP表的JOB列中数据的长度，执行结果见图6.1。

该函数会把空格也算作数据的长度，所以在开发过程中使用该函数时，尽量把数据的前后空格都去掉再使用，以防造成不必要的麻烦。有关去空格的函数，后面会做相关的介绍。

图6.1 LENGTH函数的用法

2.去除字符串首尾指定字符函数

利用TRIM函数将去除字符串中指定的前缀或尾随的字符，默认删除空格。其语法结构如下：

      TRIM(
      [LEADING|TRAILING|BOTH]
      [trim_character FROM]
      trim_source)

【语法说明】

◆ LEADING：删除trim_source的前缀字符。

◆ TRAILING：删除trim_source的后缀字符。

◆ BOTH：删除trim_source的前缀和后缀字符。

◆ trim_character：删除指定字符，默认删除空格。

◆ trim_source：被操作的字符串。

【实例6-2】利用TRIM函数去除后缀空格。

要求删除EMP表的JOB列中数据后面的空格，并和未删除空格时的长度进行对比。操作过程见图6.2。

【实例6-3】使用TRIM函数去除指定的前缀字符。

要求去除JOB列中所有数据的“S”前缀，操作过程见图6.3。从图6.3中可以看出，右边列的数据中已经没有以“S”开头的数据了，因为已经通过TRIM函数把“S”前缀删除了。

3.字符串截取函数

使用SUBSTR函数可以对提供的参数进行截取，返回截取后的字符串。其语法结构如下：

      {[SUBSTR]|
      [SUBSTRB]|
      [SUBSTRC]|
      [SUBSTR2]|
      [SUBSTR4]}(char,position[,substring_length])

图6.2 使用TRlM函数去除后缀空格

图6.3 使用TRlM函数去除前缀“S”

【语法说明】

◆ SUBSTR：按字符截取，表示以字符为单位。

◆ SUBSTRB：按字节截取，表示以字节为单位。

◆ SUBSTRC：表示以Unicode字符为单位。

◆ SUBSTR2：表示以UCS2代码点为单位。

◆ SUBSTR4：表示以UCS4代码点为单位。

◆ char：参数，为原始字符串。

◆ position：要截取字符串的开始位置。初始值为1，如果该值为负数，则表示从char的右边算起。

◆ substring_length：截取的长度。

【实例6-4】演示利用SUBSTR函数如何截取字符串。

要求把EMP表的JOB列中数据以字符和字节为单位全部截取前3位显示，并和未截取数据进行对比。操作步骤见图6.4。

SUBSTR函数并不能改变原数据的长短，而是对原数据进行操作后返回操作结果，通常可以把该结果赋值给其他变量。

4.字符串连接函数

字符串连接函数CONCAT的功能和截取函数相反，它能把提供的两个参数连接起来，并返回连接后的字符串。其语法结构如下：

      CONCAT(char1,char2)

图6.4 截取字符串

【实例6-5】演示利用CONCAT函数如何连接两个字符串。

查询EMP表的数据时，要求把EMPNO和ENAME两列的数据进行连接显示，并在JOB列的所有数据前面加上“CONCAT”字样。具体操作步骤见图6.5。

图6.5 连接字符串操作

该函数可以针对具体的字符串操作，也可以针对某列操作，当然具体的字符串和数据列进行连接也是被允许的，利用它可以帮助开发者获取更有业务意义的数据。

要想进行连接操作，也可以利用连接符“||”来实现。该操作同样不会改变原始数据，只是返回操作后的字符串。

5.小写字母转大写函数

利用UPPER函数可以将参数中的小写字母全部转成大写字母，参数可以是字符或字符串，返回转换后的字符串。语法结构如下：

      UPPER(char)

【实例6-6】演示如何使用UPPER函数。

要求把ENAME列中小写字母转成大写字母输出。具体操作步骤见图6.6。

6.大写字母转小写函数

LOWER函数的功能与UPPER函数相反，它可以把参数中的大写字母全部转成小写字母输出。语法结构如下：

      LOWER (char)

【实例6-7】演示如何使用LOWER函数。

要求把ENAME列中大写字母转成小写字母输出。具体操作步骤见图6.7。

图6.6 小写字母转大写字母

图6.7 大写字母转小写字母

字母大小写转换函数在日常开发中非常有用，Oracle数据库中的数据是区分大小写的，在查询数据时，利用它们可以忽略查询条件中的大小写问题，而把与实际业务有关的数据全部查询出来。

7.检索字符串函数

利用INSTR函数可以在一个字符串中检索另一个给定字符串出现的位置，也可以作为判定原字符串是否包含指定字符串的标准，返回结果是一个整数。其语法结构如下：

      {[INSTR]|
      [INSTRB]|
      [INSTRC]|
      [INSTR2]|
[INSTR4]}(string,substring[,position[,occurrence]])

【语法说明】

◆ INSTR：表示检索某字符串在另一个字符串中出现的位置，以字符为单位。

◆ INSTRB：表示作用在字符串中的字节数据，它返回的是字节的位置，而不是字符的位置，也就是说，它是以字节为单位的。

◆ INSTRC：表示以Unicode字符为单位。

◆ INSTR2：表示以UCS2代码点为单位。

◆ INSTR4：表示以UCS4代码点为单位。

◆ string：被检索的字符串。

◆ substring：检索字符串。

◆ position：检索的开始位置，默认值为1，表示从字符串左边开始检索；如果值为负数，则表示从字符串的右边位置开始检索。

◆ occurrence：substring第几次出现，默认值是1。

【实例6-8】演示INSTR函数的使用方法。

要求在给出的字符串中进行检索，查找“测试”出现的位置，分别从左边检索和从右边检索。操作过程见图6.8。

图6.8 检索字符串函数的用法

该查询语句中“测试一”是一个默认查询，它从左边检索，起始位置从1开始计算，所以检索结果为“3”；“测试二”表示从右边开始检索第1次出现“测试”字符串的位置，在从左边算起第“11”位置的地方；“测试三”表示从右边开始检索第2次出现“测试”字符串的位置，在从左边算起第“3”位置的地方，它和“测试一”的结果是一样的；“测试四”中使用了INSTRB函数，它以字节为单位，一个字符包含两个字节，它表示从左边开始检测“测试”第2次出现的位置，并用字节来计算，所以位置是“13”。

6.1.2 数值处理函数

数值处理函数可以接收数字类型的参数，并且返回数字类型的值，大多数的数值类型函数返回值可以精确到38位（十进制），一部分函数则精确到30位或36位。本小节主要介绍一些常用的数值处理函数。

1.绝对值函数

ABS函数用于返回绝对值。该函数包含1个参数，通常参数类型为数字类型，或可以隐式转换成数字类型（例如某些字符串）。语法结构如下：

      ABS(n)

【实例6-9】演示ABS函数的使用方法。

要求测试正数、负数，以及可自动转换成数字的字符串在绝对值函数中的使用。具体操作步骤见图6.9。

图6.9 绝对值函数的用法

2.取余函数

MOD函数包含2个参数，参数类型为数字类型，或可以隐式转换成数字类型的其他类型。具体语法结构如下：

      MOD(n2,n1)

【语法说明】

◆ 表示返回n2除以n1的余数。当n1为0时，函数将返回n2。

【实例6-10】演示MOD函数的使用方法。

要求测试对正数、负数，以及可转换成数字的字符串进行取余操作。具体的操作步骤见图6.10。

图6.10 取余操作

3.正弦函数

SIN函数可以返回参数的正弦值，参数类型为数字类型，或可以隐式转换成数字类型的其他类型。如果参数是BINARY_FLOAT类型，则函数返回BINARY_DOUBLE类型。具体语法结构如下：

      SIN(n)

【实例6-11】演示正弦函数的用法。

要求求出1.57的正弦值。具体的操作步骤见图6.11。

图6.11 正弦函数的用法

有关的三角函数主要还包含如下几个。

◆ COS(n)：返回n的余弦值。

◆ ACOS(n)：返回n的反余弦值。

◆ TAN(n)：返回n的正切值。

◆ ATAN(n)：返回n的反正切值。

◆ ASIN(n)：返回n的反正弦值。

4.近似值函数

ROUND函数为近似值函数，它包含2个参数，具体的语法结构如下：

      ROUND(n,integer)

【语法说明】

◆ n：表示原数据。当为0时，函数将返回0。

◆ integer：指定四舍五入的地方。当为正数时，表示在小数点右边指定的位数处四舍五入；当省略该参数时，和它为0时一致，表示在小数点处进行四舍五入；当为负数时，表示在小数点左边指定的位数处四舍五入。

【实例6-12】演示如何使用近似值函数。

要求分别对不同类型的数值进行四舍五入操作，具体的操作步骤见图6.12。

图6.12 近似值函数的用法

5.数字截取函数

TRUNC函数会对数字进行截取，这和近似值函数不一样，具体的语法结构如下：

      TRUNC(n1,n2)

【语法说明】

◆ 根据n2的值对参数n1进行截取。

◆ n1：为数字类型或可以隐式转换成数字类型的其他类型。

◆ n2：要求是整数，如果不是整数，那么它将被自动截取为整数部分。

◆ 当n2为正数时，表示n1将从n2位小数处截取；但n2为0或省略时，表示n1将从小数点处截取；当n2为负整数时，表示n1将从小数点左边对应的位数处开始截取，并用0补齐被截取掉的部分。

【实例6-13】演示数字截取函数的使用方法。

要求分别对不同类型的数值进行截取操作，具体的操作步骤见图6.13。

图6.13 数字截取函数的用法

从实例中可以看出，当截取时不管后面的数字是否超过5，都直接抛弃了，而不是进位，这是数字截取函数和近似值函数的最大区别；还有就是n2的值虽然包含小数，但依然按照它的整数部分进行解析了。

6.1.3 日期处理函数

所谓日期处理函数就是用来操作与日期、时间有关的函数。例如，利用日期处理函数可以得到当前的系统时间等，这类函数在某种特定业务中使用较多。

1.获取系统日期函数

SYSDATE函数可以获取数据库系统的当前日期，通常在插入数据时作为默认日期使用。此函数不带任何参数。

【实例6-14】演示SYSDATE函数的使用方法。

要求获取数据库系统的当前日期。具体的操作步骤见图6.14。

图6.14 获取系统日期

在实例中利用该函数直接获取了系统日期。这里也使用了TO_CHAR函数，它是一个转换函数，有关它会在后面的小节中做介绍。这两个函数所获取的结果实际上表示的是同一个时间，只不过表现形式不同罢了。

2.获取系统时间函数

SYSTIMESTAMP函数与SYSDATE函数相似，它会返回系统时间，时间精确到微秒。此函数不带任何参数。

【实例6-15】演示SYSTIMESTAMP函数的使用方法。

要求获取数据库系统的当前时间。具体的操作步骤见图6.15。

图6.15 获取系统时间

从实例中可以看出，该函数可以同时返回服务器所在的时区信息。

3.月份增加函数

ADD_MONTHS函数可以实现日期和月份的计算，它可以在给定的日期上添加相当于月份的整数，然后重新计算日期。语法结构如下：

      ADD_MONTHS(date,integer)

【语法说明】

◆ date：给定的日期。

◆ integer：表示要添加的月数。当为负数时，表示当前日期减去该月数。

【实例6-16】演示月份增加函数的使用方法。

要求在给定的日期上增加指定的月数。具体的操作步骤见图6.16。

图6.16 月份增加操作

当该函数中给出的日期是月的最后一天时，增加月份后返回的结果也将是新月份的最后一天；而如果新的日期月份比指定日期月份的天数少，则函数将返回当月的最后一天，例如一月份变二月份后，日期会返回2月28日。

4.获取两个日期之间的月份数函数

MONTHS_BETWEEN函数具有两个参数，可以获取这两个参数之间的月份数。具体的语法结构如下：

      MONTHS_BETWEEN(date1,date2)

【语法说明】

◆ date1：日期类型。

◆ date2：日期类型。

◆ date1＞date2时，如果两个参数表示的是某月中相同的一天，或它们都是某月中的最后一天，那么函数会返回整数；否则，将返回小数。

◆ date1＜date2时，函数返回负值。

【实例6-17】演示如何获取两个日期之间的月份数。

要求给出两个日期，求出它们之间的月份数。具体的操作步骤见图6.17。

图6.17 获取两个日期之间的月份数

5.获取某月最后一天函数

LAST_DAY函数可以返回给定日期所在月份的最后一天，它有一个参数。具体的语法结构如下：

      LAST_DAY(date)

【实例6-18】演示如何获取当月的最后一天日期。

要求查询2010年7月最后一天的日期，具体的操作步骤见图6.18。该函数平时作用不大，但根据日期查询某月的所有数据时，则会有很好的效果。

图6.18 获取某月最后一天日期

6.1.4 转换函数

转换函数很重要，它可以完成不同数据类型之间的转换，平时开发者使用比较多的就是字符串与日期之间以及字符串与数字之间的相互转换。部分转换函数在前面已经接触到了，本小节将对这类函数进行详细的介绍。

1.类型转换函数

CAST函数会把内置的数据类型或集合类型值转换成内置的其他数据类型或集合类型值。其简化的语法结构如下：

      CAST(expr as type_name)

【语法说明】

◆ expr：待转换的数据。

◆ type_name：目标数据类型。

◆ 该函数提供了一套转换规则表，这里不再给出，感兴趣的读者可以查找官方资料。

【实例6-19】演示如何使用CAST函数。

要求把日期转换成字符串类型，把数字转换成字符串类型，把字符串转换成数字类型。具体的操作步骤见图6.19。

图6.19 类型转换操作

2.数值或日期转字符串函数

TO_CHAR函数可以将数值型参数转换成字符型数据，并可以对其指定格式。具体的语法结构如下：

      TO_CHAR(n,[,fmt[,nlsparam]])

【语法说明】

◆ n：要转换的数据。

◆ fmt：要转换成字符的格式。

◆ nlsparam：指定fmt的特征，通常包括小数点字符、组分隔符、本地钱币符号等。

【实例6-20】演示TO_CHAR函数的使用方法。

给出一个数值或日期，要求对其格式化。具体的操作步骤见图6.20。

图6.20 数据转换并格式化

3.字符转日期函数

TO_DATE函数可以把给出的字符串按照一定的格式转换成日期型数据。具体的语法如下：

      TO_DATE(char[,fmt[,nlsparam]])

【语法说明】

◆ char：待转换的字符。

◆ fmt：表示转换的格式。

◆ nlsparam：控制格式时使用的语言类型。

【实例6-21】演示如何使用TO_DATE函数。

要求给出一个字符串，尝试把该字符串转换成日期类型。具体的操作步骤见图6.21。

图6.21 将字符串转换成日期格式

4.字符串转数字函数

TO_NUMBER函数和前面几个函数的功能相似，它可以把字符串转换成数字类型。具体的语法结构如下：

      TO_NUMBER(expr[,fmt[,nlsparam]])

【语法说明】

◆ expr：待转换的字符串。

◆ fmt：指定转换的数字格式。

◆ nlsparam：该参数指定fmt的特征。

【实例6-22】演示如何使用TO_NUMBER函数。

要求把字符串转换成数字，具体的操作步骤见图6.22。

图6.22 字符串转换成数字

不是所有的字符串都能转换成数字，例如'm'这种字符就不可以。

6.1.5 替换NULL值函数

NVL函数可以替换数据中的NULL值。它具有两个参数，这两个参数类型需要一致，或之间能进行隐式转换，否则会出错。具体的语法结构如下：

      NVL(expr1，expr2)

【语法说明】

◆ expr1：原数据。

◆ expr2：用来替换的数据。

◆ 此函数的作用是，如果expr1的值为NULL，则会返回expr2的值，否则将返回expr1的值。

【实例6-23】演示如何使用NVL函数。

要求查询EMP表中数据时，把ENAME列中为空的数据替换成“TEST”字样。具体的操作步骤见图6.23。

从图6.23中的标记部分可以看出，利用该函数，已经成功地把空字符串替换成了指定的字符串。

图6.23 替换空字符串

6.1.6 排除指定条件函数

LNNVL函数带有一个参数，可以得到除了参数条件之外的数据，包括NULL的条件。其语法结构如下：

      LNNVL(condition)

【语法说明】

◆ condition：表达式。

【实例6-24】演示如何使用LNNVL函数。

要求查询表EMP中所有的ENAME列不为空并且SAL大于1000的数据。具体的操作步骤见图6.24。

图6.24 LNNVL函数的使用

6.2 聚合函数

在访问数据库时，经常需要对表中的某列数据进行统计分析，如求其最大值、最小值、平均值、方差等。所有这些针对表中一列或者多列数据的分析就称为聚合分析。Oracle提供了一些聚合函数，通过使用这些函数，可以快速地实现数据的聚合分析。

6.2.1 聚合函数的种类

Oracle提供的常用的聚合函数有SUM（求和函数）、MAX（最大值函数）、MIN（最小值函数）、AVG（平均值函数）、COUNT（计数函数）等，所有的聚合函数及其功能如表6.1所示。

表6.1 聚合函数及其功能

下面分别介绍这些函数的使用方法。

6.2.2 计数函数

COUNT函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。其语法结构如下：

      01  COUNT(＊)
      02  COUNT(column)

【语法说明】

◆ 第1行表示计算表中行的总数，即使表中行的数据为NULL，也被计入在内。

◆ 第2行表示计算column列包含的行的数目，如果该列中某行数据为NULL，则该行不计入统计总数。

【实例6-25】获取指定字段记录数。

要求使用COUNT函数获取BOOKINFO表中的所有记录和READER列的记录数。实例代码如下：

      01  SELECT
      02  COUNT(＊) AS RowsCount,
      03  COUNT(READER) AS ReaderCount
      04  FROM BOOKINFO
      05  /

【代码解析】

◆ 第2行使用了COUNT(＊)格式。

◆ 第3行使用了COUNT(column)格式。

【执行效果】

在SQL＊Plus中执行以上代码，执行效果见图6.25。

图6.25 获取指定字段记录数

可见，所获取的READER列的记录数和整个BOOKINFO表中的所有记录数是不同的。这是因为如果列中某行数据为NULL，则使用COUNT函数时，该行不计入统计总数。

如果要使用含有NULL值的列，如READER列，获取其所在表的所有记录数，则可以使用在前面介绍的NVL函数，首先利用它将空值替换为其他值，然后使用COUNT函数。比如下面的代码：

      01  SELECT
      02  COUNT(＊) AS RowsCount,
      03  COUNT(NVL(TO_CHAR(READER),'TEST')) AS ReaderCount
      04  FROM BOOKINFO
      05  /

【代码解析】

◆ COUNT(NVL(TO_CHAR(READER),'TEST'))：表示首先把READER列的数据转换成字符串类型，然后利用NVL函数对空值进行替换，最后调用COUNT函数对记录数进行计算。

【执行效果】

修改后代码的执行效果见图6.26。

图6.26 修改后获取指定字段记录数

从修改后的执行效果可以看出，两次统计的记录数现在是相同的，该函数对于空值的处理希望能够引起读者的注意。

【实例6-26】使用COUNT函数实现对满足某种条件的记录计数。

要求利用COUNT函数，获取BOOKINFO表中青年出版社出版的所有图书数目。具体的代码如下：

      SELECT COUNT(PUBLISH) AS 青年出版社图书数目
      FROM BOOKINFO
      WHERE PUBLISH='青年出版社'
      /

【执行效果】

执行上述代码，效果见图6.27。

图6.27 指定条件计算记录数

之所以可以通过WHERE子句定义COUNT函数的计数条件，这与SELECT语句各个子句的执行顺序是分不开的。整个查询语句会首先执行FROM子句，然后是WHERE子句，最后才是SELECT子句。所以COUNT函数只能作用于满足WHERE子句定义的查询条件的记录。

6.2.3 求和函数

SUM函数用于对数据求和，它分组计算指定列的和，如果不使用分组，则函数默认把整个表作为一组。SUM函数只能作用于数值类型数据。其语法结构如下：

      SUM([distinct|all] expr)

【语法说明】

◆ distinct：表示去除重复的记录。

◆ all：代表所有的记录，是默认选项。

◆ expr：表的列。

◆ 当对某列数据进行求和操作时，如果该列存在NULL值，则SUM函数会忽略该值。

【实例6-27】使用SUM函数进行求和计算。

要求使用SUM函数，获取BOOKINFO表的PRICE列中的所有数据的总和。具体的代码如下：

      01  SELECT SUM(PRICE) AS SUM的使用
      02  FROM BOOKINFO
      03  /

【执行效果】

在SQL＊Plus中执行以上代码，执行效果见图6.28。

图6.28 SUM函数的使用方法

也可以为SUM函数指定查询条件，下面的实例演示了查询中为SUM函数指定一定的条件。

【实例6-28】根据条件使用SUM函数进行求和计算。

要求使用SUM函数，获取BOOKINFO表中出版社为“大众出版社”的PRICE列的数据总和。具体的代码如下：

      SELECT SUM(PRICE) AS SUM的使用
      FROM BOOKINFO
      WHERE PUBLISH='大众出版社'
      /

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.29。

图6.29 为SUM函数指定条件

6.2.4 均值函数

AVG函数用于计算结果集中所有数据的算术平均值，也可以求某组数据的平均值。同样，它也只能作用于数值类型数据，返回数值类型值。其语法结构如下：

      AVG([distinct|all]expr)

【语法说明】

◆ distinct：表示去除重复的值。

◆ all：表示所有的值，包括重复的值，是默认值。

◆ expr：表达式，只能是数值类型。

【实例6-29】演示AVG函数的使用方法。

要求使用AVG函数，获取BOOKINFO表中PRICE字段的所有数据的算术平均值。具体的脚本如下：

      SELECT AVG (PRICE) AS 平均价格
      FROM BOOKINFO
      /

【执行效果】

执行以上脚本，执行效果见图6.30。

图6.30 AVG函数的使用方法

在计算平均值时，AVG函数将忽略NULL值。因此，如果要计算平均值的列中有NULL值时，要特别注意。

在实例代码中，AVG(PRICE)等价于SUM(PRICE)/COUNT(PRICE)，而SUM、COUNT函数均忽略了NULL值，因此AVG函数也忽略了NULL值。实际上，所有的聚合函数都忽略了含有NULL值的记录。

当然，除了可以显示表中某列的平均值外，还可以用AVG函数作为WHERE子句的一部分。但是不能直接用于WHERE子句中，必须以子查询的形式出现，比如下面的实例。

【实例6-30】AVG函数用作子查询。

要求使用AVG函数，实现从BOOKINFO表中查询价格高于平均价格的所有图书信息。相关脚本如下：

      01  SELECT BOOKID,BOOKNAME,PUBLISH,PRICE
      02  FROM BOOKINFO
      03  WHERE PRICE ＞= (SELECT AVG (PRICE) FROM BOOKINFO)
      04  ORDER BY PRICE
      05  /

【代码解析】

◆ 第3行是一个子查询语句，这里表示查询价格比平均价格高的数据。

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.31。

图6.31 AVG函数用作子查询

6.2.5 最大值/最小值函数

利用MAX和MIN函数，可以获取结果集记录数据中的最大值和最小值。与前面介绍的其他函数不同，这里的数据可以是数值、字符串或日期时间数据类型。其中，字符串是根据字符串的ASCII码的顺序来获取最大值和最小值的。具体的语法结构如下：

      01  MAX([distinct|all]expr)
      02  MIN([distinct|all]expr)

【语法说明】

◆ distinct：去除重复的记录。

◆ all：代表所有的记录，是默认选项。

◆ expr：表的列。

在求取列中的最大值/最小值时，MAX、MIN函数忽略NULL值。但是，如果在该列中所有的行的值都是NULL，则MAX、MIN函数将返回NULL值。

【实例6-31】演示MAX和MIN函数的使用方法。

要求从BOOKINFO表中查询PRICE字段和PUBDATE字段的最大值、最小值。相关脚本如下：

      01  SELECT MAX(PRICE) AS 最高价格,
      02  MIN(PRICE) AS 最低价格,
      03  TO_CHAR(MAX(PUBDATE),'YYYY-MM-DD') AS 最近日期,
      04  TO_CHAR(MIN(PUBDATE),'YYYY-MM-DD') AS 最早日期
      05  FROM BOOKINFO
      06  /

【代码解析】

◆ 第3行表示获取最大值后转换成字符串格式。

◆ 第4行表示获取最小值后转换成字符串格式。

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.32。

图6.32 MAX和MlN函数的使用方法

然而，在实际应用中，经常用到的却不是求取某列的最大值/最小值，而是要得到拥有最大值/最小值的记录。这时，就需要在WHERE子句中使用MAX和MIN函数，进行子查询。

【实例6-32】在条件中使用最大值/最小值函数。

要求使用MAX和MIN函数，实现从BOOKINFO表中查询出版最早和出版最晚的图书记录。具体的相关脚本如下：

      01  SELECT BOOKID,BOOKNAME,PUBDATE
      02  FROM  BOOKINFO
      03  WHERE
      04  PUBDATE=(SELECT MAX(PUBDATE) FROM BOOKINFO)
      05  OR PUBDATE=(SELECT MIN(PUBDATE) FROM BOOKINFO)
      06  /

【代码解析】

◆ 第4行和第5行使用了子查询。

【执行效果】

执行上述脚本，效果见图6.33。

对于上面实例也可以不使用WHERE子句，而是通过ORDER BY子句按照PUBDATE字段对表中的所有记录进行排序，然后使用ROWNUM关键词，只选取结果集中的第1条记录。比如下面的一段脚本：

      SELECT BOOKID,BOOKNAME,PUBDATE
      FROM
      (SELECT ＊ FROM BOOKINFO
      ORDER BY PUBDATE DESC)
      WHERE ROWNUM = 1

图6.33 在条件中使用最大值/最小值函数

这样，可以获取最大值记录，如果把ORDER BY子句中的DESC关键词去除，就可以得到最小值记录。

如果记录中有多条记录拥有所需的最大值/最小值，就只能采用【实例6-32】所使用的子查询方式。

6.2.6 统计函数

STDDEV和STDDEV_POP函数用于获取给定记录数据的标准偏差。VARIANCE函数则用于获取给定记录数据的方差。这些函数只能作用于数值类型的数据，同样，NULL值将被忽略。这里只介绍STDDEV函数，它的简单语法结构如下：

      STDDEV([ distinct | all ] expr)

【语法说明】

◆ distinct：去除重复的记录。

◆ all：代表所有的记录，是默认选项。

◆ expr：表的列。

【实例6-33】获取记录的标准偏差。

要求实现从BOOKINFO表中查询所有图书价格的标准偏差和科学出版社图书的标准偏差。具体的脚本如下：

      SELECT '所有图书价格标准偏差' AS 类型,STDDEV(PRICE) AS 标准偏差值
      FROM  BOOKINFO
      UNION
      SELECT '大众出版社社图书价格标准偏差',STDDEV(PRICE)
      FROM  BOOKINFO
      WHERE PUBLISH='大众出版社'
      /

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.34。

图6.34 获取记录的标准偏差

其他函数的使用方法和该函数类似，这里不再一一介绍。

6.2.7 聚合函数的重值处理

与SELECT子句相同，在聚合函数中，也可以使用ALL、DISTINCT关键词，决定是对所选列中的所有记录还是只对非重值的记录进行统计计算。

在缺省状态下，默认是ALL关键词（在函数语法中已经解释过），即不管是否有重值，处理所有的数据。其他聚合函数的用法与此相同。

【实例6-34】聚合函数的重值处理。

要求使用COUNT函数分别统计BOOKINFO表中PUBLISH字段的所有记录和所有非重值记录的个数。具体脚本如下：

      01  SELECT COUNT(ALL PUBLISH) AS 所有记录,
      02  COUNT(DISTINCT PUBLISH) AS 去除重复
      03  FROM BOOKINFO
      04  /

【代码解析】

◆ 第1行表示查询所有记录。

◆ 第2行表示去除重复记录。

【执行效果】

在SQL＊Plus中执行以上脚本，具体效果见图6.35。

图6.35 去除重复记录

当然，在代码中去除ALL关键词，也可以得到相同的结果。对比两个结果，使用DISTINCT关键词后，PUBLISH列中的重值并没有列入统计范围之内。另外还要强调一点，除了COUNT(＊)函数外，其他的聚合函数在计算过程中都忽略NULL值，即把NULL值的行排除在外，不进行分析。

6.3 分组查询

在大多数情况下，使用聚合函数返回的是所有行数据的统计结果。但这通常不是开发人员所需要的，一般情况下需要按某一列数据的值进行分类，在分类的基础上再进行查询。这就需要使用GROUP BY子句进行分组查询了。

6.3.1 简单分组

创建分组是通过GROUP BY子句实现的。与WHERE子句不同，GROUP BY子句用于归纳信息类型，以汇总相关数据。下面给出一个简单的分组实例。

【实例6-35】简单分组。

要求使用GROUP BY子句，对BOOKINFO表中的记录按出版社分类，使用聚合函数求得各出版社图书的数量、均价和最高价格。相关脚本如下：

      01  SELECT PUBLISH,COUNT(＊) AS BOOK_COUNT,
      02  AVG(PRICE) AS AVG_PRICE,
      03  MAX(PRICE) AS MAX_PRICE
      04  FROM BOOKINFO
      05  GROUP BY PUBLISH
      06  ORDER BY PUBLISH

【代码解析】

◆ 第2行表示求平均值。

◆ 第3行表示求最大值。

◆ 第5行表示按PUBLISH列分组。

◆ 第6行表示按PUBLISH列排序。

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.36。

图6.36 简单分组操作

通过这个结果可以看出，所有的聚合函数都是对查询出的每一行数据进行分类后再进行统计计算的。所以在结果集中，对所进行分类的列的每一种数据都有一行统计结果值与之对应。

GROUP BY子句中不支持对列分配的别名，也不支持使用了统计函数的集合列。另外，对SELECT后面每一列数据，除了出现在统计函数中的列以外，都必须在GROUP BY子句中应用。

比如下面的查询代码就是错误的：

      SELECT BOOKNAME,PUBLISH,COUNT(＊) AS BOOK_COUNT,
      AVG(PRICE) AS AVG_PRICE,
      MAX(PRICE) AS MAX_PRICE
      FROM BOOKINFO
      GROUP BY PUBLISH
      ORDER BY PUBLISH

【执行效果】

执行以上脚本，执行效果见图6.37。

BOOKNAME列在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUP BY子句中。

图6.37 错误的分组查询

6.3.2 多列分组

上节介绍的使用GROUP BY子句进行分组查询，在GROUP BY子句中只有一列，它是分组查询的最简单形式。如果表中记录的分组依赖于多列，只要在查询的GROUP BY子句中列出定义分组所需的所有列即可。

在SELECT语句的GROUP BY子句中，列出的列的数目没有上限，对组合列的唯一限制是其必须是查询的FROM子句中列出的表中的列。

【实例6-36】根据多列进行分组。

要求使用GROUP BY子句，对BOOKINFO表中的记录按出版社和图书状态（STORE）分类，使用聚合函数求得各类图书的数量。具体脚本如下：

      01  SELECT PUBLISH,STORE,COUNT(＊) AS BOOK_COUNT
      02  FROM BOOKINFO
      03  GROUP BY PUBLISH,STORE
      04  ORDER BY PUBLISH
      05  /

【代码解析】

◆ 第3行表示根据PUBLISH和STORE两列进行分组。

◆ 第4行表示依据PUBLISH列进行排序。

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.38。

图6.38 多列分组

可见，聚合函数按照GROUP BY子句列出的这些列的唯一组合来进行统计计算。另外，在ORDER BY子句中，也可以根据要查询的信息（图书数量）对结果进行排序，比如下面所写的脚本：

      SELECT PUBLISH,STORE,COUNT(＊) AS BOOK_COUNT
      FROM BOOKINFO
      GROUP BY PUBLISH,STORE
      ORDER BY COUNT(＊),STORE
      /

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.39。

图6.39 排序结果

在代码中，ORDER BY子句的COUNT(＊)也可以替换为Book_Count。

6.3.3 分组查询中NULL值的处理

当GROUP BY子句中用于分组的列中出现NULL值时，将如何分组呢？在GROUP BY子句中，将所有的空值分在同一组，即认为它们是“相等”的。下面用一个实例进行说明。

【实例6-37】GROUP BY子句对空值的处理。

要求使用GROUP BY子句，对BOOKINFO表中的记录按借阅者（READER）分类，使用聚合函数求得各借阅者所借的图书数量。具体的相关脚本如下：

      01  SELECT READER,COUNT(＊) AS BOOK_COUNT
      02  FROM BOOKINFO
      03  GROUP BY READER
      04  ORDER BY COUNT(＊)
      05  /

【代码解析】

◆ 第3行表示以READER列进行分组。

◆ 第4行表示以COUNT(＊)的结果排序。

【执行效果】

在SQL＊Plus中执行以上脚本，最终效果见图6.40。

图6.40 空值的处理效果

从执行效果可以看出，READER列中的空值被划归为一组进行统计。

6.3.4 汇总数据运算符

在SELECT语句的GROUP BY子句中，为要分组的列使用CUBE和ROLLUP运算符，可以快捷、有效地对存储在数据库里的数据进行汇总分析。CUBE和ROLLUP是SQL的扩展命令，可以在Oracle 8i（及以上版本）中使用。

1.CUBE运算符

CUBE生成的结果集是多维数据集。多维数据集是事实数据（即记录个别事件的数据）的扩展。扩展是基于用户要分析的列建立的，这些列称为维度。多维数据集是结果集，其中包含各维度的所有可能组合的交叉表格。

下面通过具体实例讲解其使用方法。

【实例6-38】使用CUBE进行数据汇总。

要求实现与【实例6-36】相同的分组查询功能，只是使用CUBE运算符扩展查询结果。相关脚本如下：

      01  SELECT PUBLISH,STORE,COUNT(＊) AS BOOK_COUNT
      02  FROM BOOKINFO
      03  GROUP BY CUBE(PUBLISH,STORE)
      04  /

【代码解析】

◆ 第3行表示对PUBLISH列和STORE列使用CUBE扩展。

【执行效果】

为了看得更加清楚，作者选择在SQL Developer工具的SQL窗口中执行以上脚本，读者依然可以在SQL＊Plus中执行。执行效果见图6.41。

图6.41 使用CUBE汇总数据

可以发现，比【实例6-36】的运行结果（见图6.38）多出了很多数据。注意表中出现了很多额外的分组，包括空行，这些绝不会出现在标准的GROUP BY命令所返回的结果中，它们都是CUBE命令所添加的汇总项。

图6.41中第4行记录报告了在PUBLISH列中包含“大众出版社”值的所有行的数目小计。对STORE列返回了空值，用以表示该行报告的聚合包括STORE列为任意值的行。图中第1行记录报告了多维数据集的总计。PUBLISH和STORE维度（列）都包含空值，这表示此行中汇总了这两个维度（列）的所有值。

而有时，用户对借出和未借出（STORE列）图书的总数不感兴趣，只关心每个出版社借出图书的总数，就可以使用ROLLUP运算符。

2.ROLLUP运算符

在生成包含小计和合计的报表时，ROLLUP运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。CUBE和ROLLUP之间的区别在于：CUBE生成的结果集显示了所选列中值的所有组合的聚合；ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。

下面通过一个具体实例说明ROLLUP运算符的使用方法。

【实例6-39】使用ROLLUP进行数据汇总。

要求实现与【实例6-36】相同的分组查询功能，只是使用ROLLUP运算符扩展查询结果。相关脚本如下：

      01  SELECT PUBLISH,STORE,COUNT(＊) AS BOOK_COUNT
      02  FROM BOOKINFO
      03  GROUP BY ROLLUP(PUBLISH,STORE)

【代码解析】

◆ 第3行表示使用了ROLLUP扩展命令。

【执行效果】

为了看得更加清楚，作者选择在SQL Developer工具的SQL窗口中执行以上脚本，执行效果见图6.42。

图6.42 使用ROLLUP进行数据汇总

可见，与【实例6-38】的CUBE运算符相比，结果中只包含了PUBLIS列的统计信息和所有记录的统计信息，而没有包含STORE列的统计信息。

如果需要统计STORE列的信息，而不统计PUBLIS列的信息，则只需要在GROUP BY子句中将STORE列放在最前面即可。

3.区分不同的NULL值

使用CUBE和ROLLUP运算符操作生成空值将会带来一个问题：如何区分CUBE操作生成的NULL值和在实际数据中返回的NULL值？可以使用GROUPING函数解决此问题。如果列值来自实际数据，GROUPING函数将返回0；如果列值是由CUBE操作生成的NULL，则返回1。

在CUBE（ROLLUP）操作中，生成的NULL代表所有值。因此，可以编写SELECT语句，使用GROUPING函数判断NULL的来源，将生成的NULL替换为字符串“ALL”。而实际数据中的NULL表示数据值未知，因此也可以将其SELECT编码为返回字符串“UNKNOWN”，用于表示实际数据中的NULL。

【实例6-40】使用GROUPING函数判断NULL值。

要求在分组查询中使用GROUPING函数，区分使用ROLLUP运算符生成的NULL和实际数据中的NULL。具体脚本如下：

      01  SELECT CASE WHEN (GROUPING(PUBLISH) = 1)
      02      THEN 'ALL'
      03          ELSE NVL(PUBLISH, 'UNKNOWN')
      04      END AS PUBLISH,
      05      CASE WHEN (GROUPING(BOOKNAME) = 1)
      06      THEN 'ALL'
      07         ELSE NVL(BOOKNAME, 'UNKNOWN')
      08      END AS BOOKNAME,
      09  COUNT(＊) AS BOOK_COUNT
      10  FROM BOOKINFO
      11  GROUP BY ROLLUP(PUBLISH,BOOKNAME)

【代码解析】

◆ 代码使用了CASE WHEN语句，利用它进行了分支判断。

◆ 第1行表示判断PUBLISH列为空。

◆ 第2行表示当第1行判断为真时，该列数据则写成“ALL”。

◆ 第3行表示其他列的数据如果出现空的情况，则写成“UNKNOWN”。

◆ 第4行表示结束当前的判断。

◆ 第5～8行进行同样的操作。

◆ 第11行表示调用ROLLUP扩展命令。

【执行效果】

为了看得更加清楚，作者选择在SQL Developer工具的SQL窗口中执行以上脚本，执行效果见图6.43。

图6.43 GROUPlNG函数的使用效果

替换的数据类型要与该字段的数据类型相匹配，即只有字符类型的字段采用将其替换为“ALL”或者“UNKNOWN”。

6.3.5 筛选分组结果

利用GROUP BY子句分组，只是简单地依据所选列的数据进行分组，将该列具有相同值的行划为一组。而在实际应用中，往往还需要删除那些不能满足条件的行组，为了实现这个功能，SQL提供了HAVING子句指定组或聚合的搜索条件。

HAVING通常与GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的行为与WHERE子句一样。其语法结构如下：

      HAVING search_conditions

【语法说明】

◆ search_conditions：HAVING子句的条件。

【实例6-41】用HAVING子句筛选分组结果。

要求在分组查询中使用HAVING子句，将BOOKINFO表中的记录按出版社（PUBLISH）进行分组，选取拥有价格高于40元的图书，数量大于2的出版社并且其拥有价格高于30元。实例代码如下：

      01  SELECT PUBLISH,COUNT(＊) AS BOOK_COUNT
      02  FROM BOOKINFO
      03  WHERE PRICE＞40
      04  GROUP BY PUBLISH
      05  HAVING COUNT(＊)＞2

【代码解析】

◆ 第1行给出了所有的查询列表。

◆ 第4行表示分组。

◆ 第5行利用HAVING子句，给出了COUNT(＊)大于2的条件。

【执行效果】

在SQL＊Plus中执行以上脚本，执行效果见图6.44。

图6.44 HAVlNG子句的使用

HAVING子句和WHERE子句的相似之处在于，它也定义搜索条件。但与WHERE子句不同， HAVING子句与组有关，而不是与单个的记录行有关。

◆ 如果指定了GROUP BY子句，那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组上。

◆ 如果指定WHERE子句，而没有指定GROUP BY子句，那么HAVING子句定义的搜索条件将作用于WHERE子句的输出上，并把这个输出看作一个组。

◆ 如果既没有指定GROUP BY子句也没有指定WHERE子句，那么HAVING子句定义的搜索条件将作用于FROM子句的输出上，并把这个输出看作一个组。

有关WHERE子句的所有操作，如使用连接符、通配符、函数等，在HAVING子句中都可以使用。

6.4 其他函数

前面介绍了单行函数以及聚合函数在查询中的使用，下面介绍几个属于系统环境和编码方面的函数。

6.4.1 返回登录名函数

USER函数可以得到当前会话的登录名。

【实例6-42】演示USER函数的用法。

要求得到当前的登录用户名，具体的操作步骤见图6.45。

图6.45 得到当前的登录用户名

6.4.2 返回SESSlONlD和LANGUAGE函数

保留USERENV函数是为了保持向下兼容，利用它可以获取当前SESSION中的信息。具体的语法结构如下：

      USERENV(OPTION)

【语法说明】

◆ OPTION：获取信息对象，可以是“ISDBA”、“SESSIONID”、“INSTANCE”、“LANGUAGE”等。

◆ ISDBA：表示当前用户是否为DBA角色。

◆ SESSIONID：表示当前会话ID。

◆ INSTANCE：表示当前实例。

◆ LANGUAGE：表示当前数据库字符集。

【实例6-43】获取当前连接SESSIONID和LANGUAGE。

要求获取当前连接的SESSIONID和LANGUAGE，具体的操作步骤见图6.46。

图6.46 USERENV函数的用法

其实作者更推荐使用SYS_CONTEXT函数调用USERENV命名空间来获取相关信息，它的简单语法结构如下：

      SYS_CONTEXT('namespace', 'parameter')

【语法说明】

◆ namespace：命名空间，可以使用USERENV参数。

◆ parameter：命名空间对应的参数名，可以是“DB_NAME”、“ISDBA”、“SESSIONID”、“INSTANCE”、“LANGUAGE”、“SERVER_HOST”等。有关USERENV命名的相关参数Oracle数据库给出了一个列表，这里只列出了一些常用的选项。

【实例6-44】SYS_CONTEXT函数的使用方法。

要求利用SYS_CONTEXT函数获取当前用户是否是DBA，以及当前数据库的LANGUAGE。具体的操作步骤见图6.47。

图6.47 SYS_CONTEXT函数的使用方法

由此可见，它和直接使用USERENV函数的效果是一样的。但这里建议读者使用SYS_CONTEXT函数获取相关信息。

6.4.3 数据匹配函数

DECODE函数将输入值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当没有匹配结果时，将返回默认值。相关语法结构如下：

      DECODE(expr,search,result[,search1,result1][,default])

【语法说明】

◆ 当expr参数符合条件search时就返回result的值；当符合search1条件时，将返回result1；该判断可以重复操作，当最后没有匹配的结果时，则返回默认值default。注意：它是一对一的匹配过程。

【实例6-45】演示DECODE函数的使用方法。

要求利用DECODE函数判断图书价格高于50元的提示“高”，低于50元的提示“低”。具体的相关脚本如下：

      01  SELECT BOOKNAME,PUBLISH,PRICE,DECODE(SIGN(PRICE-50),1,'高',-1,'低')
      02  FROM BOOKINFO

【代码解析】

◆ 第1行中的SIGN函数属于数值类型的函数，它带有一个参数，该函数可以返回参数的正负，当参数为正数时返回1，为0时返回0，为负数时返回-1，利用它可以得到图书价格是否高于50元。

◆ 第1行中的DECODE表示当价格高于50元时，也就是返回结果为1时，得到“高”提示，反之得到“低”提示。

【执行效果】

在SQL＊Plus中执行以上脚本，具体效果见图6.48。

图6.48 DECODE函数的使用效果

6.5 小结

本章介绍了查询中常用的单行函数和聚合函数，还详细介绍了在SELECT查询语句中使用GROUP BY子句和HAVING子句进行分组查询。通过本章的讲解，有关SELECT查询语句的所有子句都介绍完毕，相信读者学习完这些内置函数的使用方法后，能够熟练地完成日常的查询操作。