一、轉換函數
函數的轉換主要對資料類型的轉換
包括： 隐式資料類型轉換、顯示資料類型轉換
1、隐式轉換：
在背景轉換，資料類型與預設格式比對
Oracle服務器可以在表達式中自動執行資料類型轉換。
可以隐式地将VARCHAR2或CHAR值轉換為表達式中的數字或日期資料類型。
2、顯示資料轉換
a、使用TO_CHAR函數對日期的轉換
TO_CHAR将datetime資料類型轉換為format_model指定格式的VARCHAR2資料類型值。
格式模型是描述存儲在字元串中的datetime格式的字元文字。
to_char(date,‘format_model’)
格式：必須包含在單引号中
大小寫敏感
可以包含任意的有效的日期格式
可以使用fm去掉多餘的空格或者前導零
與日期值用逗号隔開
SQL> select hiredate,to_char(hiredate,‘mm-yyyy’) from emp;
HIREDATE TO_CHAR
--------- -------
17-DEC-80 12-1980
20-FEB-81 02-1981
22-FEB-81 02-1981
02-APR-81 04-1981
28-SEP-81 09-1981
01-MAY-81 05-1981
09-JUN-81 06-1981
19-APR-87 04-1987
17-NOV-81 11-1981
08-SEP-81 09-1981
23-MAY-87 05-1987
03-DEC-81 12-1981
03-DEC-81 12-1981
23-JAN-82 01-1982
14 rows selected.
日期格式的組成
b 、TO_CHAR函數對數字進行轉換
to_char(number,‘format_model’)
常用的格式：
Oracle伺服器顯示一串數字元号(#)，而不是數字超過格式模型中提供的數字的整數。
Oracle服務器将存儲的decimal值四舍五入到格式模型中提供的小數位數。
c 、to_number函數
使用to_number函數将字元轉換為數字格式：
to_number(char,[ , ‘format_madel’])
d 、 to_date 函數
使用 to_date 函數将字元串轉換為日期格式：
to_date(char,[ , ‘format_madel’])
使用TO_NUMBER和TO_DATE函數
字元串轉換為數字或日期。要完成此任務，可使用TO_NUMBER或TO_DATE函數。
• 字元參數中的标點符号和引号必須與格式模型的相應部分完全比對(大小寫除外)。
• 字元參數不能有額外的空格。
• 字元參數中的數字資料必須與格式模型中的相應元素具有相同的位數。如果沒有fm，字元參數中的數字可以省略前導零。
3. 嵌套函數
單層函數可以嵌套到任意層級
嵌套函數的執行順序是由内到外。
4. 通用函數
a、 nvl 函數
nvl (expr1,expr2)
将空值轉換成一個已知的值：
可以使用的資料類型有數字、日期、字元。
資料類型必須比對：
b 、nvl2 函數
nvl2(expr1,expr2,expr3)
如果expr1不是null，那麼轉換為expr2，如果exprt1 是空值，則轉換為expr3
SQL> select ename , sal , comm , sal+nvl(comm,0),nvl2(comm,sal+comm,sal)
2 from emp;
ENAME SAL COMM SAL+NVL(COMM,0) NVL2(COMM,SAL+COMM,SAL)
---------- ---------- ---------- --------------- -----------------------
SMITH 800 800 800
ALLEN 1600 300 1900 1900
WARD 1250 500 1750 1750
JONES 2975 2975 2975
MARTIN 1250 1400 2650 2650
BLAKE 2850 2850 2850
CLARK 2450 2450 2450
SCOTT 3000 3000 3000
KING 5000 5000 5000
TURNER 1500 0 1500 1500
ADAMS 1100 1100 1100
JAMES 950 950 950
FORD 3000 3000 3000
MILLER 1300 1300 1300
14 rows selected.
c 、 nullif 函數
NULLIF函數比較兩個表達式。如果它們相等，函數傳回null。
如果它們不相等，函數傳回第一個表達式。但是不能為第一個表達式指定文字NULL。
SQL> select length(ename),length(job),nullif(length(ename),length(job))
2 from emp;
LENGTH(ENAME) LENGTH(JOB) NULLIF(LENGTH(ENAME),LENGTH(JOB))
------------- ----------- ---------------------------------
5 5
5 8 5
4 8 4
5 7 5
6 8 6
5 7 5
5 7 5
5 7 5
4 9 4
6 8 6
5 5
d 、coalesce 函數
傳回表達式清單中的第一個非空表達式。
coalesce與nvl相比的優點在于coalesce可以同時處理交替的多個值。
如果第一個表達式非空，則傳回這個表達式，對其他的參數進行coalesce。
二 、條件表達式
在SQL語句中使用 IF - THEN - ELSE邏輯
使用兩種方式：
- CASE表達式
- DECODE函數
#查詢員工姓名，職位，薪資和獎金
當職位為ANALYST的員工年薪按照150%進行發放
如果職位是salesman的員工年薪按照140%進行發放
如果職位是MANAGER的員工年薪按照130%進行發放
其他職位的員工年薪按照正常的進行發放
1、case表達式
SQL> select ename,job,sal,comm,
2 case job when ‘ANALYST’ then (sal*12+nvl(comm,0))1.5
3 when ‘SALESMAN’ then (sal12+nvl(comm,0))1.4
4 when ‘MANAGER’ then (sal12+nvl(comm,0))1.3
5 else sal12+nvl(comm,0) end “year_sal”
6 from emp;
ENAME JOB SAL COMM year_sal
---------- --------- ---------- ---------- ----------
SMITH CLERK 800 9600
ALLEN SALESMAN 1600 300 27300
WARD SALESMAN 1250 500 21700
JONES MANAGER 2975 46410
MARTIN SALESMAN 1250 1400 22960
BLAKE MANAGER 2850 44460
CLARK MANAGER 2450 38220
SCOTT ANALYST 3000 54000
KING PRESIDENT 5000 60000
TURNER SALESMAN 1500 0 25200
ADAMS CLERK 1100 13200
ENAME JOB SAL COMM year_sal
---------- --------- ---------- ---------- ----------
JAMES CLERK 950 11400
FORD ANALYST 3000 54000
MILLER CLERK 1300 15600
14 rows selected.
2，decode函數
SQL> select ename,job,sal,comm,
2 decode(job,'ANALYST',(sal*12+nvl(comm,0))*1.5,
3 'SALESMAN',(sal*12+nvl(comm,0))*1.4,
4 'MANAGER',(sal*12+nvl(comm,0))*1.3,
5 sal*12+nvl(comm,0)) "new_sal"
6 from emp;
ENAME JOB SAL COMM new_sal
---------- --------- ---------- ---------- ----------
SMITH CLERK 800 9600
ALLEN SALESMAN 1600 300 27300
WARD SALESMAN 1250 500 21700
JONES MANAGER 2975 46410
MARTIN SALESMAN 1250 1400 22960
BLAKE MANAGER 2850 44460
CLARK MANAGER 2450 38220
SCOTT ANALYST 3000 54000
KING PRESIDENT 5000 60000
TURNER SALESMAN 1500 0 25200
ADAMS CLERK 1100 13200
ENAME JOB SAL COMM new_sal
---------- --------- ---------- ---------- ----------
JAMES CLERK 950 11400
FORD ANALYST 3000 54000
MILLER CLERK 1300 15600
14 rows selected.