1、單行函數(shù),目標,通過本章學習，您將可以: SQL中不同類型的函數(shù)。 在 SELECT 語句中使用字符，數(shù)字和日期函數(shù)。 描述轉換型函數(shù)的用途。,SQL 函數(shù),函數(shù),函數(shù)執(zhí)行,兩種 SQL 函數(shù),函數(shù),單行函數(shù),多行函數(shù),單行函數(shù),單行函數(shù): 操作數(shù)句對象 接受函數(shù)返回一個結果 只對一行進行變換 每行返回一個結果 可以轉換數(shù)據(jù)類型 可以嵌套 參數(shù)可以是一列或一個值,function_name (arg1, arg2,.),單行函數(shù),轉換,字符,數(shù)值,日期,通用,單行函數(shù),字符函數(shù),字符函數(shù),LOWER UPPER INITCAP,CONCAT SUBSTR LENGTH INSTR LPAD |

2、 RPAD TRIM REPLACE,大小寫控制函數(shù),字符控制函數(shù),函數(shù),結果,大小寫控制函數(shù),這類函數(shù)改變字符的大小寫。,LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course),sql course SQL COURSE Sql Course,大小寫控制函數(shù),顯示員工 Higgins的信息:,SELECT employee_id, last_name, department_id FROM employees WHERE last_name = higgins; no rows selected,SELECT employee_id,

3、last_name, department_id FROM employees WHERE LOWER(last_name) = higgins;,CONCAT(Hello, World) SUBSTR(HelloWorld,1,5) LENGTH(HelloWorld) INSTR(HelloWorld, W) LPAD(salary,10,*) RPAD(salary, 10, *) TRIM(H FROM HelloWorld),HelloWorld Hello 10 6 *24000 24000* elloWorld,函數(shù),結果,字符控制函數(shù),這類函數(shù)控制字符:,SELECT empl

4、oyee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a? FROM employees WHERE SUBSTR(job_id, 4) = REP;,字符控制函數(shù),1,2,3,1,2,3,數(shù)字函數(shù),ROUND: 四舍五入 ROUND(45.926, 2)45.93 TRUNC: 截斷 TRUNC(45.926, 2) 45.92 MOD: 求余 MOD(1600, 300)100,SELECT ROUND(45.923,2), ROUND(45

5、.923,0), ROUND(45.923,-1) FROM DUAL;,ROUND 函數(shù),DUAL 是一個偽表，可以用來測試函數(shù)和表達式。,1,2,3,3,1,2,SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;,TRUNC 函數(shù),3,1,2,1,2,3,SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = SA_REP;,MOD 函數(shù),日期,Oracle 內(nèi)部使用數(shù)字存儲日期: 世紀,年,月,日,小時,分鐘,秒。

6、默認的日期格式是 DD-MON-RR. 可以只指定年的后兩位在20世紀存放21世紀的日期。 同樣可以在21世紀存放20世紀的日期。,SELECT last_name, hire_date FROM employees WHERE last_name like G%;,日期,函數(shù)SYSDATE 返回: 日期 時間,日期的數(shù)學運算,在日期上加上或減去一個數(shù)字結果仍為日期。 兩個日期相減返回日期之間相差的天數(shù)。 可以用數(shù)字除24來向日期中加上或減去小時。,日期的數(shù)學運算,SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees W

7、HERE department_id = 90;,日期函數(shù),兩個日期相差的月數(shù),MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月數(shù),指定日期的下一個日期,本月的最后一天,日期四舍五入,日期截斷,函數(shù),描述,MONTHS_BETWEEN (01-SEP-95,11-JAN-94),日期函數(shù),ADD_MONTHS (11-JAN-94,6),NEXT_DAY (01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-9

8、5,ROUND(SYSDATE,MONTH) 01-AUG-95,ROUND(SYSDATE ,YEAR) 01-JAN-96,TRUNC(SYSDATE ,MONTH) 01-JUL-95,TRUNC(SYSDATE ,YEAR) 01-JAN-95,日期函數(shù),Assume SYSDATE = 25-JUL-95:,轉換函數(shù),隱性,顯性,數(shù)據(jù)類型轉換,隱式數(shù)據(jù)類型轉換,Oracle 自動完成下列轉換：,VARCHAR2 or CHAR,源數(shù)據(jù)類型,目標數(shù)據(jù)類型,VARCHAR2 or CHAR,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,隱式數(shù)據(jù)類型轉

9、換,表達式計算中, Oracle 自動完成下列轉換:,VARCHAR2 or CHAR,源數(shù)據(jù)類型,目標數(shù)據(jù)類型,VARCHAR2 or CHAR,NUMBER,DATE,顯式數(shù)據(jù)類型轉換,NUMBER,CHARACTER,TO_CHAR,TO_CHAR 函數(shù)對日期的轉換,格式: 必須包含在單引號中而且大小寫敏感。 可以包含任意的有效的日期格式。 可以使用 fm 去掉多余的空格或者前導零。 與日期指用逗號隔開。,TO_CHAR(date, format_model),YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND AND FOUR,02,

10、MON,MONDAY,JULY,MON,JUL,DD,02,日期格式的元素,時間格式 使用雙引號向日期中添加字符 日期在月份中的位置,TO_CHAR 函數(shù)對日期的轉換,SELECT last_name, TO_CHAR(hire_date, fmDD Month YYYY) AS HIREDATE FROM employees;,TO_CHAR 函數(shù)對數(shù)字的轉換,下面是在TO_CHAR 函數(shù)中經(jīng)常使用的幾種格式:,TO_CHAR(number, format_model),9,0,$,L,.,數(shù)字,零,美元符,本地貨幣符號,小數(shù)點,千位符,SELECT TO_CHAR(salary, $99,

11、999.00) SALARY FROM employees WHERE last_name = Ernst;,TO_CHAR函數(shù)對數(shù)字的轉換,TO_NUMBER 和 TO_DATE 函數(shù),使用 TO_NUMBER 函數(shù)將字符轉換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉換成日期: 這些函數(shù)可以使用fx 修飾符。 n,TO_NUMBER(char, format_model),TO_DATE(char, format_model),TO_NUMBER 和 TO_DATE 函數(shù),使用 TO_NUMBER 函數(shù)將字符轉換成數(shù)字: 使用 TO_DATE 函數(shù)將字符轉換成日期: 這些函數(shù)可以使用 fx

12、 修飾符。,TO_NUMBER(char, format_model),TO_DATE(char, format_model),RR 日期格式,當前年 1995 1995 2001 2001,日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95,RR 格式 1995 2017 2017 1995,YY 格式 1995 1917 2017 2095,當前的年份:,049,049,5099,5099,The return date is in the current century,The return date is in the century after th

13、e current one,The return date is in the century before the current one,The return date is in the current century,指定的年份:,RR 日期格式,SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY) FROM employees WHERE hire_date TO_DATE(01-Jan-90, DD-Mon-RR);,使用RR日期格式查找雇傭日期在1990年之前的員工， 在1999或現(xiàn)在使用下面的命令會產(chǎn)生相同的結果:,嵌套函數(shù),單行函

14、數(shù)可以嵌套。 嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。,F3(F2(F1(col,arg1),arg2),arg3),步驟1 = 結果1,步驟2 =結果2,步驟3 =結果3,SELECT last_name, NVL(TO_CHAR(manager_id), No Manager) FROM employees WHERE manager_id IS NULL;,嵌套函數(shù),通用函數(shù),這些函數(shù)適用于任何數(shù)據(jù)類型，同時也適用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, exp

15、r2, ., exprn),NVL 函數(shù),將空值轉換成一個已知的值： 可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。 函數(shù)的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet),SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SAL FROM employees;,使用NVL函數(shù),1,2,1,2,SELECT last_name, salary, com

16、mission_pct, NVL2(commission_pct, SAL+COMM, SAL) income FROM employees WHERE department_id IN (50, 80);,使用 NVL2 函數(shù),1,2,1,2,SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(first_name), LENGTH(last_name) result FROM employees;,使用 NULLIF 函數(shù),1,2,3,1,2,3,使用

17、COALESCE 函數(shù),COALESCE 與 NVL 相比的優(yōu)點在于 COALESCE 可以同時處理交替的多個值。 如果第一個表達式費空,則返回這個表達式，對其他的參數(shù)進行COALESCE 。,SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;,使用 COALESCE 函數(shù),條件表達式,在 SQL 語句中使用IF-THEN-ELSE 邏輯。 使用兩種方法: CASE 表達式 DECODE 函數(shù),CASE 表達式,在需要使用 IF-THEN-ELSE

18、 邏輯時:,CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END,SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARY FROM employees;,CASE