【Oracle学习08】单行函数与转换函数
8.1 描述和使用SQL中的字符、数字和日期函数
8.1.1 函数定义
函数定义: 接收输入参数，执行运算并返回单个值的程序。每次执行时，函数只返回一个值。 函数输入参数可以为0或多个。函数可以嵌套在其它函数中。
8.1.2 函数类型:
- 单行函数: 单行函数(Single-row SQL functions)每次只做作用于数据集的一行，若行有5行，则要执行5次。 如字符，数字，日期,转换函数和通用函数都是单行函数。
#length执行了4次，即结果集的次数
select region_id,region_name ,length(region_name) from hr.regions;
- 多行函数: 函数用于多行。 通常用于求和或平均值，记录总数。 有时称为聚合函数或分组函数。
8.1.3 大小写转换函数
#
select lower(100+200),lower('SQL'),lower('sysdate') from dual;
select first_name,last_name,lower(last_name) from hr.employees
where lower(last_name) like '%ur%';
# UPPER
select * from hr.countries where upper(country_name) like '%U%S%A%';
#INITCAP 首字母大写，其它字母小写。
SQL> select initcap(' init CAP or init_cap or init%cap') as initcap from dual;
INITCAP
----------------------------------------------------------------------
Init Cap Or Init_Cap Or Init%Cap
8.1.4 字符串操作函数
- Trim : TRIM只能截取一个字符
#CONCAT
SQL> select concat('Today is:',sysdate) from dual;
CONCAT('TODAYIS:',SYSDATE)
--------------------------------------------------------
Today is:2020-01-21 03:43:22
#LENGTH
select * from hr.countries where length(country_name) > 10;
# LPAD/RPAD 函数 ,填充到指定位数。若是大于指定位数，就会截止
select rpad(first_name || ' ' || last_name,18) || ' earns ' || lpad(salary,6,' ' ) from hr.employees where department_id=100;
# TRIM函数
# TRIM只能截取一个字符。
SQL> select trim(trailing 'son' from 'Anderson') from dual;
ORA-30001: trim set should have only one character
SQL> select trim(both '*' from '**Hiden**'), trim(leading '*' from '**Hiden**') , trim(trailing '*' from '**Hiden**') from dual;
TRIM(BOTH' TRIM(LEADING'* TRIM(TRAILING'
---------- -------------- --------------
Hiden Hiden** **Hiden
#INSTR 搜索字符串在给定字符串的位置，返回数字位置
#instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
select instr('1#3#5#7#9#','#') from dual;
select instr('1#3#5#7#9#','#',5) from dual;
select instr('1#3#5#7#9#','#',3,4) from dual; -- 第三个字符开始，第4次出现的位置。
INSTR('1#3#5#7#9#','#',3,4)
---------------------------
10
#SUBSTR 指定位置取子字符串,substr( string, start_position, [ length ] )
SQL> select substr('1#3#5#7#9#',5) from dual;
SUBSTR('1#3#
------------
5#7#9#
select substr('1#3#5#7#9#',5,3) from dual; --第5个字符开始取3个。
SUBSTR
------
5#7
select substr('1#3#5#7#9#',-3,2) from dual; --倒数第3个字符开始，取2个字符。
SUBS
----
#9
## replace(原字段，“原字段旧内容“,“原字段新内容“)
update demo set name = replace(name,'医院','医院住院部')
SQL> select replace('1#3#5#7#9#','#','->') from dual;
REPLACE('1#3#5#7#9#','#','->')
------------------------------
1->3->5->7->9->
SQL> select replace('1#3#5#7#9#','#') from dual; -- 用null替换#,即删除#号
REPLACE('1
----------
13579
字符串综合示例:
#到出hostname信息。
define email='thx@hostname.163.com';
select instr('&email','@') from dual; #pos=4
select substr('&email',instr('&email','@')+1) from dual; # hostname.163.com
SUBSTR('THX@HOSTNAME.163.COM',IN
--------------------------------
hostname.163.com
select substr('&email',instr('&email','@')+1, instr('&email','.163.com')-instr('&email','@')-1) from dual; #到hostname
SUBSTR('THX@HOST
----------------
hostname
8.1.5 数字函数
ROUND:
#数字函数
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
45.92 45 40
#ROUND 函数,对n+1位4舍5入.
SQL> select round(1601.916,1) from dual;
ROUND(1601.916,1)
-----------------
1601.9
SQL> select round(-1601.916,1) from dual;
ROUND(-1601.916,1)
------------------
-1601.9
SQL> select round(1601.916,2) from dual;
ROUND(1601.916,2)
-----------------
1601.92
SQL> select round(1601.916,-3) from dual;
ROUND(1601.916,-3)
------------------
2000
TRUNC:
#Truncate
SQL> select trunc(1601.916,1) from dual; -- 1601.9
SQL> select trunc(1601.916,2) from dual; -- 1601.91
SQL> select trunc(1601.916,-3) from dual;
TRUNC(1601.916,-3)
------------------
1000
SQL> SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;
TRUNC(ROUND(156.00,-2),-1)
--------------------------
200
MOD
#Modulus
SQL> select mod(6,2) from dual;
MOD(6,2)
----------
0
SQL> select mod(5,3) from dual;
MOD(5,3)
----------
2
8.1.6 日期
日期函数:
- SYSDATE,ADD_MONTHS,MONTHS_BETWEEN,LAST_DAY,NEXT_DAY,ROUND,TRUNC. 日期相减是数字类型。
- TO_NUMBER(SYSDATE)
- TO_DATE : 和NLS_DATE_FORMAT要一致
- Default 格式是 'DD-MM-RR'
#日期
SELECT SESSIONTIMEZONE, CURRENT_DATE,CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE CURRENT_TIMESTAMP
--------------------------------------------------------------------------------
+08:00 2020-01-24 06:15:16 24-JAN-20 06.15.16.049367 AM +08:00
SQL> select ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR'),TRUNC(SYSDATE,'MONTH'),TRUNC(SYSDATE,'YEAR') from dual;
ROUND(SYSDATE,'MONT ROUND(SYSDATE,'YEAR TRUNC(SYSDATE,'MONT TRUNC(SYSDATE,'YEAR
------------------- ------------------- ------------------- -------------------
2020-02-01 00:00:00 2020-01-01 00:00:00 2020-01-01 00:00:00 2020-01-01 00:00:00
SQL> SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;
LAST_NAME WEEKS
-------------------------------------------------- ----------
King 1701.32318
Kochhar 1583.18032
De Haan 1410.32318
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-01-21 06:19:51
SQL> select sysdate-2 from dual;
SYSDATE-2
-------------------
2020-01-19 06:22:09
#日期-->字符串
SQL> select to_char(sysdate,'YYYY-MM-DD W HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDWHH24:MI:SS')
------------------------------------------
2020-01-21 3 06:30:23
# 字符串-->日期
select * from nls_session_parameters where PARAMETER='NLS_DATE_FORMAT';
SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';
SQL> select add_months(SYSDATE,6) from dual;
ADD_MONTHS(S
------------
25-JUL-20
select TO_DATE(ADD_MONTHS(SYSDATE,6),'MON-DD-YYYY') from dual; --ORA-01843: 无效的月份
SQL> select TO_DATE(ADD_MONTHS(SYSDATE,6),'DD-MON-YYYY') from dual; --ok
TO_DATE(ADD_
------------
25-JUL-20
##
SQL> select to_date('2020-01-07 10:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
TO_DATE('2020-01-07
-------------------
2020-01-07 10:00:00
## Date1- Date2 = num1
SQL> select to_date('2020-01-07 10:00:00','yyyy-mm-dd hh24:mi:ss') - to_date('2020-01-01','yyyy-mm-dd') from dual;
TO_DATE('2020-01-0710:00:00','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2020-01-01','YYYY-
--------------------------------------------------------------------------------
6.41666667
#MONTHS_BETWEEN
SQL> select months_between(sysdate , sysdate-31) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE-31)
----------------------------------
1
#
SQL> select add_months(to_date('2015.12.29','YYYY/MM/DD'),1) from dual;
ADD_MONTHS(TO_DATE(
-------------------
2016-01-29 00:00:00
#
SQL> select add_months(to_date('07-APR-2009','DD-Mon-YYYY'),2.5) from dual;
ADD_MONTHS(TO_DATE(
-------------------
2009-06-07 00:00:00
# NEXT_DAY(start_date , day of the week) 下一个星期几出现的日期
SQL> select next_day(to_date('01-JAN-2020','DD-MON-YYYY'),'tue') from dual;
NEXT_DAY(TO_DATE('0
-------------------
2020-01-07 00:00:00
select next_day(sysdate,'mon') from dual; 查询出下一个星期一是哪一天
#last_day函数的使用是返回指定日期月份的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2020-01-31 07:07:59
#ROUND 日期的4舍5入
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-01-21 07:11:22
SQL> select round(sysdate) day , round(sysdate,'w') week,round(sysdate,'month') month ,round(sysdate,'year') year from dual;
DAY WEEK MONTH YEAR
------------------- ------------------- ------------------- -------------------
2020-01-21 00:00:00 2020-01-22 00:00:00 2020-02-01 00:00:00 2020-01-01 00:00:00
#TRUNC 函数 ,可对日期截取
SQL> select trunc(sysdate) from dual;
TRUNC(SYSDATE)
-------------------
2020-01-21 00:00:00
SQL> select trunc(sysdate) day , trunc(sysdate,'w') week,trunc(sysdate,'month') month ,trunc(sysdate,'year') year from dual;
DAY WEEK MONTH YEAR
------------------- ------------------- ------------------- -------------------
2020-01-21 00:00:00 2020-01-15 00:00:00 2020-01-01 00:00:00 2020-01-01 00:00:00
8.2 SQL中可用的类型转换函数
SQL 转换函数是单行函数，如TO_CHAR, TO_NUMBER,TO_DATE。
1)隐式数据类型转换
#数字转字符，日期转字符
select length(12345678) from dual;
select sysdate from dual; #
select length(sysdate) from dual;
#字符转数字
select mod('123',2) from dual; -- 1
SQL> select mod('11.123',2) from dual;
MOD('11.123',2)
---------------
1.123
>select mod('11.123.123',2) from dual; --会出错。
#字串到日期
select add_months('24-JAN-09',1) from dual;
ADD_MONTHS('24-JAN-
-------------------
0024-02-09 00:00:00
SQL> select add_months(to_date('2015.12.29','YYYY/MM/DD'),1) from dual;
ADD_MONTHS(TO_DATE(
-------------------
2016-01-29 00:00:00
2)显式数据类型转换
#TO_CHAR
SQL> select to_char(0001), to_char(0001,'099999') from dual;
TO TO_CHAR(0001,'
-- --------------
1 000001
#||后要接单引号
SQL> select to_char(0001) || " is a special number" from dual; --error
ORA-00904: " is a special number": invalid identifier
SQL> select to_char(0001) || ' is a special number' from dual; --ok
TO_CHAR(0001)||'ISASPECIALNUMBER'
------------------------------------------
1 is a special number
#
SQL> select 1 || 'is a special number' from dual; --ok
1||'ISASPECIALNUMBER'
----------------------------------------
1is a special number
#
SQL> select to_char(14,'L0999') from dual;
TO_CHAR(14,'L0999')
------------------------------
$0014
8.3 使用TO_CHAR,TO_NUMBER,TO_DATE
国家语言支持参数(nls_parameters) 对于指定返回日期和数字元素的语言和格式非常有用。
#nls_session_parameters
select * from nls_session_parameters;
select * from nls_session_parameters where PARAMETER='NLS_DATE_FORMAT';
alter session set NLS_DATE_FORMAT='DD-MON-RR'; --RR是相当于YY,year
SQL> select to_char(sysdate,'fmDD Month YYYY') as today from dual;
TODAY
------------------------------------------------------------
24 January 2020
# FM去除前导0,即01-->1
SQL> select to_char(to_date('2020-01-21','YYYY-MM-DD'),'DD-MM-YYYY') from dual;
TO_CHAR(TO_DATE('202
--------------------
21-01-2020
SQL> select to_char(to_date('2020-01-21','YYYY-MM-DD'),'fm DD-MM-YYYY') from dual;
TO_CHAR(TO_DATE('202
--------------------
21-1-2020
SQL> select to_char(30.1,'$99,999.99') from dual;
TO_CHAR(30.1,'$99,999.
----------------------
$30.10
SQL> SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');
LAST_NAME TO_CHAR(HIRE_DATE,'DD-MON-YYYY')
-------------------------------------------------- ----------------------------------------
Whalen 17-Sep-1987
King 17-Jun-1987
Kochhar 21-Sep-1989
日期转换操作:
- TO_CHAR
#TO_CHAR
select to_char(sysdate,'YYYY-MM-DD') from dual;
select to_char(sysdate,'year') from dual;
select to_char(sysdate,'day') from dual;
SQL> select to_char(sysdate,'HH24:MI:SS AM') from dual;
TO_CHAR(SYSDATE,'HH24:
----------------------
11:52:53 AM
SQL> select to_char(sysdate) from dual;
TO_CHAR(SYSDATE)
--------------------------------------
2020-01-21 16:02:32
SQL> select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------------------------------------------------------
January
#
SQL> select TO_CHAR(TO_DATE('01-JAN-00','DD-MON-RR'),'Day') from dual;
TO_CHAR(TO_DATE('01-JAN-00','DD-MON-RR'),'DAY')
------------------------------------------------------------------------
Saturday
SQL> select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
--------------------------------------
2020-01-21 04:09:10
SQL> SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees;
LAST_NAME HIREDATE
------------------------------------- -----------------------------------------------------------------------
OConnell 21 June 1999
Grant 13 January 2000
- TO_DATE
SQL> select * from nls_session_parameters where PARAMETER='NLS_DATE_FORMAT';
SQL> select to_date('01-DEC-2010') from dual;
TO_DATE('01-DEC-201
-------------------
0001-12-20 10:00:00
SQL> select to_date('25-DEC','DD-MON') from dual;
TO_DATE('25-DEC','D
-------------------
2020-12-25 00:00:00
SQL> select to_date('25-DEC') from dual; --出错
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> select to_date('25-DEC-10','fxDD-MON-YYYY') from dual; --出错
ORA-01862: the numeric value does not match the length of the format item
SQL> select to_date('01-DEC-2010','DD-MON-YYYY') from dual;
TO_DATE('01-DEC-201
-------------------
2010-12-01 00:00:00
- TO_NUMBER
select to_number('$100.55') from dual; --错误
ORA-01722: invalid number
SQL>select to_number('$1,000.55','$99,999.99') from dual; --ok
TO_NUMBER('$1,000.55','$99,999.99')
-----------------------------------
1000.55
SQL> select to_number(123.56,'999.9') from dual; --error 。
select to_number(123.56,'999.999') from dual; --right 123.56
8.4 SELECT中应用条件表达式
8.4.1 嵌套函数
SQL> select next_day(last_day(sysdate) - 7 ,'tue') from dual;
NEXT_DAY(LAST_DAY(S
-------------------
2020-01-28 20:29:41
8.4.2 条件函数
- NVL(original,ifnull) : 测试数为空，返回ifnull。original,ifnull要类型相同否则出错。
- NVL2(orginal,ifnotnull,ifnull): 不为空则返回,ifnotnull，否则返回ifnull。ifnotnull,ifnull类型要相同。
- NULLIF(expr1,expr2) ： 相等，则返回null，否则返回expre1
- COALESCE: 返回第一个非空值。COALESCE(expr1,expr2) 等价于 NUL(expr1,expr2)。
- Decode(expr1,comp1,iftrue1,comp2,iftrue2,.,iffalse) : 返回第一个相等的值,否则返回iffalse。
- Case: 类似if then else。
#nvl(original,ifnull)要参数类型相同,nvl2第二，第三参数类型也要相同，否则出错。
#select nvl(10,'abc') from dual; --error
01722. 00000 - "invalid number"
SQL>select nvl(to_char(10),'abc') from dual; --ok
SQL> select nvl(null,1234) from dual;
NVL(NULL,1234)
--------------
1234
SQL> select nvl(substr('abc',4),'No substring exists') from dual;
NVL(SUBSTR('ABC',4),'NOSUBSTRINGEXISTS
--------------------------------------
No substring exists
#NVL2
SQL> select nvl2(1234,1,'a string') from dual; -- 会出错。
SQL> select nvl2(null ,1234,5678) from dual;
NVL2(NULL,1234,5678)
--------------------
5678
# NULLIF
SQL> select nullif(1234,1234) from dual;
NULLIF(1234,1234)
-----------------
null
SQL> select nullif(1234,5678) from dual;
NULLIF(1234,5678)
-----------------
1234
# coalesce 单词意思为合并
SQL> select coalesce(null,null,'astring') from dual;
COALESCE(NULL,
--------------
astring
# Decode
SQL> select decode(1234,123,'123 is a match') from dual;
D
-
(null)
SQL> select decode(1234,123,'123 is a match','not match') from dual;
DECODE(1234,123,'1
------------------
not match
SQL> SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
#CASE
SQL> select case substr(1234,1,3)
when '1234' then '1234 is match'
when '123' then '123 match'
else 'no match'
end
from dual;
CASESUBSTR(1234,1,
------------------
123 match
#
SQL> SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees where last_name like 'D%';
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
-------------------------------------------------- ---------- --------------------- ----------
Davies 3100 0 37200
De Haan 17000 0 204000
Dellinger 3400 0 40800
Dilly 3600 0 43200
Doran 7500 .3 117000
#
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
#
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees where rownum<10;
LAST_NAME SALARY QUALIFIED_SALARY
-------------------------------------------------- ---------- ------------------
OConnell 2600 Low
Grant 2600 Low
Whalen 4400 Low
Hartstein 13000 Good
Fay 6000 Medium
Mavris 6500 Medium
Baer 10000 Good
Higgins 12000 Good
Gietz 8300 Medium
