05oracle分页查询、常用函数及集合运算

1、分页查询

1.1、简单分页

需求：分页查询台账表 T_ACCOUNT，每页 10 条记录
分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询
我们首先显示前 10 条记录，语句如下：

select rownum,t.* from T_ACCOUNT t where rownum<=10

那么我们显示第 11 条到第 20 条的记录呢？编写语句：

select rownum,t.* from T_ACCOUNT t
where rownum>10 and rownum<=20

嗯？怎么没有结果？
这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。那怎么办呢？我们可以使用子查询来实现

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>10

即先让rownum生成，并取出小于的部分，然后再取大于的部分，这种两层嵌套查询能解决简单的分页

1.2、基于排序的分页

但有很多业务场景中，我们需要先进行排序，再进行分页，并且rownum的顺序还不能乱，这时我们需要用到三层嵌套查询，来实现我们想要的效果

select * from
(select rownum num ,tmptable.*  from (select * from T_ACCOUNT  order by usenum desc) tmptable)
 where num>10 and num<=20

总结：在遇到需要用到伪列rownum辅助进行分析时，记住rownum的生成时机是在遍历每一行的时候产生，遇到问题从此出发就可解决。

2、单行函数

2.1、字符函数

函 数	说 明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串，与
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（以字节）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
LOWER	将字符串转换成小写
UPPER	将字符串变为大写

2.1.1、求字符串长度 LENGTH

语句：

select length('ABCD') from dual;

这里说明一下，本来‘12345’这个字符串时跟任何表都没有关系的，但是oracle严格遵循sql语法标准，查询的时候必须得有from子句，为了满足这个要求，系统提供了一个伪表dual，这个表实际没有意义，只是方便我们在查询与具体表无关的，系统数据时使用，例如查询系统时间

2.1.2、求字符串的子串 SUBSTR

语句：

select substr('ABCD',2,2) from dual;

2.1.3、字符串拼接 CONCAT

语句：

select concat('ABC','D') from dual;

我们也可以用|| 对字符串进行拼接

select 'ABC'||'D' from dual;

2.2、数值函数

函数	说明
ABS(value)	绝对值
CEIL(value)	大于或等于 value 的最小整数
COS(value)	余弦
COSH(value)	反余弦
EXP(value)	e 的 value 次幂
FLOOR(value)	小于或等于 value 的最大整数
LN(value)	value 的自然对数
LOG(value)	value 的以 10 为底的对数
MOD(value,divisor)	求模
POWER(value,exponent)	value 的 exponent 次幂
ROUND(value,precision)	按 precision 精度 4 舍 5 入
SIGN(value) value	为正返回 1;为负返回-1;为 0 返回 0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value 的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,按 precision)	按照 precision 截取 value
VSIZE(value)	返回 value 在 ORACLE 的存储空间大小

2.2.1、四舍五入函数 ROUND

语句：

select round(100.567,2) from dual

2.2.2、截取函数 TRUNC

语句：

select trunc(100.567) from dual

select trunc(100.567,2) from dual

2.2.3、取模 MOD

语句：

select mod(10,3) from dual

3、日期函数

函 数	描 述
ADD_MONTHS	在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .)	从日期列表中选出最晚的日期
LAST_DAY( date )	返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .)	从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1)	给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’)	给出日期 date 之后下一天的日期，这里的 day 为星期，如： MONDAY,Tuesday 等。
NEW_TIME(date,’this’,’ other’)	给出在 this 时区=Other 时区的日期和时间
ROUND(date,’format’)	未指定 format 时，如果日期中的时间在中午之前，则将日期中的时间截断为 12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为 12 A.M.(午夜，一天的开始), 否则进到第二天。
TRUNC(date,’format’)	未指定 format 时，将日期截为 12 A.M.( 午夜，一天的开始)

3.1、加月函数 ADD_MONTHS ：在当前日期基础上加指定的月

语句：

select add_months(sysdate,2) from dual

3.2、求所在月最后一天 LAST_DAY

语句：

select last_day(sysdate) from dual

3.3、日期截取 TRUNC

语句：

select TRUNC(sysdate) from dual

select TRUNC(sysdate,'yyyy') from dual

select TRUNC(sysdate,'mm') from dual

这里如果想要截取到分，formate=‘mi’，不能按秒截取

4、转换函数

函 数	描 述
CHARTOROWID	将 字符转换到 rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到 raw 类型
RAWTOHEX	转换 raw 到十六进制
ROWIDTOCHAR	转换 ROWID 到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

4.1、数字转字符串 TO_CHAR

语句：

select TO_CHAR(1024) from dual

4.2、日期转字符串 TO_CHAR

语句：

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

4.3、字符串转日期 TO_DATE

语句：

select TO_DATE('2021-10-01','yyyy-mm-dd') from dual

4.4、字符串转数字 TO_NUMBER

语句：

select to_number('100') from dual

4.5、其它函数

4.5.1、空值处理函数 NVL

用法：

NVL（检测的值，如果为 null 的值）；

语句：

select NVL(NULL,0) from dual

需求：
显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
语句：

select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1

4.5.2、空值处理函数 NVL2

用法：

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；

需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不
限”.

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1

4.5.3、 nullif(a,b)

当a=b的时候，返回null，否则返回a

4.5.4、coalesce(a,b,c,d)

从左往右查找，当找到第一个不为null的值的时候，就显示这第一个有值的值。

4.5.5、条件取值 decode

语法：
decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值
需求：显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）
语句：

select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位
',3,'商业') as 类型 from T_OWNERS

上边的语句也可以用 case when then 语句来实现

select name ,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其它'
end
) from T_OWNERS

还有另外一种写法：

select name,(case
when ownertypeid= 1 then '居民'
when ownertypeid= 2 then '行政事业'
when ownertypeid= 3 then '商业'
end )

5、行列转换

需求：按月份统计 2012 年各个地区的水费

select (select name from T_AREA where id= areaid ) 区域,
sum( case when month='01' then money else 0 end) 一月,
sum( case when month='02' then money else 0 end) 二月,
sum( case when month='03' then money else 0 end) 三月,
sum( case when month='04' then money else 0 end) 四月,
sum( case when month='05' then money else 0 end) 五月,
sum( case when month='06' then money else 0 end) 六月,
sum( case when month='07' then money else 0 end) 七月,
sum( case when month='08' then money else 0 end) 八月,
sum( case when month='09' then money else 0 end) 九月,
sum( case when month='10' then money else 0 end) 十月,
sum( case when month='11' then money else 0 end) 十一月,
sum( case when month='12' then money else 0 end) 十二月
from T_ACCOUNT where year='2012' group by areaid

需求：按季度统计 2012 年各个地区的水费

select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else
0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else
0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else
0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else
0 end) 第四季度
from T_ACCOUNT where year='2012' 

6、分析函数

以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例

6.1、RANK 相同的值排名相同，排名跳跃

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃

select rank() over(order by usenum desc ),usenum from
T_ACCOUNT

6.2、 DENSE_RANK 相同的值排名相同，排名连续

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续
语句：

select dense_rank() over(order by usenum desc ),usenum
from T_ACCOUNT

6.3、ROW_NUMBER 返回连续的排名，无论值是否相等

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等
语句：

select row_number() over(order by usenum desc ),usenum
from T_ACCOUNT

用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10 and rownumber<=20

7、集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
··UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
··UNION(并集)，返回各个查询的所有记录，不包括重复记录。
··INTERSECT(交集)，返回两个查询共有的记录。
··MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

7.1、UNION ALL 不去掉重复记录

select * from t_owners where id<=7
union all
select * from t_owners where id>=5

7.2、UNION 去掉重复记录

select * from t_owners where id<=7
union
select * from t_owners where id>=5

7.3、交集运算

select * from t_owners where id<=7
intersect
select * from t_owners where id>=5

7.4、差集运算

select * from t_owners where id<=7
minus
select * from t_owners where id>=5

如果我们用 minus 运算符来实现分页，语句如下：

select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10