oracle內(nèi)部講義3-6ppt課件

1、單行函數(shù)目的經(jīng)過(guò)本章學(xué)習(xí)，您將可以:SQL中不同類型的函數(shù)。在 SELECT 語(yǔ)句中運(yùn)用字符，數(shù)字和日期函數(shù)。描畫轉(zhuǎn)換型函數(shù)的用途。SQL 函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)執(zhí)行輸出結(jié)果兩種 SQL 函數(shù)函數(shù)單行函數(shù)多行函數(shù)單行函數(shù)單行函數(shù):操作數(shù)句對(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ù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE大小寫控制函數(shù)字

2、符控制函數(shù)函數(shù)結(jié)果大小寫控制函數(shù)這類函數(shù)改動(dòng)字符的大小寫。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course大小寫控制函數(shù)顯示員工 Higgins的信息:SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = higgins;no rows selectedSELECT employee_id, last_name, department_idFROM employeesWHERE L

3、OWER(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)HelloWorldHello106*2400024000*elloWorld函數(shù)結(jié)果字符控制函數(shù)這類函數(shù)控制字符:SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_

4、name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP;字符控制函數(shù)123123數(shù)字函數(shù)ROUND: 四舍五入ROUND(45.926, 2)45.93TRUNC: 截?cái)郥RUNC(45.926, 2) 45.92MOD: 求余MOD(1600, 300)100SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND 函數(shù)DUAL 是一個(gè)偽表，可以用來(lái)測(cè)試函數(shù)和表達(dá)式。123312SELECT TRU

5、NC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC 函數(shù)312123SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;MOD 函數(shù)日期Oracle 內(nèi)部運(yùn)用數(shù)字存儲(chǔ)日期: 世紀(jì),年,月,日,小時(shí),分鐘,秒。默許的日期格式是 DD-MON-RR.可以只指定年的后兩位在20世紀(jì)存放21世紀(jì)的日期。 同樣可以在21世紀(jì)存放20世紀(jì)的日期。SELECT last_name, hire_dateFROM employeesWHE

6、RE last_name like G%;日期函數(shù)SYSDATE 前往:日期時(shí)間日期的數(shù)學(xué)運(yùn)算在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。兩個(gè)日期相減前往日期之間相差的天數(shù)。可以用數(shù)字除24來(lái)向日期中加上或減去小時(shí)。日期的數(shù)學(xué)運(yùn)算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;日期函數(shù)兩個(gè)日期相差的月數(shù)MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期中加上假設(shè)干月數(shù)指定日期的下一個(gè)日期本月的最后一天日期四舍五入

7、 日期截?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.677419411-JUL-9408-SEP-9528-FEB-95ROUND(SYSDATE,MONTH) 01-AUG-95ROUND(SYSDATE ,YEAR) 01-JAN-96TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95日期函數(shù)Assume SYSDATE =

8、 25-JUL-95:轉(zhuǎn)換函數(shù)隱性顯性數(shù)據(jù)類型轉(zhuǎn)換隱式數(shù)據(jù)類型轉(zhuǎn)換Oracle 自動(dòng)完成以下轉(zhuǎn)換：VARCHAR2 or CHAR源數(shù)據(jù)類型目的數(shù)據(jù)類型VARCHAR2 or CHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2隱式數(shù)據(jù)類型轉(zhuǎn)換表達(dá)式計(jì)算中, Oracle 自動(dòng)完成以下轉(zhuǎn)換:VARCHAR2 or CHAR源數(shù)據(jù)類型目的數(shù)據(jù)類型VARCHAR2 or CHARNUMBERDATE顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換格式:必需包含在單引號(hào)中而且

9、大小寫敏感?？梢园б獾挠行У娜掌诟袷?。可以運(yùn)用 fm 去掉多余的空格或者前導(dǎo)零。與日期指用逗號(hào)隔開。TO_CHAR(date, format_model)YYYY日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02日期格式的元素時(shí)間格式運(yùn)用雙引號(hào)向日期中添加字符日期在月份中的位置HH24:MI:SS AM15:45:32 PMDD of MONTH12 of OCTOBERddspthfourteenthTO_CHAR 函數(shù)對(duì)日期的轉(zhuǎn)換SELECT last_name, TO_CHAR(hire_

10、date, fmDD Month YYYY) AS HIREDATEFROM employees;TO_CHAR 函數(shù)對(duì)數(shù)字的轉(zhuǎn)換下面是在TO_CHAR 函數(shù)中經(jīng)常運(yùn)用的幾種格式:TO_CHAR(number, format_model)90$L.,數(shù)字零美圓符本地貨幣符號(hào)小數(shù)點(diǎn)千位符SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換TO_NUMBER 和 TO_DATE 函數(shù) 運(yùn)用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字:運(yùn)用 TO_DATE 函數(shù)將字符

11、轉(zhuǎn)換成日期:這些函數(shù)可以運(yùn)用fx 修飾符。 nTO_NUMBER(char, format_model)TO_DATE(char, format_model)TO_NUMBER 和 TO_DATE 函數(shù) 運(yùn)用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字:運(yùn)用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期:這些函數(shù)可以運(yùn)用 fx 修飾符。TO_NUMBER(char, format_model)TO_DATE(char, format_model)RR 日期格式當(dāng)前年1995199520012001日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式19952021202

12、11995YY 格式1995191720212095當(dāng)前的年份:04904950995099The return date is in the current centuryThe return date is in the century after the current oneThe return date is in the century before the current oneThe return date is in the current century指定的年份:RR 日期格式SELECT last_name, TO_CHAR(hire_date, DD-Mon-YYYY

13、)FROM employeesWHERE hire_date TO_DATE(01-Jan-90, DD-Mon-RR);運(yùn)用RR日期格式查找雇傭日期在1990年之前的員工，在1999或如今運(yùn)用下面的命令會(huì)產(chǎn)生一樣的結(jié)果:嵌套函數(shù)單行函數(shù)可以嵌套。嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。F3(F2(F1(col,arg1),arg2),arg3)步驟1 = 結(jié)果1步驟2 =結(jié)果2步驟3 =結(jié)果3SELECT last_name, NVL(TO_CHAR(manager_id), No Manager)FROM employeesWHERE manager_id IS NULL;嵌套函數(shù)通用函數(shù)這些函數(shù)適

14、用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL (expr1, expr2)NVL2 (expr1, expr2, expr3)NULLIF (expr1, expr2)COALESCE (expr1, expr2, ., exprn)NVL 函數(shù)將空值轉(zhuǎn)換成一個(gè)知的值：可以運(yùn)用的數(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*

15、12*NVL(commission_pct, 0) AN_SALFROM employees;運(yùn)用NVL函數(shù)1212SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);運(yùn)用 NVL2 函數(shù)1212SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(fi

16、rst_name), LENGTH(last_name) resultFROM employees;運(yùn)用 NULLIF 函數(shù)123123運(yùn)用 COALESCE 函數(shù)COALESCE 與 NVL 相比的優(yōu)點(diǎn)在于 COALESCE 可以同時(shí)處置交替的多個(gè)值。假設(shè)第一個(gè)表達(dá)式費(fèi)空,那么前往這個(gè)表達(dá)式，對(duì)其他的參數(shù)進(jìn)展COALESCE 。SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;運(yùn)用 COALESCE 函數(shù)條件表達(dá)式在 SQL 語(yǔ)句中運(yùn)用IF-THEN

17、-ELSE 邏輯。運(yùn)用兩種方法:CASE 表達(dá)式DECODE 函數(shù)CASE 表達(dá)式在需求運(yùn)用 IF-THEN-ELSE 邏輯時(shí):CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprENDSELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK

18、 THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;CASE 表達(dá)式下面是運(yùn)用case表達(dá)式的一個(gè)例子：DECODE 函數(shù)在需求運(yùn)用 IF-THEN-ELSE 邏輯時(shí):DECODE(col|expression, search1, result1 , search2, result2,., , default)DECODE 函數(shù)SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM employees;DECODE 函