Oracle数据库（常⽤的函数）
本⽂转⾃：
oracle常⽤函数
使⽤函数可以⼤⼤提⾼select语句操作数据库的能⼒。在oracle中函数划分为单⾏函数和多⾏函数
单⾏函数作⽤于数据库表的某⼀⾏并返回⼀个值：字符函数、数字函数、⽇期函数、转换函数、其他函数。
多⾏函数基于数据库多⾏进⾏运算，返回⼀个值：例如对多⾏记录的某个字段求和、求最⼤值运算等
字符函数
函数功能⽰例结果initcap(char)⾸字母⼤写initcap('hello')Hello lower(char)转换为⼩写lower('FUN')fun upper(char)转换为⼤写upper('sun')SUN ltrim(char,ser)左剪裁ltrim('xyzadams','xyz')adams
rerim(char,set)右剪裁rtrim('xyzadams','ams'
)
xyzad
translate(char,from,to)按字符翻译translate('jack','abcd','1234')j13k
replace(char,search_str,replace_str)字符串替换replace('jack and jue','j','bl')black and blue instr(char,substr[,post])查⼦串位置instr('worldwide','d')5
substr(char,pos,len)取⼦字符串substr('abcdefg',3,2)cd
length(char)字符串长度length('adsfd')5
concat(char1,char2)连接字符串concat('Hello','world')Helloworld
范例：观察转⼩写的操作，将所有的雇员姓名按照⼩写字母返回
SELECT LOWER(ename) FROM emp;
范例：将每⼀个雇员姓名的开头⾸字母⼤写
SELECT INITCAP(ename) FROM emp;
范例：查询出每个雇员姓名的长度
SELECT ename,LENGTH(ename) FROM emp;
范例：要求查询出姓名长度正好是5的雇员信息
SELECT ename,LENGTH(ename) FROM emp
WHERE LENGTH(ename)=5;
范例：使⽤字母“_”替换掉姓名中的所有字母“A”
SELECT REPLACE(ename,'A','_') FROM emp;
字符串截取操作有两种语法：
语法⼀：SUBSTR(字符串 | 列，开始点)，表⽰从开始点⼀直截取到结尾；
SELECT ename,SUBSTR(ename,3) FROM emp;
语法⼆：SUBSTR(字符串 | 列，开始点，结束点)，表⽰从开始点截取到结束点，截取部分内容；
SELECT ename,SUBSTR(ename,0,3) FROM emp;
SELECT ename,SUBSTR(ename,1,3) FROM emp;oracle四舍五入
范例：要求截取每个雇员姓名的后三个字母
· 正常思路：通过长度-2确定开始点
SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;
· 新思路：设置负数，表⽰从后指定截取位置；
SELECT ename,SUBSTR(ename,-3) FROM emp;
⾯试题：请问SUBSTR()函数截取的时候下标从0还是从1开始？
· 在Oracle数据库之中，SUBSTR()函数从0或1开始都是⼀样的；
· SUBSTR()也可以设置为负数，表⽰由后指定截取的开始点；
数字函数
函数功能⽰例结果
abs(n)取绝对值abs(-15)15
ceil(n)向上取值ceil(44.778)45
sin(n)正弦sin(1.571).999999979 cos(n)余弦cos(0)1
sign(n)取符号sign(-32)-1
floor(n)向下取整floor(100.2)100
power(m,n)m的n次幂power(4,2)16
mod(m,n)取余数mod(10,3)1
round(m,n)四舍五⼊round(100.256,2)100.26
trunc(m,n)截断tranc(100.256,2)100.25
sqrt(n)平⽅根sqrt(4)2
-- 四舍五⼊
select round(255,-1) from dual;
-- 舍弃⼩数位
select trunc(255,-1) from dual;
常⽤⽇期函数
函数功能
month_between(date1,date2)返回两个⽇期间的⽉份
add_month(date,n)返回把⽉份数n加到⽇期date上的新⽇期
next_day(date,week)返回指定⽇期后的星期对应的新⽇期
last-day(date)返回指定⽇期所在⽉的最后⼀天
⽇期常⽤的三个公式：
· ⽇期 + 数字 = ⽇期，表⽰若⼲天之后的⽇期；
SELECT SYSDATE + 3,SYSDATE + 300 FROM dual;
· ⽇期 – 数字 = ⽇期，表⽰若⼲天前的⽇期；
SELECT SYSDATE - 3,SYSDATE - 300 FROM dual;
· ⽇期 – ⽇期 = 数字，表⽰的是两个⽇期间的天数，但是肯定是⼤⽇期 – ⼩⽇期；
范例：求出每个雇员到今天为⽌的雇佣天数
SELECT ename,hiredate,SYSDATE-hiredate FROM emp;
范例：求出本⽉的最后⼀天⽇期
SELECT LAST_DAY(SYSDATE) FROM dual;
· NEXT_DAY(⽇期，星期数)：求出下⼀个指定星期X的⽇期；
范例：求出下⼀个周⼀
SELECT NEXT_DAY(SYSDATE,'星期⼀') FROM dual;
范例：求出四个⽉后的⽇期
SELECT ADD_MONTHS(SYSDATE,4) FROM dual;
范例：求出每个雇员到今天为⽌的雇佣⽉份
SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;
常⽤的转换函数
函数功能⽰例结果
to_char(字符串 | 列，格式字符串)转换成字符串类型to_char(1234.5,'$9999.9')$1234.5 to_date(字符串，格式字符串)转换成⽇期类型to_date('1980-01-01','yyyy-mm-dd')01-1⽉-80 to_number(字符串)转换成数值类型to_number('1234.5')1234.5
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year, TO_CHAR(SYSDATE,'mm') month,
TO_CHAR(SYSDATE,'dd') day FROM dual;
TO_CHAR(SY YEAR MO DA
---------- ---- -- --
2012-08-12 2012 08 12
但是这个时候的显⽰数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加⼊⼀个“fm”。
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM dual;
DAY
----------
2012-8-12
正常⼈都加0，所以这个标记知道就⾏了，可是在Oracle之中，DATE⾥⾯是包含了时间的，但是之前的代码没有显⽰出时间，要想显⽰时间则需要增加标记：
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual;
DAY
-------------------
2012-8-12 16:13:38
⼀定要注意，使⽤TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR()函数也可以⽤于数字的格式化上，这个时候每⼀个“9”表⽰⼀位数字的概念，⽽不是数字9的概念。
SELECT TO_CHAR(89078907890,'L999,999,999,999,999') FROM dual;
TO_CHAR(89078907890,'L999,999,
------------------------------
￥89,078,907,890
其中的字母“L”，表⽰的是“Local”的含义，即：当前的所在的语⾔环境下的货币符号。
b、TO_DATE()函数
此函数的主要功能是将⼀个字符串变为DATE型数据。
SELECT TO_DATE('1989-09-12','yyyy-mm-dd') FROM dual;
--------------
12-9⽉ -89
常⽤其他函数
函数功能
nvl(exp1,exp2)如果exp1的值为null，则返回exp2的值，否则返回exp1的值
nvl2(exp1,exp2,exp3)如果exp1的值不为null，则返回exp2的值，否则返回exp3的值
decode(value,if1,then1,if2,then2,......,else)如果value的值为if1，则返回then1的值，如果为if2，则返回then2的值，.....，否则返回else的值
a、NVL()函数，处理null
范例：要求查询出每个雇员的全部年薪
SELECT ename,sal,comm,(sal+NVL(comm,0))*12,NVL(comm,0) FROM emp;
b、DECODE()函数：多数值判断
DECODE()函数⾮常类似于程序中的if…else…语句，唯⼀不同的是DECODE()函数判断的是数值，⽽不是逻辑条件。例如，现在要求显⽰全部雇员的职位，但是这些职位要求替换为中⽂显⽰：
· CLERK：办事员；
· SALESMAN：销售；
· MANAGER：经理；
· ANALYST：分析员；
· PRESIDENT：总裁；
这种判断肯定是逐⾏进⾏判断，所以这个时候就必须采⽤DECODE(),⽽此函数的语法如下：
DECODE(数值 | 列 ，判断值1，显⽰值1，判断值2，显⽰值2，判断值3，显⽰值3，…)
范例：实现显⽰的操作功能
SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售⼈员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')
FROM emp;
DECODE()函数是整个Oracle之中最具特点的函数，⼀定要将其掌握。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。