Oracle中的常用函數(shù)詳解
一、數(shù)值函數(shù)
1、mod(n1,n2):n1除以n2的余數(shù)。
如果n2為0，則返回n1。
select mod(23,8),mod(24,8) from dual;--返回：7,0
2、power(n1,n2):返回?cái)?shù)字n1的n2次冪;
exp(y)：返回e的y次冪。（e為數(shù)學(xué)常量）;
log(x,y)：返回以x為底的y的對(duì)數(shù)；
ln(y)：返回e為底的自然對(duì)數(shù)。
select power(2.5,2),power(1.5,0),power(20,-1) from dual;
3、sqrt(n):平方根。
select sqrt(64),sqrt(10) from dual;--返回：8 , 3.16227766
4、ceil(n):返回大于等于n的最小整數(shù)。;
floor(n):返回小于等于n的最大整數(shù)。
select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;--返回4，5，0
5、sign(x)：返回x的正負(fù)值
若為正值返回1，負(fù)值返回-1，0返回0。
select sign(100),sign(-100),sign(0) from dual;
6、trunc(n[,len]):n截取到小數(shù)點(diǎn)len位。
len默認(rèn)為0。len>0，截取到小數(shù)點(diǎn)右len位。len<0，截取到小數(shù)點(diǎn)左len位。
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;--返回：5555.66 5500 5555
7、round(n[,len]):n四舍五入到小數(shù)點(diǎn)len位，規(guī)則同trunc。
select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;--返回： 5555.67 ,5600 ,5556
8、sys.dbms.random.value()：產(chǎn)生0-1之間的隨機(jī)數(shù)。
DBMS_RANDOM.VALUE()是隨機(jī)產(chǎn)生( 0,1 )之間的數(shù)。
DBMS_RANDOM.VALUE(n1,n2)：產(chǎn)生n1-n2之間的隨機(jī)數(shù)。
trunc(dbms_random.value(10,100)) //80：生成10-100之間的隨機(jī)數(shù)。
二、字符函數(shù)
1：lower(c1):返回字符串,并將所有的字符小寫
SELECT LOWER('AbcDedf Gbad') FROM DUAL;2：upper(c1):返回字符串,并將所有的字符大寫
SELECT UPPER('abcdEf') FROM DUAL;3: initcap(c1):返回字符串并將字符串的第一個(gè)字母變?yōu)榇髮?/strong>
全部單詞的首字母大寫
SELECT INITCAP('your didn''t try your best') FROM DUAL;4: initcap(c1,n[,c2]):在列的左邊填充字符
- C1 字符串
- n 追加后字符總長(zhǎng)度
- c2 追加字符串,默認(rèn)為空格
SELECT LPAD('WELCOME', 20, 'HELLO') FROM DUAL;5: rpad(c1,n[,c2]):在列的右邊填充字符
注意長(zhǎng)度值并不是粘貼字符的長(zhǎng)度，而是整個(gè)字符串的長(zhǎng)度，如果長(zhǎng)度小于原始字符串
--SELECT RPAD('HELLO', 4, '*') FROM DUAL; 的值為HELL
SELECT RPAD('HELLO', 10, '*') FROM DUAL;
SELECT RPAD('HELLO', 10, 'E') FROM DUAL;6: ltrim(X,[TRIM_STRING]):刪除左邊出現(xiàn)的字符串。
默認(rèn)為空字符串
SELECT LTRIM(' hello world!') FROM DUAL;
SELECT LTRIM('hello, world', 'hello') FROM DUAL;7: rtrim(X, [TRIM_STRING]):刪除右邊出現(xiàn)的字符串
TRIM_STRING,默認(rèn)為空字符串。
SELECT RTRIM('hello world! ') FROM DUAL;8: trim('s' from 'string'):刪除兩邊出現(xiàn)的字符串
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默認(rèn)為空格符
SELECT TRIM('Y' FROM 'YOU') FROM DUAL;9: instr(C1,C2,I,J):在一個(gè)字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;
- C1 被搜索的字符串
- C2 希望搜索的字符串
- I 搜索的開始位置,默認(rèn)為1
- J 出現(xiàn)的位置,默認(rèn)為1
SELECT INSTR('HELLO WORLD! WELCOME', 'WORLD', 1) FROM DUAL;10：substr(string,start,count):取子字符串,從start開始,取count個(gè)
SELECT SUBSTR('you are right!, come on', 3, 30) FROM DUAL;11：replace('string','s1','s2'):替換
- string 希望被替換的字符或變量
- s1 被替換的字符串
- s2 要替換的字符串
SELECT REPLACE('HE LOVE YOU', 'HE' ,'I') FROM DUAL;12：translate(c1,c2,c3)：將指定字符替換為新字符
- c1 希望被替換的字符或變量
- c2 查詢?cè)嫉淖址?/li>
- c3： 替換新的字符集，將c2對(duì)應(yīng)順序字符，替換為c3對(duì)應(yīng)順序字符
select TRANSLATE('he love you','he','i'),
TRANSLATE('重慶的人','重慶的','上海男'),
TRANSLATE('重慶的人','重慶的重慶','北京男士們'),
TRANSLATE('重慶的人','重慶的重慶','1北京男士們'),
TRANSLATE('重慶的人','1重慶的重慶','北京男士們') from dual;
--i love you,上海男人,北京男人,1北京人,京男士人13: length(c1)：返回字符串的長(zhǎng)度;
返回表某條數(shù)據(jù)某個(gè)列實(shí)際長(zhǎng)度，如果該表沒有數(shù)據(jù)，返回0
SELECT LENGTH(TYPE_NAME) FROM USER_TYPES
14：ascii（x1)：返回字符串的ASCII值
SELECT ASCII('A') FROM DUAL;
SELECT ASCII('a') FROM DUAL;15: chr(n1)：返回整數(shù)所對(duì)應(yīng)的ASCII字符
SELECT CHR('65') FROM DUAL;
SELECT CHR(400) FROM DUAL; --如果超出ACII值，則返回空16: concat(c1,c2)：連接字符串A和字符串B
SELECT CONCAT('您好', '歡迎來(lái)到ORACLE世界') AS TEXT FROM DUAL;
--如果要連接表里面的兩個(gè)字段可以用||
SELECT TYPECODE || '____' || TYPE_NAME AS "TYPE" FROM USER_TYPES;三、日期時(shí)間函數(shù)
1：sysdate、current_date：系統(tǒng)的當(dāng)前日期
（1）日期加上范圍日期，得到新日期
- data+n,加減n天。（n為負(fù)數(shù)，表示減去）
- date+n/24:加減n小時(shí)
- date+n/24/60:加減n分鐘
- date+n/24/60/3600:加減n秒
（2）date1-date2:兩日期相差的天數(shù)：
(date1-date2)*24*3600:兩日期相差的秒數(shù)
SELECT SYSDATE FROM DUAL;
2：add_months(date,n1)：增加或減去月份
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL3: months_between(date2,date1)：給出date2-date1的月份
SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03', 'YYYY-MM-DD'), TO_DATE('2011-01-23', 'YYYY-MM-DD')) FROM DUAL;
SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;
--SELECT MONTHS_BETWEEN('2011-1月-23', '2011-9月-1') FROM DUAL; 文字與格式字符串不匹配4: last_day(date)：返回日期的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;
5：next_day(date[,fmt]）:返回日期d1在下周，星期幾(參數(shù)c1)的日期
星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;--下周星期一,
6、round(date[,fmt]）:日期時(shí)間四舍五入結(jié)果。
fmt默認(rèn)是day.
7: trunc(date[,fmt]）:TRUNC函數(shù)為指定元素而截去的日期值。
TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am') ='24-Nov-1999 12:00:00 am'
TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'8：extract(c1 from date) :找出日期或間隔值的字段值
SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL; SELECT EXTRACT(DAY FROM SYSDATE) AS "DAY" FROM DUAL; SELECT EXTRACT(YEAR FROM SYSDATE) AS "YEAR" FROM DUAL;
9：new_time(date,'this','that'):給出在this時(shí)區(qū)=other時(shí)區(qū)的日期和時(shí)間
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,TO_CHAR(NEW_TIME(SYSDATE, 'PDT', 'GMT'), 'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS FROM DUAL;
簡(jiǎn)寫 時(shí)區(qū)
- AST OR ADT 大西洋標(biāo)準(zhǔn)時(shí)間
- HST OR HDT 阿拉斯加—夏威夷時(shí)間
- BST OR BDT 英國(guó)夏令時(shí)
- MST OR MDT 美國(guó)山區(qū)時(shí)間
- CST OR CDT 美國(guó)中央時(shí)區(qū)
- NST 新大陸標(biāo)準(zhǔn)時(shí)間
- EST OR EDT 美國(guó)東部時(shí)間
- PST OR PDT 太平洋標(biāo)準(zhǔn)時(shí)間
- GMT 格倫威治標(biāo)準(zhǔn)時(shí)間
- YST OR YDT Yukon標(biāo)準(zhǔn)時(shí)間
10: dbtimezone() :返回時(shí)區(qū)
SELECT DBTIMEZONE FROM DUAL;
11: sessiontimezone:返回會(huì)話時(shí)區(qū)
其中DBTIMEZONE是數(shù)據(jù)庫(kù)的，session是針對(duì)當(dāng)前會(huì)話的，因?yàn)闀r(shí)區(qū)在會(huì)話級(jí)可以改變
SELECT SESSIONTIMEZONE FROM DUAL; ALTER SESSION SET TIME_ZONE = '8:00'; SELECT SESSIONTIMEZONE FROM DUAL;
12、常用時(shí)間查詢：
---- 上月最后一天 SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL; ----: 上各月的今天 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL; ---- 上個(gè)月第一天 SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL; --- 要找到某月中所有周五的具體日期 SELECT TO_CHAR(T.D, 'YY-MM-DD') FROM (SELECT TRUNC(SYSDATE, 'MM') + ROWNUM -1 AS D FROM DBA_OBJECTS WHERE ROWNUM < 32) T WHERE TO_CHAR(T.D, 'MM') = TO_CHAR(SYSDATE, 'MM') AND TRIM(TO_CHAR(T.D, 'DAY')) = '星期五'
四、轉(zhuǎn)換函數(shù)
1： to_char(date,'format') ：把對(duì)應(yīng)的數(shù)據(jù)轉(zhuǎn)換為字符串類型
TO_CHAR的fmt:格式字符串，不分大小寫。
select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual
- Y或YY或YYY 年的最后一位，兩位或三位
- SYEAR或YEAR: SYEAR使公元前的年份前加一負(fù)號(hào) --TWENTY ELEVEN
- Q： 季度，1～3月為第一季度 -- 2表示第二季度
- MM： 月份數(shù) --04表示4月
- RM： 月份的羅馬表示 --IV表示4月
- MON： 月份 --4月
- Month： 用9個(gè)字符長(zhǎng)度表示的月份名 -- 4月
- WW： 當(dāng)年第幾周 -- 24表示2002年6月13日為第24周
- W： 本月第幾周 -- 2011年04月26日為第4周
- DDD： 當(dāng)年第幾天. 1月1日為001，2月1日為032
- DD： 當(dāng)月第幾天
- D： 周內(nèi)第幾天
- DY： 周內(nèi)第幾天縮寫
- HH或HH12： 12進(jìn)制小時(shí)數(shù)
- HH24： 24小時(shí)制
- MI： 分鐘數(shù)(0～59) ：提示注意不要將MM格式用于分鐘(分鐘應(yīng)該使用MI)。MM是用于月份的格式，將它用于分鐘也能工作，但結(jié)果是錯(cuò)誤的。
- SS： 秒數(shù)(0～59)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2： to_char(n,'format')：把對(duì)應(yīng)的數(shù)字為字符串類型
FORMAT格式符：
- 9：帶有指定位數(shù)的值
- 0：前導(dǎo)零的值
- . （句點(diǎn)）：小數(shù)點(diǎn)
- , （逗號(hào)）：分組（千）分隔符
- PR：尖括號(hào)內(nèi)負(fù)值
- S：帶負(fù)號(hào)的負(fù)值（使用本地化）
- L：貨幣符號(hào)（使用本地化）
- D：小數(shù)點(diǎn)（使用本地化）
- G：分組分隔符（使用本地化）
- MI：在指明的位置的負(fù)號(hào)（如果數(shù)字 < 0）
- PL：在指明的位置的正號(hào)（如果數(shù)字 > 0）
- SG：在指明的位置的正/負(fù)號(hào)
- RN：羅馬數(shù)字（輸入在 1 和 3999 之間）
- TH or th：轉(zhuǎn)換成序數(shù)
SELECT TO_CHAR(122323.45, '$99999999.99') FROM DUAL;
3： to_date(string,'format')：將字符串轉(zhuǎn)化為日期
主要用于比較和修改日期。
SELECT TO_DATE('2011/03/24', 'YYYY-MM-DD') FROM DUAL;4： to_number：將給出的字符轉(zhuǎn)換為數(shù)字
SELECT TO_NUMBER('￥2008.00'，'L9999D99') AS Year FROM DUAL;五、輔助函數(shù)
1、decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值):根據(jù)條件返回相應(yīng)值
值1……n 不能為條件表達(dá)式,這種情況只能用case when then end解決。
select decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期FROM xqb
2、greatest(exp1,exp2,exp3,……,expn):返回表達(dá)式列表中值最大的一個(gè)。 ; least(exp1,exp2,exp3,……,expn):返回表達(dá)式列表中值最小的一個(gè)。
如果表達(dá)式類型不同，會(huì)隱含轉(zhuǎn)換為第一個(gè)表達(dá)式類型。
SELECT greatest(10,32,'123','2006') FROM dual;
3、nullif (expr1, expr2)：expr1和expr2相等返回NULL，不相等返回expr1。
SELECT NULLIF( 'a', 'b' ) ;--返回值 a SELECT NULLIF( 'a', 'a');--返回 NULL
實(shí)際應(yīng)用：
--添加函數(shù)查詢結(jié)果，要求（將日期類型默認(rèn)'0001/1/1',改成null,不相等，返回本身日期）--
select NULLIF(RECEIVEDATE,TO_DATE('0001/1/1','yyyy-mm-dd hh24:mi:ss')) 收貨日期,NAME 單據(jù)名稱 from tab4、nvl (expr1, expr2):若expr1為NULL，返回expr2；expr1不為NULL，返回expr1。
select NVL(null,'未知') SexType from dual;--expr1為空，返回expr2，結(jié)果：'未知'
select NVL('1','2') SexType from dual;--expr1不為空，返回expr1， 結(jié)果：1實(shí)際應(yīng)用：
--加函數(shù)時(shí)情況，要求（field 為null，返回0 ；field 不為null,返回本身 select NVL(UseFlag,'0') 使用標(biāo)志,NAME 單據(jù)名稱 from tab
5、nvl2(expr1, expr2, expr3) :expr1不為NULL，返回expr2；expr2為NULL，返回expr3。
expr2和expr3類型不同的話，expr3會(huì)轉(zhuǎn)換為expr2的類型
select NVL2(0,1,2) from dual;--不為null時(shí)，返回expr2 ，結(jié)果：1 select NVL2(null,1,2) from dual;--為null時(shí)，返回expr3 ，結(jié)果：2
實(shí)際應(yīng)用：
--應(yīng)用到實(shí)際查詢中，要求（field 為null,返回0; field 不為null,返回本身） select NVL2(UseFlag,UseFlag,'0') 使用標(biāo)志,A.NAME 單據(jù)名稱 from tab
6、coalesce(c1, c2, ...,cn)：返回列表中第一個(gè)非空的表達(dá)式，如果所有表達(dá)式都為空值則返回1個(gè)空值。
select COALESCE(null,3*5,44) hz from dual; --返回15
7、sys_context('USERENV',c2)：返回系統(tǒng)'USERENV'變量中c2對(duì)應(yīng)的的值。
SYS_CONTEXT('USERENV','LANGUAGE') language,8、sys_connect_by_path(column_name,'分隔符')：把一個(gè)父節(jié)點(diǎn)下的所有子節(jié)點(diǎn)通過(guò)某個(gè)字符進(jìn)行區(qū)分，然后連接在一個(gè)列中顯示
- 第一個(gè)參數(shù)是形成樹形式的字段，第二個(gè)參數(shù)是父級(jí)和其子級(jí)分隔顯示用的分隔符
- 偽列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE
結(jié)構(gòu)化查詢：START WITH ...CONNNECT BY PRIOR基本語(yǔ)法是：
SELECT ...FROM WHERE （過(guò)濾返回記錄，僅過(guò)濾被限定節(jié)點(diǎn)，其根節(jié)點(diǎn)和子節(jié)點(diǎn)均不受影響） START WITH (根節(jié)點(diǎn)，可以指定多個(gè)節(jié)點(diǎn)) CONNECT BY PRIOR= （連接條件，PRIOR置于等號(hào)前，則從根節(jié)點(diǎn)到葉節(jié)點(diǎn)開始檢索；置于等號(hào)后，則從葉節(jié)點(diǎn)到根節(jié)點(diǎn)開始檢索）
該查詢?cè)L問(wèn)路徑如下：從根節(jié)點(diǎn)開始，向下掃描子節(jié)點(diǎn)，該子節(jié)點(diǎn)已被訪問(wèn)則轉(zhuǎn)向其最左側(cè)未被訪問(wèn)的子節(jié)點(diǎn)，否則判斷該節(jié)點(diǎn)是否為根節(jié)點(diǎn)，是則訪問(wèn)完畢，否則返回父節(jié)點(diǎn)重新執(zhí)行判斷。
SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;
--得到結(jié)果為：
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
SELECT SYS_CONNECT_BY_PATH(ename, </'>') "Path" FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;
--得到結(jié)果為：
KING
KING>JONES
KING>JONES>SCOTT
KING>JONES>SCOTT>ADAMS
KING>JONES>FORD
KING>JONES>FORD>SMITH
KING>BLAKE
KING>BLAKE>ALLEN
KING>BLAKE>WARD
KING>BLAKE>MARTIN
KING>BLAKE>TURNER
KING>BLAKE>JAMES
KING>CLARK
KING>CLARK>MILLER
六、聚合函數(shù)
- AVG(DISTINCT|ALL)：平均值，DISTINCT表示對(duì)不同的值求平均值,重復(fù)值的列的只取一次。
SELECT AVG(DISTINCT SAL) FROM SCOTT.EMP;
- MAX(DISTINCT|ALL)：最大值
- MIN(DISTINCT|ALL)：最小值
- SUM(DISTINCT|ALL)：求和
- COUNT(DISTINCT|ALL)：求記錄數(shù)
- wmsys.wm_concat(DISTINCT|ALL)：合并列。
將一列的多行記錄合并到一列，用逗號(hào)隔開。例如表的有兩個(gè)字段，要按airport_id合并成兩行可用sql語(yǔ)句
select airport_id, wmsys.wm_concat(distinct account) from AIRPORT_MODIFY group by airport_id
七、分析函數(shù)
見專題：Oracle分析函數(shù)
到此這篇關(guān)于Oracle常用函數(shù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)
這篇文章主要介紹了Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)的相關(guān)資料,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-08-08
一些實(shí)用的sql，需要的朋友可以參考下。2009-11-11
本篇文章是對(duì)oracle sql執(zhí)行過(guò)程進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
這篇文章主要介紹了Oracle自動(dòng)備份及自動(dòng)備份步驟的相關(guān)資料,需要的朋友可以參考下2016-02-02
Oracle數(shù)據(jù)庫(kù)基本常用命令匯總
以下是對(duì)Oracle中的數(shù)據(jù)庫(kù)基本常用命令進(jìn)行了總結(jié)介紹，需要的朋友可以過(guò)來(lái)參考下2013-08-08
最近需要用的oracle，所以大家好好的學(xué)習(xí)下基礎(chǔ)并整理下資料，希望能幫助到需要的朋友。2011-12-12
Oracle DATABASE LINK(DBLINK)創(chuàng)建與刪除方法
這篇文章主要介紹了Oracle DATABASE LINK(DBLINK)創(chuàng)建與刪除方法,需要的朋友可以參考下2016-02-02
相信大家對(duì)正則表達(dá)式都不陌生,從linux下的命令到Java等編程語(yǔ)言,正則表達(dá)式無(wú)處不在,下面這篇文章主要給大家介紹了關(guān)于ORACLE中常用的幾種正則表達(dá)式的相關(guān)資料,需要的朋友可以參考下2022-09-09
oracle數(shù)據(jù)庫(kù)sql的優(yōu)化總結(jié)
自己對(duì)oracle sql的一些優(yōu)化總結(jié)，特分享下，方便需要的朋友2013-08-08
