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

1、普通聚合函数

一般关系型数据库(如 Oracle、SQL Server 等)都会内置的 5 个聚合函数,分别是 COUNT、SUM、MAX、MIN、AVG,它们是最普通、最常用的聚合函数。聚合函数通常与 GROUP BY 子句一起使用。默认所有聚合函数都会忽略 NULL 值,其中 COUNT 函数稍有不同,本人觉得 SQL 的这个设计还是蛮贴心的,因为这会帮开发者规避掉很多繁琐的细节问题。

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

1.1、COUNT 函数

函数语法:COUNT({ * | [ DISTINCT | ALL ] expr })。函数功能:返回查询所返回的行数。查询语法:

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 函数

函数语法:SUM([ DISTINCT | ALL ] expr)。函数功能:返回 expr 的值的和。查询语法:

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 函数

函数语法:MAX([ DISTINCT | ALL ] expr)。函数功能:返回 expr 的最大值。查询语法:

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 函数

函数语法:MIN([ DISTINCT | ALL ] expr)。函数功能:返回 expr 的最小值。查询语法:

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 函数

函数语法:AVG([ DISTINCT | ALL ] expr)。函数功能:返回 expr 的平均值。查询语法:

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

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

  • 第 1 个,既然 ROWNUM 的值来自于结果集的行编号,那么也就是说先有结果集,然后才有 ROWNUM 值的。
  • 第 2 个,行编号从 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。在 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 等再执行还是报错,直到很熟练之后才能一次写对。后来想想,主要是此前在 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 来个。本人感觉大部分还是比较实用的,譬如 DECODENVL2LNNVL 等函数都挺好用的,唯一的问题是——它们都是 Oracle 的“方言”。在数据库编程规范的第 4 节中,已经阐述了为什么要尽量使用 SQL 标准而不是 Oracle 的“方言”。本节将列出 15 个我个人感觉还比较实用的函数,但只会具体讲解 CASENVLSQLCODESQLERRM 4 个在 Oracle 中找不到替代方案的函数,以及 USERUSERENV 两个在处理系统权限或环境问题时可能会用上的函数,其它函数不推荐使用,暂不介绍,有兴趣的读者可自行研究。

序号 函数名 语法原型 常用
1 CASE CASE [ expression ] WHEN condition_1 THEN result_1 ... WHEN condition_n THEN result_n ELSE result END
2 DECODE DECODE( expression , search , result [, search , result]... [, default] ) ×
3 EMPTY_BLOB EMPTY_BLOB() ×
4 EMPTY_CLOB EMPTY_CLOB() ×
5 GROUP_ID GROUP_ID() ×
6 LNNVL LNNVL( condition ) ×
7 NANVL NANVL( value, replace_with ) ×
8 NULLIF NULLIF( expr1, expr2 ) ×
9 NVL NVL( string1, replace_with )
10 NVL2 NVL2( string1, value_if_not_null, value_if_null ) ×
11 USER USER ×
12 USERENV USERENV( parameter ) ×
13 SYS_CONTEXT SYS_CONTEXT( namespace, parameter [, length] ) ×

3.2、语法说明及案例

USER:当前登录的用户的用户名。在实际开发中,当需要使用当前登录用户的 USER 或 SCHEMA 时,该函数就派上用场了。示例:

SELECT USER res FROM DUAL;  -- res:DEMO

USERENV & SYS_CONTEXT:这两个函数都用于检索当前 Oracle 及会话的信息。不过USERENV是个遗留函数,该函数相关功能具有向后的兼容性,但官方还是建议用SYS_CONTEXT替代之。比之USERENVSYS_CONTEXT更为强大,能检索更多的信息(包括USERENV函数能提供的全部信息)。

USERENV 示例:

SELECT
USERENV('ISDBA') isdba, -- 若当前用户是 DBA,则为 TRUE,否则 FALSE
USERENV('TERMINAL') terminal, -- 当前客户端的计算机名
USERENV('SESSIONID') sessionid, -- 当前会话的标识符
USERENV('LANG') lang, -- 当前语言名称的(ISO)缩写
USERENV('LANGUAGE') charset -- 当前数据库的字符集
FROM DUAL;

SYS_CONTEXT 示例:

SELECT
SYS_CONTEXT('USERENV','ISDBA') isdba, -- 若当前用户是 DBA,则为 TRUE,否则 FALSE
SYS_CONTEXT('USERENV','TERMINAL') terminal, -- 当前客户端的计算机名
SYS_CONTEXT('USERENV','SESSIONID') sessionid, -- 当前会话的标识符
SYS_CONTEXT('USERENV','LANG') lang, -- 当前语言名称的(ISO)缩写
SYS_CONTEXT('USERENV','LANGUAGE') charset, -- 当前数据库的字符集
SYS_CONTEXT('USERENV','IP_ADDRESS') ip, -- 客户端计算机的 IP 地址
SYS_CONTEXT('USERENV','HOST') host, -- 客户端的主机名称
SYS_CONTEXT('USERENV','OS_USER') os, -- 客户端的操作系统用户名
SYS_CONTEXT('USERENV','DB_NAME') db, -- 数据库的实例名
SYS_CONTEXT('USERENV','CURRENT_SCHEMA') cschema, -- 当前会话的 SCHEMA
SYS_CONTEXT('USERENV','CURRENT_USER') cuser, -- 当前会话的 USER
SYS_CONTEXT('USERENV','CURRENT_USERID') cuid, -- 当前会话的 USERID
SYS_CONTEXT('USERENV','SESSION_USER') suser, -- 当前会话所属的 USER
SYS_CONTEXT('USERENV','SESSION_USERID') suid, -- 当前会话所属的 USERID
SYS_CONTEXT('USERENV','NLS_TERRITORY') territory, -- 日期和货币的格式
SYS_CONTEXT('USERENV','NLS_CURRENCY') currency, -- 货币符号
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') date_format, -- 日期格式
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') date_lang, -- 日期语言
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') protocol -- 协议
FROM DUAL;

4、总结

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

本文链接http://www.cnblogs.com/hanzongze/p/oracle-systemfunction-3.html

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

上一篇:.Net程序员学用Oracle系列(15):DUAL、ROWID、NULL


下一篇:eclipse maven jar工程导出项目依赖的jar包