oracle教學(xué)課件尚硅谷宋紅康03單行函數(shù)

1、第3節(jié)單 行 函 數(shù),新浪微博：尚硅谷-,目 標(biāo),通過本章學(xué)習(xí)，您將可以: SQL中不同類型的函數(shù) 在 SELECT 語句中使用字符，數(shù)字，日期和轉(zhuǎn)換函數(shù) 使用條件表達(dá)式,SQL 函數(shù),函 數(shù),函數(shù)執(zhí)行,y = f(x1,x2),兩種 SQL 函數(shù),函 數(shù),單行函數(shù),多行函數(shù),單行函數(shù),單行函數(shù): 操作數(shù)據(jù)對(duì)象 接受參數(shù)返回一個(gè)結(jié)果 只對(duì)一行進(jìn)行變換 每行返回一個(gè)結(jié)果 可以轉(zhuǎn)換數(shù)據(jù)類型 可以嵌套 參數(shù)可以是一列或一個(gè)值,function_name (arg1, arg2,.),單行函數(shù),轉(zhuǎn) 換,字 符,數(shù) 值,日 期,通 用,單行函數(shù),字符函數(shù),字符函數(shù),LOWER UPPER INITCAP

2、,CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE,大小寫控制函數(shù),字符控制函數(shù),函數(shù),結(jié)果,大小寫控制函數(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

3、rows selected,SELECT employee_id, 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) REPLACE(abcd,b,m),HelloWorld Hello 10 6 *24000

4、 24000* elloWorld amcd,函數(shù),結(jié)果,字符控制函數(shù),這類函數(shù)控制字符:,SELECT employee_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: 截?cái)?TRUNC(45.926, 2)45.92 MO

5、D: 求余 MOD(1600, 300) 100,SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;,ROUND 函數(shù),DUAL 是一個(gè)偽表，可以用來測試函數(shù)和表達(dá)式,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 =

6、 SA_REP;,MOD 函數(shù),日 期,Oracle 中的日期型數(shù)據(jù)實(shí)際含有兩個(gè)值: 日期和時(shí)間。,SELECT last_name, hire_date FROM employees WHERE last_name like G%;,日 期,函數(shù)SYSDATE 返回: 日期 時(shí)間,日期的數(shù)學(xué)運(yùn)算,在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。 兩個(gè)日期相減返回日期之間相差的天數(shù)。 日期不允許做加法運(yùn)算，無意義 可以用數(shù)字除24來向日期中加上或減去天數(shù)。,日期的數(shù)學(xué)運(yùn)算,SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees W

7、HERE department_id = 90;,日期函數(shù),兩個(gè)日期相差的月數(shù),MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,向指定日期中加上若干月數(shù),指定日期的下一個(gè)星期 * 對(duì)應(yīng)的日期,本月的最后一天,日期四舍五入,日期截?cái)?函數(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,

8、28-FEB-95,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:,yyyy 年 mm 月 dd 日 day 星期 hh 小時(shí) mi 分鐘 ss 秒,轉(zhuǎn)換函數(shù),隱 性,顯 性,數(shù)據(jù)類型轉(zhuǎn)換,隱式數(shù)據(jù)類型轉(zhuǎn)換,Oracle 自動(dòng)完成下列轉(zhuǎn)換：,VARCHAR2 or CHAR,源數(shù)據(jù)類型,目標(biāo)數(shù)據(jù)類型,VARCHAR2 or CHA

9、R,NUMBER,DATE,NUMBER,DATE,VARCHAR2,VARCHAR2,varchar2,number,date,顯式數(shù)據(jù)類型轉(zhuǎn)換,NUMBER,CHARACTER,TO_CHAR,TO_CHAR函數(shù)對(duì)日期的轉(zhuǎn)換,格式: 必須包含在單引號(hào)中而且大小寫敏感。 可以包含任意的有效的日期格式。 日期之間用逗號(hào)隔開。,TO_CHAR(date, format_model),SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss) FROM dual;,YYYY,日期格式的元素,YEAR,MM,MONTH,DY,DAY,2004,TWO THOUSAND A

10、ND FOUR,02,MON,MONDAY,JULY,MON,JUL,DD,02,日期格式的元素,時(shí)間格式 使用雙引號(hào)向日期中添加字符,TO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換,SELECT last_name, TO_CHAR(hire_date, DD Month YYYY) AS HIREDATE FROM employees;,select employee_id,last_name,hire_date from employees where to_char(hire_date,yyyy-mm-dd) = 1987-09-17,TO_DATE 函數(shù)對(duì)字符的轉(zhuǎn)換,使用 TO_DATE 函數(shù)將字

11、符轉(zhuǎn)換成數(shù)字:,TO_DATE(char, format_model),使用 TO_DATE :,TO_DATE(2012年10月29日 08:10:21,yyyy“年”mm”月”dd“日”hh:mi:ss) From dual,練習(xí)：返回hire_date 為 */*/*的員工信息，使用顯示日期表達(dá),TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換,下面是在TO_CHAR 函數(shù)中經(jīng)常使用的幾種格式:,TO_CHAR(number, format_model),9,0,$,L,.,數(shù)字,零,美元符,本地貨幣符號(hào),小數(shù)點(diǎn),千位符,SELECT TO_CHAR(salary, $99,999.00) SALARY

12、FROM employees WHERE last_name = Ernst;,TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換,TO_NUMBER 函數(shù)對(duì)字符的轉(zhuǎn)換,使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成日期:,TO_NUMBER(char, format_model),使用 TO_NUMBER :,TO_NUMBER(￥1,234,567,890.00,L999,999,999,999.99) from dual,通用函數(shù),這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值： NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2)

13、 COALESCE (expr1, expr2, ., exprn),NVL 函數(shù),將空值轉(zhuǎn)換成一個(gè)已知的值： 可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。 函數(shù)的一般形式: NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet),練習(xí)1：求公司員工的年薪（含commission_pct) 練習(xí)2：輸出last_name,department_id，當(dāng)department_id為null時(shí)，顯示沒有部門。,SELECT last_name, salary, NVL(commission_pct, 0), (salar

14、y*12) + (salary*12*NVL(commission_pct, 0) AN_SAL FROM employees;,使用NVL函數(shù),1,2,1,2,SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) income FROM employees WHERE department_id IN (50, 80);,使用 NVL2 函數(shù),1,2,1,2,NVL2 (expr1, expr2, expr3) : expr1不為NULL，返回expr2；為NULL，返回expr3。,exp

15、1 != null ? exp2 : exp3,練習(xí)：查詢員工的獎(jiǎng)金率，若為空，返回0.01, 若不為空，返回實(shí)際獎(jiǎng)金率+0.015,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,NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1,使用 COALESCE 函數(shù),C

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

17、pr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END,練習(xí)：查詢部門號(hào)為 10, 20, 30 的員工信息, 若部門號(hào)為 10, 則打印其工資的 1.1 倍, 20 號(hào)部門, 則打印其工資的 1.2 倍, 30 號(hào)部門打印其工資的 1.3 倍數(shù),SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THE

18、N 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 表達(dá)式,下面是使用case表達(dá)式的一個(gè)例子：,DECODE 函數(shù),在需要使用 IF-THEN-ELSE 邏輯時(shí):,DECODE(col|expression, search1, result1 , , search2, result2,., , default),DECODE 函數(shù),SELECT last_name, job_id, salary, DECODE(job_id, IT_PR