7.单行函数
函数的分类:单行函数、多行函数
7.1 字符函数
字符函数又分为两类:大小写控制函数、字符控制函数
1. 大小写控制函数
1) LOWER:全部转换为小写字母
2) UPPER:全部转换为大写字母
3) INITCAP:首字母大写
范例:
SELECT LOWER('ORACLE DATABASE') FROM dual; |
SELECT UPPER('Oracle database') FROM dual; |
SELECT initcap('oracle database') FROM dual; |
2. 字符控制函数
1) CONCAT:连接
CONCAT(a,b):只能连接两个
SELECT CONCAT(ename,sal) FROM emp; |
2) SUBSTR:截取
SUBSTR(值,起始位置,截取的长度)
注:*截取长度省略,取起始位置开始的所有字符
*起始位置为负值,表示从后面第起始位置开始向右截取
SELECT SUBSTR('helloworld',2,3) FROM dual; |
SUB
---
ell
SELECT SUBSTR('helloworld',-5,3) FROM dual; |
wor
3) LENGTH(A):求长度
4) INSTR(A,B,n1,n2):位置
注:B在A中,从n1开始,第n2次出现的位置
n1和n2可省略,默认值为1
SELECT INSTR('hello,world','w') FROM dual; |
INSTR('HELLO,WORLD','W')
------------------------
7
5) LPAD(A,值,B):左填充
SELECT sal,LPAD(sal,10,'*') FROM emp; |
LPAD(SAL,10,'*')
--------------------
*******800
******1600
******1250
******2975
6) RPAD(A,值,B):右填充,属性同左填充
7) TRIM:去掉指定的字符(只能去两边的)
SELECT trim('h' from 'hello,world') FROM dual; |
TRIM('H'FR
----------
ello,world
TRIM:去掉左右两边的空格
SELECT trim(' hhh hh ') FROM dual; |
TRIM('HHHHH')
-------------------------
hhh hh
8)REPLACE(a,b,c):把a中的b用c替换
7.2 数学函数
1)ROUND:四舍五入
注:正数指小数点后面,负数指小数点前面,第二个属性默认0,指取整
SELECT ROUND(45.688,2) FROM DUAL; |
ROUND(45.688,2)
---------------
45.69
SELECT ROUND(45.688,-2) FROM DUAL; |
ROUND(45.688,-2)
----------------
0
2) TRUNC:截断
注:正数指小数点后面,负数指小数点前面,第二个属性默认0,指取整
select TRUNC(45.878,2) from dual; |
结果:
TRUNC(45.878,2)
---------------
45.87
3)MOD:求余数
SELECT MOD(10,3) FROM DUAL; |
结果:
MOD(10,3)
----------
1
7.3 日期函数
默认日期格式:DD-MON-RR
日期格式模型的元素:YYYY(数字表示),YEAR(英文表示),
MM(两位数字),MONTH(月份全名),
DAY(星期几),DY(星期几的英文缩写)
格式码:世纪CC,年YY,月MM,日DD,小时HH24,分MI,秒SS
通过双引号“ ”可添加字符
1.SYSDATE——系统日期
SELECT SYSDATE FROM dual; |
结果:
SYSDATE
-----------
09-3月 -18
2.日期的算术运算
l 日期 – 数字(天数) = 日期
l 日期 + 数字(天数) = 日期
l 日期 – 日期 = 数字(天数)
注:加减小时的话将数字除以24,即
日期 +或- 数字/24 =日期
SELECT SYSDATE, SYSDATE+1 FROM dual; |
结果:
SYSDATE SYSDATE+1
-------------- ------------
09-3月 -18 10-3月 -18
SELECT SYSDATE, SYSDATE-1 FROM dual; |
结果:
SYSDATE SYSDATE-1
-------------- -------------
09-3月 -18 08-3月 -18
SELECT TRUNC(SYSDATE-hiredate) FROM emp; |
结果:
TRUNC(SYSDATE-HIREDATE)
-----------------------
13596
13531
13529
3.函数
1) MONTHS_BETWEEN:两个日期之间相差的月份(前大后小)
SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp; |
MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
446.768443
444.671669
441
2) ADD_MONTHS:在指定的日期上加上月数
SELECT ADD_MONTHS(SYSDATE,2) FROM dual; |
ADD_MONTHS(SYS
--------------
09-5月 -18
3) NEXT_DAY:指定日期的下一个日期
SELECT SYSDATE, NEXT_DAY(SYSDATE,'星期一') FROM dual; |
SYSDATE NEXT_DAY(SYSDA
-------------- --------------
09-3月 -18 12-3月 -18
4) LAST_DAY:月末最后一天
SELECT LAST_DAY(SYSDATE) FROM dual; |
LAST_DAY(SYSDA
--------------
31-3月 -18
5)ROUND(date,'fmt'):将date按指定格式fmt进行四舍五入
注:fmt默认格式DD,四舍五入为最近的天
6)TRUNC(date,'fmt'):将date按指定格式fmt进行截断
用法同ROUND
7)EXTRACT(a from date):返回日期类型数据中的年份,月份或者日
a的值为YEAR,MONTH,DAY
7.4 转换函数
显性、隐性
1) TO_CHAR():转换为字符
转换数字的格式:9(1位数字),0(显示前导0),$(显示美元符号),
L(显示本地货币符号),.(显示小数点),
,(显示千位符)
注:用9表位数时,格式中的宽度一定要超过或等于实际宽度,否则会显示为#
SELECT ename,hiredate,TO_CHAR (hiredate,'yyyy-mm-dd') FROM emp; |
ENAME HIREDATE TO_CHAR(HI
---------- -------------- ----------
SMITH 17-12月-80 1980-12-17
ALLEN 20-2月 -81 1981-02-20
WARD 22-2月 -81 1981-02-22
SELECT ename,sal,TO_CHAR(sal,'99,999') FROM emp; |
ENAME SAL TO_CHAR
---------- ---------- -------
SMITH 800 800
ALLEN 1600 1,600
WARD 1250 1,250
2) TO_NUMBER():转换为数字
SELECT TO_NUMBER ('123')+ TO_NUMBER ('123') FROM dual; |
结果:
TO_NUMBER('123')+TO_NUMBER('123')
---------------------------------
246
3) TO_DATE():转换为日期
SELECT TO_DATE ('2018-12-12','yyyy-mm-dd') FROM dual; |
结果:
TO_DATE('2018-
--------------
12-12月-18
7.5 通用函数
1) NVL(a,expr):a为空时,返回expr
2) NVL2(a,expr1,expr2):a不是空,返回expr1;a是空,返回expr2
3) NULLIF(expr1,expr2):expr1,expr2相等,返回null,否则返回expr1
4) COALESCE(expr1,expr2,....,exprn):返回第一个不为空的参数
范例:查询雇员姓名、雇员月总收入。
SELECT ename,NVL2(comm,sal+comm,sal) FROM emp; |
ENAME NVL2(COMM,SAL+COMM,SAL)
---------- -----------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
5) DECODE():类似于if….elseif…else语句
DECODE(列名,条件1,结果1, 条件2,结果2, …. 条件n,结果n, 结果 ) |
6) case语法
CASE expr
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
WHEN 条件n THEN 结果n
ELSE 结果
END
或
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
WHEN 条件n THEN 结果n
ELSE 结果
END