OraleDB使用單行函數(shù)定制輸出

1、描述SQL提供的各類(lèi)函數(shù)在SELECT語(yǔ)句中使用字符、數(shù)字和日期函數(shù)SQL函數(shù)函數(shù)是SQL的一項(xiàng)非常強(qiáng)大的功能，可用于執(zhí)行以下操作：執(zhí)行數(shù)據(jù)計(jì)算修改單個(gè)數(shù)據(jù)項(xiàng)處理成組行的輸出設(shè)置日期和數(shù)字的顯示格式轉(zhuǎn)換列數(shù)據(jù)類(lèi)型SQL函數(shù)有時(shí)接受多個(gè)參數(shù)，但始終返回一個(gè)值。兩種類(lèi)型的SQL函數(shù)有兩種類(lèi)型的函數(shù)：?jiǎn)涡泻瘮?shù)多行函數(shù)單行函數(shù)這些函數(shù)僅對(duì)單行進(jìn)行處理，為每行返回一個(gè)結(jié)果。單行函數(shù)具有多種不同類(lèi)型。介紹以下幾種函數(shù)：字符數(shù)字日期轉(zhuǎn)換常規(guī)多行函數(shù)這些函數(shù)可以處理成組的行，為每組行返回一個(gè)結(jié)果。單行函數(shù)單行函數(shù)：1/17處理數(shù)據(jù)項(xiàng)接受參數(shù)并返回一個(gè)值對(duì)每個(gè)返回行進(jìn)行處理為每行返回一個(gè)結(jié)果可能會(huì)修改數(shù)據(jù)類(lèi)型可

2、以嵌套接受參數(shù)，這些參數(shù)可以是列或表達(dá)式function_name(arg1,arg2,.)在該語(yǔ)法中：function_name：是函數(shù)的名稱(chēng)arg1,arg2：是函數(shù)使用的任意參數(shù)，可以是列名稱(chēng)或表達(dá)式。單行函數(shù)用于處理數(shù)據(jù)項(xiàng)。其接受一個(gè)或多個(gè)參數(shù)，并對(duì)查詢(xún)返回的每一行返回一個(gè)值。參數(shù)可以是下列對(duì)象之一：用戶(hù)提供的常量變量值列名表達(dá)式單行函數(shù)的特點(diǎn)包括：對(duì)查詢(xún)中返回的每一行進(jìn)行處理為每行返回一個(gè)結(jié)果可能會(huì)返回一個(gè)與所引用類(lèi)型不同的數(shù)據(jù)值可能需要一個(gè)或多個(gè)參數(shù)可用于SELECT、WHERE和ORDERBY子句中；也可以嵌套。將介紹以下單行函數(shù)：字符函數(shù)：接受字符輸入，可以返回字符值和數(shù)字值數(shù)

3、字函數(shù)：接受數(shù)字輸入，可以返回?cái)?shù)字值日期函數(shù)：對(duì)DATE數(shù)據(jù)類(lèi)型的值進(jìn)行處理（所有日期函數(shù)都會(huì)返回一個(gè)DATE數(shù)據(jù)類(lèi)型的值，只有MONTHS_BETWEEN函數(shù)返回一個(gè)數(shù)字。）轉(zhuǎn)換函數(shù)：將值從一種數(shù)據(jù)類(lèi)型轉(zhuǎn)換為另一種數(shù)據(jù)類(lèi)型常規(guī)函數(shù)：-NVL2/17-NVL2-NULLIF-COALESCE-CASE-DECODE字符函數(shù)單行字符函數(shù)接受的輸入是字符數(shù)據(jù)，可以返回字符值和數(shù)字值。字符函數(shù)可以分為以下幾類(lèi)：大小寫(xiě)轉(zhuǎn)換函數(shù)字符處理函數(shù)3/17注：以下是一些完全或部分符合SQL:2003的函數(shù)：UPPERLOWERTRIMLENGTHSUBSTRINSTR大小寫(xiě)轉(zhuǎn)換函數(shù)以下函數(shù)用于轉(zhuǎn)換字符串的大小寫(xiě)

4、：LOWER、UPPER和INITCAP是三個(gè)大小寫(xiě)轉(zhuǎn)換函數(shù)。LOWER：將大小寫(xiě)混合或大寫(xiě)的字符串轉(zhuǎn)換為小寫(xiě)UPPER：將大小寫(xiě)混合或小寫(xiě)的字符串轉(zhuǎn)換為大寫(xiě)INITCAP：將每個(gè)單詞的首字母轉(zhuǎn)換為大寫(xiě)，其余字母保留為小寫(xiě)4/17hrTEST0924SELECTThejobidfor|UPPER(last_name)|is|LOWER(job_id)ASEMPLOYEEDETAILSFROMemployees;EMPLOYEEDETAILS-ThejobidforABELissa_repThejobidforANDEissa_repThejobidforATKINSONisst_clerk使用

5、大小寫(xiě)轉(zhuǎn)換函數(shù)使用以下語(yǔ)句可顯示雇員Higgins的雇員編號(hào)、姓名和部門(mén)編號(hào)：hrTEST0924SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name=higgins;norowsselected第一個(gè)SQL語(yǔ)句的WHERE子句將雇員姓名指定為higgins。因?yàn)镋MPLOYEES表中的所有數(shù)據(jù)都是以正常大小寫(xiě)形式存儲(chǔ)的，所以姓名higgins在表中沒(méi)有找到匹配項(xiàng)，因此不會(huì)選擇任何行。hrTEST0924SELECTemployee_id,last_name,department_idFROMemployees

6、WHERELOWER(last_name)=higgins;EMPLOYEE_IDLAST_NAMEDEPARTMENT_ID-205Higgins110第二個(gè)SQL語(yǔ)句中的WHERE子句指定將EMPLOYEES表中的雇員姓名與higgins進(jìn)行比較，但是先將LAST_NAME列轉(zhuǎn)換為小寫(xiě)，然后再進(jìn)行比較。因?yàn)閮蓚€(gè)姓名都是小寫(xiě)的，因此找到了匹配項(xiàng)，從而選擇了一行。可以按以下方式重寫(xiě)WHERE子句，產(chǎn)生的結(jié)果相同：.WHERElast_name=Higgins輸出中姓名的顯示格式與其存儲(chǔ)在數(shù)據(jù)庫(kù)中的一樣。要以大寫(xiě)形式顯示姓名，可以在SELECT語(yǔ)句中使用UPPER函數(shù)。5/17hrTEST092

7、4SELECTemployee_id,UPPER(last_name),department_idFROMemployeesWHEREINITCAP(last_name)=Higgins;EMPLOYEE_IDUPPER(LAST_NAME)DEPARTMENT_ID-205HIGGINS110字符處理函數(shù)下面的函數(shù)用于處理字符串：CONCAT、SUBSTR、LENGTH、INSTR、LPAD、RPAD和TRIM是要介紹的字符處理函數(shù)。CONCAT：將值聯(lián)接在一起（CONCAT函數(shù)中只能使用兩個(gè)參數(shù)）SUBSTR：提取確定長(zhǎng)度的字符串LENGTH：以數(shù)字值的形式顯示字符串的長(zhǎng)度INSTR：查找

8、指定字符串的數(shù)字位置LPAD：返回一個(gè)表達(dá)式，左邊使用一個(gè)字符表達(dá)式填充到n個(gè)字符的長(zhǎng)度RPAD：返回一個(gè)表達(dá)式，右邊使用一個(gè)字符表達(dá)式填充到n個(gè)字符的長(zhǎng)度TRIM：截去字符串首字符或尾字符（或者兩者都截去）如果trim_character或trim_source是一個(gè)字符文字，則必須將其放在單引號(hào)內(nèi)）注：可以將UPPER和LOWER等函數(shù)與&替代變量組合使用。例如，如果使用UPPER(&job_title)，用戶(hù)就不必以特定的大小寫(xiě)形式輸入職務(wù)了。6/17instr(string1,string2,start_position,nth_appearance)string1string2源字符

9、串，要在此字符串中查找。要在string1中查找的字符串。start_position代表string1的哪個(gè)位置開(kāi)始查找。此參數(shù)可選，如果省略默認(rèn)為1.字符串索引從1開(kāi)始。如果此參數(shù)為正，從左到右開(kāi)始檢索，如果此參數(shù)為負(fù)，從右到左檢索，返回要查找的字符串在源字符串中的開(kāi)始索引。nth_appearance代表要查找第幾次出現(xiàn)的string2.此參數(shù)可選，如果省略，默認(rèn)為1.如果為負(fù)數(shù)系統(tǒng)會(huì)報(bào)錯(cuò)。使用字符處理函數(shù)hrTEST0924l1SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INST

10、R(last_name,a)Containsa?2*FROMemployeesWHERESUBSTR(job_id,4)=REPhrTEST0924/EMPLOYEE_IDNAMEJOB_IDLENGTH(LAST_NAME)Containsa?-202PatFayMK_REP32203SusanMavrisHR_REP62204HermannBaerPR_REP42示例中顯示從其職務(wù)ID的第四個(gè)位置開(kāi)始包含字符串REP的所有雇員的以下信息：聯(lián)接在一起的名字和姓氏、雇員姓氏的長(zhǎng)度，以及字母a在雇員姓氏中的數(shù)字位置。示例：”修改幻燈片中的SQL語(yǔ)句，以顯示姓氏以字母“n為結(jié)尾的雇員的數(shù)據(jù)。hrT

11、EST0924SELECTemployee_id,CONCAT(first_name,last_name)NAME,LENGTH(last_name),INSTR(last_name,a)Containsa?7/172FROMemployeesWHERESUBSTR(last_name,-1,1)=n;EMPLOYEE_IDNAMELENGTH(LAST_NAME)Containsa?-102LexDeHaan75105DavidAustin60數(shù)字函數(shù)ROUND：將值舍入到指定的小數(shù)位TRUNC：將值截?cái)嗟街付ǖ男?shù)位MOD：返回除法運(yùn)算的余數(shù)數(shù)字函數(shù)接受數(shù)字形式的輸入并返回?cái)?shù)字值。使用RO

12、UND函數(shù)hrTEST0924SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)-45.9246508/17ROUND函數(shù)用于將列、表達(dá)式或值舍入到n位小數(shù)位。如果第二個(gè)參數(shù)為0或者缺失，則會(huì)將值舍入為整數(shù)。如果第二個(gè)參數(shù)為2，則會(huì)將值舍入到2位小數(shù)位。相反，如果第二個(gè)參數(shù)為-2，則會(huì)將值舍入到小數(shù)點(diǎn)左邊2位（即舍入到最近的百位）。ROUND函數(shù)還可與日期函數(shù)一起使用。在本課稍后您將看到相應(yīng)的示例。DUAL表DUAL表屬于SYS

13、用戶(hù)，但所有用戶(hù)都可以訪問(wèn)它。它包含一個(gè)DUMMY列和一個(gè)值為X的行。如果某個(gè)值只需返回一次，例如，不是從含有用戶(hù)數(shù)據(jù)的表中導(dǎo)出的常量值、偽列值或表達(dá)式值，則DUAL表非常有用。DUAL表通常用于保持SELECT子句語(yǔ)法的完整性，因?yàn)镾ELECT和FROM子句都是必需的，而有些計(jì)算并不需要從實(shí)際表中進(jìn)行選擇。使用TRUNC函數(shù)hrTEST0924SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)FROMDUAL;TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)-45.924540TRUNC函數(shù)用于將列

14、、表達(dá)式或值截?cái)嗟絥位小數(shù)位。TRUNC函數(shù)使用參數(shù)的方式與ROUND函數(shù)非常類(lèi)似。如果第二個(gè)參數(shù)為0或者缺失，則會(huì)將值截?cái)酁檎麛?shù)。如果第二個(gè)參數(shù)為2，則會(huì)將值截?cái)嗟?位小數(shù)位。相反，如果第二個(gè)參數(shù)為-2，則會(huì)將值截?cái)嗟叫?shù)點(diǎn)左邊2位。如果第二個(gè)參數(shù)為-1，則將值截?cái)嗟叫?shù)點(diǎn)左邊1位。與ROUND函數(shù)一樣，TRUNC函數(shù)也可與日期函數(shù)一起使用。-Oracletrunc()函數(shù)的用法/*日期*/1.selecttrunc(sysdate)fromdual-2011-3-18今天的日期為2011-3-182.selecttrunc(sysdate,mm)fromdual-2011-3-1返回當(dāng)月第

15、一天.3.selecttrunc(sysdate,yy)fromdual-2011-1-1返回當(dāng)年第一天4.selecttrunc(sysdate,dd)fromdual-2011-3-18返回當(dāng)前年月日5.selecttrunc(sysdate,yyyy)fromdual-2011-1-1返回當(dāng)年第一天9/176.selecttrunc(sysdate,d)fromdual-2011-3-13(星期天)返回當(dāng)前星期的第一天7.selecttrunc(sysdate,hh)fromdual-2011-3-1814:00:00當(dāng)前時(shí)間為14:418.selecttrunc(sysdate,mi)

16、fromdual-2011-3-1814:41:00TRUNC()函數(shù)沒(méi)有秒的精確/*數(shù)字*/*TRUNC（number,num_digits）Number需要截尾取整的數(shù)字。Num_digits用于指定取整精度的數(shù)字。Num_digits的默認(rèn)值為0。TRUNC()函數(shù)截取時(shí)不進(jìn)行四舍五入*/9.selecttrunc(123.458)fromdual-12310.selecttrunc(123.458,0)fromdual-12311.selecttrunc(123.458,1)fromdual-123.412.selecttrunc(123.458,-1)fromdual-12013.s

17、electtrunc(123.458,-4)fromdual-014.selecttrunc(123.458,4)fromdual-123.45815.selecttrunc(123)fromdual-12316.selecttrunc(123,1)fromdual-12317.selecttrunc(123,-1)fromdual-120使用MOD函數(shù)針對(duì)職務(wù)為銷(xiāo)售代表的所有雇員，計(jì)算薪金除以5,000后的余數(shù)。hrTEST0924SELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id=SA_REP;LAST_NAMES

18、ALARYMOD(SALARY,5000)-Tucker100000Bernstein95004500Hall90004000MOD函數(shù)將返回第一個(gè)參數(shù)除以第二個(gè)參數(shù)之后的余數(shù)。在示例中，針對(duì)職務(wù)ID為SA_REP的所有雇員計(jì)算薪金除以5,000后的余數(shù)。注：MOD函數(shù)經(jīng)常用于確定一個(gè)值是奇數(shù)還是偶數(shù)。MOD函數(shù)也是Oracle散列函數(shù)。10/17處理日期OracleDB以?xún)?nèi)部數(shù)字格式存儲(chǔ)日期：世紀(jì)、年、月、日、小時(shí)、分鐘和秒。默認(rèn)的日期顯示格式為DD-MON-RR。通過(guò)僅指定年份的后兩位，可以在20世紀(jì)存儲(chǔ)21世紀(jì)的日期同樣，也可以在21世紀(jì)存儲(chǔ)20世紀(jì)的日期hrTEST0924SELECT

19、last_name,hire_dateFROMemployeesWHEREhire_dateSELECTsysdateFROMdual;SYSDATE-17-OCT-13SYSDATE是一個(gè)日期函數(shù)，它返回當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器的日期和時(shí)間?？梢韵袷褂萌魏纹渌忻Q(chēng)一樣使用SYSDATE。例如，通過(guò)從表中選擇SYSDATE，可以顯示當(dāng)前日期。通常從稱(chēng)為DUAL的公用表中選擇SYSDATE。注：SYSDATE將返回為數(shù)據(jù)庫(kù)所在操作系統(tǒng)設(shè)置的當(dāng)前日期和時(shí)間。因此，如果本人位于澳大利亞，但連接到位于美國(guó)(U.S.)某地的遠(yuǎn)程數(shù)據(jù)庫(kù)，則sysdate函數(shù)將返回美國(guó)的日期和時(shí)間。在這種情況下，可以使用CURR

20、ENT_DATE函數(shù)返回會(huì)話(huà)時(shí)區(qū)的當(dāng)前日期。與日期有關(guān)的運(yùn)算對(duì)日期加上或減去一個(gè)數(shù)字可以獲得一個(gè)新的日期值。將兩個(gè)日期相減可以得出它們之間的天數(shù)。將小時(shí)數(shù)除以24可以將小時(shí)添加到日期中。因?yàn)閿?shù)據(jù)庫(kù)將日期作為數(shù)字進(jìn)行存儲(chǔ)，因此可以使用算術(shù)運(yùn)算符（如加和減）執(zhí)行計(jì)算。可以加減數(shù)字常量和日期。可以執(zhí)行下列操作：13/17使用算術(shù)運(yùn)算符處理日期rTEST0924SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;LAST_NAMEWEEKS-King539.384281Kochhar421.241

21、424DeHaan665.812852示例中顯示部門(mén)90中所有雇員的姓氏和聘用周數(shù)。它用當(dāng)前日期(SYSDATE)減去聘用雇員的日期，然后用所得的結(jié)果除以7計(jì)算出雇員的聘用周數(shù)。注：SYSDATE是一個(gè)SQL函數(shù)，它用于返回當(dāng)前日期和時(shí)間。在運(yùn)行SQL查詢(xún)時(shí)，返回的結(jié)果將隨為本地?cái)?shù)據(jù)庫(kù)所在操作系統(tǒng)設(shè)置的日期和時(shí)間而變化。如果從較早的日期中減去較晚的日期，差值將是一個(gè)負(fù)數(shù)。日期處理函數(shù)日期函數(shù)用于處理Oracle日期。所有日期函數(shù)都返回一個(gè)DATE數(shù)據(jù)類(lèi)型的值，只有MONTHS_BETWEEN函數(shù)返回一個(gè)數(shù)字值。MONTHS_BETWEEN(date1,date2)：計(jì)算date1和date2之

22、間的月數(shù)。結(jié)果可以是正數(shù)，也可以是負(fù)數(shù)。如果date1晚于date2，則結(jié)果為正數(shù)；如果date1早于date2，則結(jié)果為負(fù)數(shù)。結(jié)果中的非整數(shù)部分代表月份的一部分。ADD_MONTHS(date,n)：將n個(gè)日歷月添加到date。n的值必須為整數(shù)，但可以為負(fù)數(shù)。NEXT_DAY(date,char)：計(jì)算date之后一周內(nèi)下一個(gè)指定日(char)的日期。char的值可以是代表某一天的一個(gè)數(shù)字或者是一個(gè)字符串。14/17LAST_DAY(date)：計(jì)算包含date的月份的最后一天的日期。此列表只列出了部分可用日期函數(shù)。ROUND和TRUNC數(shù)字函數(shù)也可用來(lái)處理日期值，如下所示：ROUND(date,fmt)：返回舍入到由格式樣式fmt指定的單位的date。如果省略格式樣式fmt，則date將舍入到最近的一天。TRUNC(date,fmt)：返回包含時(shí)間部分的日期date，該日期已截?cái)嗟接筛袷綐邮絝mt指定的單位。如果省略格式樣式fmt，則date將截?cái)嗟阶罱囊惶臁Ｊ褂萌掌诤瘮?shù)在示例中，ADD_MONTHS函數(shù)在提供的日期值“31-JAN-96”上添加了一個(gè)月，因而返回“29-FEB-96”。該函數(shù)將1996年識(shí)別為閏年，因此返回二月份的最后一天。如果將輸入日期值更改為“31-JAN-95