單行函數(shù)市公開課獲獎(jiǎng)?wù)n件

1、第五章 單行函數(shù)本章關(guān)鍵點(diǎn)：SQL中不同類型函數(shù)。在 SELECT 語句中使用字符，數(shù)字和日期函數(shù)。描述轉(zhuǎn)換型函數(shù)用途。第1頁第1頁SQL 函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)執(zhí)行輸出結(jié)果第2頁第2頁兩種 SQL 函數(shù)函數(shù)單行函數(shù)多行函數(shù)第3頁第3頁單行函數(shù)單行函數(shù):操作數(shù)據(jù)對(duì)象接受函數(shù)返回一個(gè)結(jié)果只對(duì)一行進(jìn)行變換每行返回一個(gè)結(jié)果能夠轉(zhuǎn)換數(shù)據(jù)類型能夠嵌套參數(shù)能夠是一列或一個(gè)值function_name (arg1, arg2,.)第4頁第4頁單行函數(shù)轉(zhuǎn)換字符數(shù)值日期通用單行函數(shù)第5頁第5頁字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD |

2、 RPADTRIMREPLACE大小寫控制函數(shù)字符控制函數(shù)第6頁第6頁字符函數(shù)字符函數(shù)：主要指參數(shù)類型是字符型，不同函數(shù)返回值可能是字符型或數(shù)字類型。1.大小寫轉(zhuǎn)換函數(shù)： LOWER(列名|表示式)：將大寫或大小寫混合字符轉(zhuǎn)換成小寫UPPER(列名|表示式) ：將小寫或大小寫混合字符轉(zhuǎn)換成大寫INITCAP(列名|表示式) ：將每個(gè)單詞第一個(gè)字母轉(zhuǎn)換成大寫，其余字母都轉(zhuǎn)換成小寫 第7頁第7頁函數(shù)結(jié)果大小寫控制函數(shù)這類函數(shù)改變字符大小寫。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Cou

3、rse第8頁第8頁大小寫控制函數(shù)顯示員工 scott信息:第9頁第9頁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,n1,n2)第10頁第10頁LENGTH:取字符長(zhǎng)度 格式:LENGTH(column | expression) INSTR:返回s1中，子串s2從n1開始，第n2次出現(xiàn)位置

4、。n1,n2默認(rèn)值為1 格式:INSTR(s1,s2,n1,n2)第11頁第11頁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替換。 格式：REPLACE(s1,s2,s3)第12頁第12頁去掉前面*去掉前面空格去掉前后*第13頁第13頁CONCAT(Hello, Wo

5、rld)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ù)控制字符:第14頁第14頁SELECT ename, concat(ename, sal) name, mgr, length(ename)length, instr(ename, S) instr FROM emp WHERE SUBSTR(mg

6、r, 3) like%9%;字符控制函數(shù)312213第15頁第15頁數(shù)字函數(shù)1、ROUND(列名|表達(dá)式, n)：將列或表達(dá)式所表示數(shù)值四舍五入到小數(shù)點(diǎn)后第n位。2、TRUNC(列名|表達(dá)式,n)：將列或表達(dá)式所表示數(shù)值截取到小數(shù)點(diǎn)后第n位。注意：第二個(gè)參數(shù)可認(rèn)為負(fù)數(shù)，表示為小數(shù)點(diǎn)左邊指定位數(shù)后面部分截去，即均以0記。3、MOD(m,n)：取m除以n后得到余數(shù)。ROUND: 四舍五入ROUND(45.926, 2)45.93TRUNC: 截?cái)郥RUNC(45.926, 2)45.92MOD: 求余MOD(1600, 300)100第16頁第16頁SELECT ROUND(45.923,2),

7、ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND 函數(shù)DUAL 是一個(gè)偽表，能夠用來測(cè)試函數(shù)和表示式。123312第17頁第17頁SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC 函數(shù)312123第18頁第18頁SELECT empno,ename, sal, MOD(sal, 1000)FROM empWHERE job = MANAGER;MOD 函數(shù)11第19頁第19頁日期Oracle 內(nèi)部使用數(shù)字存儲(chǔ)日期: 世紀(jì),年,月,日,小時(shí),分鐘,秒。默認(rèn)日

8、期格式是 DD-MON-RR.SELECT sysdate FROM dual;11第20頁第20頁日期數(shù)學(xué)運(yùn)算在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。兩個(gè)日期相減返回日期之間相差天數(shù)。能夠用數(shù)字除24來向日期中加上或減去小時(shí)。第21頁第21頁日期數(shù)學(xué)運(yùn)算SELECT ename, (SYSDATE-hiredate)/7 AS WEEKSFROM empWHERE deptno = 30;第22頁第22頁日期函數(shù)兩個(gè)日期相差月數(shù)MONTHS_BETWEEN(d1,d2)ADD_MONTHS(d,n)NEXT_DAY（d,s）LAST_DAY（d）ROUND（date，fmt）TRUNC （d

9、ate，fmt） 向指定日期d中加上n個(gè)月指定日期d下一個(gè)星期幾所在日期d所在月份最后一天將日期date按fmt指定格式取整將日期date按fmt指定格式截?cái)嗪瘮?shù)描述第23頁第23頁日期函數(shù)MONTHS_BETWEEN (14-11月-12,11-10月-11)ADD_MONTHS (14-11月-12, 6)NEXT_DAY (14-11月-12, 星期五) LAST_DAY(14-11月-12） 13.096774214-5月 -1316-11月-1230-11月-12第24頁第24頁ROUND(SYSDATE,MONTH) 01-11月-12ROUND(SYSDATE ,YEAR) 01

10、-1月 -13TRUNC(SYSDATE ,MONTH) 01-11月-12 TRUNC(SYSDATE ,YEAR) 01-1月 -12日期函數(shù)Assume SYSDATE = 14-11月-12:按月份小于等于6舍，不小于6入按天小于等于15舍，不小于15入第25頁第25頁轉(zhuǎn)換函數(shù)隱性顯性數(shù)據(jù)類型轉(zhuǎn)換自動(dòng)類型轉(zhuǎn)換明確強(qiáng)制類型轉(zhuǎn)換轉(zhuǎn)換函數(shù)用于將數(shù)值從一個(gè)數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換為另一個(gè)數(shù)據(jù)類型。第26頁第26頁隱式數(shù)據(jù)類型轉(zhuǎn)換Oracle 自動(dòng)完畢下列轉(zhuǎn)換：VARCHAR2 or CHAR源數(shù)據(jù)類型目的數(shù)據(jù)類型VARCHAR2 or CHARNUMBERDATEVARCHAR2NUMBERDATEV

11、ARCHAR2SELECT 12.5+11 FROM dual;SELECT 12.5|11 FROM dual; 第27頁第27頁TO_CHAR 函數(shù)對(duì)日期轉(zhuǎn)換format_model闡明：式:必須包括在單引號(hào)中。能夠包括任意有效日期格式。MM YYYY DD能夠使用 fm 去掉多出空格或者前導(dǎo)零。與日期逗號(hào)隔開。TO_CHAR(date, format_model)第28頁第28頁TO_CHAR 函數(shù)對(duì)日期轉(zhuǎn)換SELECT ename,hiredate , TO_CHAR(hiredate, DD MM YYYY) AS HIRE_DATEFROM emp第29頁第29頁TO_CHAR 函

12、數(shù)對(duì)日期轉(zhuǎn)換SELECT ename, TO_CHAR(hiredate, fmDD Month YYYY) AS HIREDATEFROM emp;第30頁第30頁TO_CHAR 函數(shù)對(duì)數(shù)字轉(zhuǎn)換下面是在TO_CHAR 函數(shù)中format_model經(jīng)常使用幾種格式:TO_CHAR(number, format_model)90$L.,數(shù)字零美元符當(dāng)?shù)刎泿欧?hào)小數(shù)點(diǎn)千位符第31頁第31頁SELECT sal, TO_CHAR(sal, $99,999.00) SALARYFROM empWHERE ename = SCOTT;TO_CHAR函數(shù)對(duì)數(shù)字轉(zhuǎn)換第32頁第32頁TO_NUMBER 和

13、TO_DATE 函數(shù) 使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)字:使用 TO_DATE 函數(shù)將字符轉(zhuǎn)換成日期: TO_NUMBER(char, format_model)TO_DATE(char, format_model)第33頁第33頁SELECT TO_DATE(-05, YYYY-MM) FROM DUALTO_NUMBER 和 TO_DATE 函數(shù)忽略了DD參數(shù)，Oracle會(huì)采用1作為日默認(rèn)值，也就是說會(huì)取整到月。忽略MM參數(shù)，Oracle并不會(huì)取整到年，取整到當(dāng)前月。第34頁第34頁SELECT TO_DATE(-05, YYYY-MM) FROM DUAL第35頁第35頁嵌

14、套函數(shù)單行函數(shù)能夠嵌套。嵌套函數(shù)執(zhí)行順序是由內(nèi)到外。F3(F2(F1(col,arg1),arg2),arg3)環(huán)節(jié)1 = 結(jié)果1環(huán)節(jié)2 =結(jié)果2環(huán)節(jié)3 =結(jié)果3第36頁第36頁SELECT ename, NVL(TO_CHAR(mgr), No Manager)FROM empWHERE deptno=10;嵌套函數(shù)第37頁第37頁通用函數(shù)這些函數(shù)適合用于任何數(shù)據(jù)類型，同時(shí)也適合用于空值：NVL (表示式1, 表示式2)函數(shù)功效是空值轉(zhuǎn)換，把空值轉(zhuǎn)換為其它值，處理空值問題。表示式1是需要轉(zhuǎn)換列或表示式，表示式2是假如第一個(gè)參數(shù)為空時(shí)，需要轉(zhuǎn)換值。 NVL (expr1, expr2)NVL2 (expr1,expr2,expr3)函數(shù)功效是用于處理null，假如expr1值為NULL，則該函數(shù)顯示expr3值；不為NULL，顯示expr2值 。NVL2 (expr1, expr2, expr3)第38頁第38頁SELECT ename, NVL2(mgr, *, No Manager)FROM