.Net程序员学用Oracle系列(11)：系统函数（下）

2023-10-09 08:58
文章标签 oracle 系统 函数 程序员 系列 net 学用

本文主要是介绍.Net程序员学用Oracle系列(11)：系统函数（下），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

  • 1、聚合函数
    • 1.1、COUNT 函数
    • 1.2、SUM 函数
    • 1.3、MAX 函数
    • 1.4、MIN 函数
    • 1.5、AVG 函数
  • 2、ROWNUM 函数
    • 2.1、ROWNUM 函数简介
    • 2.2、利用 ROWNUM 函数实现分页功能
  • 3、高级函数
    • 3.1、高级函数简介
    • 3.2、语法说明及案例
  • 4、总结

1、聚合函数

常见的聚合函数有 COUNT、SUM、MAX、MIN、AVG 共 5 个，它们都可以对一组值执行聚合计算，并返回单个值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。默认所有聚合函数都会忽略 NULL 值，其中 COUNT 函数稍有不同，本人觉得 SQL 的这个设计还是蛮贴心的，因为这会帮开发者规避掉很多繁琐的细节问题。

因为 COUNT 函数返回值是数据集的行数，与统计字段的值大小无关，所以也可以让 COUNT 函数统计所有行，即不忽略 NULL 值。如果需要 COUNT 函数统计字段值为 NULL 的行，只需要将真实的字段名换成 * 或者是一个具体的常量或变量，如：1、ROWNUM 等。实际开发中一般没人会用变量（不易理解），本人推荐一律用常量（一般来说比用 * 要高效）。

1.1、COUNT 函数

语法：

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

单独使用：如要统计研发一部的人数。示例：

SELECT COUNT(t.staff_id) count_staff FROM demo.t_staff t WHERE t.dept_code='010101';

结合 GROUP BY 使用：如要统计开发部下各三级部门的人数。示例：

SELECT t.dept_code,COUNT(t.staff_id) count_staff 
FROM demo.t_staff t
WHERE INSTR(t.dept_code,'0101')=1 
GROUP BY t.dept_code;

1.2、SUM 函数

语法：

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

单独使用：如要统计研发一部的固定工资之和。示例：

SELECT SUM(v.fixed_salary) sum_salary FROM demo.v_staff v WHERE v.dept_code='010101';

结合 GROUP BY 使用：如要统计开发部下各三级部门的固定工资之和。示例：

SELECT v.dept_code,SUM(v.fixed_salary) sum_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

1.3、MAX 函数

语法：

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

单独使用：如要统计研发一部的最高工资。示例：

SELECT MAX(v.fixed_salary) max_salary FROM demo.v_staff v WHERE v.dept_code='010101';

结合 GROUP BY 使用：如要统计开发部下各三级部门的最高工资。示例：

SELECT v.dept_code,MAX(v.fixed_salary) max_salary FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 GROUP BY v.dept_code;

1.4、MIN 函数

语法：

SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
MIN(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

单独使用：如要统计研发一部的最低工资。示例：

SELECT MAX(v.fixed_salary) min_salary FROM demo.v_staff v WHERE v.dept_code='010101';

结合 GROUP BY 使用：如要统计开发部下各三级部门的最低工资。示例：

SELECT v.dept_code,MAX(v.fixed_salary) min_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

1.5、AVG 函数

语法：

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

单独使用：如要统计研发一部的平均工资。示例：

SELECT AVG(v.fixed_salary) avg_salary FROM demo.v_staff v WHERE v.dept_code='010101';

结合 GROUP BY 使用：如要统计开发部下各三级部门的平均工资。示例：

SELECT v.dept_code,AVG(v.fixed_salary) avg_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

2、ROWNUM 函数

2.1、ROWNUM 函数简介

SQL 标准中规定了 SELECT TOP 语法，用于限制查询返回的行数，如 (MS)SQL Server 就实现了标准的 SELECT TOP；而 Oracle 却没有直接实现，但 Oracle 中的 ROWNUM 函数功能与 TOP 极其相似，也算是间接的实现了 TOP 吧！ROWNUM 函数的语法比较灵活，相应的语法陷阱也比较多，多数初学者由于对 ROWNUM 函数理解不够透彻，时常会写出令自己差异的语句。

在执行查询的时候，Oracle 会顺序的把查询结果集的行编号赋值给 ROWNUM 函数。这里有两个需要注意的细节问题：

  • 第一个，既然 ROWNUM 的值来自于结果集的行编号，那么也就是说先有结果集，然后才有 ROWNUM 值的。
  • 第二个，行编号从 1 开始，逐行递增。也就是说，ROWNUM 的值集总是一个首项为 1，公差为 1 的等差数列。

很多人都喜欢称 ROWNUM 为伪列，我猜一方面由于 Oracle 中“伪造”的对象相对较多，另一方面 ROWNUM 的用法真心跟普通列没啥区别，就想真有 ROWNUM 列一样。从这个角度来说，把 ROWNUM 叫伪列也很贴切。示例：

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM < 1; -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM = 1; -- res: 1

这两条语句应该比较好理解的，因为 ROWNUM 的值从 1 开始，所以 ROWNUM < 1 的记录有 0 条，只有第 1 条记录的 ROWNUM = 1。在 (MS)SQL Server 中查前 N 条记录只需在查询字段列表前加上 TOP N 即可，若要在 Oracle 中实现类似功能则只需在 WHERE 条件中加上 ROWNUM <= N。示例：

-- 查询年龄最小的 3 名员工的姓名、出生日期、基本工资和岗位工资
SELECT * FROM(SELECT t.staff_name,t.birthday,t.base_salary,t.post_salary FROM demo.t_staff t ORDER BY t.birthday DESC) WHERE ROWNUM<=3;
结果：
STAFF_NAME                                         BIRTHDAY     BASE_SALARY  POST_SALARY
-------------------------------------------------- ----------- ------------ ------------
小玲                                               1994-06-17       2500.00      2900.00
韩三                                               1993-08-18       2500.00      5050.00
王二                                               1992-09-02       2500.00      1850.00

再来看看如下 6 条 SQL 语句（目前员工表中总数据条数为 16）：

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM <= 10; -- res: 10
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM >= 1;  -- res: 16
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM = 10;  -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM > 1;   -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM != 1;  -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM != 10; -- res: 9

前两条语句似乎很好理解，如果给员工表的行按数字从 1、2、3 …… 16 编个号，那么其中编号 <= 10 的有 10 行，即第 1 条语句的结果，编号 >= 1 的有 16 行，即第 2 条语句的结果。但为什么第 3、4、5 条语句的结果全都是 0 呢？从数学角度来看似乎说不通啊？这就牵扯到上文提到的两个细节问题了，首先 ROWNUM 是结果集的行编号，有结果集才会有行编号，而 ROWNUM 永远都是从 1 开始的，换句话说选出的结果集不可能没有 ROWNUM = 1 的行。结合本例也可以这么来理解，ROWNUM/行编号总是从 1 开始与运算符右边的数字做比较，若结果为 TRUE，则该行被选出，并继续用下一个 ROWNUM/行编号做比较，若结果为 FALSE，则下一行过来后 ROWNUM/行编号还是 1，如此循环，就不会产生结果行，效果上相当于停止了比较，最终的结果集为之前所有被选出行的集合。再来看第 3 条语句，由于 1 != 10，所以结果为 0 行。同理，第 4、5 条语句中，1 不 > 1，也不 != 1，所以结果也是 0 行。第 6 条语句中，从 1 到 9 都 != 10，所以能被选出，而 10 不 != 10，从第 10 行开始往后的每一行 ROWNUM 都是 10，都不会被选出（始终选不出第 10 行记录），所以最终结果是 9 行。讲到这里，相信你已经能够自己分析出下面两条语句的结果了吧！

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM BETWEEN 1 AND 5; -- res: 5
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM BETWEEN 2 AND 5; -- res: 0

本人刚接触 Oracle 时，总是习惯性的把 ROWNUM 写成 ROW_NUMBER()，一执行就报错，然后改成 ROW_NUMBER、ROW_NUM、ROW 等再执行还是报错，直到很熟练之后才能一次写对。后来想想，主要是此前在 (MS)SQL Server 中用过 ROW_NUMBER()，但并不熟练，且 Oracle 中也有 ROW_NUMBER()，实际上在 Oracle 中还有 ROWID、ROWS、ROW 等都是关键字。对一个初学者而言，如果不及早把这些关键字罗列到一起，仔细区分它们的含义和写法，着实容易张冠李戴甚至拼写错误。本人在此浓墨重笔，只愿读者你不再和我走一样的弯路！

2.2、利用 ROWNUM 函数实现分页功能

普通分页：若把员工表中数据按每页 5 条来分页，下面将演示取第 2 页数据的 3 种普通分页写法。
写法一：

SELECT t.staff_name,t.birthday FROM(
SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0
) t WHERE t.rn >= ((2-1)*5+1) AND t.rn <= (2*5); -- 或 WHERE t.rn BETWEEN 6 AND 10

写法二：

SELECT t.staff_name,t.birthday FROM(
SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0 AND ROWNUM <= (2*5)
) t WHERE t.rn >= ((2-1)*5+1);

写法三：

SELECT t.staff_name,t.birthday FROM demo.t_staff t WHERE t.is_disabled=0 AND ROWNUM <= (2*5)
MINUS
SELECT t.staff_name,t.birthday FROM demo.t_staff t WHERE t.is_disabled=0 AND ROWNUM <= ((2-1)*5+1)

上例中的 MINUS 是补集运算符，将在.Net程序员学用Oracle系列(14)：子查询、集合查询中具体讲解。

排序分页：实际开发过程中，一般分页都需要排序，可能用 ROWNUM 写过排序分页功能的开发人员都曾遇到过一个陷阱，且听我细细道来。沿用上半节的案例，再加一个按出生日期顺序排序，错误示例（仅分析写法一，其它写法原理相同）：

SELECT t.staff_name,t.birthday FROM(
SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0 ORDER BY n.birthday
) t WHERE t.rn >= ((2-1)*5+1) AND t.rn <= (2*5) ORDER BY t.birthday;

没有这方面经验的开发人员十有八九会改写成上面这样，如果你改动一下页码就会发现这个分页的排序根本就不对，给人感觉好像子查询里的那个 ORDER BY 压根儿就没起作用。当初我第一次遇到这个陷阱时，也是诧异万分，其实是因为在这个子查询中，会先选出满足 WHERE 条件的记录，并按物理存储位置(ROWID)顺序给 ROWNUM 赋值，然后再按 ORDER BY 的字段进行排序，而外部查询是先选出满足 ROWNUM 条件的记录，然后再按 ORDER BY 的字段进行排序，所以子查询中的 ORDER BY 会失效。正确示例：

SELECT t3.staff_name,t3.birthday FROM(
SELECT ROWNUM rn,t2.staff_name,t2.birthday FROM(
SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 WHERE t1.is_disabled=0 ORDER BY t1.birthday
) t2
) t3 WHERE t3.rn >= ((1-1)*5+1) AND t3.rn <= (1*5);

注意：有一种特殊情况，就是当 ORDER BY 的字段是主键时，Oracle 会先生成 ROWNUM，然后再来排序。

3、高级函数

3.1、高级函数简介

在 Oracle 提供的高级函数中，除去分析函数仍有 20 来个。本人感觉大部分还是比较实用的，譬如 DECODE、NVL2、LNNVL 等函数都挺好用的，唯一的问题是——它们都是 Oracle 的“方言”。在数据库编程规范的第 4 节中，已经阐述了为什么要尽量使用 SQL 标准而不是 Oracle 的“方言”。本节将列出 15 个我个人感觉还比较实用的函数，但只会具体讲解 CASE、NVL、SQLCODE 和 SQLERRM 4 个在 Oracle 中找不到替代方案的函数，以及 USER 和 USERENV 两个在处理系统权限或环境问题时可能会用上的函数，其它函数不推荐使用，暂不介绍，有兴趣的读者可自行研究。

序号函数名语法原型常用
1CASECASE [ expression ] WHEN condition_1 THEN result_1 ... WHEN condition_n THEN result_n ELSE result END√
2DECODEDECODE( expression , search , result [, search , result]... [, default] )×
3EMPTY_BLOBEMPTY_BLOB()×
4EMPTY_CLOBEMPTY_CLOB()×
5GROUP_IDGROUP_ID()×
6LNNVLLNNVL( condition )×
7NANVLNANVL( value, replace_with )×
8NULLIFNULLIF( expr1, expr2 )×
9NVLNVL( string1, replace_with )√
10NVL2NVL2( string1, value_if_not_null, value_if_null )×
11SQLCODESQLCODE×
12SQLERRMSQLERRM×
13SYS_CONTEXTSYS_CONTEXT( namespace, parameter [, length] )×
14USERUSER×
15USERENVUSERENV( parameter )×

3.2、语法说明及案例

SQLCODE & SQLERRM：当需要在过程中处理异常时，就会用到这两个函数。经典示例：

EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;

USER：当前登录的用户的 USERNAME。实际开发中，有时候我们需要知道当前登录用户到底是那一个，这个函数就派上用场了。示例：

SELECT USER res FROM DUAL;  -- res：DEMO

USERENV：用于检索当前 Oracle 会话信息，可检索信息的代码包括：{CLIENT_INFO/ENTRYID/INSTANCE/ISDBA/LANG/LANGUAGE/SESSIONID/TERMINAL}。这个函数替代函数是 SYS_CONTEXT，但这个功能仍然存在向后兼容，不过本人只在开发过程中用用，不必考虑这些因素。下面四个是我觉得比较实用的参数，尤其是第三个，在解决某些由编码造成的问题时很有用。示例：

SELECT USERENV('ISDBA') res FROM DUAL;    -- res：FALSE
SELECT USERENV('LANG') res FROM DUAL;     -- res：ZHS
SELECT USERENV('LANGUAGE') res FROM DUAL; -- res：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SELECT USERENV('TERMINAL') res FROM DUAL; -- res：HZZ-PC

4、总结

本文主要讲述了 Oracle 中两种十分常用而又极其特殊的数字函数和部分高级函数。从上上篇博文到上一篇博文，再到本文，本人根据自己工作需要和个人喜好，把 Oracle 中常用的一些系统函数基本都介绍了一遍。

本文链接：http://www.cnblogs.com/hanzongze/p/Oracle-SystemFunction-3.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

这篇关于.Net程序员学用Oracle系列(11)：系统函数（下）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/171721。

相关文章

MySQL中FIND_IN_SET函数与INSTR函数用法解析

MySQL中FIND_IN_SET函数与INSTR函数用法解析

《MySQL中FIND_IN_SET函数与INSTR函数用法解析》：本文主要介绍MySQL中FIND_IN_SET函数与INSTR函数用法解析,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一... 目录一、功能定义与语法1、FIND_IN_SET函数2、INSTR函数二、本质区别对比三、实际场景案例分
阅读更多...
利用Python快速搭建Markdown笔记发布系统

利用Python快速搭建Markdown笔记发布系统

《利用Python快速搭建Markdown笔记发布系统》这篇文章主要为大家详细介绍了使用Python生态的成熟工具,在30分钟内搭建一个支持Markdown渲染、分类标签、全文搜索的私有化知识发布系统... 目录引言：为什么要自建知识博客一、技术选型：极简主义开发栈二、系统架构设计三、核心代码实现（分步解析
阅读更多...
C++ Sort函数使用场景分析

C++ Sort函数使用场景分析

《C++Sort函数使用场景分析》sort函数是algorithm库下的一个函数,sort函数是不稳定的,即大小相同的元素在排序后相对顺序可能发生改变,如果某些场景需要保持相同元素间的相对顺序,可使... 目录C++ Sort函数详解一、sort函数调用的两种方式二、sort函数使用场景三、sort函数排序
阅读更多...
C语言函数递归实际应用举例详解

C语言函数递归实际应用举例详解

《C语言函数递归实际应用举例详解》程序调用自身的编程技巧称为递归,递归做为一种算法在程序设计语言中广泛应用,：本文主要介绍C语言函数递归实际应用举例的相关资料,文中通过代码介绍的非常详细,需要的朋... 目录前言一、递归的概念与思想二、递归的限制条件 三、递归的实际应用举例（一）求 n 的阶乘（二）顺序打印
阅读更多...
C/C++错误信息处理的常见方法及函数

C/C++错误信息处理的常见方法及函数

《C/C++错误信息处理的常见方法及函数》C/C++是两种广泛使用的编程语言,特别是在系统编程、嵌入式开发以及高性能计算领域,：本文主要介绍C/C++错误信息处理的常见方法及函数,文中通过代码介绍... 目录前言1. errno 和 perror()示例：2. strerror()示例：3. perror(
阅读更多...
Oracle数据库常见字段类型大全以及超详细解析

Oracle数据库常见字段类型大全以及超详细解析

《Oracle数据库常见字段类型大全以及超详细解析》在Oracle数据库中查询特定表的字段个数通常需要使用SQL语句来完成,：本文主要介绍Oracle数据库常见字段类型大全以及超详细解析,文中通过... 目录前言一、字符类型（Character）1、CHAR：定长字符数据类型2、VARCHAR2：变长字符数
阅读更多...
Python FastAPI+Celery+RabbitMQ实现分布式图片水印处理系统

Python FastAPI+Celery+RabbitMQ实现分布式图片水印处理系统

《PythonFastAPI+Celery+RabbitMQ实现分布式图片水印处理系统》这篇文章主要为大家详细介绍了PythonFastAPI如何结合Celery以及RabbitMQ实现简单的分布式... 实现思路FastAPI 服务器Celery 任务队列RabbitMQ 作为消息代理定时任务处理完整
阅读更多...
Linux系统中卸载与安装JDK的详细教程

Linux系统中卸载与安装JDK的详细教程

《Linux系统中卸载与安装JDK的详细教程》本文详细介绍了如何在Linux系统中通过Xshell和Xftp工具连接与传输文件,然后进行JDK的安装与卸载,安装步骤包括连接Linux、传输JDK安装包... 目录1、卸载1.1 linux删除自带的JDK1.2 Linux上卸载自己安装的JDK2、安装2.1
阅读更多...
Kotlin 作用域函数apply、let、run、with、also使用指南

Kotlin 作用域函数apply、let、run、with、also使用指南

《Kotlin作用域函数apply、let、run、with、also使用指南》在Kotlin开发中,作用域函数（ScopeFunctions）是一组能让代码更简洁、更函数式的高阶函数,本文将... 目录一、引言：为什么需要作用域函数？二、作用域函China编程数详解1. apply：对象配置的 “流式构建器”最
阅读更多...
Android Kotlin 高阶函数详解及其在协程中的应用小结

Android Kotlin 高阶函数详解及其在协程中的应用小结

《AndroidKotlin高阶函数详解及其在协程中的应用小结》高阶函数是Kotlin中的一个重要特性,它能够将函数作为一等公民（First-ClassCitizen）,使得代码更加简洁、灵活和可... 目录1. 引言2. 什么是高阶函数？3. 高阶函数的基础用法3.1 传递函数作为参数3.2 Lambda
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2