鴨爸的隨手寫寫

常常要用到一些函數還是做什麼計算的時候

有時知道是哪一個可是會忘記對不對或是如何使用

放一份自己找的到的很快就可以查到也不錯.....

--1.ASCII=>返回與指定的字符對應的十進制數
select ascii('A') A, ascii('a') a, ascii('0') zero, ascii(' ') space, ascii('趙') Word from dual
--結果：
--A	  A	  ZERO SPACE WORD
--65	97	48	 32    48047

--2.CHR=>給出整數,返回對應的字符;
select chr(48047) zhao, chr(65) chr65 from dual
--結果：
-- 	ZHAO	CHR65
--	趙	  A

--3.CONCAT=>連接兩個字串;
select concat('0000-','111111')||'轉33663' As PhoneNo from dual
--結果：
-- 	PHONENO
--	0000-111111轉33663

--4.INITCAP=>返回字串並將字串的第一個字母變為大寫;
select initcap('simle to me') FirstWordToupper from dual
--結果
--  FIRSTWORDTOUPPER
--  Simle To Me

--5.INSTR(S1,S2,B,E)
--在一個字串中尋找指定的字串,回傳指定的字串位置;
--S1 被搜尋字串 S2 要搜尋字串 B 開始位置,預設為1 E 結束位置,預設為1
select instr('oracle traning','ra',1,2) instring from dual
--結果
--  INSTRING
--	9

--6.LENGTH=>回傳字串長度
select '我是誰' As CName, length('我是誰') AS CLength,
'WHO AND I' As EName, length('WHO AND I') As ELength,
'99999.99' As iNum,length(to_char(99999.99)) As iLength
from dual
--結果
-- 	CNAME	  |CLENGTH	|ENAME	    |ELENGTH	|INUM	    |ILENGTH
--	我是誰	|3	      |WHO AND I	|9	      |99999.99	|8

--7.LOWER=>將所有字串轉為小寫
select lower('AaBbCcDd') As strResult from dual
--結果
--  STRRESULT
--	aabbccdd

--8.UPPER=>將所有字串轉為大寫
select upper('AaBbCcDd') As strResult from dual
--結果
--  STRRESULT
--	AABBCCDD

--9.RPAD和LPAD
--RPAD  在右邊補足設定長度或是截掉多於設定長度字串
--LPAD  在左邊補足設定長度或是截掉多於設定長度字串
select rpad('web',1,'~') as strResult from dual
select rpad('web',5,'~') as strResult from dual
select lpad('web',1,'~') as strResult from dual
select lpad('web',5,'~') as strResult from dual
select lpad(rpad('test',11,'~'),18,'~')from dual
--結果
--strResult
--w
--web~~
--w
--~~web

--10.LTRIM和RTRIM
--LTRIM  由左至右清除與設定相同的內容Oracle是一個一個比對有出現就清除至找不到為止之後出現也不會清除
--RTRIM  由右至左清除與設定相同的內容Oracle是一個一個比對有出現就清除至找不到為止之後出現也不會清除
select ltrim('902100111000991110224323','1') from dual
--結果
--902100111000991110224323
select ltrim('90100111000991110224323','109') from dual
--結果
--224323

--------------------------------------------------------------

--11.SUBSTR(string,start,count)取字串,從start開始,取count
select substr('123456789',3,2) As sSubstr from dual
--結果
--sSubstr
--34

--12.REPLACE('string','s1','s2')
--string   被取代的字串或變數 s1 被取代的字串 s2 要取代的字串
select replace('This is test word','test','prod') As sResult from dual
--結果
--sResult
--This is prod word

--13.SOUNDEX=>回傳與參數的字串讀音相同的字串
--下列三者讀音都相同所以回傳的值都一樣
select soundex('two') As t1, soundex('too') As t2, soundex('to') Ast3 from dual
select soundex('cap') As c1,soundex('cup') As c2 from dual
--結果
-- 	t1	  t2	  t3
--	T000	T000	T000
--  c1    c2
--  C100  C100

--14.TRIM去除空白或指定字元
select trim(' as ') As t1 from dual
select ltrim('@as','@') As t1 from dual
select ltrim(' as') As t1 from dual
select rtrim('as@','@') As t1 from dual
select rtrim('as ') As t1 from dual
--結果
--t1
--as
--as
--as
--as

--15.ABS 回傳指定值的絕對值
select abs(100) AS a1, abs(-100)As a2 from dual
--結果
-- a1  a2
-- 100 100

--16.ACOS=>反餘弦的值(oracle沒有提供內建的弧度和角度的轉換函數)
select ACOS(-1) pi,ACOS(1) ZERO FROM dual
--結果
--pi         ZERO
--3.1415927  0

--17.ASIN=>反正玄函數，返回-1到1，弧度
select asin(0.5),asin(1),asin(-1) from dual
--結果
--ASIN(.5)	        ASIN(1)	        ASIN(-1)
--0.523598775598299	1.5707963267949	-1.5707963267949

--18.ATAN=>反正切函數
select atan(1) from dual
--結果
--ATAN(1)
--0.78539816

--19.CEIL=>返回大於或等於輸入數字的最小整數
select ceil(93.1541) from dual
--結果
--CEIL(93.1541)
--94

--20.COS=>返回輸入數字的餘弦
select cos(-3.1415927) from dual
-- 結果
-- COS(-3.1415927)
-- -1

--------------------------------------------------------------

--21.COSH=>返回輸入數字反餘弦值
select cosh(20) from dual
--結果
--COSH(20)
--242582597.704895


--22.EXP=>返回輸入數字e的n次方根
select exp(2),exp(1) from dual
--結果
--EXP(2)    EXP(1)
--7.3890561 2.7182818


--23.FLOOR=>輸入數字取整數
select floor(2345.67) from dual;
--結果
--FLOOR(2345.67)
--2345

--24.LN=>返回輸入數字的自然對數
select ln(1),ln(2),ln(2.7182818) from dual
--結果
--LN(1)  LN(2)              LN(2.7182818)
--0      0.693147180559945  0.999999989530502


--25.LOG(n1,n2)=>返回以n1為底n2的對數
select log(2,1),log(2,4) from dual
--結果
--LOG(2,1)  LOG(2,4)
--0         2


--26.MOD(n1,n2)=>返回n1除以n2的餘數
select mod(10,3),mod(3,3),mod(2,3) from dual
--結果
--MOD(10,3)  MOD(3,3)  MOD(2,3)
--1         0         2


--27.POWER=>返回n1的n2次方根
select power(2,15),power(3,3) from dual
--結果
--POWER(2,50) POWER(3,3)
--32768         27


--28.ROUND和TRUNC=>按照指定的精度進行捨入
select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual
select ROUND(12355,-2),ROUND(12345.54521,2), trunc(12355,-2),trunc(12345.54521,2) FROM dual
--結果
--ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
--56          -55          55          -55
--12400       12345.55	   12300	     12345.54

--29.SIGN=>取數字n的符號,大於0返回1,小於0返回-1,等於0返回0
select sign(123),sign(-100),sign(0) from dual
--結果
--SIGN(123) SIGN(-100)   SIGN(0)
--1         -1           0


--30.SIN=>返回輸入數字的正弦值
select sin(1.57079) from dual
--結果
--SIN(1.57079)
--1

--------------------------------------------------------------

--31.SIGH=>返回雙曲正弦的值
select sin(20),sinh(20) from dual
--結果
--SIN(20)   SINH(20)
--.91294525 242582598

--32.SQRT=>返回數字n的根
select sqrt(64),sqrt(10) from dual
--結果
--SQRT(64)  SQRT(10)
--      8   3.1622777

--33.TAN=>返回數字的正切值
select tan(20),tan(10) from dual
--結果
--TAN(20)    TAN(10)
--2.2371609  .64836083

--34.TANH=>返回數字n的雙曲正切值
select tanh(20),tan(20) from dual
--結果
--TANH(20)   TAN(20)
        1    2.2371609
 
--35.TRUNC=>按照指定的精度截取一個數
select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual
--結果
--TRUNC1   TRUNC(124.16666,2)
--   100   124.16

--36.ADD_MONTHS=>增加或減去月份
select to_char(add_months(to_date('201006','yyyymm'),2),'yyyymm') from dual
--TO_CHAR
--201008
select to_char(add_months(to_date('201006','yyyymm'),-2),'yyyymm') from dual
--TO_CHA
--201004

--37.LAST_DAY=>返回日期的最後一天
select last_day(sysdate) strLastDate from dual
--結果
--strLastDate
--2010/7/31 下午 02:48:23

--38.MONTHS_BETWEEN(date2,date1)=>date2-date1的月份
select months_between('06-7月-2010','06-5月-2010') mon_between from dual
--結果
--MON_BETWEEN
--         2
select months_between(to_date('2009.05.20','yyyy.mm.dd'),to_date('2010.05.20','yyyy.mm.dd')) mon_betw from dual
--結果
--MON_BETW
--     -12

--39.NEW_TIME(date,'this','that')=>在this時區=other時區的日期和時間
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
    (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual
--結果
--BJ_TIME               LOS_ANGLES
--2010.07.06 14:53:32   2010.07.06 21:53:32

--40.NEXT_DAY(date,'day')=>日期date和星期x之後計算下一個星期x的日期
select next_day('6-7月-2010','星期二') next_day from dual
--結果
--NEXT_DAY
--2010/7/13 

--------------------------------------------------------------

--41.SYSDATE=>用來得到系統的當前日期及星期x
select to_char(sysdate,'dd-mm-yyyy day') strToday from dual
--結果
--strToday
--06-07-2010 星期二

--42.trunc(date,fmt) 按照給出的要求將日期截斷,如果fmt='mi'表示保留分,截斷秒
select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
       to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual
--結果
--HH                    HHMM
--2010.07.06 14:00:00   2010.07.06 14:58:00

--43.CHARTOROWID=>將字串類型轉換為ROWID類型
select rowid,rowidtochar(rowid),ENAME from usertable
--結果
--ROWID              ROWIDTOCHAR(ROWID) ENAME
--AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
--AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

--44.CONVERT(c,dset,sset)=>將原字串 sset從一個語言字符集轉換到另一個目的dset字符集
select convert('strutz','we8hp','f7dec') "conversion" from dual
--結果
--conversion
--strutz

--45.HEXTORAW=>將一個十六進位構成的字串轉換為二進位
--46.RAWTOHEXT=>將一個二進餒構成的字串轉換為十六進位 
--47.ROWIDTOCHAR=>將ROWID數據類型轉換為字符類型

--48.TO_CHAR(date,'format')=>將日期轉為字串
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') strTOCHAR from dual
--結果
--strTOCHAR
--2010/07/06 15:11:27
 
--49.TO_DATE(string,'format')將字串轉為日期
select to_date('20100706','YYYYMMDD') strTODATE from dual
--結果
--strTODATE
--2010/7/6

--50.TO_MULTI_BYTE=>將字串中的單字節字轉化為多字節字
select to_multi_byte('高') As tomultibyte from dual
--結果
--tomultibyte
--高

--------------------------------------------------------------

--51.TO_NUMBER=>將輸入的字串轉換為數字
select to_number('1999') year from dual
--結果
--year
--1999

--52.BFILENAME(dir,file)=>指定一個外部二進制文件
insert into test_tbl values(bfilename('lob_dir1','image1.gif'))

--53.CONVERT('x','desc','source')=>將x字段或變量的source轉換為desc
SELECT CONVERT('GroB','US7ASCII','WE8HP') FROM dual

--54.DUMP(s,fmt,start,length)、EMPTY_BLOB()、EMPTY_CLOB()
--PS：沒用到不會解釋

--55.GREATEST=>返回一組表達式中的最大值,比較字串的編碼大小.
select greatest('AA','AB','AC') from dual
--結果
--GR
--AC
select greatest('啊','安','天') from dual
--結果
--GR
--啊

--56.LEAST=>返回一組表達式中的最小值
select least('啊','安','天') from dual
--結果
--LE
--天

--57.USER=>返回目前用戶的名字
select user from  dual
--結果
--USER
--xxxx


--58.USEREVN=>返回當前用戶環境的信息,opt可以是:
--ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
--ISDBA  查看當前用戶是否是DBA如果是則返回true
SQL> select userenv('isdba') from dual;
--結果
--USEREN
--FALSE

--59.AVG(DISTINCT|ALL)=>all表示對所有的值求平均值,distinct只對不同的值求平均值
select avg(distinct img11) from img_file
--結果
--AVG(DISTINCTSAL)
--27657.5592840114
select avg(all img11) from img_file
--結果
--AVG(ALLSAL)
--4540.0510831344

--60.MAX(DISTINCT|ALL)=>求最大值,ALL表示對所有的值求最大值,DISTINCT 表示對不同的值求最大值,相同的只取一次
select MAX(distinct img11) from img_file
--MAX(DISTINCTSAL)
--        4800000

--------------------------------------------------------------

--61.MIN(DISTINCT|ALL)=>求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
select MIN(distinct img11) from img_file
--結果
--MIN(ALLSAL)
--    -18106

--62.STDDEV(distinct|all)=>求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差
select stddev(img11) from img_file
--結果
--  STDDEV(SAL)
--  23598.901717834

--63.VARIANCE(DISTINCT|ALL)=>求協方差
select variance(img11) from img_file
--結果
--VARIANCE(SAL)
--556908162.287986
       
--64.NVL(expr1, expr2) 拿expr1來判斷，是null就用expr2取代
--65.NVL2(expr1, expr2, expr3) 拿expr1判斷，不是null填expr2，是null填expr3
--66.NULLIF(expr1, expr2) 如果1=2就塞null，不相等就填1
--67.COALESCE (expr1， expr2,…,exprn) 從1開始找，不是null就回傳該值
--68.Decode(條件,值1,回傳值1,值2,回傳值2,...值n,回傳值n,ELSE回傳值)
select decode(10,10,1,2) As strResult from dual
--結果
--1