一.單行函數(shù)介紹

---單行函數(shù)對單行操作

---每行返回一個結(jié)果

---有可能返回值與原參數(shù)數(shù)據(jù)類型不一致(轉(zhuǎn)換函數(shù))

---單行函數(shù)可以寫在SELECT,WHERE,ORDER BY子句中

---有些函數(shù)沒有參數(shù)，有些函數(shù)有一個或多個參數(shù)

---函數(shù)可以嵌套

分類：字符函數(shù)，數(shù)字函數(shù)，日期函數(shù)，轉(zhuǎn)換函數(shù)，通用函數(shù)

二.字符函數(shù)

1.簡介

定義：主要指參數(shù)類型是字符型，不同函數(shù)返回值可能是字符或數(shù)字類型

<1>.LOWER：全小寫

       LOWER('SQL Course')——>sql course

<2>.UPPER：全大寫

       UPPER('SQL Course')——>SQL COURSE

<3>.INITCAP：首字母大寫

       INITCAP('SQL Course')——>Sql course

<4>.CONCAT：字符串連接

       CONCAT('Good','String')——>GoodString

<5>.SUBSTR：字符串截取

       SUBSTR('String',1,3)——>Str

<6>.LENGTH：返回字符串長度

       LENGTH('String')——>6

<7>.INSTR：返回一個字符串在另一個字符串中的位置

       INSTR('String','r')——>3

<8>.LPAD：左填充

       LPAD(sal,10,'*')——>******5000

<9>.RPAD：右填充

        RPAD(sal,10,'*')——>5000******

<10>.TRIM：去掉左右兩邊指定字符

         TRIM('S' FROM 'SSMITH')——>MITH

<11>.REPLACE:替換字符串

         REPLACE('abc','b','d')——>adc

三.數(shù)字函數(shù)

<1>.ROUND:四舍五入函數(shù)

       ROUND(12.3)——>12

       ROUND(12.5)——>13

<2>.TRUNC:截斷函數(shù)

       TRUNC(65.654,0)——>65

       TRUNC(65.654,-1)——>60

<3>.MOD:取余函數(shù)

       MOD(10,12)——>10

       MOD(7,12)——>7

       MOD(10,4)——>2

四.日期函數(shù)

<1>.SYSDATE:返回系統(tǒng)日期

<2>.MONTHS_BETWEEN:返回兩個日期間隔的月數(shù)

       select months_between('02-2月-06','02-2月-06') from dual;

<3>.ADD_MONTHS:在指定日期基礎(chǔ)上加上相應(yīng)的月數(shù)

       select add_months('02-2月-06',8) from dual;

<4>.NEXT_DAY:返回某一日期的下一個指定日期

       select next_day('1-2月-12','星期一')  next_day from dual;

<5>.LAST_DAY:返回指定日期當(dāng)月最后一天的日期

       select  last_day('1-2月-12') last_day from dual;

<6>.ROUND(date[,'fmt'])對日期進(jìn)行指定格式的四舍五入操作，按照YEAR、MONTH、DAY等進(jìn)行四舍五入

       SELECT  employee_id, hire_date, ROUND(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';

<7>.TRUNC(date[,'fmt'])對日期進(jìn)行指定格式的截斷操作。按照 YEAR、MONTH、DAY等進(jìn)行截斷

       SELECT employee_id, hire_date, TRUNC(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';

<8>.EXTRACT：返回從日期類型中取出指定年、月、日

       SELECT  last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTH FROM   employees WHERE  department_id = 90;

五.轉(zhuǎn)換函數(shù)

---通常在字符類型、日期類型、數(shù)字類型之間進(jìn)行顯性轉(zhuǎn)換。

<1>.TO_CHAR(date|number|'fmt'):把日期類型/數(shù)字類型的表達(dá)式或列轉(zhuǎn)換為字符類型

--‘fmt’:指的是需要顯示的格式，需要寫在單引號中，并且是大小寫敏感，可包含任何有效的日期格式

常用日期格式：

---YYYY：4位數(shù)字表示年份                                            ---DY:星期的英文前三位字母

---YY：兩位數(shù)字年份，但是無世紀(jì)轉(zhuǎn)換                          ---DAY:星期的英文拼寫

---RR：2位數(shù)字表示年份，有世紀(jì)轉(zhuǎn)換                           ---D：數(shù)字表示一星期的第幾天，星期天是一周的第一天

---YEAR:年份的英文拼寫                                                 ---DD：數(shù)字表示一個月中的第幾天

---MM：兩位數(shù)字表示月份                                              ---DDD：數(shù)字表示一年中的第幾天

---MONTH:月份英文拼寫                                                 ---AM或PM：上下午表示

---HH 或HH12或HH24：數(shù)字表示小時                            ---MI：數(shù)字表示分鐘   

HH12代表12小時計時,HH24代表24小時計時                  ---SS：數(shù)字表示秒；

特殊格式：

TH：顯示數(shù)字表示的英文序數(shù)詞，如：DDTH顯示天數(shù)的序數(shù)詞
SP：顯示數(shù)字表示的拼寫
SPTH：顯示數(shù)字表示的序數(shù)詞的拼寫

           select to_char(sysdate,’ddspth’) from dual;

“字符串”:如在格式中顯示字符串，需要兩端加雙引號
           select to_char(sysdate,’dd “of” month ‘)  from dual;        

數(shù)字到字符型轉(zhuǎn)換：

進(jìn)行數(shù)字類型到字符型轉(zhuǎn)換，格式中的寬度一定要超過實際列寬度，否則會顯示為###

-- 9：一位數(shù)字
-- 0：一位數(shù)字或前
-- $：顯示為美元符
-- L：顯示按照區(qū)域
--  .：小數(shù)點
--  ,：千位分割符         

select to_char(9832, '$9,999.00')  from  dual;      

<2>.TO_NUMBER(char[,’fmt’]): 把字符類型列或表達(dá)式轉(zhuǎn)換為數(shù)字類型

       select to_number('9832', 9999)  from  dual;

<3>.TO_DATE(char[,‘fmt’]): 把字符類型列或表達(dá)式轉(zhuǎn)換為日期類型

        select to_date('20120304', 'yy-mm-dd')  from  dual;

六.通用函數(shù)

<1>NVL(表達(dá)式1，表達(dá)式2)函數(shù)：該函數(shù)功能是空值轉(zhuǎn)換，把空值轉(zhuǎn)換為其他值，解決空值問題。如果表達(dá)式1為空，則表達(dá)式2就是要轉(zhuǎn)換成的值。

注意：數(shù)據(jù)格式可以是日期、字符、數(shù)字，但數(shù)據(jù)類型必須匹配

      select nvl(null,1) from dual;
      select nvl(2,1) from dual;

<2>.NVL2(表達(dá)式1, 表達(dá)式2, 表達(dá)式3)函數(shù)：該函數(shù)是對第一個參數(shù)進(jìn)行檢查。如果第一個參數(shù)不為空，則輸出第二個參數(shù)，如果第一個參數(shù)為空，則輸出低三個參數(shù)，表達(dá)式1可以為任何數(shù)據(jù)類型。

      select nvl2(null,1,2) from dual;
      select nvl2(3,1,2) from dual;

<3>.NULLIF(表達(dá)式1，表達(dá)式2)函數(shù)：該函數(shù)主要完成兩個參數(shù)的比較。當(dāng)兩個參數(shù)不相等時，返回值是第一個參數(shù)值；當(dāng)兩個參數(shù)相等時，返回值是空值。

      select nullif(1,1) from dual;

      返回值是：不是null;
      select nullif(1,2) from dual; 

<4>.COALESCE(表達(dá)式1, 表達(dá)式2, ... 表達(dá)式n)函數(shù)：該函數(shù)是對NVL函數(shù)的擴展。COALESCE函數(shù)功能是返回第一個不為空的參數(shù)，參數(shù)個數(shù)不受限制。

      select coalesce(null,null,0,1) from dual;

<5>.CASE表達(dá)式：

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1  THEN '低'
WHEN 0.2  THEN '中'
WHEN 0.3  THEN '高'
ELSE '無'
END)   Commission
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY last_name;

<6>.DECODE函數(shù)：

DECODE(字段|表達(dá)式, 條件1,結(jié)果1[,條件2,結(jié)果2…，][,缺省值])
 

 select last_name, commission_pct,decode( commission_pct,0.1,'低',0.2,'中',0.3 , '高','無') commission 

   from employees where commission_pct is not null orderbylast_name;