一.單行函數介紹

---單行函數對單行操作

---每行返回一個結果

---有可能返回值與原參數數據類型不一致(轉換函數)

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

---有些函數沒有參數，有些函數有一個或多個參數

---函數可以嵌套

分類：字符函數，數字函數，日期函數，轉換函數，通用函數

二.字符函數

1.簡介

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

<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

三.數字函數

<1>.ROUND:四舍五入函數

       ROUND(12.3)——>12

       ROUND(12.5)——>13

<2>.TRUNC:截斷函數

       TRUNC(65.654,0)——>65

       TRUNC(65.654,-1)——>60

<3>.MOD:取余函數

       MOD(10,12)——>10

       MOD(7,12)——>7

       MOD(10,4)——>2

四.日期函數

<1>.SYSDATE:返回系統日期

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

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

<3>.ADD_MONTHS:在指定日期基礎上加上相應的月數

       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:返回指定日期當月最后一天的日期

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

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

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

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

       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;

五.轉換函數

---通常在字符類型、日期類型、數字類型之間進行顯性轉換。

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

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

常用日期格式：

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

---YY：兩位數字年份，但是無世紀轉換                          ---DAY:星期的英文拼寫

---RR：2位數字表示年份，有世紀轉換                           ---D：數字表示一星期的第幾天，星期天是一周的第一天

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

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

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

---HH 或HH12或HH24：數字表示小時                            ---MI：數字表示分鐘   

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

特殊格式：

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

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

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

數字到字符型轉換：

進行數字類型到字符型轉換，格式中的寬度一定要超過實際列寬度，否則會顯示為###

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

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

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

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

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

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

六.通用函數

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

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

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

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

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

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

      select nullif(1,1) from dual;

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

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

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

<5>.CASE表達式：

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函數：

DECODE(字段|表達式, 條件1,結果1[,條件2,結果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;