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

三.處理單行的函數(shù)目標完成本節(jié)課后，應當達到如下的目標：描述在SQL語句中可能用到的各種函數(shù)。在SELECT語句中使用字符串、數(shù)字、日期的函數(shù)。描述轉換函數(shù)的用法SQL函數(shù)函數(shù)輸入?yún)?shù)1參數(shù)2參數(shù)n函數(shù)處理動作輸出結果值兩種類型的SQL函數(shù)函數(shù)單行函數(shù)多行函數(shù)單行函數(shù)操作數(shù)據(jù)項接收參數(shù)并返回一個值在返回的每一行上進行處理每一行都有一個處理結果可能要修改數(shù)據(jù)類型可以進行嵌套function_name(column|expression,[arg1,arg2,...])字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPADTRIM大小寫轉換函數(shù)字符操作函數(shù)函數(shù)結果大小寫轉換對字符串的大小寫進行轉換LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse使用大小寫轉換函數(shù)查出員工Blake的號碼、名字、部門號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ù)結果字符操作函數(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:對指定的值進行四舍五入 小數(shù)ROUND(45.926,2) 45.93TRUNC: 對指定的值進行取整 小數(shù)TRUNC(45.926,2) 45.92MOD:返回除法計算后的余數(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ù)計算工作為salesman的員工的工資（Sal），除以COMM后的余數(shù)。SQL>SELECT ename,sal,comm,MOD(sal,comm)2FROM emp

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

3WHEREdeptno=10;ENAMEWEEKS-------------------KING830.93709CLARK853.93709MILLER821.36566日期函數(shù)兩個日期之間的月數(shù)MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 為一個日期增加月份一個日期的下一個指定日子（例如：星期1）的日期某個月份的最后一天對日期進行四舍五入計算 對日期進行取整計算函數(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轉換函數(shù)隱式數(shù)據(jù)類型轉換顯式數(shù)據(jù)類型轉換數(shù)據(jù)類型轉換隱式數(shù)據(jù)類型轉換Oracle服務器可以自動進行下列類型的轉換:VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2隱式數(shù)據(jù)類型轉換對于表達式的計算,Oracle服務器可以自動進行下列轉換:VARCHAR2orCHARFromToVARCHAR2orCHARNUMBERDATE顯式數(shù)據(jù)類型轉換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類型轉換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類型轉換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR函數(shù)Fmt的格式:必須用單引號括起來，并且是大小寫敏感的日期格式必須是可用的前面加上fm字符以壓縮掉開始和結束的空格同日期值用逗號隔開TO_CHAR(date,'fmt')YYYY日期元素的格式YEARMMMONTHDYDAY四個數(shù)字表示的年份年份的名字兩個數(shù)字表示的月份每星期某天的3字母縮寫天的完整名字月份的名字日期元素的格式日期中時間部分的格式.

增加編碼字串需要用引號將字符串括起來.

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ù)中使用這些格式，以按照某種字符格式展示一個數(shù)字值TO_CHAR(number,'fmt')90$L.,代表一個數(shù)字值強制顯示一個0在一個浮點值前面顯示一個$符號使用浮點類型的本地貨幣符號顯示一個小數(shù)點千位的指示符TO_CHAR函數(shù)中使用數(shù)字SQL>SELECT TO_CHAR(sal,'$99,999')SALARY2FROM emp

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

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

函數(shù)TO_DATE(char[,'fmt'])NVL函數(shù)NVL(expr1,expr2)如果expr1為null,則返回expr2,否則返回expr1可以使用的數(shù)據(jù)類型是數(shù)字、日期、字符型.數(shù)據(jù)類型必須能夠匹配NVL(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ù)的作用類似于CASEorIF-THEN-ELSE

語句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