Oracle的SQL语句中用到的函数分为单行函数(单值函数)和多行函数(又称多值函数、组合函数、组函数)。
一、单行函数
下面介绍部分单行函数:
1)lower()函数①将指定字段(传参)在表中的每条记录都变成小写
select lower(ename) from emp;
LOWER(ENAM
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
LOWER(ENAM
----------
james
ford
miller
已选择14行。
②查询ename中第二个字母是'a'或'A'的记录(使用到正则表达式):
select ename from emp where ename like '_a%' or ename like '_A%';
ENAME
----------
WARD
MARTIN
JAMES
或者把该字段转换成小写,与a做比较即可:
select ename from emp where lower(ename) like '_a%';
ENAME
----------
WARD
MARTIN
JAMES
2)Upper()函数
将指定字段(传参)在表中的每条记录都变成大写
select upper(ename) from emp;
UPPER(ENAM
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
UPPER(ENAM
----------
JAMES
FORD
MILLER
已选择14行。
3)substr()函数
substr(ename, 2, 3)表示从ename字段的第2个字符开始,一共截3个长度:
select substr(ename, 2, 3) from emp;
SUBSTR
------
MIT
LLE
ARD
ONE
ART
LAK
LAR
COT
ING
URN
DAM
SUBSTR
------
AME
ORD
ILL
已选择14行。
4)chr()函数
把一个数字转换成对应ASCII码的字符:
select chr(65) from dual;
C
-
A
5)ascii()函数
把字符转换成ASCII码所对应的数字:
select ascii('B') from dual;
ASCII('B')
----------
66
6)round()函数
①小数点后四舍五入:
select round(123.456) from dual;
ROUND(123.456)
--------------
123
②重载形式的第二个参数指定保留几位小数:
select round(123.456, 0) from dual;
ROUND(123.456,0)
----------------
123
③保留2位小数:
select round(123.456, 2) from dual;
ROUND(123.456,2)
----------------
123.46
④保留-1位:
select round(123.456, -1) from dual;
ROUND(123.456,-1)
-----------------
120
7)to_char()函数
①指定数字格式:
9代表一位数字,对于整数部分,如果该位没有数字则不显示,对于小数部分,如果没有数字强制显示一个0。
select to_char(sal, '$99,999.9999') from emp;
TO_CHAR(SAL,'
-------------
$800.0000
$1,600.0000
$1,250.0000
$2,975.0000
$1,250.0000
$2,850.0000
$2,450.0000
$3,000.0000
$5,000.0000
$1,500.0000
$1,100.0000
TO_CHAR(SAL,'
-------------
$950.0000
$3,000.0000
$1,300.0000
已选择14行。
L表示本地货币:
select to_char(sal, 'L99,999.9999') from emp;
TO_CHAR(SAL,'L99,999.9
----------------------
¥800.0000
¥1,600.0000
¥1,250.0000
¥2,975.0000
¥1,250.0000
¥2,850.0000
¥2,450.0000
¥3,000.0000
¥5,000.0000
¥1,500.0000
¥1,100.0000
TO_CHAR(SAL,'L99,999.9
----------------------
¥950.0000
¥3,000.0000
¥1,300.0000
已选择14行。
②日期格式:
select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
TO_CHAR(HIREDATE,'Y
-------------------
1980-12-17 12:00:00
1981-02-20 12:00:00
1981-02-22 12:00:00
1981-04-02 12:00:00
1981-09-28 12:00:00
1981-05-01 12:00:00
1981-06-09 12:00:00
1987-04-19 12:00:00
1981-11-17 12:00:00
1981-09-08 12:00:00
1987-05-23 12:00:00
TO_CHAR(HIREDATE,'Y
-------------------
1981-12-03 12:00:00
1981-12-03 12:00:00
1982-01-23 12:00:00
已选择14行。
按照指定格式显示当前系统时间:
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-04-02 04:13:42
如果要按照24小时制来显示时间呢:
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-04-02 16:15:05
之前的博文中提到过日期处理,筛选出某个时间点以后的字段记录,如:
select ename, hiredate from emp where hiredate > '20-2月-82';
ENAME HIREDATE
---------- --------------
SCOTT 19-4月 -87
ADAMS 23-5月 -87
也就是说DATE类型必须与指定格式的字符串进行比较,而这种格式并不符合我们的习惯,可以通过to_date()函数来解析我们自定义的日期格式:
select ename, hiredate from emp where hiredate > to_date('1982-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
ENAME HIREDATE
---------- --------------
SCOTT 19-4月 -87
ADAMS 23-5月 -87
8)to_number()函数
把对特定格式的数字进行解析:
select sal from emp where sal > to_number('$1,250.00', '$9,999.999');
SAL
----------
1600
2975
2850
2450
3000
5000
1500
3000
1300
已选择9行。
9)nvl()函数
使用nvl()函数进行空值处理,nvl(comm, 0)表示如果comm字段中某条记录为NULL,则返回0,如果不为NULL,则返回该记录的值。
select ename, sal*12 + nvl(comm, 0) from emp;
ENAME SAL*12+NVL(COMM,0)
---------- ------------------
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
ENAME SAL*12+NVL(COMM,0)
---------- ------------------
JAMES 11400
FORD 36000
MILLER 15600
已选择14行。