|
|
2008年12月14日
轉(zhuǎn)載自：http://wangyu.javaeye.com/blog/192292
感于總有些網(wǎng)友提出一些非常基礎(chǔ)的問(wèn)題，比如有沒(méi)有實(shí)現(xiàn)某某功能的函數(shù)啊，某某函數(shù)是做什么用的啊，格式是什么等等，同時(shí)也感受到自己對(duì)oracle函數(shù)認(rèn)識(shí)的不足，于是集中月余時(shí)間專(zhuān)注于oracle函數(shù)，小有心得不敢私藏，發(fā)之與諸公共享。
本文并不準(zhǔn)備介紹全部的oracle函數(shù)，當(dāng)前情勢(shì)下，俺也還沒(méi)這個(gè)時(shí)間，需要學(xué)習(xí)的東西太多了，要把多數(shù)時(shí)間花在學(xué)習(xí)經(jīng)常能用上的技術(shù)方面:)，所以如果是準(zhǔn)備深入了解所有oracle函數(shù)的朋友，還是去關(guān)注：Oracle SQL Reference官方文檔更靠譜一些。
本文更多將會(huì)介紹三思在日常中經(jīng)常會(huì)用到的，或者雖然很少用到，但是感覺(jué)挺有意思的一些函數(shù)。分二類(lèi)介紹，分別是：
著名函數(shù)篇 －經(jīng)常用到的函數(shù)
非著名函數(shù)篇－即雖然很少用到，但某些情況下卻很實(shí)用
注：N表示數(shù)字型，C表示字符型，D表示日期型，[]表示內(nèi)中參數(shù)可被忽略，fmt表示格式。
單值函數(shù)在查詢(xún)中返回單個(gè)值，可被應(yīng)用到select，where子句，start with以及connect by 子句和having子句。
(一).數(shù)值型函數(shù)(Number Functions)
數(shù)值型函數(shù)輸入數(shù)字型參數(shù)并返回?cái)?shù)值型的值。多數(shù)該類(lèi)函數(shù)的返回值支持38位小數(shù)點(diǎn)，諸如：COS, COSH, EXP, LN, LOG,
SIN, SINH, SQRT, TAN, and TANH 支持36位小數(shù)點(diǎn)。ACOS, ASIN, ATAN, and
ATAN2支持30位小數(shù)點(diǎn)。
1、MOD(n1,n2) 返回n1除n2的余數(shù)，如果n2=0則返回n1的值。
例如：SELECT MOD(24,5) FROM DUAL;
2、
ROUND(n1[,n2])
返回四舍五入小數(shù)點(diǎn)右邊n2位后n1的值，n2缺省值為0，如果n2為負(fù)數(shù)就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上(雖然oracle
documents上提到n2的值必須為整數(shù)，事實(shí)上執(zhí)行時(shí)此處的判斷并不嚴(yán)謹(jǐn)，即使n2為非整數(shù)，它也會(huì)自動(dòng)將n2取整后做處理，但是我文檔中其它提到必須為整的地方需要特別注意，如果不為整執(zhí)行時(shí)會(huì)報(bào)錯(cuò)的)。
例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小數(shù)的n1的值，n2缺省設(shè)置為0，當(dāng)n2為缺省設(shè)置時(shí)會(huì)將n1截尾為整數(shù)，如果n2為負(fù)值，就截尾在小數(shù)點(diǎn)左邊相應(yīng)的位上。
例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
(二).字符型函數(shù)返回字符值(Character Functions Returning Character Values)
該類(lèi)函數(shù)返回與輸入類(lèi)型相同的類(lèi)型。
返回的CHAR類(lèi)型值長(zhǎng)度不超過(guò)2000字節(jié)；
返回的VCHAR2類(lèi)型值長(zhǎng)度不超過(guò)4000字節(jié)；
如果上述應(yīng)返回的字符長(zhǎng)度超出，oracle并不會(huì)報(bào)錯(cuò)而是直接截?cái)嘀磷畲罂芍С珠L(zhǎng)度返回。
返回的CLOB類(lèi)型值長(zhǎng)度不超過(guò)4G；
對(duì)于CLOB類(lèi)型的函數(shù)，如果返回值長(zhǎng)度超出，oracle不會(huì)返回任何錯(cuò)誤而是直接拋出錯(cuò)誤。
1、LOWER(c) 將指定字符串內(nèi)字符變?yōu)樾?xiě)，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類(lèi)型
例如：SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 將指定字符串內(nèi)字符變?yōu)榇髮?xiě)，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類(lèi)型
例如：SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定長(zhǎng)度=n的字符串，需要注意的有幾點(diǎn)：
如果n<c1.length則從右到左截取指定長(zhǎng)度返回；
如果n>c1.length and c2 is null，以空格從左向右補(bǔ)充字符長(zhǎng)度至n并返回；
如果n>c1.length and c2 is not null，以指定字符c2從左向右補(bǔ)充c1長(zhǎng)度至n并返回；
例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
最后大家再猜一猜，如果n<0，結(jié)果會(huì)怎么樣
4、RPAD(c1,n[,c2]) 返回指定長(zhǎng)度=n的字符串，基本與上同，不過(guò)補(bǔ)充字符是從右向左方向正好與上相反；
例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺無(wú)敵的形容方式搞暈頭了吧，這個(gè)地方還是看圖更明了一些。
看起來(lái)很復(fù)雜，理解起來(lái)很簡(jiǎn)單：
如果沒(méi)有指定任何參數(shù)則oracle去除c1頭尾空格
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
如果指定了c2參數(shù)，則oracle去掉c1頭尾c2(這個(gè)建議細(xì)致測(cè)試，有多種不同情形的喲)
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了leading參數(shù)則會(huì)去掉c1頭部c2
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了trailing參數(shù)則會(huì)去掉c1尾部c2
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了both參數(shù)則會(huì)去掉c1頭尾c2(跟不指定有區(qū)別嗎？沒(méi)區(qū)別！)
例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意：c2長(zhǎng)度=1
6、LTRIM(c1[,c2]) 千萬(wàn)表以為與上面那個(gè)長(zhǎng)的像，功能也與上面的類(lèi)似，本函數(shù)是從字符串c1左側(cè)截取掉與指定字符串c2相同的字符并返回。如果c2為空則默認(rèn)截取空格。
例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2)與上同，不過(guò)方向相反
例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) 將c1字符串中的c2替換為c3，如果c3為空，則從c1中刪除所有c2。
例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
9、SOUNDEX(c) 神奇的函數(shù)啊，該函數(shù)返回字符串參數(shù)的語(yǔ)音表示形式，對(duì)于比較一些讀音相同，但是拼寫(xiě)不同的單詞非常有用。計(jì)算語(yǔ)音的算法如下：
保留字符串首字母，但刪除a、e、h、i、o、w、y。
將下表中的數(shù)字賦給相對(duì)應(yīng)的字母：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：R
如果字符串中存在擁有相同數(shù)字的2個(gè)以上（包含2個(gè)）的字母在一起（例如b和f），或者只有h或w，則刪除其他的，只保留1個(gè)；
只返回前4個(gè)字節(jié)，不夠用0填充
例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定長(zhǎng)度的字符串。稍不注意就可能充滿(mǎn)了陷阱的函數(shù)。
n1=開(kāi)始長(zhǎng)度；
n2=截取的字符串長(zhǎng)度，如果為空，默認(rèn)截取到字符串結(jié)尾；
? 如果n1=0 then n1=1
? 如果n1>0，則oracle從左向右確認(rèn)起始位置截取
例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;
? 如果n1<0，則oracle從右向左數(shù)確認(rèn)起始位置
例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;
? 如果n1>c1.length則返回空
例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;
然后再請(qǐng)你猜猜，如果n2<1，會(huì)如何返回值呢
11、
TRANSLATE(c1,c2,c3)
就功能而言，此函數(shù)與replace有些相似。但需要注意的一點(diǎn)是，translate是絕對(duì)匹配替換，這點(diǎn)與replace函數(shù)具有非常大區(qū)別。什么是絕對(duì)匹配替換呢？簡(jiǎn)單的說(shuō)，是將字符串c1中按一定的格式c2替換為c3。如果文字形容仍然無(wú)法理解，我們通過(guò)幾具實(shí)例來(lái)說(shuō)明：
例如：
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
結(jié)果都是空。來(lái)試試這個(gè)：
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再來(lái)看這個(gè)：
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了點(diǎn)呢？Replace函數(shù)理解比較簡(jiǎn)單，它是將字符串中指定字符替換成其它字符，它的字符必須是連續(xù)的。而translate中，則是指定字符串
c1中出現(xiàn)的c2，將c2中各個(gè)字符替換成c3中位置順序與其相同的c3中的字符。明白了？Replace是替換，而translate則像是過(guò)濾
(三).字符型函數(shù)返回?cái)?shù)字值(Character Functions Returning Number Values)
本類(lèi)函數(shù)支持所有的數(shù)據(jù)類(lèi)型
1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
c1:原字符串
c2:要尋找的字符串
n1:查詢(xún)起始位置，正值表示從左到右，負(fù)值表示從右到左 (大小表示位置,比如3表示左面第3處開(kāi)始,-3表示右面第3處開(kāi)始)。黑黑，如果為0的話(huà)，則返回的也是0
n2:第幾個(gè)匹配項(xiàng)。大于0
例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;
2、LENGTH(c) 返回指定字符串的長(zhǎng)度。如果
例如：SELECT LENGTH('A123中') FROM DUAL;
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么
(四).日期函數(shù)(Datetime Functions)
本類(lèi)函數(shù)中，除months_between返回?cái)?shù)值外，其它都將返回日期。
1、ADD_MONTHS() 返回指定日期月份+n之后的值，n可以為任何整數(shù)。
例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間
例如：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、
SYSDATE
功能與上相同，返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間。但是需要注意的一點(diǎn)是，如果同時(shí)使用sysdate與current_date獲得的時(shí)間不一
定相同，某些情況下current_date會(huì)比sysdate快一秒。經(jīng)過(guò)與xyf_tck(兄臺(tái)的大作ORACLE的工作機(jī)制寫(xiě)的很好，深入淺出)的
短暫交流，我們認(rèn)為current_date是將current_timestamp中毫秒四舍五入后的返回，雖然沒(méi)有找到文檔支持，但是想來(lái)應(yīng)該八九不
離十。同時(shí)，僅是某些情況下會(huì)有一秒的誤差，一般情況下并不會(huì)對(duì)你的操作造成影響，所以了解即可。
例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d) 返回指定時(shí)間所在月的最后一天
例如：SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) 返回指定日期后第一個(gè)n的日期，n為一周中的某一天。但是，需要注意的是n如果為字符的話(huà)，它的星期形式需要與當(dāng)前session默認(rèn)時(shí)區(qū)中的星期形式相同。
例如：三思用的中文nt，nls_language值為SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
兩種方式都可以取到正確的返回，但是：
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
則會(huì)執(zhí)行出錯(cuò)，提供你說(shuō)周中的日無(wú)效，就是這個(gè)原因了。
6、MONTHS_BETWEEN(d1,d2) 返回d1與d2間的月份差，視d1,d2的值大小，結(jié)果可正可負(fù)，當(dāng)然也有可能為0
例如：
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;
7、ROUND(d[,fmt]) 前面講數(shù)值型函數(shù)的時(shí)候介紹過(guò)ROUND，此處與上功能基本相似，不過(guò)此處操作的是日期。如果不指定fmt參數(shù)，則默認(rèn)返回距離指定日期最近的日期。
例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) 與前面介紹的數(shù)值型TRUNC原理相同，不過(guò)此處也是操作的日期型。
例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
(五).轉(zhuǎn)換函數(shù)(Conversion Functions)
轉(zhuǎn)換函數(shù)將指定字符從一種類(lèi)型轉(zhuǎn)換為另一種，通常這類(lèi)函數(shù)遵循如下慣例：函數(shù)名稱(chēng)后面跟著待轉(zhuǎn)換類(lèi)型以及輸出類(lèi)型。
1、TO_CHAR() 本函數(shù)又可以分三小類(lèi)，分別是
轉(zhuǎn)換字符->字符TO_CHAR(c)：將nchar,nvarchar2,clob,nclob類(lèi)型轉(zhuǎn)換為char類(lèi)型；
例如：SELECT TO_CHAR('AABBCC') FROM DUAL;
轉(zhuǎn)換時(shí)間->字符TO_CHAR(d[,fmt])：將指定的時(shí)間(data,timestamp,timestamp with time zone)按照指定格式轉(zhuǎn)換為varchar2類(lèi)型；
例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
轉(zhuǎn)換數(shù)值->字符TO_CHAR(n[,fmt])：將指定數(shù)值n按照指定格式fmt轉(zhuǎn)換為varchar2類(lèi)型并返回；
例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、
TO_DATE(c[,fmt[,nls]])
將char,nchar,varchar2,nvarchar2轉(zhuǎn)換為日期類(lèi)型，如果fmt參數(shù)不為空，則按照f(shuō)mt中指定格式進(jìn)行轉(zhuǎn)換。注意這里的
fmt參數(shù)。如果ftm為'J'則表示按照公元制(Julian day)轉(zhuǎn)換，c則必須為大于0并小于5373484的正整數(shù)。
例如：
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
為什么公元制的話(huà)，c的值必須不大于5373484呢？因?yàn)镺racle的DATE類(lèi)型的取值范圍是公元前4712年1月1日至公元9999年12月31日。看看下面這個(gè)語(yǔ)句：
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2型字串按照f(shuō)mt中指定格式轉(zhuǎn)換為數(shù)值類(lèi)型并返回。
例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它輔助函數(shù)(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一個(gè)增強(qiáng)型的if else，只不過(guò)它并不通過(guò)多行語(yǔ)句，而是在一個(gè)函數(shù)內(nèi)實(shí)現(xiàn)if else的功能。
exp做為初始參數(shù)。s做為對(duì)比值，相同則返回r，如果s有多個(gè)，則持續(xù)遍歷所有s，直到某個(gè)條件為真為止，否則返回默認(rèn)值def(如果指定了的話(huà))，如果沒(méi)有默認(rèn)值，并且前面的對(duì)比也都沒(méi)有為真，則返回空。
毫無(wú)疑問(wèn)，decode是個(gè)非常重要的函數(shù)，在實(shí)現(xiàn)行轉(zhuǎn)列等功能時(shí)都會(huì)用到，需要牢記和熟練使用。
例如：select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST(n1,n2....n) 返回序列中的最小值
例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2)
Nullif也是個(gè)很有意思的函數(shù)。邏輯等價(jià)于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) 邏輯等價(jià)于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何類(lèi)型。如果兩者類(lèi)型不同，則oracle會(huì)自動(dòng)將c2轉(zhuǎn)換為c1的類(lèi)型。
例如：SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用過(guò)nvl2嗎？如果c1非空則返回c2,如果c1為空則返回c3
例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 該函數(shù)只能應(yīng)用于樹(shù)狀查詢(xún)。返回通過(guò)c1連接的從根到節(jié)點(diǎn)的路徑。該函數(shù)必須與connect by 子句共同使用。
例如：
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);
insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;
8、SYS_CONTEXT(c1,c2[,n]) 將指定命名空間c1的指定參數(shù)c2的值按照指定長(zhǎng)度n截取后返回。
Oracle9i提供內(nèi)置了一個(gè)命名空間USERENV，描述了當(dāng)前session的各項(xiàng)信息，其擁有下列參數(shù)：
? CURRENT_SCHEMA:當(dāng)前模式名；
? CURRENT_USER:當(dāng)前用戶(hù)；
? IP_ADDRESS:當(dāng)前客戶(hù)端IP地址；
? OS_USER:當(dāng)前客戶(hù)端操作系統(tǒng)用戶(hù)；
等等數(shù)十項(xiàng)，更詳細(xì)的參數(shù)列還請(qǐng)大家直接參考Oracle Online Documents
例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注：N表示數(shù)字型，C表示字符型，D表示日期型，[]表示內(nèi)中參數(shù)可被忽略，fmt表示格式。
單值函數(shù)在查詢(xún)中返回單個(gè)值，可被應(yīng)用到select，where子句，start with以及connect by 子句和having子句。
(一).數(shù)值型函數(shù)(Number Functions)
數(shù)
值型函數(shù)輸入數(shù)字型參數(shù)并返回?cái)?shù)值型的值。多數(shù)該類(lèi)函數(shù)的返回值支持38位小數(shù)點(diǎn)，諸如：COS, COSH, EXP, LN, LOG, SIN,
SINH, SQRT, TAN, and TANH 支持36位小數(shù)點(diǎn)。ACOS, ASIN, ATAN, and ATAN2支持30位小數(shù)點(diǎn)。
1、ABS(n) 返回?cái)?shù)字的絕對(duì)值
例如：SELECT ABS(-1000000.01) FROM DUAL;
2、COS(n) 返回n的余弦值
例如：SELECT COS(-2) FROM DUAL;
3、ACOS(n) 反余弦函數(shù)，n between -1 and 1，返回值between 0 and pi。
例如：SELECT ACOS(0.9) FROM DUAL;
4、BITAND(n1,n2) 位與運(yùn)算，這個(gè)太有意思了，雖然沒(méi)想到可能用到哪里，詳細(xì)說(shuō)明一下：
假設(shè)3,9做位與運(yùn)算，3的二進(jìn)制形式為：0011，9的二進(jìn)制形式為：1001，則結(jié)果是0001，轉(zhuǎn)換成10進(jìn)制數(shù)為1。
例如：SELECT BITAND(3,9) FROM DUAL;
5、CEIL(n) 返回大于或等于n的最小的整數(shù)值
例如：SELECT ceil(18.2) FROM DUAL;
考你一下，猜猜ceil(-18.2)的值會(huì)是什么呢
6、FLOOR(n) 返回小于等于n的最大整數(shù)值
例如：SELECT FLOOR(2.2) FROM DUAL;
再猜猜floor(-2.2)的值會(huì)是什么呢
7、BIN_TO_NUM(n1,n2,....n) 二進(jìn)制轉(zhuǎn)向十進(jìn)制
例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;
8、SIN(n) 返回n的正玄值,n為弧度。
例如：SELECT SIN(10) FROM DUAL;
9、SINH(n) 返回n的雙曲正玄值,n為弧度。
例如：SELECT SINH(10) FROM DUAL;
10、ASIN(n) 反正玄函數(shù)，n between -1 and 1，返回值between pi/2 and -pi/2。
例如：SELECT ASIN(0.8) FROM DUAL;
11、TAN(n) 返回n的正切值,n為弧度
例如：SELECT TAN(0.8) FROM DUAL;
12、TANH(n) 返回n的雙曲正切值,n為弧度
例如：SELECT TANH(0.8) FROM DUAL;
13、ATAN(n) 反正切函數(shù)，n表示弧度，返回值between pi/2 and -pi/2。
例如：SELECT ATAN(-444444.9999999) FROM DUAL;
14、EXP(n) 返回e的n次冪，e = 2.71828183 ...
例如：SELECT EXP(3) FROM DUAL;
15、LN(n) 返回n的自然對(duì)數(shù)，n>0
例如：SELECT LN(0.9) FROM DUAL;
16、LOG(n1,n2) 返回以n1為底n2的對(duì)數(shù)，n1 >0 and not 1 ，n2>0
例如：SELECT LOG(1.1,2.2) FROM DUAL;
17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以為任意數(shù)值，不過(guò)如果m是負(fù)數(shù)，則n必須為整數(shù)
例如：SELECT POWER(2.2,2.2) FROM DUAL;
18、SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.
例如：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;
19、SQRT(n) 返回n的平方根,n為弧度。n>=0
例如：SELECT SQRT(0.1) FROM DUAL;
(二).字符型函數(shù)返回字符值(Character Functions Returning Character Values)
該類(lèi)函數(shù)返回與輸入類(lèi)型相同的類(lèi)型。
? 返回的CHAR類(lèi)型值長(zhǎng)度不超過(guò)2000字節(jié)；
? 返回的VCHAR2類(lèi)型值長(zhǎng)度不超過(guò)4000字節(jié)；
如果上述應(yīng)返回的字符長(zhǎng)度超出，oracle并不會(huì)報(bào)錯(cuò)而是直接截?cái)嘀磷畲罂芍С珠L(zhǎng)度返回。
? 返回的CLOB類(lèi)型值長(zhǎng)度不超過(guò)4G；
對(duì)于CLOB類(lèi)型的函數(shù)，如果返回值長(zhǎng)度超出，oracle不會(huì)返回任何錯(cuò)誤而是直接拋出錯(cuò)誤。
1、CHR(N[ USING NCHAR_CS]) 返回指定數(shù)值在當(dāng)前字符集中對(duì)應(yīng)的字符
例如：SELECT CHR(95) FROM DUAL;
2、CONCAT(c1,c2) 連接字符串，等同于||
例如：SELECT concat('aa','bb') FROM DUAL;
3、INITCAP(c) 將字符串中單詞的第一個(gè)字母轉(zhuǎn)換為大寫(xiě)，其它則轉(zhuǎn)換為小寫(xiě)
例如：SELECT INITCAP('whaT is this') FROM DUAL;
4、NLS_INITCAP(c) 返回指定字符串，并將字符串中第一個(gè)字母變大寫(xiě)，其它字母變小寫(xiě)
例如：SELECT NLS_INITCAP('中華miNZHu') FROM DUAL;
它還具有一個(gè)參數(shù)：Nlsparam用來(lái)指定排序規(guī)則，可以忽略，默認(rèn)狀態(tài)該參數(shù)為當(dāng)前session的排序規(guī)則。
(三).字符型函數(shù)返回?cái)?shù)字值(Character Functions Returning Number Values)
本類(lèi)函數(shù)支持所有的數(shù)據(jù)類(lèi)型
1、ASCII(c) 與chr函數(shù)的用途剛剛相反，本函數(shù)返回指定字符在當(dāng)前字符集下對(duì)應(yīng)的數(shù)值。
例如：SELECT ASCII('_') FROM DUAL;
(四).日期函數(shù)(Datetime Functions)
本類(lèi)函數(shù)中，除months_between返回?cái)?shù)值外，其它都將返回日期。
1、CURRENT_TIMESTAMP([n]) 返回當(dāng)前session所在時(shí)區(qū)的日期和時(shí)間。n表示毫秒級(jí)的精度，不大于6
例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
2、LOCALTIMESTAMP([n]) 與上同，返回當(dāng)前session所在時(shí)區(qū)的日期和時(shí)間。n表示毫秒級(jí)的精度，不大于6
例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;
3、SYSTIMESTAMP([n]) 與上同，返回當(dāng)前數(shù)據(jù)庫(kù)所在時(shí)區(qū)的日期和時(shí)間，n表示毫秒級(jí)的精度，>0 and <6
例如：SELECT SYSTIMESTAMP(4) FROM DUAL;
4、DBTIMEZONE 返回?cái)?shù)據(jù)庫(kù)的當(dāng)前時(shí)區(qū)
例如：SELECT DBTIMEZONE FROM DUAL;
5、SESSIONTIMEZONE 返回當(dāng)前session所在時(shí)區(qū)
例如：SELECT SESSIONTIMEZONE FROM DUAL;
6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 從指定時(shí)間提到指定日期列
例如：SELECT EXTRACT(year from sysdate) FROM DUAL;
7、TO_TIMESTAMP(c1[,fmt]) 將指定字符按指定格式轉(zhuǎn)換為timestamp格式。
例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
(五).轉(zhuǎn)換函數(shù)(Conversion Functions)
轉(zhuǎn)換函數(shù)將指定字符從一種類(lèi)型轉(zhuǎn)換為另一種，通常這類(lèi)函數(shù)遵循如下慣例：函數(shù)名稱(chēng)后面跟著待轉(zhuǎn)換類(lèi)型以及輸出類(lèi)型。
1、BIN_TO_NUM(n1,n2...n) 將一組位向量轉(zhuǎn)換為等價(jià)的十進(jìn)制形式。
例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
2、CAST(c as newtype) 將指定字串轉(zhuǎn)換為指定類(lèi)型，基本只對(duì)字符類(lèi)型有效，比如char,number,date,rowid等。此類(lèi)轉(zhuǎn)換有一個(gè)專(zhuān)門(mén)的表列明了哪種類(lèi)型可以轉(zhuǎn)換為哪種類(lèi)型，此處就不作酹述。
例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
3、CHARTOROWID(c) 將字符串轉(zhuǎn)換為rowid類(lèi)型
例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
4、ROWIDTOCHAR(rowid) 轉(zhuǎn)換rowid值為varchar2類(lèi)型。返回串長(zhǎng)度為18個(gè)字節(jié)。
例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;
5、TO_MULTI_BYTE(c) 將指定字符轉(zhuǎn)換為全角并返回char類(lèi)型字串
例如：SELECT TO_MULTI_BYTE('ABC abc 中華') FROM DUAL;
6、TO_SINGLE_BYTE(c) 將指定字符轉(zhuǎn)換為半角并返回char類(lèi)型字串
例如：SELECT TO_SINGLE_BYTE('ＡＢＣ ａｂｃ中華') FROM DUAL;
(六).其它輔助函數(shù)(Miscellaneous Single-Row Functions)
1、COALESCE(n1,n2,....n) 返回序列中的第一個(gè)非空值
例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;
2、DUMP(exp[,fmt[,start[,length]]])
dump是個(gè)功能非常強(qiáng)悍的函數(shù)，對(duì)于深入了解oracle存儲(chǔ)的人而言相當(dāng)有用。所以對(duì)于我們這些僅僅只是應(yīng)用的人而言就不知道能將其應(yīng)用于何處了。此處僅介紹用法，不對(duì)其功能做深入分析。
如上所示，dump擁有不少參數(shù)。其本質(zhì)是以指定格式，返回指定長(zhǎng)度的exp的內(nèi)部表示形式的varchar2值。fmt含4種格
式：8||10||16||17，分別表示8進(jìn)制，10進(jìn)制，16進(jìn)制和單字符，默認(rèn)為10進(jìn)制。start參數(shù)表示開(kāi)始位置，length表示以,分隔
的字串?dāng)?shù)。
例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;
3、EMPTY_BLOB,EMPTY_CLOB 這兩個(gè)函數(shù)都是返回空l(shuí)ob類(lèi)型，通常被用于insert和update等語(yǔ)句以初始化lob列，或者將其置為空。EMPTY表示LOB已經(jīng)被初始化，只不過(guò)還沒(méi)有用來(lái)存儲(chǔ)數(shù)據(jù)。
4、NLS_CHARSET_NAME(n) 返回指定數(shù)值對(duì)應(yīng)的字符集名稱(chēng)。
例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;
5、NLS_CHARSET_ID(c) 返回指定字符對(duì)應(yīng)的字符集id。
例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一個(gè)NCHAR值的聲明寬度(以字符為單位).n1是該值以字節(jié)為單位的長(zhǎng)度，n2是該值的字符集ID
例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;
7、SYS_EXTRACT_UTC(timestamp) 返回標(biāo)準(zhǔn)通用時(shí)間即格林威治時(shí)間。
例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;
8、SYS_TYPEID(object_type) 返回對(duì)象類(lèi)型對(duì)應(yīng)的id。
例如：這個(gè)這個(gè)，沒(méi)有建立過(guò)自定義對(duì)象，咋做示例？
9、UID 返回一個(gè)唯一標(biāo)識(shí)當(dāng)前數(shù)據(jù)庫(kù)用戶(hù)的整數(shù)。
例如：SELECT UID FROM DUAL;
10、USER 返回當(dāng)前session用戶(hù)
例如：SELECT USER FROM DUAL;
11、USERENV(c) 該函數(shù)用來(lái)返回當(dāng)前session的信息，據(jù)oracle文檔的說(shuō)明，userenv是為了保持向下兼容的遺留函數(shù)。oracle公司推薦你使用sys_context函數(shù)調(diào)用USERENV命名空間來(lái)獲取相關(guān)信息，所以大家了解下就行了。
例如：SELECT USERENV('LANGUAGE') FROM DUAL;
12、VSIZE(c) 返回c的字節(jié)數(shù)。
例如：SELECT VSIZE('abc中華') FROM DUAL;
把字符串賦值給日期型，解決：先把字符串轉(zhuǎn)換成日期型再賦值如：where c.pbeg_date >= to_date('2007-11-12','yyyy
- MM - DD') and c.pbeg_date <= to_date('2007-12-12','yyyy -MM - DD')
收集的關(guān)于ERP論壇的站點(diǎn)。
摘要: Oracle中的NULL既是一個(gè)難點(diǎn)也是一個(gè)重點(diǎn)，如何理解它，以及如何處理它，都關(guān)系到我們查詢(xún)結(jié)果的正確性。 閱讀全文
2008年12月9日
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)
該函數(shù)的含義如下：
IF 條件=值1 THEN
RETURN(翻譯值1)
ELSIF 條件=值2 THEN
RETURN(翻譯值2)
......
ELSIF 條件=值n THEN
RETURN(翻譯值n)
ELSE
RETURN(缺省值)
END IF
· 使用方法：
1、比較大小
select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; --取較小值
sign()函數(shù)根據(jù)某個(gè)值是0、正數(shù)還是負(fù)數(shù)，分別返回0、1、-1
例如：
變量1=10，變量2=20
則sign(變量1-變量2)返回-1，decode解碼結(jié)果為“變量1”，達(dá)到了取較小值的目的。
下面的例子說(shuō)明了，如何讀取用戶(hù)CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的項(xiàng)目，作為DECODE函數(shù)的實(shí)參支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM
checkup;
2008年1月29日
2008年1月16日
剛剛接觸Hibernate不長(zhǎng)時(shí)間，今天想動(dòng)手做一個(gè)小例子，數(shù)據(jù)庫(kù)使用的是Oracle。打算使用Oracle中的序列作為表的主鍵，卻不知道使用Hibernate如何與其關(guān)聯(lián)在一起。請(qǐng)教同事，被告知要使用諸如select語(yǔ)句進(jìn)行查詢(xún)“select sequence.nextval from dual”，暈倒～
終于等到中午，上網(wǎng)去查資料。得解：
1、在oracle 首先創(chuàng)建sequence
 create sequence seq_id
minvalue 1
start with 1
increment by 1
cache 20;
2.在你的hbm.xml中的配置
<id column="ID0000" name="id" type="integer">
<generator class="sequence">
<param name="sequence">seq_id</param>
</generator>
</id>
這樣再插入數(shù)據(jù)的時(shí)候，Hibernate回自動(dòng)生成如下語(yǔ)句：
hibernate: select seq_id.nextval from dual
hibernate: insert into YXJK.T_YXJK_WHRYTXL (XM0000, ZW0000, LXDH00,SJHM00,DZYJ00,IP0000,ID0000) values (?, ?, ?, ?, ?, ?, ?)
自動(dòng)生成下一個(gè)序列值，然后將對(duì)象插入表中。
這樣問(wèn)題得解！
PS：
sequence就是采用數(shù)據(jù)庫(kù)提供的sequence機(jī)制生成主鍵。如oralce中的Sequence
native就是由hibernate根據(jù)數(shù)據(jù)庫(kù)的Dialect，自動(dòng)采用identity，hilo，sequence的其中一種作為主鍵生成方式
2008年1月6日
將此作為第一篇隨筆，謹(jǐn)以自勉！祝愿自己今后一路好走～
程序員每天該做的事
1、總結(jié)自己一天任務(wù)的完成情況
最好的方式是寫(xiě)工作日志，把自己今天完成了什么事情，遇見(jiàn)了什么問(wèn)題都記錄下來(lái)，日后翻看好處多多
2、考慮自己明天應(yīng)該做的主要工作
把明天要做的事情列出來(lái)，并按照優(yōu)先級(jí)排列，第二天應(yīng)該把自己效率最高的時(shí)間分配給最重要的工作
3、考慮自己一天工作中失誤的地方，并想出避免下一次再犯的方法
出錯(cuò)不要緊，最重要的是不要重復(fù)犯相同的錯(cuò)誤，那是愚蠢
4、考慮自己一天工作完成的質(zhì)量和效率能否還能提高
一天只提高1%，365天你的效率就能提高多少倍你知道嗎？ (1+0.01)^365 = 37 倍
5、看一個(gè)有用的新聞網(wǎng)站或讀一張有用的報(bào)紙，了解業(yè)界動(dòng)態(tài)
閉門(mén)造車(chē)是不行的，了解一下別人都在做什么，對(duì)自己能帶來(lái)很多啟示
6、記住一位同事的名字及其特點(diǎn)
你認(rèn)識(shí)公司的所有同事嗎？你了解他們嗎？
7、清理自己的代碼
今天完成的代碼，把中間的調(diào)試信息，測(cè)試代碼清理掉，按照編碼風(fēng)格整理好，注釋都寫(xiě)好了嗎？
8、清理自己的桌面
當(dāng)日事當(dāng)日畢，保持清潔干勁的桌面才能讓你工作時(shí)不分心，程序員特別要把電腦的桌面清理干凈
程序員每月該做的事
1、至少和一個(gè)同事一起吃飯或喝茶
不光了解自己工作伙伴的工作，還要了解他們的生活
2、自我考核一次
相對(duì)正式地考核自己一下，你對(duì)得起這個(gè)月的工資嗎？
3、對(duì)你的同事考核一次
你的同事表現(xiàn)怎么樣？哪些人值得學(xué)習(xí)，哪些人需要幫助？
3、制定下月的計(jì)劃，確定下月的工作重點(diǎn)
4、總結(jié)自己工作質(zhì)量改進(jìn)狀況
自己的質(zhì)量提高了多少？
5、有針對(duì)性地對(duì)一項(xiàng)工作指標(biāo)做深入地分析并得出改進(jìn)的方案
可以是對(duì)自己的，也可以是對(duì)公司的，一定要深入地分析后拿出自己的觀點(diǎn)來(lái)。要想在老板面前說(shuō)得上話(huà)，做的成事，工作上功夫要做足。
6、與老板溝通一次
最好是面對(duì)面地溝通，好好表現(xiàn)一下自己，虛心聽(tīng)取老板的意見(jiàn)，更重要的是要了解老板當(dāng)前關(guān)心的重點(diǎn)
程序員每年該做的事
1、年終總結(jié)
每個(gè)公司都會(huì)做的事情，但你真正認(rèn)真地總結(jié)過(guò)自己?jiǎn)幔?/span>
2、兌現(xiàn)給自己、給家人的承諾
給老婆、兒子的新年禮物買(mǎi)了沒(méi)有？給自己的呢？
3、下年度工作規(guī)劃
好好想想自己明年的發(fā)展目標(biāo)，爭(zhēng)取升職/加薪、跳槽還是自己出來(lái)干？
4、掌握一項(xiàng)新技術(shù)
至少是一項(xiàng)，作為程序員一年要是一項(xiàng)新技術(shù)都學(xué)不到手，那就一定會(huì)被淘汰。
掌握可不是看本書(shū)就行的，要真正懂得應(yīng)用，最好你能夠?qū)懸黄坛贪l(fā)表到你的blog
5、推出一種新產(chǎn)品
可以是一個(gè)真正的產(chǎn)品，也可以只是一個(gè)類(lèi)庫(kù)，只要是你創(chuàng)造的東西就行，讓別人使用它，也為世界作點(diǎn)貢獻(xiàn)。當(dāng)然如果真的很有價(jià)值，收點(diǎn)注冊(cè)費(fèi)也是應(yīng)該的
6、與父母團(tuán)聚一次
常回家看看，常回家看看
|