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

100使用ROUND函數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函數使用MOD函數計算工作為salesman的員工的工資（Sal），除以COMM后的余數。SQL>SELECT ename,sal,comm,MOD(sal,comm)2FROM emp

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

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

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

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

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

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

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

語句DECODE(col/expression,search1,result1

[,search2,result2,...,]

[,default])使用DECODE函數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函數SQL>SELECTename,sal,2DECODE(TRUNC(sal/1000,0),30,0.00,4 1,0.09,52,0.20,6