【Oracle 基础】Oracle单行函数

一、字符函数

1)大小写控制函数

1.upper(char):字符串转换成大写

2.lower(char):字符串转换成小写

3.initcap(chat):字符串每个单词的首字母大写

--大小写控制函数
--upper
select * from emp where job = upper('salesman');

--lower
select * from emp where lower(job) = 'clerk';

--initcap
select empno, initcap(ename) from emp;

--upper、lower、initcap这三个函数的共同点，如果输入参数值为null时，则返回null
select empno, initcap(null) from emp;

2)字符控制函数

1.concat(str1,str2)：连接两个字符串

• 只能连接两个字符串，多个字符串需要嵌套使用
• 连接符||

select ename||':'|| sal from emp
select concat(CONCAT(ename,':'),sal) from emp

2.substr(char,m[,n])：将char从m位置截取n位

• n省略则截取指定位置之后的所有字符串
• m为0则从首位开始截取，相当于m为1
• m为负数则从尾部开始截取

select substr('hello',2,3) from dual --ell
select substr('hello',2) from dual --ello

select substr('hello',0,1) from dual --h
select substr('hello',1,1) from dual --h

select substr('hello',-3,2) from dual --ll

3.length(char)：返回字符串长度

select * from emp where length(ename) = 6;

4.instr(char1,char2[,n[,m]])：子串char2在源字符串char1从n位置搜索第m次出现的位置

select instr('hello oracle','oracle') from dual;--7
select instr('hello oracle hello oracle', 'oracle', 1, 2) from dual;--20

5.lpad(char1,n,char2)：将字符串char2在源字符串char1左边填充，使得填充后的字符串总长度为n

• 当n<length(char1)时，该函数无法填充，而是将char1从头截取到只剩3位

select lpad('hello',10,'#') from dual--#####hello
select lpad('hello',3,'#') from dual--hel

6.rpad(char1,n,char2)：右补足，将字符串char2在源字符串char1右边填充，使得填充后的字符串总长度为n

• 当n<length(char1)时，该函数无法填充，而是将char1从头截取到只剩3位

select rpad('hello',10,'#') from dual--hello#####
select rpad('hello',3,'#') from dual--hel
select lpad('123',6,0) col1 from dual;--000123（左补足）
select rpad('123',6,0) col1 from dual;--123000

7.trim(char)：去除字符串前后空格

• ltrim去除左端空格，rtrim去除右端空格
• 中间空格不会去除

select trim(' hel lo  ') from dual--hel lo
select ltrim(' hel lo  ') from dual--hel lo__
select rtrim(' hel lo  ') from dual--_hel lo

8.replace(char,search_string[,replacemenr_string])：在char里面找到要替换的search_string,替换成replacemenr_string

select replace('hello oracle','oracle','world') from dual;

9.正则表达式函数 REGEXP

REGEXP_LIKE ：与LIKE的功能相似，可以支持按正则表达式与文本进行匹配
REGEXP_INSTR ：返回指定字符串中与正则表达式匹配部分第一次出现的位置
REGEXP_COUNT ：返回指定字符串中与正则表达式匹配部分出现的次数
REGEXP_SUBSTR ：截取指定字符串中与正则表达式匹配的部分
REGEXP_REPLACE ：替换指定字符串中与正则表达式匹配的部分
参考链接：https://blog.csdn.net/qq_43838883/article/details/109748416

二、数值函数

1.round(n,[,m])：将数字n四舍五入到小数点后m位

• m省略或者为0则四舍五入到个位
• m为负数则四舍五入到个位（0位）前第m位

select round(273.568) from dual--274
select round(273.568,0) from dual--274

select round(273.568,2) from dual--273.57
select round(273.568,-2) from dual--300

2.trunc(n,[,m])：截取数字n到m位

• 参数规则同round,区别是trunc函数只舍不入

select trunc(273.568) from dual--273
select trunc(273.568,0) from dual--273

select trunc(273.568,2) from dual--273.56
select trunc(273.568,-2) from dual--200

3.mod(m,n)：m除以n后的余数

select mod(25,2) from dual--1

三、日期和时间函数

1.sysdate：用于返回当前日期时间

select sysdate-1,sysdate,sysdate+1 from dual

2.months_between(d1,d2)：返回两日期之间相差月数

select MONTHS_BETWEEN(sysdate,'02-10月-19') from dual

3.add_months(d,n)：日期d在n月后对应的日期

select ADD_MONTHS(SYSDATE, 5*12) from dual

4.next_day(d,char)：日期d的下一个周次char对应的日期

select NEXT_DAY(sysdate, '星期一') from dual

5.last_day(d)：返回指定日期d所在月的最后一天

select last_DAY(sysdate) from dual

6.round(d[,fmt])：返回日期时间d的四舍五入结果

select round(sysdate,'year') from dual --2020-01-01 00:00:00

7.trunc(d[,fmt])：返回日期时间d的截断结果

select trunc(sysdate,'year') from dual --2019-01-01 00:00:00

常用取时方法（初末、对日等）:

--当前时间
select sysdate  from dual;--2020-03-17 15:50:36
select ADD_MONTHS(sysdate,-12)  from dual;--去年对日 2019-03-17 15:50:36
--月初月末
select trunc(sysdate,'MM') from dual;--2020-03-01 00:00:00
select trunc(sysdate,'month') from dual;--同上

select LAST_DAY(sysdate) from dual;--2020-03-31 15:57:24
--年初年末
select trunc(sysdate,'YY')  from dual;--2020-01-01 00:00:00
select trunc(sysdate,'year')  from dual;--同上
select trunc(sysdate,'YYYY')  from dual;--同上
select ADD_MONTHS(trunc(sysdate,'YY'),-12)  from dual;--去年年初2019-01-01 00:00:00


select ADD_MONTHS(trunc(sysdate,'YY'),12)-1  from dual;--2020-12-31 00:00:00
select trunc(sysdate,'YY')-1  from dual;--去年年末2019-12-31 00:00:00

--季初季末
select trunc(sysdate,'Q')  from dual;--2020-01-01 00:00:00
select ADD_MONTHS(trunc(sysdate,'Q'),3)-1  from dual;--2020-03-31 00:00:00
--日初
select trunc(sysdate,'DD')  from dual;--2020-03-17 00:00:00
--周初周末
select trunc(sysdate,'day')+1  from dual;--2020-03-16 00:00:00
select trunc(sysdate,'D')+1  from dual;--同上

select trunc(sysdate,'day')+7  from dual;--2020-03-22 00:00:00
--截断到小时
 select trunc(sysdate,'hh24') from dual;--2010-12-10 20:00:00
--截断到分钟
select trunc(sysdate,'mi') from dual;--2022/9/27 9:53:00
-----混合应用
--周初周末的去年对日
select ADD_MONTHS(trunc(sysdate,'day')+1,-12)  from dual;
select ADD_MONTHS(trunc(sysdate,'day')+7,-12)  from dual;

获取时间数

extract (
{ year | month | day | hour | minute | second | 某一时区 }
from { date类型值 | interval类型值} )

四、转换函数

隐式数据转换：

源数据类型	目标数据类型
varchar2或char	number
varchar2或char	date
number	varchar2
date	varchar2

1.to_date(char[,fmt[,‘nlsparams’]])：将字符串转换成日期

select MONTHS_BETWEEN(sysdate,to_date('1992-10-02','yyyy-mm-dd'))/12 from dual

2.to_char：将日期或数值转换成字符

• to_char(d[,fmt[,‘nlsparams’]])：将日期d按照指定日期显示语言nlsparams转换成fmt格式的数值

select to_char(sysdate,'yyyymmdd') from dual --20191130

对于时间戳，Oracle没有像mysql那样直接的函数转化，而是通过计算

--时间戳是13位的毫秒量级
select to_char(时间戳的那一列 / (1000 * 60 * 60 * 24) +  
      to_date('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS cdate
FROM 表名 ;

• to_char(n[,fmt])：将数值n转换成fmt格式的数值

fmt元素	格式
9	显示数字，忽略前面的0
0	显示数字，如果位数不足，则用0补齐
.	指定位置显示小数点
,	指定位置显示逗号
$	在数字前加美元符号
L	在数字前加本地货币符号

select to_char(3.1415927,'L0,000,000,000') from dual-- ￥0,000,000,003
select to_char(3.1415927,'0,000,000.000') from dual-- 0,000,003.142
select to_char(33.1415927,'9,999,999,999') from dual-- ___________$33
select to_char(33.1415927,'9999999999') from dual--____33.14159

3.to_number(n[,fmt])：将包含数值的字符串转换成数值类型

select to_number('$200,000.00','$999999999.99') from dual--200000

五、通用函数

1.nvl(expr1,expr2)：在expr1为null时返回expr2，否则返回expr1

2.nvl2(expr1,expr2,expr3)：如果expr1不为null，就返回expr2，否则expr1为null就返回expr3

3.nullif(expr1,expr2)：如果expr1与expr2相等则返回null，否则返回expr1

4.coalesce(expr1[,expr2][,expr2])：返回参数列表中第一个不为null的表达式结果

六、条件函数

1.case表达式

case 条件
	when 表达式1 then 返回值1
	when 表达式2 then 返回值2
	......
	else el表达式
end

或者
case when 条件表达爱是1 then 返回值1
	 when 条件表达式2 then 返回值2
	 ......
	 else el表达式
end

2.decode()

decode(列名|条件，查找值1，返回值1，
				 [查找值2，返回值2，
				 ......
				 默认值]

自定义排序

order by decode(name,'andy',1,'july',2,'nacy',3,'hana',4,'jack',5)

七、嵌套函数

单行函数可以嵌套，嵌套函数的志向顺序是由内到外

select NEXT_DAY(ADD_MONTHS(SYSDATE, 5*12), '星期日') from dual

补充

pivot转置函数

select *  
from tmp pivot ( max(score) for course in ('物理' as wl,'语文' as yw,'英语' as yy,'数学' as sx ))

unpivot逆透视

窗口函数

见窗口函数