Les03-處理單行的函數(shù)

三.處理單行的函數(shù)目標(biāo)完成本節(jié)課后，應(yīng)當(dāng)達(dá)到如下的目標(biāo)：描述在SQL語(yǔ)句中可能用到的各種函數(shù)。在SELECT語(yǔ)句中使用字符串、數(shù)字、日期的函數(shù)。描述轉(zhuǎn)換函數(shù)的用法SQL函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)處理動(dòng)作輸出結(jié)果值兩種類(lèi)型的SQL函數(shù)函數(shù)單行函數(shù)多行函數(shù)單行函數(shù)操作數(shù)據(jù)項(xiàng)接收參數(shù)并返回一個(gè)值在返回的每一行上進(jìn)行處理每一行都有一個(gè)處理結(jié)果可能要修改數(shù)據(jù)類(lèi)型可以進(jìn)行嵌套function_name(column|expression,[arg1,arg2,...])字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPADTRIM大小寫(xiě)轉(zhuǎn)換函數(shù)字符操作函數(shù)函數(shù)結(jié)果大小寫(xiě)轉(zhuǎn)換對(duì)字符串的大小寫(xiě)進(jìn)行轉(zhuǎn)換LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse使用大小寫(xiě)轉(zhuǎn)換函數(shù)查出員工Blake的號(hào)碼、名字、部門(mén)號(hào)SQL>SELECT empno,ename,deptno

2FROM emp

3WHERE ename='blake';norowsselected

EMPNOENAMEDEPTNO----------------------------7698BLAKE30SQL>SELECT empno,ename,deptno

2FROM emp

3WHERE ename=UPPER('blake');CONCAT('Good','String')SUBSTR('String',1,3)LENGTH('String')INSTR('String','r')LPAD(sal,10,'*')TRIM('S'FROM'SSMITH')GoodStringStr63******5000MITH函數(shù)結(jié)果字符操作函數(shù)操作字符串使用字符操作函數(shù)SQL>SELECTename,CONCAT(ename,job),LENGTH(ename),2 INSTR(ename,'A')3FROMemp

4WHERESUBSTR(job,1,5)='SALES';ENAMECONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,'A')----------------------------------------------------------MARTINMARTINSALESMAN62ALLENALLENSALESMAN51TURNERTURNERSALESMAN60WARDWARDSALESMAN42數(shù)字函數(shù)ROUND:對(duì)指定的值進(jìn)行四舍五入 小數(shù)ROUND(45.926,2) 45.93TRUNC: 對(duì)指定的值進(jìn)行取整 小數(shù)TRUNC(45.926,2) 45.92MOD:返回除法計(jì)算后的余數(shù)MOD(1600,300)

100使用ROUND函數(shù)SQL>SELECTROUND(45.923,2),ROUND(45.923,0),2 ROUND(45.923,-1)3FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650SQL>SELECT TRUNC(45.923,2),TRUNC(45.923),2 TRUNC(45.923,-1)3FROMDUAL;TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)-------------------------------------------45.924540使用TRUNC函數(shù)使用MOD函數(shù)計(jì)算工作為salesman的員工的工資（Sal），除以COMM后的余數(shù)。SQL>SELECT ename,sal,comm,MOD(sal,comm)2FROM emp

3WHERE job='SALESMAN';ENAMESALCOMMMOD(SAL,COMM)-----------------------------------------MARTIN125014001250ALLEN1600300100TURNER150001500WARD1250500250使用日期Oracle以一種內(nèi)部的格式來(lái)保存日期:世紀(jì),年,月,日,小時(shí),分鐘,秒.缺省的格式：DD-MON-YY.例如：23-JAN-02SYSDATE是一個(gè)返回日期和時(shí)間的函數(shù).DUAL是一個(gè)顯示SYSDATE的虛擬表.日期的計(jì)算為一個(gè)日期值增加或者減少一個(gè)數(shù)字.計(jì)算出兩個(gè)日期之間相隔的天數(shù).要為日期值增加小時(shí)數(shù)，可以將小時(shí)數(shù)除以24后加上。用數(shù)學(xué)運(yùn)算符對(duì)日期進(jìn)行計(jì)算SQL>SELECTename,(SYSDATE-hiredate)/7WEEKS2FROMemp

3WHEREdeptno=10;ENAMEWEEKS-------------------KING830.93709CLARK853.93709MILLER821.36566日期函數(shù)兩個(gè)日期之間的月數(shù)MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 為一個(gè)日期增加月份一個(gè)日期的下一個(gè)指定日子（例如：星期1）的日期某個(gè)月份的最后一天對(duì)日期進(jìn)行四舍五入計(jì)算 對(duì)日期進(jìn)行取整計(jì)算函數(shù)描述MONTHS_BETWEEN('01-SEP-95','11-JAN-94')使用Date函數(shù)ADD_MONTHS('11-JAN-94',6)NEXT_DAY('01-SEP-95','FRIDAY')LAST_DAY('01-SEP-95')19.6774194'11-JUL-94''08-SEP-95''30-SEP-95'使用日期函數(shù)ROUND('25-JUL-95','MONTH')01-AUG-95ROUND('25-JUL-95','YEAR') 01-JAN-96TRUNC('25-JUL-95','MONTH') 01-JUL-95TRUNC('25-JUL-95','YEAR') 01-JAN-95轉(zhuǎn)換函數(shù)隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換顯式數(shù)據(jù)類(lèi)型轉(zhuǎn)換數(shù)據(jù)類(lèi)型轉(zhuǎn)換隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換Oracle服務(wù)器可以自動(dòng)進(jìn)行下列類(lèi)型的轉(zhuǎn)換:VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換對(duì)于表達(dá)式的計(jì)算,Oracle服務(wù)器可以自動(dòng)進(jìn)行下列轉(zhuǎn)換:VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATE顯式數(shù)據(jù)類(lèi)型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類(lèi)型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類(lèi)型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函數(shù)Fmt的格式:必須用單引號(hào)括起來(lái)，并且是大小寫(xiě)敏感的日期格式必須是可用的前面加上fm字符以壓縮掉開(kāi)始和結(jié)束的空格同日期值用逗號(hào)隔開(kāi)TO_CHAR(date,'fmt')YYYY日期元素的格式Y(jié)EARMMMONTHDYDAY四個(gè)數(shù)字表示的年份年份的名字兩個(gè)數(shù)字表示的月份每星期某天的3字母縮寫(xiě)天的完整名字月份的名字日期元素的格式日期中時(shí)間部分的格式.

增加編碼字串需要用引號(hào)將字符串括起來(lái).

HH24:MI:SSAM15:45:32PMDD"of"MONTH12ofOCTOBER使用TO_CHAR函數(shù)SQL>SELECT ename, 2 TO_CHAR(hiredate,'fmDDMonthYYYY')HIREDATE3FROM emp;ENAMEHIREDATE---------------------------KING17November1981BLAKE1May1981CLARK9June1981JONES2April1981MARTIN28September1981ALLEN20February1981...14rowsselected.帶有數(shù)字的TO_CHAR用法在TO_CHAR函數(shù)中使用這些格式，以按照某種字符格式展示一個(gè)數(shù)字值TO_CHAR(number,'fmt')90$L.,代表一個(gè)數(shù)字值強(qiáng)制顯示一個(gè)0在一個(gè)浮點(diǎn)值前面顯示一個(gè)$符號(hào)使用浮點(diǎn)類(lèi)型的本地貨幣符號(hào)顯示一個(gè)小數(shù)點(diǎn)千位的指示符TO_CHAR函數(shù)中使用數(shù)字SQL>SELECT TO_CHAR(sal,'$99,999')SALARY2FROM emp

3WHERE ename='SCOTT';SALARY--------$3,000TO_NUMBER和TO_DATE函數(shù)將一個(gè)字符串轉(zhuǎn)化為數(shù)字使用TO_NUMBER

函數(shù)TO_NUMBER(char[,'fmt'])將一個(gè)字符串轉(zhuǎn)化為日期格式，使用TO_DATE

函數(shù)TO_DATE(char[,'fmt'])NVL函數(shù)NVL(expr1,expr2)如果expr1為null,則返回expr2,否則返回expr1可以使用的數(shù)據(jù)類(lèi)型是數(shù)字、日期、字符型.數(shù)據(jù)類(lèi)型必須能夠匹配N(xiāo)VL(comm,0)NVL(hiredate,'01-JAN-97')NVL(job,'NoJobYet')SQL>SELECTename,sal,comm,(sal*12)+NVL(comm,0)2FROMemp;使用NVL函數(shù)ENAMESALCOMM(SAL*12)+NVL(COMM,0)------------------------------------------------KING500060000BLAKE285034200CLARK245029400JONES297535700MARTIN1250140016400ALLEN160030019500...14rowsselected.DECODE函數(shù)DECODE函數(shù)的作用類(lèi)似于CASEorIF-THEN-ELSE

語(yǔ)句DECODE(col/expression,search1,result1

[,search2,result2,...,]

[,default])使用DECODE函數(shù)SQL>SELECTjob,sal,2DECODE(job,'ANALYST',SAL*1.1,3'CLERK',SAL*1.15,4'MANAGER',SAL*1.20,5SAL)6REVISED_SALARY7FROMemp;JOBSALREVISED_SALARY--------------------------------PRESIDENT50005000MANAGER28503420MANAGER24502940...14rowsselected.使用DECODE函數(shù)SQL>SELECTename,sal,2DECODE(TRUNC(sal/1000,0),30,0.00,4 1,0.09,52,0.20,6