1、福建工程學(xué)院第8章 函數(shù) 本章要點了解SQL 函數(shù)的分類；掌握常用單行函數(shù)的運用方法；了解數(shù)據(jù)類型隱式轉(zhuǎn)換的原那么，掌握顯式轉(zhuǎn)換函數(shù)的運用方法；SQL 函數(shù)函數(shù)是SQL的一個強大的特征，運用情形：對數(shù)據(jù)執(zhí)行計算改動特定的數(shù)據(jù)項生成數(shù)據(jù)行分組輸出格式化日期和數(shù)值的顯示方式對列數(shù)據(jù)類型進(jìn)展轉(zhuǎn)換SQL函數(shù)可以接納多個參數(shù)但通常只前往一個值DUAL表DUAL表屬于SYS用戶并且可以被一切的用戶運用，它包含一個列DUMMY，并且只需一行值是未知的數(shù)據(jù)。當(dāng)您只想一次得到一個值，DUAL表是非常有用的，例如，不是來自用戶數(shù)據(jù)的常量值、虛擬列或表達(dá)式。DUAL表通常用在一個語法完好的SELECT子句中，由于S

2、ELECT和FROM是必需的，并且個別的計算沒必要從實踐的表中選取。SQL 函數(shù)函數(shù)輸入?yún)?shù) 1參數(shù) 2參數(shù)n函數(shù)執(zhí)行作用輸出結(jié)果值兩種類型的 SQL 函數(shù)函數(shù)單行函數(shù)多行函數(shù)兩種類型的 SQL 函數(shù)單行函數(shù)這類函數(shù)只對單數(shù)據(jù)行進(jìn)展操作，并且每行都前往一個結(jié)果，單行函數(shù)的不同類型：字符型數(shù)值型日期型轉(zhuǎn)換函數(shù)多行函數(shù)這類函數(shù)對多數(shù)據(jù)行的群組進(jìn)展操作，并且每組前往一個結(jié)果。單行函數(shù)操作數(shù)據(jù)項接納參數(shù)并前往一個結(jié)果對每個前往的行起作用每行前往一個結(jié)果可以改動數(shù)據(jù)類型能被嵌套function_name (column|expression, arg1, arg2,.)單行函數(shù)轉(zhuǎn)換字符數(shù)值日期通用單行函

3、數(shù)字符函數(shù)字符函數(shù)LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPADTRIM大小寫轉(zhuǎn)換字符處置函數(shù)結(jié)果大小寫轉(zhuǎn)換函數(shù)轉(zhuǎn)化字符串的大小寫LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course運用大小寫轉(zhuǎn)換函數(shù)顯示Blake的雇員編號、姓名和部門編號。SQL SELECTempno, ename, deptno 2 FROMemp 3 WHEREename = blake;no rows selected EMPNO ENAME DEPTNO- -

4、- 7698 BLAKE 30SQL SELECTempno, ename, deptno 2 FROMemp 3 WHERE ename = UPPER(blake);表 字符函數(shù)CONCAT(Good, String)SUBSTR(String,1,3)LENGTH(String)INSTR(String, r)INSTR(CORPORATE FLOOR DOOR, OR,1,2)LPAD(sal,10,*)TRIM(S FROM SSMITH)REPLACE(ABC, B, D)GoodStringStr635*5000MITHADC函數(shù)結(jié)果字符處置函數(shù)字符處置函數(shù)運用字符處置函數(shù)SQL

5、 SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, A) 3 FROM emp 4 WHERESUBSTR(job,1,5) = SALES;ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,A)- - - -MARTIN MARTINSALESMAN 6 2ALLEN ALLENSALESMAN 5 1TURNER TURNERSALESMAN 6 0WARD WARDSALESMAN 4 2如何利用CHR函數(shù)在字符串里加回車？Sqlselect Welcome

6、to visit|chr(10)| as welcome from dual; WELCOME-Welcome to 運用ceil函數(shù)：SELECT ceil(2.35) FROM dual;執(zhí)行結(jié)果：CEIL(2.35)- 3闡明：該函數(shù)求得大于等于2.35的最小整數(shù)，結(jié)果為3。數(shù)值型函數(shù)數(shù)值型函數(shù)運用floor函數(shù)：SELECT floor(2.35) FROM dual;執(zhí)行結(jié)果：FLOOR(2.35)- 2闡明：該函數(shù)求得小于等于2.35的最大整數(shù)，結(jié)果為2。數(shù)值型函數(shù)運用cos函數(shù)：SELECT cos(3.14159) FROM dual

7、;執(zhí)行結(jié)果：COS(3.14159)- -1闡明：cos函數(shù)的輸入?yún)?shù)應(yīng)為弧度，3.14159的cos值為-1。數(shù)值型函數(shù)求|sin(230o)|的值，保管一位小數(shù): SELECT round(abs(sin(230*3.14159/180),1) FROM dual;結(jié)果為： ROUND(ABS(SIN(230*3.14159/180),1)- .8闡明：先將230o轉(zhuǎn)換成為弧度，然后進(jìn)展計算求值。再求絕對值。再進(jìn)展四舍五入，保管小數(shù)點后1位。 數(shù)值函數(shù)ROUND: 四舍五入到指定的小數(shù)位ROUND(45.926, 2)45.93TRUNC: 截取到指定的小數(shù)位TRUNC(45.926, 2

8、)45.92MOD: 取余數(shù)MOD(1600, 300)100運用 ROUND 函數(shù)SQL SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL;ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)- - - 45.92 46 50SQL SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,

9、-1)- - - 45.92 45 40運用 TRUNC函數(shù)運用 MOD 函數(shù)計算任務(wù)為salesman的雇員的薪水和傭金相除后的剩余。SQL SELECTename, sal, comm, MOD(sal, comm) 2 FROMemp 3 WHEREjob = SALESMAN;ENAME SAL COMM MOD(SAL,COMM)- - - -MARTIN 1250 1400 1250ALLEN 1600 300 100TURNER 1500 0 1500WARD 1250 500 250運用 SIGN 函數(shù)SIGN函數(shù)的參數(shù)假設(shè)是負(fù)數(shù)，那么前往值 是-1，假設(shè)參數(shù)是正數(shù)，那么前往值

10、是1；假設(shè)參數(shù)是0，那么前往值是0。SIGN(-50)= -1SIGN(+43)= 1SIGN(0)= 0日期的處置Oracle是以一種內(nèi)部的數(shù)值方式存儲日期的，即：世紀(jì)、年、月、日、小時、分、秒。默許的日期方式是： DD-MON-RR.有效的日期范圍是公元前4712年1月1日到公元9999年12月31日SYSDATE 是一個可以前往日期和時間的函數(shù)DUAL是一個虛擬表，可用于查看SYSDATE等表達(dá)式.例：select sysdate from dual;日期的運算對一個日期型數(shù)據(jù)加上或減去一個數(shù)可以得到一個新的日期型數(shù)據(jù)。兩個日期型數(shù)據(jù)相減得到這兩個日期的間隔數(shù)。如要以小時相加那么必需用小

11、時數(shù)除以24得到的數(shù)據(jù)進(jìn)展相加。對日期運用算術(shù)運算符SQL SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;ENAME WEEKS- -KING 830.93709CLARK 853.93709MILLER 821.36566受雇了多少周日期函數(shù)日期函數(shù)兩個日期之間間隔多少個月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向一個日期數(shù)據(jù)加一定的月份前往指定日期的下一個事件日前往某月的最后一天對日期進(jìn)展四舍五入 對日期進(jìn)展截取函數(shù)描畫EXTRAC

12、T(YEAR|MONTH|DAY FROM date) 從日期值中得出年、月或日NEW_TIME(date,existing timezone,newtimezone)前往不同時區(qū)(例如EST或PST)之間的日期和時間MONTHS_BETWEEN (01-9月-95,11-1月-94)運用日期函數(shù)ADD_MONTHS (11-1月-08,6)NEXT_DAY (01-4月-08,星期一) LAST_DAY(01-9月-95)19.677419411-7月 -0807-4月 -0830-9月-95ROUND(to_date(25-6月-95),MONTH) 01-7月-95ROUND(to_da

13、te(25-7月-95),YEAR) 01-1月-96TRUNC(to_date(25-6月-95),MONTH) 01-6月-95 TRUNC(to_date(25-7月-95),YEAR) 01-1月-95運用日期函數(shù)運用日期函數(shù)SQL SELECT EXTRACT(MONTH FROM sysdate)Month, 2 EXTRACT(DAY FROM sysdate)Day, 3 EXTRACT(YEAR FROM sysdate)Year 4 FROM DUAL; MONTH DAY YEAR- - - 3 16 2021Oracle 與日期相關(guān)的其他幾個函數(shù)CURRENT_DATE

14、 前往會話時區(qū)的當(dāng)前日期；CURRENT_TIMESTAMP前往會話時區(qū)的當(dāng)前日期和時間；DBTIMEZONE 前往數(shù)據(jù)庫的時區(qū)的值；SESSIONTIMEZONE 前往當(dāng)前會話的時區(qū)；SYSTIMESTAMP 前往數(shù)據(jù)庫所在時區(qū)的日期和時間；轉(zhuǎn)換函數(shù)隱式數(shù)據(jù)類型轉(zhuǎn)換顯式數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換隱式轉(zhuǎn)換賦值語句中，Oracle效力器自動完成以下轉(zhuǎn)換：VARCHAR2 or CHAR從到VARCHAR2 or CHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2【實例1】 自動轉(zhuǎn)換字符型數(shù)據(jù)到數(shù)值型。輸入并執(zhí)行查詢：SELECT 12.5+11 FROM dual;執(zhí)

15、行結(jié)果為：12.5+11- 23.5闡明：在本實例中，由于出現(xiàn)+運算符，闡明進(jìn)展的是算術(shù)運算，所以字符串12.5被自動轉(zhuǎn)換成數(shù)值12.5，然后參與運算。隱式轉(zhuǎn)換隱式轉(zhuǎn)換【實例2】 自動轉(zhuǎn)換數(shù)值型數(shù)據(jù)到字符型。執(zhí)行以下查詢：SELECT 12.5|11 FROM dual;結(jié)果為：12.5-12.511闡明：在本訓(xùn)練中，由于出現(xiàn)|運算符，闡明進(jìn)展的是字符串銜接運算，數(shù)值11被自動轉(zhuǎn)換成字符串11，然后參與運算。顯式轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR 用于日期型日期格式模型：必需用單引號引起來并且是大小寫敏感的可以包

16、含任何有效的日期元素可以用fm來消除前導(dǎo)空格或零運用逗號將日期型數(shù)據(jù)與日期格式模型分隔開TO_CHAR(date, fmt)Oracle顯示日期的格式是DD-MON-RR方式Oracle版本默許安裝的日期格式是DD-MON-RRRR 日期格式當(dāng)前年份1995199520012001指定的日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式1995202120211995YY 格式1995191720212095假設(shè)當(dāng)前年份的兩位數(shù)是：04904950995099前往的日期是當(dāng)前世紀(jì)的日期前往的日期是下一個世紀(jì)的日期前往的日期是上一個世紀(jì)的日期前往的日期是當(dāng)前世

17、紀(jì)的日期假設(shè)指定兩位數(shù)年份是：YYYY日期格式模型的元素YEARMMMONTHDYDAY完好的年份數(shù)字表示: 2021年份的英文表示: Two Thousand Eight用兩位數(shù)字來表示月份: 04用3個中文字符來表示星期幾: 星期二星期幾的中文表示: 星期二月份的中文表示: 4月DDTH日期序數(shù)，例如第7 : 7thfm與其他格式符號(例如,DAY)一同運用的填充方式，以便消除空白日期格式模型的元素用時間元素格式化日期的時間部分經(jīng)過運用雙引號可以添加字符串運用數(shù)字下標(biāo)可以拼寫數(shù)字號碼HH24:MI:SS AM15:45:32下午DD of MONTH08 of 4月ddspthfourte

18、enthTO_CHAR 用于日期型SQL SELECTename, 2 TO_CHAR(hiredate, fmDD Month YYYY) HIREDATE 3 FROM emp;ENAME HIREDATE- -SMITH 17 12月 1980ALLEN 20 2月 1981WARD 22 2月 1981JONES 2 4月 1981MARTIN 28 9月 1981BLAKE 1 5月 1981.已選擇16行。TO_CHAR 用于日期型系統(tǒng)日期與時間-2021-03-15 11:49:34 上午 星期日SQL SELECTTO_CHAR(sysdate,YYYY-MM-DD HH24:

19、MI:SS AM DY) AS 系統(tǒng)日期與時間 FROM dual;TO_CHAR 用于日期型【實例】 將日期顯示轉(zhuǎn)換成中文的年月日。輸入并執(zhí)行查詢：SELECT TO_CHAR(sysdate,YYYY年MM月DD日) FROM dual;執(zhí)行結(jié)果為：TO_CHAR(SYSDAT - 2021年04月08日闡明：雙引號中的中文字“年、“月、“日原樣顯示，單引號為字符串的界定標(biāo)志，區(qū)別于雙引號，不能混淆。TO_CHAR 用于日期型【實例】 時間顯示的大小寫。步驟1：執(zhí)行以下查詢： SELECT SYSDATE,to_char(SYSDATE,yyyysp) FROM dual;結(jié)果為： SYS

20、DATE TO_CHAR(SYSDATE,YYYYSP) - - 08-4月 -08 two thousand eight步驟2：執(zhí)行以下查詢： SELECT to_char(SYSDATE,Yyyysp) FROM dual;結(jié)果為：TO_CHAR(SYSDATE,YYYYSP) - Two Thousand EightTO_CHAR 用于日期型 步驟3：執(zhí)行以下查詢： SELECT SYSDATE,to_char(SYSDATE,YYyysp) FROM dual; 結(jié)果為： SYSDATE TO_CHAR(SYSDATE,YYYYSP) - - 08-4月 -08 TWO THOUSAN

21、D EIGHT闡明：步驟1輸出全拼小寫的年度，步驟2輸出全拼的以大寫開頭的年度，步驟3輸出全拼大寫的年度?！揪毩?xí)1】顯示2021年的4月9日為星期幾。select 09-4月-08, to_char(to_date(09-4月-08, DD-MON-YY),DAY) 星期幾 from dual;TO_CHAR 用于數(shù)值型經(jīng)過在TO_CHAR中運用以下方式可以把數(shù)值型數(shù)據(jù)轉(zhuǎn)化成變長的字符串：TO_CHAR(number, fmt)90$L.,一位數(shù)字顯示前導(dǎo)零顯示美圓符號顯示本地貨幣符號顯示小數(shù)點顯示千位符EEEE科學(xué)計數(shù)法TO_CHAR 用于數(shù)值型SQL SELECTTO_CHAR(sal,$

22、99,999) SALARY 2 FROMemp 3 WHEREename = SCOTT;SALARY- $3,000【實例】 將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECT TO_CHAR(123.45,0000.00), TO_CHAR(12345,L9.9EEEE) FROM dual;結(jié)果為：TO_CHAR( TO_CHAR(12345,L9.9- - 0123.45 RMB1.2E+04闡明：格式字符串中“0表示一位數(shù)字，轉(zhuǎn)換結(jié)果中相應(yīng)的位置上沒有數(shù)字那么添加0?！?表示在相應(yīng)的位置上顯示小數(shù)點?！癓將以本地貨幣符號顯示于數(shù)字前，在本例中本地貨幣符號為“RMB。“EE

23、EE將顯示轉(zhuǎn)換為科學(xué)計數(shù)法。TO_CHAR 用于數(shù)值型TO_NUMBER 和 TO_DATE 函數(shù) 運用TO_NUMBER將一個字符串轉(zhuǎn)換成數(shù)值型數(shù)據(jù)。TO_NUMBER(char, fmt)運用TO_DATE將一個字符串轉(zhuǎn)換成日期型數(shù)據(jù)。TO_DATE(char, fmt)NVL 函數(shù)將NULL轉(zhuǎn)換為一個實踐的值數(shù)據(jù)類型可以是date、 character、 number。數(shù)據(jù)類型必需匹配 NVL(comm,0) 用0替代空的Comm值。NVL(hiredate,01-1月-97) 用1997年1月1日替代空 的雇傭日期。NVL(job,No Job Yet) 用“No Job Yet替代空

24、的 職務(wù)。SQL SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;運用 NVL 函數(shù)ENAME SAL COMM (SAL*12)+NVL(COMM,0)- - - -KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700MARTIN 1250 1400 16400ALLEN 1600 300 19500.14 rows selected.運用 NVL2 函數(shù)SELECT ename, NVL2(comm,to_char(comm), 沒有傭金) 傭金FRO

25、M EMP WHERE deptno=30;NVL2函數(shù)是對NVL函數(shù)的擴(kuò)展。其參數(shù)是三個。假設(shè)列是非空值，那么顯示第二個參數(shù)，假設(shè)列是空值，那么顯示第三個參數(shù)。ENAME 傭金- -ALLEN 300WARD 500MARTIN 1400BLAKE 沒有傭金TURNER 0JAMES 沒有傭金已選擇6行。運用 NULLIF 函數(shù)SELECT e.ename, NULLIF(e.job, job) “New JobFROM emp e, job_history jWHERE e.empno = j.empnoORDER BY ename;NULLIF(exp1, exp2)邏輯上等價于如下的C

26、ASE語句：CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END運用 COALESCE 函數(shù)COALESCE(column,alternative1,alternative2,);COALESCE函數(shù)也是對NVL函數(shù)的擴(kuò)展。 NVL函數(shù)只對空值指定了一個替代值，而COALESCE函數(shù)卻提供了多個替代值。SELECT ename,COALESCE(comm,sal,-1)FROM EMPWHERE deptno=30;ENAME 傭金- -ALLEN 300WARD 500MARTIN 1400BLAKE 2850TURNER -1JAMES 950

27、已選擇6行。DECODE 函數(shù)運用DECODE函數(shù)更便于完成諸如CASE 或 IF-THEN-ELSE 語句的條件查詢DECODE(column/expression, value1, action1 , value2, action2,., , default)運用 DECODE 函數(shù)SQL SELECT ename, sal, 2 DECODE(TRUNC(sal/1000, 0), 3 0, 0.00, 4 1, 0.09, 5 2, 0.20, 6 3, 0.30, 7 4, 0.40, 8 5, 0.42, 9 6, 0.44, 10 0.45) TAX_RATE 11 FROM e

28、mp 12 WHERE deptno = 30;顯示部門編號為30的每個雇員相順應(yīng)的稅率。運用 DECODE 函數(shù)SQL SELECT job, sal, 2 DECODE(job, ANALYST, SAL*1.1, 3 CLERK, SAL*1.15, 4 MANAGER, SAL*1.20, 5 SAL) 6 REVISED_SALARY 7 FROM emp;JOB SAL REVISED_SALARY- - -CLERK 950 1092.5ANALYST 3000 3300CLERK 1300 1495.已選擇16行。顯示各雇員的崗位、工資及其預(yù)增工資，其中ANALYST增10%，CLERK增15%和MANAGER增20%，其他不變 運用 DECODE 函數(shù)ENAME SAL TAX_RATE- - -ALLEN 1600 .09WARD 1250 .09MARTIN 1250 .09BLAKE 2850 .2TURNER 1500 .09JAMES 950 0已選擇6行。運用 userenv函數(shù) 函數(shù)userenv前往用戶環(huán)境信息字符串，該函數(shù)只需一個字符串類型的參數(shù)，參數(shù)的內(nèi)容為如下之一的字符串，可以不區(qū)分大小寫：* ISDBA：判別會話用戶的角色能否為SYSDBA，是那么前往TRUE。* INSTANCE：前往會話銜接的INST