東軟o課件第4章單行函數(shù)

1、Oracle SQL開發(fā)基礎(chǔ)課程結(jié)構(gòu)2內(nèi)容課時(shí)（H）第一章 Oracle數(shù)據(jù)庫(kù)基礎(chǔ)2.5第二章 編寫簡(jiǎn)單的語(yǔ)句1.5第三章 限制數(shù)據(jù)和對(duì)數(shù)據(jù)排序2第四章 單行函數(shù)4第五章 多表4第六章 分組函數(shù)3第七章 子4第八章 數(shù)據(jù)操作與事務(wù)4第九章 表和約束4第十章 其他數(shù)據(jù)庫(kù)對(duì)象4第四章 單行函數(shù)目標(biāo):本章旨在向?qū)W員介紹：1) 字符函數(shù)、數(shù)字函數(shù)和日期函數(shù)2) 轉(zhuǎn)換函數(shù)和通用函數(shù)時(shí)間：學(xué)時(shí)教學(xué)方法：3本章要點(diǎn) 字符函數(shù) 數(shù)值函數(shù) 日期函數(shù) 轉(zhuǎn)換函數(shù) 通用函數(shù) 函數(shù)嵌套4第四章 單行函數(shù)單行函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)54

2、.1.1單行函數(shù)介紹 1/5輸入輸出參數(shù) 1函數(shù)執(zhí)行作用參數(shù) 2參數(shù)n6結(jié)果值函數(shù)4.1.1單行函數(shù)介紹 2/5函數(shù)類型Functions單行函數(shù)多行函數(shù)74.1.1單行函數(shù)介紹 3/5 語(yǔ)法:函數(shù)名(參數(shù)1，參數(shù)2,) 其中的參數(shù)可以是以下之一： 變量 列名 表84.1.1單行函數(shù)介紹 4/5 單行函數(shù)還有以下的一些特征： 單行函數(shù)對(duì)單行操作 每行返回一個(gè)結(jié)果 有可能返回值與原參數(shù)數(shù)據(jù)類型不一致（轉(zhuǎn)換函數(shù)） 單行函數(shù)可以寫在SELECT、WHERE、ORDER BY子句中 有些函數(shù)沒(méi)有參數(shù)，有些函數(shù)包括一個(gè)或多個(gè)參數(shù) 函數(shù)可以嵌套94.1.1單行函數(shù)介紹 5/5單行函數(shù)的分類數(shù)字函數(shù)日期類型

3、字符函數(shù)轉(zhuǎn)換函數(shù)通用函數(shù)10單行函數(shù)第四章 單行函數(shù)單行函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)114.2字符函數(shù) 字符函數(shù)：主要指參數(shù)類型是字符型，不同函數(shù)返回值可能是字符型或數(shù)字類型。大小寫轉(zhuǎn)換字符處理CONCAT SUBSTR LENGTH INSTR LPAD RPAD REPLACETRIMLOWER UPPERINITCAP12字符函數(shù)4.2.1字符大小寫操作函數(shù) LOWER(列名|表小寫 UPPER(列名|表大寫 INITCAP(列名|表)：將大寫或大小寫混合的字符轉(zhuǎn)換成) ：將小寫或大小寫混合的字符轉(zhuǎn)換成)

4、：將每個(gè)單詞的第一個(gè)字母轉(zhuǎn)換成大寫，其余的字母都轉(zhuǎn)換成小寫13函數(shù)結(jié)果LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course)sql course SQL COURSESql Course4.2.2字符處理函數(shù)CONCAT:連接兩個(gè)值,等同于|格式：CONCAT(column1|expression1,column2|expression2)SUBSTR:返回第一個(gè)參數(shù)中從n1字符開始長(zhǎng)度為n2的子串，如果n1是負(fù)值，表從后向前數(shù)的abs(n1)位，如果n2省略,取n1之后的所有字符格式：SUBSTR(column | expression

5、,n1,n2)LENGTH:取字符長(zhǎng)度格式:LENGTH(column | expression)INSTR:返回s1中，子串s2從n1開始，第n2次出現(xiàn)的位置。n1,n2默認(rèn)值為1格式:INSTR(s1,s2,n1,n2)LPAD:返回s1被s2從左面填充到n1長(zhǎng)度。格式：LPAD(s1,n1,s2)RPAD:返回s1被s2從右面填充到n1長(zhǎng)度。格式：RPAD(s1,n1,s2)TRIM:去除字符串頭部或尾部（頭尾）的字符格式:TRIM(leading | trailing | both trim_character From trim_source)REPLACE:把s1中的s2用s3替換

6、。格式:REPLACE(s1,s2,s3)144.2.2字符處理函數(shù)15函數(shù)結(jié)果CONCAT(Good, String) SUBSTR(String,1,3)LENGTH(String)INSTR(String, r)GoodString Str634.2.2字符處理函數(shù)16函數(shù)結(jié)果LPAD(sal,10,*)RPAD(sal,10,*) TRIM(S FROM SSMITH)REPLACE（abc,b,d)*5000 5000* MITHadc4.2.2字符處理函數(shù) 查找公司員工編號(hào)，用戶名（first_name與last_name連接成一個(gè)字符串），職位編號(hào)及l(fā)ast_name的長(zhǎng)度，要求

7、職位從第四位起匹配ACCOUNT，同時(shí)last_name中至少包含一個(gè)e 字母。17SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name) lengthFROMemployeesWHERE SUBSTR(job_id, 4) = ACCOUNTANDINSTR(last_name, e)0;第四章 單行函數(shù)單行函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)184.3數(shù)字函數(shù) ROUND(列名|表, n)：將列或表所表示的數(shù)

8、值四入到小數(shù)點(diǎn)后的第n位。 TRUNC(列名|表數(shù)點(diǎn)后的第n位。,n)：將列或表所表示的數(shù)值截取到小 MOD(m,n)：取m除以n后得到的余數(shù)。194.3數(shù)字函數(shù)20SELECT employee_id, last_name, salary,MOD(salary,900) FROM employeesWHERE department_id=90;SELECT TRUNC(65.654,2),TRUNC(65.654,0),TRUNC(65.654,-1)FROM DUAL;SELECT ROUND(65.654,2),ROUND(65.654,0), ROUND(65.654,-1)FROM

9、DUAL;第四章 單行函數(shù)單行函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)214.4.1日期類型數(shù) 常用的日期運(yùn)算如下：算 日期類型列或表可以加減數(shù)字，功能是在該日期上加減對(duì)應(yīng)的天數(shù)。如：10-AUG-06+15結(jié)果是25-AUG-06。 日期類型列或表之間可以進(jìn)行減操作，功能是計(jì)算兩個(gè)日期之間間隔了多少天。如：10-AUG-06-4-AUG-06入后是6天。結(jié)果四 如果需要加減相應(yīng)小時(shí)或分鐘，可以使用n/24來(lái)實(shí)現(xiàn)。224.4.2常用日期函數(shù) 1/6SYSDATE：返回系統(tǒng)日期MONTHS_BETWEEN：返回兩個(gè)日期間隔的月

10、數(shù)ADD_MONTHS：在指定日期基礎(chǔ)上加上相應(yīng)的月數(shù)NEXT_DAY：返回某一日期的下一個(gè)指定日期LAST_DAY：返回指定日期當(dāng)月最后一天的日期ROUND(date,fmt)將date按照f(shuō)mt指定的格式進(jìn)行四入，fmt為可選項(xiàng)，如果沒(méi)有指定fmt，則默認(rèn)為DD，將date四入為最近的天。格式碼：世紀(jì)CC,年YY，月MM,日DD，小時(shí)HH24，分MI，秒SSTRUNC(date,fmt)將date按照f(shuō)mt指定的格式進(jìn)行截取，fmt為可選項(xiàng)，如果沒(méi)有指定fmt，則默認(rèn)為DD，將date截取為最近的天。EXTRACT：返回從日期類型中取出指定年、月、日234.4.2常用日期函數(shù) 2/6 MO

11、NTHS_BETWEEN 函數(shù)演示公司員工服務(wù)的月數(shù)。 ADD_MONTHS 函數(shù)演示99年公司員工轉(zhuǎn)正日期。24SELECT last_name, salary, hire_date,ADD_MONTHS(hire_date,3) new_date FROMemployeesWHERE hire_date01-1月-1999;SELECT last_name, salary,MONTHS_BETWEEN(SYSDATE,hire_date) monthsFROM employees ORDER BY months;4.4.2常用日期函數(shù) 3/6 NEXT_DAY 函數(shù)演示下周一的日期。 LA

12、ST_DAY 函數(shù)演示06年2月2日所在月份最后一天。25SELECT LAST_DAY(02-2月-2006) LAST DAY FROM DUAL;SELECT NEXT_DAY(02-2月-06,一) NEXT_DAYFROM DUAL;4.4.2常用日期函數(shù) 4/6 ROUND函數(shù)演示98年入職員工入職日期按月四 入。26SELECT employee_id, hire_date,ROUND(hire_date, MONTH) FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;4.4.2常用日期函數(shù) 5/6 TRUNC 函數(shù)演示98年入職員工入職日

13、期按月截?cái)唷?7SELECTemployee_id, hire_date, TRUNC(hire_date, MONTH)FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;4.4.2常用日期函數(shù) 6/6 EXTRACT 函數(shù)語(yǔ)法 部門編號(hào)是90的部門中所有員工入職月份。28SELECT last_name, hire_date,EXTRACT (MONTH FROM HIRE_DATE) MONTHFROMemployeesWHERE department_id = 90;EXTRACT (YEAR MONTHDAYFROM日期類型表)第四章 單行函數(shù)單行

14、函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)294.5轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換顯式數(shù)據(jù)類型轉(zhuǎn)換隱式數(shù)據(jù)類型轉(zhuǎn)換304.5.1數(shù)據(jù)類型隱性轉(zhuǎn)換 如果不同的數(shù)據(jù)類型之間關(guān)聯(lián)，如果不顯式轉(zhuǎn)換數(shù)據(jù)，則它會(huì)根據(jù)以下規(guī)則 對(duì)數(shù)據(jù)進(jìn)行隱式轉(zhuǎn)換：對(duì)于INSERT和UPDATE操作，oracle會(huì)把字段的數(shù)據(jù)類型對(duì)于SELECT語(yǔ)句，oracle會(huì)把字段的數(shù)據(jù)類型隱式轉(zhuǎn)換為變量的數(shù)據(jù)類型當(dāng)比較一個(gè)字符型和數(shù)值型的值時(shí)，oracle會(huì)把字符型的值隱式轉(zhuǎn)換為數(shù)值型當(dāng)比較字符型和日期型的數(shù)據(jù)時(shí)，oracle會(huì)把字符型轉(zhuǎn)換為日期型用連接操作符(|)時(shí)，ora

15、cle會(huì)把非字符類型的數(shù)據(jù)轉(zhuǎn)換為字符類型如果字符類型的數(shù)據(jù)和非字符類型的數(shù)據(jù)(如number、date、rowid等)作算術(shù)運(yùn)算，則oracle會(huì)將字符類型的數(shù)據(jù)轉(zhuǎn)換為合適的數(shù)據(jù)類型，這些數(shù)據(jù)類型可能是number、date、rowid等值或者更新值隱式轉(zhuǎn)換為314.5.2數(shù)據(jù)類型顯性轉(zhuǎn)換 1/2 通常是在字符類型、日期類型、數(shù)字類型之間進(jìn)行顯性轉(zhuǎn)換。 主要有3個(gè)顯性函數(shù)： TO_CHAR TO_NUMBER TO_DATE324.5.2數(shù)據(jù)類型顯性轉(zhuǎn)換 2/2DATETO_CHARTO_DATECHARACTERTO_NUMBERTO_CHARNUMBER334.5.3TO_CHAR函數(shù) 1

16、/6 TO_CHAR(date|number ,fmt)把日期類型/數(shù)字類型的表達(dá)式或列轉(zhuǎn)換為字符類型。 fmt指的是需要顯示的格式： 可包含任何有效的日期格式344.5.3TO_CHAR函數(shù) 2/6 常用日期格式 YYYY：4位數(shù)字表示年份； YY：2位數(shù)字表示年份，但是無(wú)世紀(jì)轉(zhuǎn)換(與RR區(qū)別在后面 章節(jié)介紹)； RR：2位數(shù)字表示年份，有世紀(jì)轉(zhuǎn)換（與YY區(qū)別在后面章 節(jié)介紹）； YEAR：年份的英文拼寫； MM：兩位數(shù)字表示月份； MONTH：月份英文拼寫；354.5.3TO_CHAR函數(shù) 3/6 常用日期格式（續(xù)） DY： DAY：的英文前三位字母；的英文拼寫； D：數(shù)字表示一 DD：數(shù)

17、字表示一的第幾天，中的第幾天；天是一周的第一天。 DDD：數(shù)字表示一年中的第幾天。364.5.3TO_CHAR函數(shù) 4/6 常用時(shí)間格式 AM 或PM：上下午表示； HH 或HH12或HH24：數(shù)字表示小時(shí)。HH12代表12時(shí)，HH24代表24 MI：數(shù)字表示分鐘； SS：數(shù)字表示秒；時(shí)；374.5.3TO_CHAR函數(shù) 5/6 一些特殊格式 TH：顯示數(shù)字表示的英文序數(shù)詞，如：DDTH顯示天數(shù)的序 數(shù)詞。 SP：顯示數(shù)字表示的拼寫。 SPTH：顯示數(shù)字表示的序數(shù)詞的拼寫。 “字符串”:如在格式中顯示字符串，需要兩端加雙引號(hào)。38TO_CHAR(SYSDATE,DD “of” MONTH )T

18、O_CHAR(SYSDATE,DDSPTH)4.5.3TO_CHAR函數(shù) 6/6 日期到字符型轉(zhuǎn)換 TO_CHAR 函數(shù)進(jìn)行日期到字符型復(fù)雜格式轉(zhuǎn)換演示。39SELECT employee_id, last_name,TO_CHAR(hire_date,Day ,the Ddspth of YYYY HH24:MI:SS) hire_dateFROM employeesWHERE department_id=90;ALTER SESSION SET NLS_LANGUAGE = AMERICAN;4.5.4TO_NUMBER函數(shù)和TO_DATE函數(shù) 1/3 TO_NUMBER(char,fmt

19、) 把字符類型列或表類型。 使用格式和TO_CHAR中轉(zhuǎn)換成字符類型中的格式相同轉(zhuǎn)換為數(shù)字 TO_DATE(char,fmt) 把字符類型列或表轉(zhuǎn)換為日期類型。 格式和TO_CHAR中轉(zhuǎn)換成字符類型中的格式相同。404.5.4TO_NUMBER函數(shù)和TO_DATE函數(shù) 2/3 具體格式如下： 9：一位數(shù)字； 0：一位數(shù)字或前導(dǎo)零； $：顯示為符號(hào)； L：顯示按照區(qū)域設(shè)置的本地貨幣符號(hào)； .：小數(shù)點(diǎn); ,：千位分割符；414.5.4TO_NUMBER函數(shù)和TO_DATE函數(shù) 3/3 TO_CHAR 函數(shù)進(jìn)行數(shù)字到字符型格式轉(zhuǎn)換 注意：進(jìn)行數(shù)字類型到字符型轉(zhuǎn)換，格式中的寬度一定要超過(guò)實(shí)際列寬度，否

20、則會(huì)顯示為#。42SELECT last_name, TO_CHAR(salary, $9,999.00) salary FROM employeesWHERE last_name = King;SELECT last_name, TO_CHAR(salary, $99,999.00) salaryFROMemployeesWHERE last_name = King;4.5.5RR功能43如果指定兩位數(shù)年份是：0495099如果當(dāng)前年份的最后兩位數(shù)是：049返回的日期是當(dāng)前世紀(jì)的日期返回的日期是上一個(gè)世紀(jì)的日期5099返回的日期是下一個(gè)世紀(jì)的日期返回的日期是當(dāng)前世紀(jì)的日期當(dāng)前年份指定的日期R

21、R 格式Y(jié)Y 格式199519952001200127-OCT-9527-OCT-1727-OCT-1727-OCT-9519952017201719951995191720172095第四章 單行函數(shù)單行函數(shù):4.1 函數(shù)的概念及分類4.2 字符函數(shù)4.3 數(shù)字函數(shù)4.4 日期函數(shù)4.5 轉(zhuǎn)換函數(shù)4.6 通用函數(shù)444.6通用函數(shù) 與空值（NULL）相關(guān)的一些函數(shù)，完成對(duì)空值（NULL）的一些操作。主要包括以下函數(shù)： NVL NVL2 NULLIF COALESCE 條件處理函數(shù)： CASE表 DECODE454.6.1NVL函數(shù) NVL (表1, 表2)函數(shù)功能是空值轉(zhuǎn)換，把空值轉(zhuǎn)換為其他

22、值，1是需要轉(zhuǎn)換的列或表2是如果解決空值問(wèn)題。表，表第一個(gè)參數(shù)為空時(shí)，需要轉(zhuǎn)換的值。 NVL(comm,0) NVL(hire_date,01-JAN-06) NVL(job_id,No Job Yet) 注意：數(shù)據(jù)格式可以是日期、字符、數(shù)字，但數(shù)據(jù)類型必須匹配。464.6.2NVL2函數(shù) NVL2(表1, 表2, 表3)函數(shù)是對(duì)第一個(gè)參數(shù)進(jìn)行檢查。如果第一個(gè)參數(shù)不為空，則輸出第二個(gè)參數(shù)；如果第一個(gè)1可以為任何數(shù)據(jù)類型。參數(shù)為空，則輸出第三個(gè)參數(shù)。表47SELECT last_name, salary,NVL2(commission_pct, salary + commission_pct,s

23、alary) incomeFROM employeesWHERE last_name LIKE _a%;4.6.3NULLIF函數(shù) NULLIF (表1, 表2)函數(shù)主要是完成兩個(gè)參數(shù)的比較。當(dāng)兩個(gè)參數(shù)不相等時(shí)，返回值是第一個(gè)參數(shù)值；當(dāng)兩個(gè)參數(shù)相等時(shí)，返回值是空值。48SELECT last_name, LENGTH(last_name) LEN_last_NAME, LENGTH() LEN_,NULLIF(LENGTH(last_name), LENGTH() resultFROM employeesWHERE last_name LIKE D%;4.6.4COALESCE函數(shù) COALE

24、SCE (表1, 表2, . 表n)函數(shù)是對(duì)NVL函數(shù)的擴(kuò)展。COALESCE函數(shù)的功能是返回第一個(gè)不為空的參數(shù)，參數(shù)個(gè)數(shù)不受限制。49SELECT last_name, COALESCE(commission_pct, salary*1.1, 100) comm , department_idFROMemployeesWHERE department_id in (50,80) ORDER BY COMM;4.6.5CASE和DECODE函數(shù) 1/3 CASE語(yǔ)法50CASE exprWHEN comparison_expr1 THEN return_expr1 WHEN compariso

25、n_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND4.6.5CASE和DECODE函數(shù) 2/3CASE示例51SELECT last_name, commission_pct, (CASE commission_pctWHEN 0.1 THEN 低WHEN 0.2 THEN 中 WHEN 0.3 THEN 高 ELSE 無(wú)END)Commission FROM employeesWHERE commission_pct IS NOT NULLORDER BY last_name;4.6.5CASE和DECODE函數(shù) 3/3 DECODE(字段|表值), 條件1,結(jié)果1,條件2