日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

Oracle中的常用函數詳解_oracle

作者：springsnow ? 更新時間： 2022-06-29 編程語言

一、數值函數

1、mod(n1,n2):n1除以n2的余數。

如果n2為0，則返回n1。

select mod(23,8),mod(24,8) from dual;--返回：7,0

2、power(n1,n2):返回數字n1的n2次冪;?
exp(y)：返回e的y次冪。（e為數學常量）;?
log(x,y)：返回以x為底的y的對數；?
ln(y)：返回e為底的自然對數。

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的最小整數。;

floor(n):返回小于等于n的最大整數。

select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;--返回4，5，0

5、sign(x)：返回x的正負值

若為正值返回1，負值返回-1，0返回0。

select sign(100),sign(-100),sign(0) from dual;

6、trunc(n[,len]):n截取到小數點len位。

len默認為0。len>0，截取到小數點右len位。len<0，截取到小數點左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四舍五入到小數點len位，規則同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()：產生0-1之間的隨機數。

DBMS_RANDOM.VALUE()是隨機產生( 0,1 )之間的數。?
DBMS_RANDOM.VALUE(n1,n2)：產生n1-n2之間的隨機數。

trunc(dbms_random.value(10,100))    //80：生成10-100之間的隨機數。

二、字符函數

1：lower(c1):返回字符串,并將所有的字符小寫

SELECT LOWER('AbcDedf Gbad') FROM DUAL;

2：upper(c1):返回字符串,并將所有的字符大寫

SELECT UPPER('abcdEf') FROM DUAL;

3: initcap(c1):返回字符串并將字符串的第一個字母變為大寫

全部單詞的首字母大寫

SELECT INITCAP('your didn''t try your best') FROM DUAL;

4: initcap(c1,n[,c2]):在列的左邊填充字符

  • C1 字符串
  • n 追加后字符總長度
  • c2 追加字符串,默認為空格
SELECT LPAD('WELCOME', 20, 'HELLO') FROM DUAL;

5: rpad(c1,n[,c2]):在列的右邊填充字符

注意長度值并不是粘貼字符的長度，而是整個字符串的長度，如果長度小于原始字符串

--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]):刪除左邊出現的字符串。

默認為空字符串

SELECT LTRIM('  hello world!') FROM DUAL;
SELECT LTRIM('hello, world', 'hello') FROM DUAL;

7: rtrim(X, [TRIM_STRING]):刪除右邊出現的字符串

TRIM_STRING,默認為空字符串。

SELECT RTRIM('hello world!    ') FROM DUAL;

8: trim('s' from 'string'):刪除兩邊出現的字符串

LEADING 剪掉前面的字符?
TRAILING 剪掉后面的字符?
如果不指定,默認為空格符

SELECT TRIM('Y' FROM 'YOU') FROM DUAL;

9: instr(C1,C2,I,J):在一個字符串中搜索指定的字符,返回發現指定的字符的位置;

  • C1 被搜索的字符串
  • C2 希望搜索的字符串
  • I 搜索的開始位置,默認為1
  • J 出現的位置,默認為1
SELECT INSTR('HELLO WORLD! WELCOME', 'WORLD', 1)  FROM DUAL;

10：substr(string,start,count):取子字符串,從start開始,取count個

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 查詢原始的字符集
  • c3： 替換新的字符集，將c2對應順序字符，替換為c3對應順序字符
select TRANSLATE('he love you','he','i'),
TRANSLATE('重慶的人','重慶的','上海男'),
TRANSLATE('重慶的人','重慶的重慶','北京男士們'),
TRANSLATE('重慶的人','重慶的重慶','1北京男士們'),
TRANSLATE('重慶的人','1重慶的重慶','北京男士們') from dual;
--i love you,上海男人,北京男人,1北京人,京男士人

13: length(c1)：返回字符串的長度;

返回表某條數據某個列實際長度，如果該表沒有數據，返回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)：返回整數所對應的ASCII字符

SELECT CHR('65') FROM DUAL;
SELECT CHR(400) FROM DUAL;  --如果超出ACII值，則返回空

16: concat(c1,c2)：連接字符串A和字符串B

SELECT CONCAT('您好', '歡迎來到ORACLE世界') AS TEXT FROM DUAL;
--如果要連接表里面的兩個字段可以用||
SELECT TYPECODE || '____' || TYPE_NAME AS "TYPE" FROM USER_TYPES;

三、日期時間函數

1：sysdate、current_date：系統的當前日期

（1）日期加上范圍日期，得到新日期

  • data+n,加減n天。（n為負數，表示減去）
  • date+n/24:加減n小時
  • date+n/24/60:加減n分鐘
  • date+n/24/60/3600:加減n秒

（2）date1-date2:兩日期相差的天數：

(date1-date2)*24*3600:兩日期相差的秒數

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 DUAL

3: 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在下周，星期幾(參數c1)的日期

星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;--下周星期一,

6、round(date[,fmt]）:日期時間四舍五入結果。

fmt默認是day.

7: trunc(date[,fmt]）:TRUNC函數為指定元素而截去的日期值。

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時區=other時區的日期和時間

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;

簡寫 時區

  • AST OR ADT 大西洋標準時間
  • HST OR HDT 阿拉斯加—夏威夷時間
  • BST OR BDT 英國夏令時
  • MST OR MDT 美國山區時間
  • CST OR CDT 美國中央時區
  • NST 新大陸標準時間
  • EST OR EDT 美國東部時間
  • PST OR PDT 太平洋標準時間
  • GMT 格倫威治標準時間
  • YST OR YDT Yukon標準時間

10: dbtimezone() :返回時區

SELECT DBTIMEZONE FROM DUAL;

11: sessiontimezone:返回會話時區

其中DBTIMEZONE是數據庫的，session是針對當前會話的，因為時區在會話級可以改變

SELECT SESSIONTIMEZONE FROM DUAL;  
ALTER SESSION SET TIME_ZONE = '8:00';
SELECT SESSIONTIMEZONE FROM DUAL;

12、常用時間查詢：

----  上月最后一天
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;
---- 上個月第一天
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')) = '星期五'

四、轉換函數

1： to_char(date,'format') ：把對應的數據轉換為字符串類型

TO_CHAR的fmt:格式字符串，不分大小寫。

select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual
  • Y或YY或YYY 年的最后一位，兩位或三位
  • SYEAR或YEAR: SYEAR使公元前的年份前加一負號 --TWENTY ELEVEN
  • Q： 季度，1～3月為第一季度 -- 2表示第二季度
  • MM： 月份數 --04表示4月
  • RM： 月份的羅馬表示 --IV表示4月
  • MON： 月份 --4月
  • Month： 用9個字符長度表示的月份名 -- 4月
  • WW： 當年第幾周 -- 24表示2002年6月13日為第24周
  • W： 本月第幾周 -- 2011年04月26日為第4周
  • DDD： 當年第幾天. 1月1日為001，2月1日為032
  • DD： 當月第幾天
  • D： 周內第幾天
  • DY： 周內第幾天縮寫
  • HH或HH12： 12進制小時數
  • HH24： 24小時制
  • MI： 分鐘數(0～59) ：提示注意不要將MM格式用于分鐘(分鐘應該使用MI)。MM是用于月份的格式，將它用于分鐘也能工作，但結果是錯誤的。
  • SS： 秒數(0～59)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2： to_char(n,'format')：把對應的數字為字符串類型

FORMAT格式符：

  • 9：帶有指定位數的值
  • 0：前導零的值
  • . （句點）：小數點
  • , （逗號）：分組（千）分隔符
  • PR：尖括號內負值
  • S：帶負號的負值（使用本地化）
  • L：貨幣符號（使用本地化）
  • D：小數點（使用本地化）
  • G：分組分隔符（使用本地化）
  • MI：在指明的位置的負號（如果數字 < 0）
  • PL：在指明的位置的正號（如果數字 > 0）
  • SG：在指明的位置的正/負號
  • RN：羅馬數字（輸入在 1 和 3999 之間）
  • TH or th：轉換成序數
SELECT TO_CHAR(122323.45, '$99999999.99') FROM DUAL;

3： to_date(string,'format')：將字符串轉化為日期

主要用于比較和修改日期。

SELECT TO_DATE('2011/03/24', 'YYYY-MM-DD') FROM DUAL;

4： to_number：將給出的字符轉換為數字

SELECT TO_NUMBER('￥2008.00'，'L9999D99') AS Year FROM DUAL;

五、輔助函數

1、decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值):根據條件返回相應值

值1……n 不能為條件表達式,這種情況只能用case when then end解決。

select decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期FROM xqb

2、greatest(exp1,exp2,exp3,……,expn):返回表達式列表中值最大的一個。 ; least(exp1,exp2,exp3,……,expn):返回表達式列表中值最小的一個。

如果表達式類型不同，會隱含轉換為第一個表達式類型。

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

實際應用：

--添加函數查詢結果，要求（將日期類型默認'0001/1/1',改成null,不相等，返回本身日期）--
select NULLIF(RECEIVEDATE,TO_DATE('0001/1/1','yyyy-mm-dd hh24:mi:ss')) 收貨日期,NAME 單據名稱 from tab

4、nvl (expr1, expr2):若expr1為NULL，返回expr2；expr1不為NULL，返回expr1。

select NVL(null,'未知') SexType from dual;--expr1為空，返回expr2，結果：'未知'
select NVL('1','2') SexType from dual;--expr1不為空，返回expr1， 結果：1

實際應用：

--加函數時情況，要求（field 為null，返回0 ；field 不為null,返回本身
select NVL(UseFlag,'0') 使用標志,NAME 單據名稱 from tab

5、nvl2(expr1, expr2, expr3) :expr1不為NULL，返回expr2；expr2為NULL，返回expr3。

expr2和expr3類型不同的話，expr3會轉換為expr2的類型

select NVL2(0,1,2) from dual;--不為null時，返回expr2 ，結果：1
select NVL2(null,1,2) from dual;--為null時，返回expr3 ，結果：2

實際應用：

--應用到實際查詢中，要求（field 為null,返回0; field 不為null,返回本身）
 select NVL2(UseFlag,UseFlag,'0') 使用標志,A.NAME 單據名稱 from tab

6、coalesce(c1, c2, ...,cn)：返回列表中第一個非空的表達式，如果所有表達式都為空值則返回1個空值。

select COALESCE(null,3*5,44) hz from dual; --返回15

7、sys_context('USERENV',c2)：返回系統'USERENV'變量中c2對應的的值。

SYS_CONTEXT('USERENV','LANGUAGE') language,

8、sys_connect_by_path(column_name,'分隔符')：把一個父節點下的所有子節點通過某個字符進行區分，然后連接在一個列中顯示

詳見?Oracle遞歸查詢connect by

  • 第一個參數是形成樹形式的字段，第二個參數是父級和其子級分隔顯示用的分隔符
  • 偽列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

結構化查詢：START WITH ...CONNNECT BY PRIOR基本語法是：

SELECT ...FROM
WHERE （過濾返回記錄，僅過濾被限定節點，其根節點和子節點均不受影響）
START WITH (根節點，可以指定多個節點)
CONNECT BY PRIOR= （連接條件，PRIOR置于等號前，則從根節點到葉節點開始檢索；置于等號后，則從葉節點到根節點開始檢索）

該查詢訪問路徑如下：從根節點開始，向下掃描子節點，該子節點已被訪問則轉向其最左側未被訪問的子節點，否則判斷該節點是否為根節點，是則訪問完畢，否則返回父節點重新執行判斷。

SELECT ename   FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr;

--得到結果為：

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;

--得到結果為：

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?

六、聚合函數

  • AVG(DISTINCT|ALL)：平均值，DISTINCT表示對不同的值求平均值,重復值的列的只取一次。
SELECT AVG(DISTINCT SAL) FROM SCOTT.EMP;
  • MAX(DISTINCT|ALL)：最大值
  • MIN(DISTINCT|ALL)：最小值
  • SUM(DISTINCT|ALL)：求和
  • COUNT(DISTINCT|ALL)：求記錄數
  • wmsys.wm_concat(DISTINCT|ALL)：合并列。?
    將一列的多行記錄合并到一列，用逗號隔開。例如表的有兩個字段，要按airport_id合并成兩行可用sql語句
select airport_id,   wmsys.wm_concat(distinct  account) from AIRPORT_MODIFY group by airport_id

七、分析函數

見專題：Oracle分析函數

原文鏈接：https://www.cnblogs.com/springsnow/p/9399285.html

欄目分類
最近更新