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;