Oracle 函数进阶、xxx() over 分组排序、列转行、行转列、分组统计、cast 类型转换、dbms_random 生成随机数、sys_guid、unistr 将 Unicode 字符串解码

--查询所有人员，工资由高到低排序，第一列(xh)是行号，从1开始且连续。 select row_number() over(order by t.sal desc) as xh , t.* from emp t ;
--查询所有人员，部门内部员工的工资由高到低排序 select row_number() over(partition by t.deptno order by t.sal desc) as xh , t.* from emp t ;
--查询每个部门中工资最高的人（这里只是介绍用法，实际中可能存在工资一样的人，此时需要使用 dense_rank() over ） select t2.* from ( select row_number() over(partition by t.deptno order by t.sal desc) as xh , t.* from emp t ) t2 where t2.xh <=1;
-- 使用 row_number() over 分组排序方式进行分页（只需要嵌套1层子查询） select t2.* from (select row_number() over(order by t1.empno desc) as xh,t1.* from emp t1) t2 where t2.xh between 6 and 12; -- 使用 rownum 方式分页，查询员工数据，根据员工编号倒序排序（需要嵌套2层子查询） select * from (select rownum xh,t2.* from (select * from emp t1 order by t1.empno desc) t2) t3 where t3.xh between 6 and 12;

rank() over 跳级分组排名

dense_rank() over() 连续分组排名

1、rank() over、dense_rank() over()，用法和格式与 row_number() over 完全一样，区别如下：

--查询所有人员，按工资的由高到低排序，不支持并列排名
select row_number() over(order by t.sal desc) as xh , t.* from emp t ;
---查询所有人员，按工资的由高到低排序，支持并列排名，且排名不会跳级
select dense_rank() over(order by t.sal desc) as xh , t.* from emp t ;
---查询所有人员，按工资的由高到低排序，支持并列排名，但是排名会跳级
select rank() over(order by t.sal desc) as xh , t.* from emp t ;

列转行

wm_concat

1、wm_concat 函数oracle12g版本开始不支持，Oracle 官方更推荐使用下面的 listagg() within group 函数。

-- 1、查询员工表中的所有员工姓名，并转为一行，用 "," 隔开，如: 张三,李四,Jock
select wm_concat(ename) as names from emp t;

-- 2、按部门编号分组查看每个部门中的员工姓名
select deptno,wm_concat(ename) as names from emp group by deptno;

-- 3、通过Oracle 系统视图查询 emp 表的所有字段，如：EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

--这个操作还是非常有用的，比如一个表有几十个或者上百个字段，select x,y,z ...，如果靠人工一个个写，太累而且容易出错
select wm_concat(t.COLUMN_NAME) from user_tab_columns t where t.TABLE_NAME = 'EMP';

listagg() within group

1、Oracle 官方更推荐使用 listagg 函数，格式：listagg(column_nname，delimiter) [within group (order by order_by_column)];

column_nname：待转换的列名，如 ename

delimiter：分隔符，默认为 null，无分隔符

order_by_column：指定列值拼接顺序，如 order by ename

listagg 是必须的，排序的 within group 是可选的！

2、listagg 函数使用举例如下：

--查询所有员工的姓名，用 "+" 号分割，如：SMITH+张三+WARD+JONES+MARTIN+李四+MILLER
select listagg(t.ename,'+') as names from emp t ;

--分组查看各部门的员工姓名，使用 "," 分割，姓名按薪水高低排列拼接
select t.deptno,listagg(t.ename,',') within group(order by t.sal desc) as neames from emp t group by t.deptno;

--查询指定表的所有字段,对于想要快速获取某个表的字段是非常有用的操作
select listagg(t.COLUMN_NAME,',') within group(order by t.COLUMN_ID) from user_tab_columns t where t.TABLE_NAME = 'EMP';

xmlagg wellformed

1、xmlagg 函数解决listagg函数字符串拼接过长问题，返回 clob 类型。

xmlagg 函数格式：

select 需要分组的字段,
xmlagg(xmlparse(content 需要行转列合并展示的字段 || ';' wellformed) order by 排序字段)
.getclobval()
from 表名
group by 需要分组的字段;

--分组查看各部门的员工姓名，使用 "," 分割，姓名按薪水高低排列拼接
select t.deptno,
       rtrim(xmlagg(xmlparse(content t.ename || ',' wellformed) order by t.sal desc)
             .getclobval(),
             ',')
from emp t
group by t.deptno;

行转列

case when

--行转列 case when 方式：统计每年入职的人数
--    	1980  1981	1982	1983	1984
--    	1	1	10	2	1	0
select 
       sum(case when to_char(t.hiredate, 'yyyy') = '1980' then 1 else 0 end) as "1980",
       sum(case when to_char(t.hiredate, 'yyyy') = '1981' then 1 else 0 end) as "1981",
       sum(case when to_char(t.hiredate, 'yyyy') = '1982' then 1 else 0 end) as "1982",
       sum(case when to_char(t.hiredate, 'yyyy') = '1983' then 1 else 0 end) as "1983",
       sum(case when to_char(t.hiredate, 'yyyy') = '1984' then 1 else 0 end) as "1984"
  from emp t
 where hiredate is not null;

decode

--行转列 decode 方式：统计每年入职的人数
--    	1980	1981	1982	1983	1984
--    	1	1	10	2	1	0
select sum(decode(to_char(t.hiredate, 'yyyy'), '1980', 1, 0)) as "1980",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1981', 1, 0)) as "1981",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1982', 1, 0)) as "1982",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1983', 1, 0)) as "1983",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1984', 1, 0)) as "1984"
  from emp t
 where hiredate is not null;

pivot

1、pivot 函数实现将指定字段的字段值转换为列。

2、格式：select * from 表名 pivot(聚合函数(需要按行展示的字段) for 需要转列展示的字段 in ('内容1' AS 字段别名1,'内容2' AS 字段别名2,'内容n' AS 字段别名n));

--行转列 pivot 方式：统计每年入职的人数
--注意点1：外层 select 无法直接指定具体的列，且除了 pivot for 后面中的列最为最终的列之外-
--其它在 privot for 中没有体现的列，默认也会展示在最终的结果中，所以通常需要借助子查询提前过滤好列信息
select *
  from (select to_char(t.hiredate, 'yyyy') year, count(1) total
          from emp t
         group by to_char(t.hiredate, 'yyyy')) t2
pivot(sum(t2.total)
--注意点2：for 后面的列名不能用别名，否则报错：ORA-01748:此处只允许简单的列名
   for year in(1980, 1981, 1982, 1983, 1984));

--行转列 pivot 方式：统计每个部门的人数
select *
  from (select t.deptno from emp t where t.deptno is not null)
pivot(count(1)
   for deptno in(10 as "10_ACCOUNTING",
                 20 as "20_RESEARCH",
                 30 as "30_SALES",
                 40 as "40_OPERATIONS"));

分组统计

1、项目中经常会遇到分组统计的需求，以下写法仅供参考。

数据准备：sql/oracle/分组统计.sql · 汪少棠/material - Gitee.com。

 --方式3——推荐
 SELECT 
  agency_code,
  fiscal_year,
  sum((case when ui_code = '202005' and per_sta_code='1' then 1 else 0 end)) as 行政在职人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='2' then 1 else 0 end)) as 行政退休人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='3' then 1 else 0 end)) as 行政离休人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='1' then 1 else 0 end)) as 事业在职人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='2' then 1 else 0 end)) as 事业退休人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='3' then 1 else 0 end)) as 事业离休人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='1' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 行政在职工资汇总,
  sum((case when ui_code = '202005' and per_sta_code='2' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 行政退休工资汇总,
  sum((case when ui_code = '202006' and per_sta_code='1' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 事业在职工资汇总,
  sum((case when ui_code = '202006' and per_sta_code='2' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 事业退休工资汇总
FROM person_info_wmx group by agency_code, fiscal_year 
order by agency_code, fiscal_year;

cast 数据类型转换

1、cast() 函数用于转换数据类型，格式：cast(列名/值 as 目标数据类型)，值为 null 时不会有影响，转换后的值也为null。

2、使用举例如下：

-- 字符串转数值，a1= 224 , a2的值为 null , a3的值为 null
select cast('124' as number) + 100 as a1, cast('' as number) a2, cast(null as number) a3 from dual;

select concat(cast(t.sal as varchar2(32)),'00') as str from emp t; --数值转字符串，sal 值为 null 时，不会有影响。

--浮点型精度截断：emp.sal 原本类型是 number(7,2) ，转换之后为 number(18,0) 表示小数位数为 0，结果就是对 sal 字段的值取整，且四舍五入，如 2850.55 -> 2851
select cast(t.sal as number(18,0)) as str from emp t;

select cast(t.sal as number(18,4)) as str from emp t; --浮点型精度截断：同理也可以增加精度，如：2850.55 -> 2850.5500

-- N1= -0.86 N2 = -1000.86 N3 = 2000.00
select cast(-0.8596 as number(18, 2)) n1, cast('-1000.8596' as number(18, 2)) n2, cast(2000 as number(18, 2)) n3 from dual;

ORA-00932:数据类型不一致:应为CHAR,但却获得 BINARY。可以使用cast函数统一结果的数据类型。

dbms_random 生成随机数

Oracle 官网 dbms_random 文档：DBMS_RANDOM

生成随机数字

dbms_random.random 用于生成正负整数

select dbms_random.random as random from dual; -- 生成一个10位数的正负随机数，如 -765136417、1703191771

-- 生成一个10位数的正随机数，使用绝对值函数辅助，如 228821755、1542114642
select abs(dbms_random.random) as random from dual;

select abs(mod(dbms_random.random,100)) as random from dual; -- 生成一个 100 以内的随机正数，使用取余函数+绝对值函数辅助

select 100 + abs(mod(dbms_random.random,999)) as random from dual; -- 生成一个 100—999 的随机整数

dbms_random.value 用于生成随机小数

select dbms_random.value as random from dual; -- 生成一个 0～1 之间随机小数，精确到小数点后面 15 位，如 0.623657615937181

-- 生成一个 10—100 之间的随机小数，精确到小数点后面 15 位，如 14.2489446249136
select dbms_random.value(10,100) as random from dual;

-- 生成一个 10—9999之间的随机小数，精确到小数点后面 2 位，如 6538.26
select round(dbms_random.value(10,9999),2) as random from dual;

-- 生成一个100～1000之间的随机整数，使用 trunc 小数截断函数赋值，强行去掉小数位
select trunc(100 + 900 * dbms_random.value, 0) as random from dual;

-- 生成一个[10,100)之间的随机整数
select trunc(dbms_random.value(10,100)) as random from dual;

-- 生成一个由数字组成的 16 位的随机字符串，使用 cast 函数辅助，以 '.' 开头，如：.980565525834441。（注意最长也只能是 39 位）
select cast(dbms_random.value as varchar2(16)) from dual;

-- 生成一个由数字组成的 32 位的随机字符串，使用 substr、cast 函数辅助，如：74899075116317540687814596072324
select substr(cast(dbms_random.value as varchar2(39)),2,32) as random from dual ;

生成随机字符串

格式：dbms_random.string(opt, length)

opt 可取值：'u' 或者 'U' :大写字母、'l' 或者 'L' : 小写字母、'a' 或者 'A' : 大小写字母、'x' 或者 'X' : 数字与大写字母、'p' 或者 'P' : 可打印字符。

length：生成的字符串长度

select dbms_random.string('x',20) from dual; -- 生成如： BRFSJLU79P909RLZ7FJI

select dbms_random.string('P',20) from dual; -- 生成如： E}"-!GS{w,/H?PT$}y&l

select sys_guid() from dual; --生成 32 位的 guid 随机字符串，如：AE2AE001C4BC4C259CBC8CFF443E5801

从表中获取随机数据

select * from emp order by dbms_random.random; --查询所有员工，并随机排序输出

--从员工表中随机或取 4 个员工数据

select * from ( select * from emp order by dbms_random.random) where rownum < 5;

sys_guid() 生成 UUID

1、Java JDK 有 API 可以生成 UUID，Oracle 中也有相应的方法可以生成 —— sys_guid()，可以很方便的防止主键冲突。

2、正常的 UUID 是 36 位的(包括其中的4个"-")，而 sys_guid() 生成的不带 "-"，默认是 32 位。

select sys_guid() from dual; -- 生成 32 位的 GUID，大写字母+数字，如：9B654FEF9D1D43BDB05406699049CA26 select lower(sys_guid()) from dual;-- 生成 32 位的 GUID，小写字母+数字，如：6f8f179015874321a8599143e38d37b6
-- 随机生成 100 个UUID declare total number := 100; begin dbms_output.put_line('========随机生成' \|\| total \|\|' 个UUID值========'); for i in 1..total loop dbms_output.put_line(i \|\| '===>' \|\| sys_guid()); end loop; end;
create table STUDENT2 ( stuid VARCHAR2(38) not null, stuname VARCHAR2(10) not null ); insert into STUDENT2(stuid,stuname) values(sys_guid(),'张三');-- 插入数据，stuid 使用 GUID 随机生成 insert into STUDENT2(stuid,stuname) values(dbms_random.string('x',20),'老四');-- 使用20位的随机字符串作为 stuid 的值

unistr 将 Unicode 字符串解码

1、程序里面将字符串转为 unicode 的值通常是这样的：Hi，我是 123。jackson. -> Hi\uff0c\u6211\u662f 123\u3002jackson.

2、将转为 unicode 后的结果存储到数据之后，在 Oracle 中可以使用 unistr 函数进行解码。

--解码结果为：Hi，我是 123。jackson.
select unistr(REPLACE('Hi\uff0c\u6211\u662f 123\u3002jackson.','\u','\')) from dual;
-- 生僻字 存储表解码
SELECT T.*,unistr(replace(t.unicode_char,'\u','\')) 解码 FROM BAS_UNUSUAL_CHARACTERS T; 

--获取其中连续的中文内容，结果是：大家好
select regexp_substr('hello,大家好！我是蚩尤后裔，电话 123', '[' || unistr('\0391') || '-' || unistr('\9fa5') || ']+') from dual;
--获取其中全部的中文内容，结果是：大家好我是蚩尤后裔电话
select regexp_replace('hello,大家好！我是蚩尤后裔，电话 123', '[^' || unistr('\0391') || '-' || unistr('\9fa5') || ']','') from dual;

3、封装为自定义函数：

create or replace function unicode2Str(encode_text in varchar2)
  return nvarchar2 is
  -- 对 unicode 内容进行解码
  v_sql    varchar2(100);
  v_result nvarchar2(100);
begin
  v_sql := 'select unistr(''' || REPLACE(encode_text, '\u', '\') ||  ''') from dual ';
  execute immediate v_sql into v_result;-- 将执行的结果赋给 v_result 返回
  return v_result;

  exception
    when others then
    --如果发生异常：ORA-30186: ''\'' 的后面必须为四个十六进制的字符或另一个 ''\''
    --则原样返回内容
    return encode_text;
end;

--函数调用
SELECT unicode2Str('Hi\ff0c\u6211\u662f 123\u3002jackson.') FROM dual T;