8.1 描述和使用SQL中的字符、數(shù)字和日期函數(shù)

8.1.1 函數(shù)定義

函數(shù)定義: 接收輸入?yún)?shù)，執(zhí)行運算并返回單個值的程序。每次執(zhí)行時，函數(shù)只返回一個值。 函數(shù)輸入?yún)?shù)可以為0或多個。函數(shù)可以嵌套在其它函數(shù)中。

8.1.2 函數(shù)類型:

• 單行函數(shù): 單行函數(shù)(Single-row SQL functions)每次只做作用于數(shù)據(jù)集的一行，若行有5行，則要執(zhí)行5次。 如字符，數(shù)字，日期,轉(zhuǎn)換函數(shù)和通用函數(shù)都是單行函數(shù)。

csharp
復制代碼
#length執(zhí)行了4次，即結(jié)果集的次數(shù)
select region_id,region_name ,length(region_name) from hr.regions;

• 多行函數(shù): 函數(shù)用于多行。 通常用于求和或平均值，記錄總數(shù)。 有時稱為聚合函數(shù)或分組函數(shù)。

8.1.3 大小寫轉(zhuǎn)換函數(shù)

sql
復制代碼
#
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 字符串操作函數(shù)

• Trim : TRIM只能截取一個字符

sql
復制代碼
#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 函數(shù) ,填充到指定位數(shù)。若是大于指定位數(shù)，就會截止
select rpad(first_name || ' ' || last_name,18) || ' earns ' || lpad(salary,6,' ' )  from hr.employees where department_id=100;

# TRIM函數(shù)
# 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 搜索字符串在給定字符串的位置，返回數(shù)字位置
#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次出現(xiàn)的位置。
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;  --倒數(shù)第3個字符開始，取2個字符。
SUBS
----
#9

## replace(原字段，“原字段舊內(nèi)容“,“原字段新內(nèi)容“)
update demo set name = replace(name,'醫(yī)院','醫(yī)院住院部')
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

字符串綜合示例:

csharp
復制代碼
#到出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 數(shù)字函數(shù)

ROUND:

scss
復制代碼
#數(shù)字函數(shù)
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 函數(shù),對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:

sql
復制代碼
#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

sql
復制代碼
#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 日期

日期函數(shù):

• SYSDATE,ADD_MONTHS,MONTHS_BETWEEN,LAST_DAY,NEXT_DAY,ROUND,TRUNC. 日期相減是數(shù)字類型。
• TO_NUMBER(SYSDATE)
• TO_DATE : 和NLS_DATE_FORMAT要一致
• Default 格式是 'DD-MM-RR'

sql
復制代碼
#日期
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) 下一個星期幾出現(xiàn)的日期 
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函數(shù)的使用是返回指定日期月份的最后一天
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 函數(shù) ,可對日期截取
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中可用的類型轉(zhuǎn)換函數(shù)

SQL 轉(zhuǎn)換函數(shù)是單行函數(shù)，如TO_CHAR, TO_NUMBER,TO_DATE。

1)隱式數(shù)據(jù)類型轉(zhuǎn)換

csharp
復制代碼
#數(shù)字轉(zhuǎn)字符，日期轉(zhuǎn)字符
select length(12345678) from dual;
select sysdate from dual; #
select length(sysdate) from dual;

#字符轉(zhuǎn)數(shù)字
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)顯式數(shù)據(jù)類型轉(zhuǎn)換

python
復制代碼
#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

國家語言支持參數(shù)(nls_parameters) 對于指定返回日期和數(shù)字元素的語言和格式非常有用。

sql
復制代碼
#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

日期轉(zhuǎn)換操作:

• TO_CHAR

python
復制代碼
#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
復制代碼
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

sql
復制代碼
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中應(yīng)用條件表達式

8.4.1 嵌套函數(shù)

sql
復制代碼
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 條件函數(shù)

• NVL(original,ifnull) : 測試數(shù)為空，返回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。

sql
復制代碼
#nvl(original,ifnull)要參數(shù)類型相同,nvl2第二，第三參數(shù)類型也要相同，否則出錯。 
#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

參考:

• 日期計算 https://www.cnblogs.com/plmm/p/7381496.html