Oracle数据库中经常使用的函数

字符串类型函数

函数	描述
ASCII(x)	返回字符x的ASCII值。
CHR(x)	返回ASCII值为x的字符。
CONCAT(x, y)	连接两个字符串x和y，并返回连接后的字符串。
INITCAP(x)	将x中每个单词的初第一个字母转换为大写，并返回该字符串。
INSTR(x, find_string [, start] [, occurrence])	在x字符串中搜索find_string子串并返回找到的位置。
INSTRB(x)	返回字符串x在另一个字符串中第一次再现的位置，但返回值(以字节为单位)
length(x)	返回x中的字符数，也是计算字符串的长度。
LENGTHB(x)	返回单字节字符集的字符串长度(以字节为单位)。
LOWER(x)	将x字符串中的字母转换为小写，并返回此小写字符串
UPPER(x)	将x中的字母转换为大写，并返回此大写后的字符串
lpad(x, width [, pad_string])	使用空格垫放在x字符串的左边，以使字符串的长度达到宽度字符
rpad(x, width [, pad_string])	使用空格垫放在x字符串的右边，以使字符串的长度达到宽度字符。
ltrim(x [, trim_string])	修剪x字符串左边的字符
RTRIM(x [, trim_string])	从右边修剪x字符串
TRIM([trim_char FROM) x)	修剪x字符串的左边和右边的字符。
NVL(x, value)	如果x为null则返回value值; 否则返回x
NVL2(x, value1, value2)	如果x不为null则返回值value1; 如果x为null，则返回value2
REPLACE(x, search_string, replace_string)	在x字符串中搜索search_string并将其替换为replace_string
SUBSTR(x, start [, length])	返回x字符串从指定start位置开始到一个可选指定长度(length)范围内的子字符串

数字类型函数

保留两位小数

1、ROUND(A/B,2)
ROUND()函数是会将计算结果进行四舍五入的，如果所需要的值需要进行四舍五入，就可以选择这个函数，可以有一个参数，也可以有两个参数；如果有两个param，第一个是你的计算表达式，第二个是需要保留的小数位数。例子如下：

2、TRUNC(A/B,2)
TRUNC()函数是不会将计算结果进行四舍五入的，如果所需要的值不需要进行四舍五入，就可以选择这个函数，可以有一个参数，也可以有两个参数；如果有两个param，第一个是你的计算表达式，第二个是需要保留的小数位数。例子如下：

3、TO_CHAR(A/B,’FM99990.99’)
TO_CHAR()是一个格式化函数，第一个参数是计算表达式，第二个参数是指定格式化的格式，如果保留两位小数则小数点后写两个99，这里的数字9代表的数字，也是一个占位符，表示该位置上以后会是一个数字，为什么小数点前面会是一个0，而不是9，是因为如果计算结果小于1，那么只会显示小数点和小数点之后的部分，前面的0会忽略掉

日期类型函数

日期格式转换

1、日期转化为字符串 (以2020年10月20日为例)

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  strDateTime from dual;    
--获取年-月-日 时:分:秒   
--显示结果为：2020-10-20 12:35:21

select to_char(sysdate,'yyyymmdd hh24:mi:ss')  strDateTime from dual;    
--获取年月日 时:分:秒  
--显示结果为：20201020 13:39:25

select to_char(sysdate,'yyyymmdd')  strDateTime from dual;     
--获取年月日 
--显示结果为：20201020

select to_char(sysdate,'yyyy')  strYear from dual;    
--获取年  
--显示结果为：2020

select to_char(sysdate,'mm')   strMonth  from dual;    
--获取月  
--显示结果为：10

select to_char(sysdate,'dd')    strDay    from dual;     
--获取日
--显示结果为：20

select to_char(sysdate,'hh24')   strHour   from dual;     
--获取时 
--显示结果为：13

select to_char(sysdate,'mi')  strMinute from dual;   
--获取分  
--显示结果为：46

select to_char(sysdate,'ss')  strSecond from dual;   
--获取秒
--显示结果为：43

2、字符串和时间互转

select to_date('2010-10-20 13:23:44','yyyy-mm-dd hh24:mi:ss') dateTime from dual;
--显示结果：2010/10/20 13:23:44

select to_date('2010-10-20 13:23:44','yyyy/mm/dd hh24:mi:ss') dateTime from dual;

--显示结果：2010/10/20 13:23:44

select to_char( to_date(222,'J'),'Jsp') from dual;

--显示结果：Two Hundred Twenty-Two

--如果按照下面的例子写，会报错：ORA-01849:小时值必须介于1和12之间。(因为其中的hh是12进制，没有13所以报错)

select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh:mi:ss') from dual;

3、查询某天是星期几

select to_char(to_date('2012-10-20','yyyy-mm-dd'),'day') strDay from dual; 
--显示结果：星期六

select to_char(to_date('2012-10-20','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = English') strDay from dual;   
--显示结果：saturday

4、两个日期间的天数

select floor(sysdate - to_date('20211010','yyyymmdd')) strTime from dual; 

--其中sysdate=2021/10/28 17:10:51
--显示结果：18

5、时间为null的用法

select to_date(null) from dual;

6、月份差

select  months_between(date'2021-04-23',date'2020-04-23') days from dual;

行转列函数

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

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

–查询所有员工的姓名，用 “+” 号分割，如：SCOTT+SMITH+TURNER+WARD+张三，按员工姓名排序拼接select listagg(t.ename,‘+’) within group(order by 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’;

-- 列传行
select  t1.ywlx,wm_concat(t1.ywlxmc),wm_concat(t1.thcs) as ywlxmcs from aaa t1  group by t1.ywlx;


-- 列传行
select listagg(t1.ywlxmc,',')within group(order by t1.ywlx)  from aaa t1;


select listagg(t1.column_name,',')within group(order by t1.column_id) from user_tab_columns t1 where t1.table_name='aaa';

cast 数据类型转换

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

select cast('124' as number) + 100 as total from dual; -- 字符串转数值，输出 224

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

collect()

collect(): 获取任何类型的列，并从所选行中创建输入类型的嵌套表。可以实现多行转换成一行
1、创建数组类型嵌套表
PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表. 声明嵌套表类型的一般语法如下:

TYPE type_name AS TABLE OF 
{datatype | {variable | table.column} % type | table%rowtype};

create or replace type str_tab as table of varchar2(2000);

2、创建format_string格式化输出函数

create or replace function format_string(v_table in str_tab) return varchar2 is
  Results varchar2(30000);
begin
  for i in 1 .. v_table.count loop
    Results :=Results||','||v_table(i);
  end loop;
 --去掉第一个逗号--
 return(ltrim(Results,','));
end format_string;

dept表初始数据

3、开始使用

select format_string(CAST(COLLECT(loc) AS str_tab)) AS loc from dept;

Oracle中decode()和Mysql中case…when

decode函数条件判断

select decode(t1.ywlxmc,'粮食基金','唐僧','无名') from aaa t1;

case…when条件判断

select t1.ywlxmc,
case
  when to_number(replace(t1.thszb,'%','')) >50   then  '合格' 
  when to_number(replace(t1.thszb,'%','')) <50   then  '不合格' 
  else '数据不准确'  
end  a
from aaa t1;

select 
 case t1.ywlxmc
   when '粮食基金' then '唐僧'
   when '5G基金' then '孙悟空'
   else '无名' 
 end  alias
 from aaa t1;