Oracle資料庫 內置系統函數主要分為以下類別:數學函數、字元串函數、日期函數、轉換函數、聚集函數、分析/聚合函數 一、數學函數 返回數字 abs(n):返回數字n的絕對值 ceil(n):返回>=數字n的最小整數 floor(n):返回<=數字n的最大整數 round(n,[m]):做四捨五入運 ...
Oracle資料庫 內置系統函數主要分為以下類別:數學函數、字元串函數、日期函數、轉換函數、聚合函數、分析聚合函數
一、數學函數 ------------返回數字
abs(n):返回數字n的絕對值
ceil(n):返回>=數字n的最小整數
floor(n):返回<=數字n的最大整數
round(n,[m]):做四捨五入運算,如果m預設則四捨五入到整數位
m<0,四捨五入到小數點的前m位,m>0四捨五入到小數點的後m位
trunc(n,[m]):截取數字,如果m預設則將小數位截去
m<0,截取到小數點的前m位,m>0截取到小數點的後m位
1 --Demo:abs()、ceil()、floor()、trunc() 2 SELECT abs(-2.3), 3 ceil(-2.6),ceil(2.6), 4 floor(-2.6),floor(2.6), 5 round(-666.588),round(-666.588,2),round(-666.588,-2), 6 trunc(-666.588),trunc(-666.588,2),trunc(-666.588,-2) 7 FROM dual;
運算結果:
1 ABS(-2.3) CEIL(-2.6) CEIL(2.6) FLOOR(-2.6) FLOOR(2.6) ROUND(-666.588) ROUND(-666.588,2) ROUND(-666.588,-2) TRUNC(-666.588) TRUNC(-666.588,2) TRUNC(-666.588,-2) 2 ---------- ---------- ---------- ----------- ---------- --------------- ----------------- ------------------ --------------- ----------------- ------------------ 3 2.3 -2 3 -3 2 -667 -666.59 -700 -666 -666.58 -600
sqrt(x):返回數字x(x必須大於0)的平方根
power(x,y):返回數字x的y次冪,底數x和指數y都可以是任意數字,但是如果底數x為負數則指數y必須為正數
exp(x):返回常量e(2.71828183....)的x次冪
ln(x):返回數字x(x必須大於0)的自然對數
log(x,y):返回以x為底(除0及1的正整數)m的(任何正整數)對數
mod(x,y):返回x除以y的餘數,如果數字m=0則返回n
1 --demo:sqrt(),power(),exp(),ln(),log(),mod() 2 SELECT sqrt(4),power(2,3),exp(1),ln(10),log(2,8),mod(5,2) 3 FROM dual
運算結果:
1 SQRT(4) POWER(2,3) EXP(1) LN(10) LOG(2,8) MOD(5,2) 2 ---------- ---------- ---------- ---------- ---------- ---------- 3 2 8 2.71828182 2.30258509 3 1
cos(n):返回數字n(弧度單位表示的角度值)的餘弦值
cosh(n):返回數字n的雙曲餘弦值
acos(n):返回數字n的反餘弦值,求的結果單位為弧度,n的範圍為 -1 < n < 1
sin(n):返回數字n(弧度單位表示的角度值)的正弦值
sinh(n,m):返回數字n的雙曲正弦值
asin(n,m):返回數字n的反正弦值,求的結果單位為弧度,n的範圍為 -1 < n < 1
tan(n):返回數字n(弧度表示的角度值)的正切值
tanh(n):返回數字n的雙曲正切值
atan(n):返回數字n的反正切值,求的結果單位為弧度,n的範圍任意數值
atan(n,m):返回數字n/m的反正切值,求的結果單位為弧度,你可以為任意數值,m不可為0
1 --demo:cos(),cosh(),acos(),sin(),sinh(),asin(),tan(),tanh(),atan(),atan() 2 SELECT cos(0.5),cosh(2),acos(1), 3 sin(0.5),sinh(2),asin(0), 4 tan(0.5),tanh(2),atan(8),atan(16/2) 5 FROM dual
運算結果:
1 COS(0.5) COSH(2) ACOS(1) SIN(0.5) SINH(2) ASIN(0) TAN(0.5) TANH(2) ATAN(8) ATAN(16/2) 2 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 3 0.87758256 3.76219569 0 0.47942553 3.62686040 0 0.54630248 0.96402758 1.44644133 1.44644133
二、字元串函數 ------------返回字元值
ascii(n):返回字元c的ascii值
chr(n):將ascii值轉換為對應的字元
initcap(s):將字元串s所有的單詞(單詞是用.空格或給字母數字字元由空格,控制字元,標點符號進行分隔)的首字母大寫,其餘小寫
lower(s):將字元串s中所有的字元轉換為小寫
upper(s):將字元串s中所有的字元轉換為大寫
concat(s1,s2):將字元串s2連接在s1後面,等同於操作符||
length(s):將返回字串s的長度,返回的長度包括其中的所有空格(尾部空格也算);如果s為null,則返回null
1 --ascii(),chr(),concat(),initcap(), lower(),upper(), length(), 2 SELECT ascii('A'),chr(65),concat('Hello','World'), 3 initcap('hello woRld'), 4 lower('HeLLo woRld'),upper('HeLLo woRld'), 5 length('HeLLo woRld'),length('天下英雄出我輩') 6 FROM dual
運算結果:
1 ASCII('A') CHR(65) CONCAT('HELLO','WORLD') INITCAP('HELLOWORLD') LOWER('HELLOWORLD') UPPER('HELLOWORLD') LENGTH('HELLOWORLD') 2 ---------- ------- ----------------------- --------------------- ------------------- ------------------- -------------------- 3 65 A HelloWorld Hello World hello world HELLO WORLD 11
lpad(s1,n,s2):在字串s1的左端填充字串s2,直至填充後的s1的總長度為n
如果不指定s2則預設為空格
如果s1的長度>n,則直接返回s1左端的n個字元
rpad(s1,n,s2):在字串s1的右端填充字串s2,直至填充後的s1的總長度為n
如果不指定s2則預設為空格
如果s1的長度>n,則直接返回s1左端的n個字元
1 --demo:lpad、rpad 2 SELECT lpad('a',10,'8'),lpad('SnnnnnnnnR',6,'8'), 3 rpad('a',10,'8'),rpad('SnnnnnnnnR',6,'8') 4 FROM dual
運算結果:
1 LPAD('A',10,'8') LPAD('SNNNNNNNNR',6,'8') RPAD('A',10,'8') RPAD('SNNNNNNNNR',6,'8') 2 ---------------- ------------------------ ---------------- ------------------------ 3 888888888a Snnnnn a888888888 Snnnnn
instr(s1,s2,n,m):取得子串s2在字串s1中的位置
n表示在s1中開始搜索的位置,m表示字串s2出現的次數
如果n為負數,則表示從尾部開始搜索,n與m預設為1
substr(s1,m,n):取得字串s1從m開始,長度為n的子串,m>0表示從頭開始搜索,m<0表示從尾開始
1 -- instr(), substr() 2 SELECT instr('888arrayarray','array',1,1) ,instr('888arrayarray','array',1,2), 3 instr('888arrayarray','array',-1,1),instr('888arrayarray','array',-1,2), 4 substr('縱橫天下',1,2),substr('array',-4,3) 5 FROM dual
運算結果:
INSTR('888ARRAYARRAY','ARRAY', INSTR('888ARRAYARRAY','ARRAY', INSTR('888ARRAYARRAY','ARRAY', INSTR('888ARRAYARRAY','ARRAY', ------------------------------ ------------------------------ ------------------------------ ------------------------------ 4 9 9 4
ltrim(s1,str):從左端開始逐一取得字串s1左端包含的str中的任何字元
當遇到不是str中的字元是,則結束並返回剩餘結果
rtrim(s1,str):從右端開始逐一取得字串s1右端包含的str中的任何字元
當遇到不是str中的字元是,則結束並返回剩餘結果
trim(c FROM str):從字串str的頭部、尾部、或兩端截去字元c(c只能夠是一個字元)
當遇到不是str中的字元是,則結束並返回剩餘結果
1 SELECT ltrim('terry','t'),ltrim('terry','ter'),ltrim('terry','e'), 2 rtrim('terry','y'),rtrim('terry','ry'),rtrim('terry','yre'), 3 trim('e' from 'terry'),trim('t' from 'terry'),trim('y' from 'terryy') 4 FROM dual
運行結果:
1 LTRIM('TERRY','T') LTRIM('TERRY','TER') LTRIM('TERRY','E') RTRIM('TERRY','Y') RTRIM('TERRY','RY') RTRIM('TERRY','YRE') TRIM('E'FROM'TERRY') TRIM('T'FROM'TERRY') TRIM('Y'FROM'TERRYY') 2 ------------------ -------------------- ------------------ ------------------ ------------------- -------------------- -------------------- -------------------- --------------------- 3 erry y terry terr te t terry erry terr
replace(s1,s2,s3):將s1字串中的子串s2用s3替代,如果s2為null則返回原來的字串s1
註意:如果s3為null,則會去掉子串s2
translate(s1,froms,tos):將字元串s1按照froms和tos的對應關係進行轉換
1 SELECT replace('風清揚_array','風清揚','令狐沖'),replace('風清揚_array','風清揚'), 2 translate('aerry','abcdefgxyz','888888666') 3 FROM dual 4 5 REPLACE('風清揚_ARRAY','風 REPLACE('風清揚_ARRAY','風 TRANSLATE('AERRY','ABCDEFGXYZ' 6 ------------------------------ ------------------------------ ------------------------------ 7 令狐沖_array _array 88rr6
regexp_substr(s1,pattern,position,occurrence,match_parameter):
按照正則表達式pattern從s1字串中的position位置開始
截取第occurrence次出現的匹配pattern的字串,matche_parameter為預設匹配的文本
position,occurrence,matche_parameter 預設為1,1," "
regexp_replace(s1,pattern,position,occurrence,match_parameter):
正則表達式擴展replace的功能,用於按照特定的表達式pattern的規則替換字串串s1
s1指定替換字元串,position指定起始搜索位置
occurrence指定替換出現的第n個字元串
matche_parameter指定預設匹配操作的文本串
regexp_like():用正則表達式擴展後的like
regexp_instr():用正則表達式擴展後的instr
1 SELECT regexp_substr('我的箱[email protected]','[[:lower:]]{1,}+@{1}+[[:alnum:]]{1,}+\.{1}+[[:alpha:]]{1,}'), 2 regexp_replace('這是什麼http://www.space.com.tw/product','http://([[:alnum:]]+\.?){3,4}+[[:print:]]{1,}','www.terry.com') 3 FROM dual 4 5 REGEXP_SUBSTR('我的箱HELLOW REGEXP_REPLACE('這是什麼HT 6 ------------------------------ ------------------------------ 7 helloword@126.com 這是什麼www.terry.com
三、日期函數
sysdate:返回系統當前日期時間
systimestamp:返回系統當前日期時間和時區
current_date:返回當前回話時區所對應的日期和時間
current_timestamp:返回當前回話時區所對應的日期時間
localtimestamp:返回當前回話時區所對應的日期時間
systimestamp:返回系統當前日期時間和時區
sessiontimezone:返回當前回話所在的時區
dbtimezone:返回資料庫所在的時區
1 SELECT sysdate,systimestamp,current_date,current_timestamp, 2 localtimestamp,sessiontimezone,dbtimezone 3 FROM dual 4 YSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP SESSIONTIMEZONE DBTIMEZONE 5 ----------- -------------------------------------- ------------ --------------------------------------- ------------------------------ ------------------ ---------- 6 2017/10/10 10-OCT-17 10.23.03.532728 AM +08:00 2017/10/10 1 10-OCT-17 10.23.03.532742 AM +08:00 10-OCT-17 10.23.03.532742 AM +08:00 +00:00
add_months(d,n):返回指定日期d之後(或前)的n個月所對應的日期時間;n>0:之後,n<0:之前
extract():用於從特定的日期時間值里取出所需要的特定數據(如日期、月份、日、時間等)
last_day(d):用於返回指定日期所在月份的最後一天
next_day(d,char):返回指定日期後的一個工作日(由char指定)所對應的日前
round(d[,fmt]):返回日期時間的四舍五如結果,如果fmt指定年度則7月1日為分界線
如果fmt指定月則16日為分界線,如果fmt指定天則中午12:00為分界線
trunc(d[,fmt]):用於截斷日期時間數據,如果fmt指定年度則結果為本年的1月1日
如果fmt指定月則結果為本月1日,如果fmt空則結果為為截取日期時間數據中的日期
1 SELECT sysdate,add_months(sysdate,4),add_months(sysdate,-4), 2 extract(MONTH from sysdate),last_day(sysdate),next_day(sysdate,'sunday'), 3 round(sysdate,'YEAR'),round(sysdate,'MONTH'),round(sysdate,'DAY'),round(sysdate), 4 trunc(sysdate,'YEAR'),trunc(sysdate,'MONTH'),trunc(sysdate,'DAY'),trunc(sysdate) 5 FROM dual
運算結果:
1 SYSDATE ADD_MONTHS(SYSDATE,4) ADD_MONTHS(SYSDATE,-4) EXTRACT(MONTHFROMSYSDATE) 2 ----------- --------------------- ---------------------- ------------------------- 3 2017/10/10 2018/2/10 11:29:23 2017/6/10 11:29:23 10 4 5 LAST_DAY(SYSDATE) NEXT_DAY(SYSDATE,'SUNDAY') ROUND(SYSDATE,'YEAR') ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'DAY') 6 ----------------- -------------------------- --------------------- ---------------------- -------------------- 7 2017/10/31 11:29: 2017/10/15 11:29:23 2018/1/1 2017/10/1 2017/10/8 8 9 ROUND(SYSDATE) TRUNC(SYSDATE,'YEAR') TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'DAY') TRUNC(SYSDATE) 10 -------------- --------------------- ---------------------- -------------------- -------------- 11 2017/10/10 2017/1/1 2017/10/1 2017/10/8 2017/10/10
to_timestamp(s1,fmt):將符合特定日期和時間按格式的字元串轉變為 timestamp 類型
month_between(d1,d2):返回日期d1和d2之間相差的月份數,
&