ORACLE中函數(shù)參考模板

1、Oracle認(rèn)證專員（OCA）2.3.7 ORACLE中函數(shù)：    ORACLE已經(jīng)內(nèi)建了許多函數(shù)，不同的函數(shù)有不同的作用和用法，有的函數(shù)只能作用在一個(gè)記錄行上，有的能夠作用在多個(gè)記錄行上，不同的函數(shù)可能處理不同的數(shù)據(jù)類型。常見的有兩類，單行函數(shù)和分組函數(shù)。單行函數(shù)：?jiǎn)涡泻瘮?shù)分類函數(shù)功能示例字符函數(shù)LPAD(<c1>,<i>,<c2>)在字符串c1的左邊添加字符串c2直到c1字符串的長(zhǎng)度等于i。SELECT  LPAD(Hello!,8, ) leftpad,RPAD(Hello!,8, ) rightpadFROM

2、 DUAL;RPAD(<c1>,<i>,<c2>)在字符串c1的右邊添加字符串c2直到c1字符串的長(zhǎng)度等于i。LOWER(<c1>)把字符串c1轉(zhuǎn)換為小寫SELECT LOWER(ename)  one,UPPER(ename) two, INITCAP(ename) FROM EMP;UPPER(<c1>)把字符串c1轉(zhuǎn)換為大寫INITCAP(<c1>)把c1字符串的每一個(gè)單詞的第一個(gè)字母轉(zhuǎn)換成大寫字母LENGTH(<c1>)返回字符串c1的長(zhǎng)度SELECT LENGTH(How are you)

3、FROM DUAL;SUBSTR(<c1>,<i>,<j>)返回字符串c1中從第i個(gè)位置開始的j個(gè)字符（向右）。如果省略j，則返回c1中從第i個(gè)位置開始的所有字符。如果j為負(fù)，則返回字符串c1中從第i個(gè)位置開始的j個(gè)字符（向左）。SELECT SUBSTR(Hello,World,1,5) FROM DUAL;INSTR(<c1>,<c2>,<i>,<j>)在c1中從位置i開始查找c2在c1中出第j次的位置，i可以為負(fù)(此時(shí)，從c1的尾部開始)。SELECT INSTR(Mississippi,i,3,3) F

4、ROM DUAL; 返回結(jié)果11。SELECT INSTR(Mississippi,i,-2,3) FROM DUAL; 返回結(jié)果2。LTRIM(<c1>,<c2>)從c1前面開始去掉出現(xiàn)在c2的中任何前導(dǎo)字符集。SELECT LTRIM(Mississippi,Mis) FROM DUAL; 返回結(jié)果ppi。SELECT RTRIM(Mississippi,ip) FROM DUAL; 返回結(jié)果Mississ。RTRIM(<c1>,<c2>)從c1后面開始去掉出現(xiàn)在c2的中任何前導(dǎo)字符集。數(shù)學(xué)函數(shù)ABS(<n>)返回n的絕對(duì)值SEL

5、ECT ABC(-2),ABS(2) FROM DUAL;1 / 6ROUND(<n1>,<n2>)將n1的小數(shù)點(diǎn)后保留n2位（四舍五入）并返回。如果n2小于零，n1舍入到小數(shù)點(diǎn)左邊。SELECT ROUND(12345.678,-2),ROUND(12345.678,2) FROM  DUAL; 分別返回結(jié)果：12300，12345.68。CEIL(<n>)將n 向上取整，并返回。SELECT CEIL(5.1),CEIL(-21.4) FROM  DUAL;分別返回：6, -21。FLOOR(<n>)將n 向下取

6、整，并返回。SELECT FLOOR(5.1),FLOOR(-21.4) FROM  DUAL;分別返回：5, -22。MOD(<n1>,<n2>)返回n1模n2后的余數(shù)。SELECT MOD(14,5),MOD(8,25),MOD(-64,7) FROM DUAL;分別返回結(jié)果：4，0.5，-1。SIGN(<n>)符號(hào)函數(shù)，n>0，返回1。n<0，返回-1。n=0，返回0。SELECT SIGN(-2.3),SIGN(2.3),SIGN(0) FROM DUAL;SQRT(<n>)返回n的平方根SELECT SQRT(9)

7、 FROM DUAL;TRUNC(<n1>,<n2>)功能類似ROUND函數(shù)。但不做四舍五入。SELECT TRUNC(123.456,2),TRUNC(123.456,-1) FROM DUAL;分別返回結(jié)果：123.45、120。VSIZE(n)返回?cái)?shù)字n的存儲(chǔ)字節(jié)SELECT VSIZE(123) FROM DUAL;日期函數(shù)(日期可以進(jìn)行算術(shù)運(yùn)算SYSDATE返回相同日期SELECT SYSDATE FROM DUAL;)ADD_MONTHS(<d>,<i>)返回日期d 加上i個(gè)月后的新日期(i正可負(fù))。SELECT SYSDATE, A

8、DD_MONTHS(SYSDATE,2),ADD_MONTHS(SYSDATE,-2)FROM DUAL;LAST_DAY(<d>)返回日期d所在的月的最后一天。SELECT SYSDATE,LAST_DAY(SYSDATE) FROM DUALMONTHS_BETWEEN(<d1>,<d2>)返回日期d1比d2大多少月數(shù)。SELECT MONTHS_BETWEEN(19-Dec-1999,19-Mar-2000 FROM DUAL;NEW_TIME(<d>,<tz1>,<tz2>)將時(shí)區(qū)tz1的時(shí)間d，轉(zhuǎn)換為時(shí)區(qū)tz2里

9、的時(shí)間。SELECT SYSDATE,NEW_TIME(SYSDATE,CDT,PDT) FROM DUAL;NEXT_DAY(<d>,<dow>)返回日期d后的第一個(gè)dow。(dow：day of week)SELECT NEXT_DAY(SYSDATE,Monday) FROM DUAL;常用轉(zhuǎn)換函數(shù)TO_CHAR(<x>,<fmt>,<nlsparm>)將x轉(zhuǎn)換成字符串。（參數(shù)含義請(qǐng)看ORACLE的聯(lián)機(jī)幫助）SELECT TO_CHAR(SYSDATE,YYYY-MM-DD) FROM DUAL;TO_NUMBER(<c&

10、gt;,<fmt>,<nlsparm>)將字符串c轉(zhuǎn)換成數(shù)字。（參數(shù)含義請(qǐng)看ORACLE的聯(lián)機(jī)幫助）SELECT TO_NUMBER(123) FROM DUAL;TO_DATE(<c>,<fmt>,<nlsparm>)(常見的日期格式請(qǐng)查聯(lián)機(jī)幫助。)將字符串c轉(zhuǎn)換成日期。SELECT TO_DATE(19-Mar-99,DD-Mon-YYYY) FROM DUAL;兩個(gè)重要函數(shù)DECODE(<x>,<m1>,<r1>,<m2>,<r2>,<d>)(DECODE函

11、數(shù)功能非常強(qiáng)大，請(qǐng)仔細(xì)玩味。)一個(gè)功能非常強(qiáng)大的函數(shù)，它使得SQL非常高效。它的功能類似于一系列的ifthenelse語(yǔ)句。SELECT sid,serial#,username,DECODE(command,0,None,2,Insert,3,Select,6,Update,7,Delete,8,Drop,Other) cmdFROM V$SESSION WHERE type<>BACKGROUND;NVL(x1,x2)注意ORACLE中的NULL值，注意該函數(shù)作用如果x1為空返回x2，否則返回x1。SELECT NVL(ename,無姓名)  FROM  E

12、MP;分組函數(shù)AVG(DISTINCT|ALL<n>)求返回行的指定列的平均值SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM SCOTT.EMP;COUNT(*|DISTINCT|ALL<x>)統(tǒng)計(jì)返回的行數(shù)SELECT COUNT (*), COUNT(DISTINCT mgr),COUNT(mgr)FROM SCOTT.EMPMAX(DISTINCT|ALL<x>)求返回行的指定列的最大值SELECT MAX(sal),MAX(DISTINCT sal) FROM EMP;MIN(DISTINCT|ALL<x>)求返回行的指定列的最小值SELECT MIN(sal),MIN(DISTINCT sal) FROM EMP;STDDEV(DISTINCT|ALL<x>)求返回行的指定列的標(biāo)準(zhǔn)方差SELECT STDDEV(sal),STDDEV(DISTINCT sal) FROM EMP;SUM()求返回行的指定列的和SELECT SUM(sal) FROM EMP;V