整理前輩，變成自己。
******************************oracle函式介紹著名函式之單值函式**************************************

ORACLE函式介紹

第一篇 著名函式之單值函式 2007.8.13

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　單值函式在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。

(一).數值型函式(Number Functions)

數值型函式輸入數字型引數並返回數值型的值。多數該類函式的返回值支援38位小數點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支援36位小數點。ACOS, ASIN, ATAN, and ATAN2支援30位小數點。

1、MOD(n1,n2) 返回n1除n2的餘數，如果n2=0則返回n1的值。

例如：SELECT MOD(24,5) FROM DUAL;

2、ROUND(n1[,n2]) 返回四捨五入小數點右邊n2位後n1的值，n2預設值為0，如果n2為負數就舍入到小數點左邊相應的位上(雖然oracle documents上提到n2的值必須為整數，事實上執行時此處的判斷並不嚴謹，即使n2為非整數，它也會自動將n2取整後做處理，但是我文件中其它提到必須為整的地方需要特別注意，如果不為整執行時會報錯的)。

例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;

3、TRUNC(n1[,n2] 返回截尾到n2位小數的n1的值，n2預設設定為0，當n2為預設設定時會將n1截尾為整數，如果n2為負值，就截尾在小數點左邊相應的位上。

例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;

(二).字元型函式返回字元值(Character Functions Returning Character Values)

　　該類函式返回與輸入型別相同的型別。

l 返回的CHAR型別值長度不超過2000位元組；

l 返回的VCHAR2型別值長度不超過4000位元組；

如果上述應返回的字元長度超出，oracle並不會報錯而是直接截斷至最大可支援長度返回。

l 返回的CLOB型別值長度不超過4G；

對於CLOB型別的函式，如果返回值長度超出，oracle不會返回任何錯誤而是直接丟擲錯誤。

1、LOWER(c) 將指定字串內字元變為小寫，支援CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB型別

例如：SELECT LOWER('WhaT is tHis') FROM DUAL;

2、UPPER(c) 將指定字串內字元變為大寫，支援CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB型別

例如：SELECT UPPER('WhaT is tHis') FROM DUAL;

3、LPAD(c1,n[,c2]) 返回指定長度=n的字串，需要注意的有幾點：

l 如果n則從右到左擷取指定長度返回；

l 如果n>c1.length and c2 is null，以空格從左向右補充字元長度至n並返回；

l 如果n>c1.length and c2 is not null，以指定字元c2從左向右補充c1長度至n並返回；

例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;

最後大家再猜一猜，如果n<0，結果會怎麼樣

4、RPAD(c1,n[,c2]) 返回指定長度=n的字串，基本與上同，不過補充字元是從右向左方向正好與上相反；

例如：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) 哈哈，被俺無敵的形容方式搞暈頭了吧，這個地方還是看圖更明瞭一些。

6、LTRIM(c1[,c2]) 千萬表以為與上面那個長的像，功能也與上面的類似，本函式是從字串c1左側擷取掉與指定字串c2相同的字元並返回。如果c2為空則預設擷取空格。

例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;

7、RTRIM(c1,c2)與上同，不過方向相反

例如：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) 神奇的函式啊，該函式返回字串引數的語音表示形式，對於比較一些讀音相同，但是拼寫不同的單詞非常有用。計算語音的演算法如下：

l 保留字串首字母，但刪除a、e、h、i、o、w、y。

l 將下表中的數字賦給相對應的字母：

1：b、f、p、v

2：c、g、k、q、s、x、z

3：d、t

4：l

5：m、n

6：R

l 如果字串中存在擁有相同數字的2個以上（包含2個）的字母在一起（例如b和f），或者只有h或w，則刪除其他的，只保留1個；

l 只返回前4個位元組，不夠用0填充

例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;

10、SUBSTR(c1,n1[,n2]) 擷取指定長度的字串。稍不注意就可能充滿了陷阱的函式。

n1=開始長度；

n2=擷取的字串長度，如果為空，預設擷取到字串結尾；

l 如果n1=0 then n1=1

l 如果n1>0，則oracle從左向右確認起始位置擷取

例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;

l 如果n1<0，則oracle從右向左數確認起始位置

例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;

l 如果n1>c1.length則返回空

例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;

然後再請你猜猜，如果n2<1，會如何返回值呢

11、TRANSLATE(c1,c2,c3) 就功能而言，此函式與replace有些相似。但需要注意的一點是，translate是絕對匹配替換，這點與replace函式具有非常大區別。什麼是絕對匹配替換呢？簡單的說，是將字串c1中按一定的格式c2替換為c3。如果文字形容仍然無法理解，我們透過幾具例項來說明：

例如：

SELECT TRANSLATE('What is this','','-') FROM DUAL;

SELECT TRANSLATE('What is this','-','') FROM DUAL;

結果都是空。來試試這個：

SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;

再來看這個：

SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;

是否明白了點呢？Replace函式理解比較簡單，它是將字串中指定字元替換成其它字元，它的字元必須是連續的。而translate中，則是指定字串c1中出現的c2，將c2中各個字元替換成c3中位置順序與其相同的c3中的字元。明白了？Replace是替換，而translate則像是過濾。

(三).字元型函式返回數字值(Character Functions Returning Number Values)

本類函式支援所有的資料型別

1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置

l c1:原字串

l c2:要尋找的字串

l n1:查詢起始位置，正值表示從左到右，負值表示從右到左 (大小表示位置,比如3表示左面第3處開始,-3表示右面第3處開始)。黑黑，如果為0的話，則返回的也是0

l n2:第幾個匹配項。大於0

例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;

2、LENGTH(c) 返回指定字串的長度。如果

例如：SELECT LENGTH('A123中') FROM DUAL;

猜猜SELECT LENGTH('') FROM DUAL;的返回值是什麼

(四).日期函式(Datetime Functions)

本類函式中，除months_between返回數值外，其它都將返回日期。

1、ADD_MONTHS() 返回指定日期月份+n之後的值，n可以為任何整數。

例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;

2、CURRENT_DATE 返回當前session所在時區的預設時間

例如：

SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;

SQL> select current_date from dual;

3、SYSDATE 功能與上相同，返回當前session所在時區的預設時間。但是需要注意的一點是，如果同時使用sysdate與current_date獲得的時間不一定相同，某些情況下current_date會比sysdate快一秒。經過與xyf_tck(兄臺的大作ORACLE的工作機制寫的很好，深入淺出)的短暫交流，我們認為current_date是將current_timestamp中毫秒四捨五入後的返回，雖然沒有找到文件支援，但是想來應該八九不離十。同時，僅是某些情況下會有一秒的誤差，一般情況下並不會對你的操作造成影響，所以瞭解即可。

例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL;

4、LAST_DAY(d) 返回指定時間所在月的最後一天

例如：SELECT last_day(SYSDATE) FROM DUAL;

5、NEXT_DAY(d,n) 返回指定日期後第一個n的日期，n為一週中的某一天。但是，需要注意的是n如果為字元的話，它的星期形式需要與當前session預設時區中的星期形式相同。

例如：三思用的中文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;

則會執行出錯，提供你說週中的日無效，就是這個原因了。

6、MONTHS_BETWEEN(d1,d2) 返回d1與d2間的月份差，視d1,d2的值大小，結果可正可負，當然也有可能為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]) 前面講數值型函式的時候介紹過ROUND，此處與上功能基本相似，不過此處操作的是日期。如果不指定fmt引數，則預設返回距離指定日期最近的日期。

例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;

8、TRUNC(d[,fmt]) 與前面介紹的數值型TRUNC原理相同，不過此處也是操作的日期型。

例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;

(五).轉換函式(Conversion Functions)

轉換函式將指定字元從一種型別轉換為另一種，通常這類函式遵循如下慣例：函式名稱後面跟著待轉換型別以及輸出型別。

1、TO_CHAR() 本函式又可以分三小類，分別是

l 轉換字元->字元TO_CHAR(c)：將nchar,nvarchar2,clob,nclob型別轉換為char型別；

例如：SELECT TO_CHAR('AABBCC') FROM DUAL;

l 轉換時間->字元TO_CHAR(d[,fmt])：將指定的時間(data,timestamp,timestamp with time zone)按照指定格式轉換為varchar2型別；

例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

l 轉換數值->字元TO_CHAR(n[,fmt])：將指定數值n按照指定格式fmt轉換為varchar2型別並返回；

例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

2、TO_DATE(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2轉換為日期型別，如果fmt引數不為空，則按照fmt中指定格式進行轉換。注意這裡的fmt引數。如果ftm為'J'則表示按照公元制(Julian day)轉換，c則必須為大於0並小於5373484的正整數。

例如：

SELECT TO_DATE(2454336, 'J') FROM DUAL;

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

3、TO_NUMBER(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式轉換為數值型別並返回。

例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;

(六).其它輔助函式(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一個增強型的if else，只不過它並不透過多行語句，而是在一個函式內實現if else的功能。

exp做為初始引數。s做為對比值，相同則返回r，如果s有多個，則持續遍歷所有s，直到某個條件為真為止，否則返回預設值def(如果指定了的話)，如果沒有預設值，並且前面的對比也都沒有為真，則返回空。

毫無疑問，decode是個非常重要的函式，在實現行轉列等功能時都會用到，需要牢記和熟練使用。

例如：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也是個很有意思的函式。邏輯等價於：CASE WHEN c1 = c2 THEN NULL ELSE c1 END

例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

5、NVL(c1,c2) 邏輯等價於IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何型別。如果兩者型別不同，則oracle會自動將c2轉換為c1的型別。

例如：SELECT NVL(null, '12') FROM DUAL;

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用過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) 該函式只能應用於樹狀查詢。返回透過c1連線的從根到節點的路徑。該函式必須與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的指定引數c2的值按照指定長度n擷取後返回。

Oracle9i提供內建了一個名稱空間USERENV，描述了當前session的各項資訊，其擁有下列引數：

l CURRENT_SCHEMA:當前模式名；

l CURRENT_USER:當前使用者；

l IP_ADDRESS:當前客戶端IP地址；

l OS_USER:當前客戶端作業系統使用者；

等等數十項，更詳細的引數列還請大家直接參考Oracle Online Documents

例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
*******************************************************************************************

ORACLE函式介紹

第二篇 非著名函式之單值函式 2007.8.9

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　單值函式在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。

(一).數值型函式(Number Functions)

數值型函式輸入數字型引數並返回數值型的值。多數該類函式的返回值支援38位小數點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支援36位小數點。ACOS, ASIN, ATAN, and ATAN2支援30位小數點。

1、ABS(n) 返回數字的絕對值

例如：SELECT ABS(-1000000.01) FROM DUAL;

2、COS(n) 返回n的餘弦值

例如：SELECT COS(-2) FROM DUAL;

3、ACOS(n) 反餘弦函式，n between -1 and 1，返回值between 0 and pi。

例如：SELECT ACOS(0.9) FROM DUAL;

4、BITAND(n1,n2) 位與運算，這個太有意思了，雖然沒想到可能用到哪裡，詳細說明一下：

假設3,9做位與運算，3的二進位制形式為：0011，9的二進位制形式為：1001，則結果是0001，轉換成10進位制數為1。

例如：SELECT BITAND(3,9) FROM DUAL;

5、CEIL(n) 返回大於或等於n的最小的整數值

例如：SELECT ceil(18.2) FROM DUAL;

考你一下，猜猜ceil(-18.2)的值會是什麼呢

6、FLOOR(n) 返回小於等於n的最大整數值

例如：SELECT FLOOR(2.2) FROM DUAL;

再猜猜floor(-2.2)的值會是什麼呢

7、BIN_TO_NUM(n1,n2,....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) 反正玄函式，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) 反正切函式，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的自然對數，n>0

例如：SELECT LN(0.9) FROM DUAL;

16、LOG(n1,n2) 返回以n1為底n2的對數，n1 >0 and not 1 ，n2>0

例如：SELECT LOG(1.1,2.2) FROM DUAL;

17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以為任意數值，不過如果m是負數，則n必須為整數

例如：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;

(二).字元型函式返回字元值(Character Functions Returning Character Values)

　　該類函式返回與輸入型別相同的型別。

l 返回的CHAR型別值長度不超過2000位元組；

l 返回的VCHAR2型別值長度不超過4000位元組；

如果上述應返回的字元長度超出，oracle並不會報錯而是直接截斷至最大可支援長度返回。

l 返回的CLOB型別值長度不超過4G；

對於CLOB型別的函式，如果返回值長度超出，oracle不會返回任何錯誤而是直接丟擲錯誤。

1、CHR(N[ USING NCHAR_CS]) 返回指定數值在當前字符集中對應的字元

例如：SELECT CHR(95) FROM DUAL;

2、CONCAT(c1,c2) 連線字串，等同於||

例如：SELECT concat('aa','bb') FROM DUAL;

3、INITCAP(c) 將字串中單詞的第一個字母轉換為大寫，其它則轉換為小寫

例如：SELECT INITCAP('whaT is this') FROM DUAL;

4、NLS_INITCAP(c) 返回指定字串，並將字串中第一個字母變大寫，其它字母變小寫

例如：SELECT NLS_INITCAP('中華miNZHu') FROM DUAL;

它還具有一個引數：Nlsparam用來指定排序規則，可以忽略，預設狀態該引數為當前session的排序規則。

(三).字元型函式返回數字值(Character Functions Returning Number Values)

本類函式支援所有的資料型別

1、ASCII(c) 與chr函式的用途剛剛相反，本函式返回指定字元在當前字符集下對應的數值。

例如：SELECT ASCII('_') FROM DUAL;

(四).日期函式(Datetime Functions)

本類函式中，除months_between返回數值外，其它都將返回日期。

1、CURRENT_TIMESTAMP([n]) 返回當前session所在時區的日期和時間。n表示毫秒級的精度，不大於6

例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;

2、LOCALTIMESTAMP([n]) 與上同，返回當前session所在時區的日期和時間。n表示毫秒級的精度，不大於6

例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;

3、SYSTIMESTAMP([n]) 與上同，返回當前資料庫所在時區的日期和時間，n表示毫秒級的精度，>0 and <6

例如：SELECT SYSTIMESTAMP(4) FROM DUAL;

4、DBTIMEZONE 返回資料庫的當前時區

例如：SELECT DBTIMEZONE FROM DUAL;

5、SESSIONTIMEZONE 返回當前session所在時區

例如：SELECT SESSIONTIMEZONE FROM DUAL;

6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 從指定時間提到指定日期列

例如：SELECT EXTRACT(year from sysdate) FROM DUAL;

7、TO_TIMESTAMP(c1[,fmt]) 將指定字元按指定格式轉換為timestamp格式。

例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

(五).轉換函式(Conversion Functions)

轉換函式將指定字元從一種型別轉換為另一種，通常這類函式遵循如下慣例：函式名稱後面跟著待轉換型別以及輸出型別。

1、BIN_TO_NUM(n1,n2...n) 將一組位向量轉換為等價的十進位制形式。

例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

2、CAST(c as newtype) 將指定字串轉換為指定型別，基本只對字元型別有效，比如char,number,date,rowid等。此類轉換有一個專門的表列明瞭哪種型別可以轉換為哪種型別，此處就不作酹述。

例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;

3、CHARTOROWID(c) 將字串轉換為rowid型別

例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;

4、ROWIDTOCHAR(rowid) 轉換rowid值為varchar2型別。返回串長度為18個位元組。

例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;

5、TO_MULTI_BYTE(c) 將指定字元轉換為全形並返回char型別字串

例如：SELECT TO_MULTI_BYTE('ABC abc　中華') FROM DUAL;

6、TO_SINGLE_BYTE(c) 將指定字元轉換為半形並返回char型別字串

例如：SELECT TO_SINGLE_BYTE('ＡＢＣ　ａｂｃ中華') FROM DUAL;

(六).其它輔助函式(Miscellaneous Single-Row Functions)

1、COALESCE(n1,n2,....n) 返回序列中的第一個非空值

例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;

2、DUMP(exp[,fmt[,start[,length]]])

dump是個功能非常強悍的函式，對於深入瞭解oracle儲存的人而言相當有用。所以對於我們這些僅僅只是應用的人而言就不知道能將其應用於何處了。此處僅介紹用法，不對其功能做深入分析。

如上所示，dump擁有不少引數。其本質是以指定格式，返回指定長度的exp的內部表示形式的varchar2值。fmt含4種格式：8||10||16||17，分別表示8進位制，10進位制，16進位制和單字元，預設為10進位制。start參數列示開始位置，length表示以,分隔的字串數。

例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;

3、EMPTY_BLOB,EMPTY_CLOB 這兩個函式都是返回空lob型別，通常被用於insert和update等語句以初始化lob列，或者將其置為空。EMPTY表示LOB已經被初始化，只不過還沒有用來儲存資料。

4、NLS_CHARSET_NAME(n) 返回指定數值對應的字符集名稱。

例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;

5、NLS_CHARSET_ID(c) 返回指定字元對應的字符集id。

例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;

6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一個NCHAR值的宣告寬度(以字元為單位).n1是該值以位元組為單位的長度，n2是該值的字符集ID

例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;

7、SYS_EXTRACT_UTC(timestamp) 返回標準通用時間即格林威治時間。

例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;

8、SYS_TYPEID(object_type) 返回物件型別對應的id。

例如：這個這個，沒有建立過自定義物件，咋做示例？

9、UID 返回一個唯一標識當前資料庫使用者的整數。

例如：SELECT UID FROM DUAL;

10、USER 返回當前session使用者

例如：SELECT USER FROM DUAL;

11、USERENV(c) 該函式用來返回當前session的資訊，據oracle文件的說明，userenv是為了保持向下相容的遺留函式。oracle公司推薦你使用sys_context函式呼叫USERENV名稱空間來獲取相關資訊，所以大家瞭解下就行了。

例如：SELECT USERENV('LANGUAGE') FROM DUAL;

12、VSIZE(c) 返回c的位元組數。

例如：SELECT VSIZE('abc中華') FROM DUAL;

**********************************************************************************

ORACLE函式介紹

第三篇 著名函式之聚合函式 2007.8.16

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　聚合函式可被用於select,order by以及having子句中。其運算可以基於group by的結果，也可以直接對所有記錄進行運算。當然，這種型別語句相信大家平常也用的比較多，概念性的就不多介紹了，有一點需要注意的是，除了count和grouping之後，其它的統計運算均會忽略值為null的列。

　　為更好演示此類函式應用，我們需要首先構建一個測試表，如下：

create table tmp1(col varchar2(10),value number(5));

insert into tmp1 values ('test1',60);

insert into tmp1 values ('test2',120);

insert into tmp1 values ('test3',460);

insert into tmp1 values ('test4',225);

insert into tmp1 values ('test5',119);

insert into tmp1 values ('test6',350);

insert into tmp1 values ('test7',23);

insert into tmp1 values ('test1',120);

insert into tmp1 values ('test3',69);

insert into tmp1 values ('test4',89);

insert into tmp1 values ('test6',145);

後續章節示例中也均以此表做例。

　　注意，多數聚合函式都支援DISTINCT子句(distinct是什麼？過濾記錄集中重複記錄用的)，如果不指定則預設針對所有計錄，舉個最簡單的例子：select count(col),count(distinct col) from tmp1。

　　本類函式中部分函式同時也屬於分析函式。其做為分析函式時的具體用法將會在後面章節介紹，本節不做描述。

1、AVG([distinct|all]n) 求取記錄集中的平均值。

例如：SELECT col, avg(value) FROM tmp1 GROUP BY col ORDER BY col

2、COUNT([distinct|all]col) 該函式返回查詢涉及到的記錄行數。

例如：

SELECT col, count(0) FROM tmp1 GROUP BY col ORDER BY col;

SELECT count(col), count(distinct col) FROM tmp1;

例如：計算數值100在記錄集中的排序值。

SELECT dense_rank(100) within group( order by value) FROM tmp1

4、RANK() 引數及形式完全與上同，二區最大的區別是：RANK函式在處理指定數值在記錄集中的排序值時，如果值有重複，則後面的排序值會跳過這個值，直接從當前排序值＋重複記錄數開始，而DENSE_RANK則不會，排序值依然是個連續的序列。提到這個，在後面講分析函式講到row_number()時大家會又發現不同，先刨個坑，後面再填。

例如：
insert into tmp1 values ('test2',120);

SELECT dense_rank(121) within group( order by value) FROM tmp1;

SELECT rank(121) within group( order by value) FROM tmp1;

5、FIRST() 此函式格式描述看起來極複雜無比，其實也確實複雜無比。其本質是從DENSE_RANK返回的集合中取出排在第一的行（可能多行，因為值可能相等），因此完整的語法需要在開始處加上一個集合函式以從中取出記錄，功能與FIRST_VALUE相同。

MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",

MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"

FROM tmp1

GROUP BY col

6、LAST 形式引數等均與上同，不過就是把FIRST換成LAST即可。FIRST是取第一行，LAST則是取最後一行。

例如：

7、MAX([distinct|all] col) 取同組序列資料集中最大值。

例如：

SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"

FROM tmp1

GROUP BY col

8、MIN([distinct|all] col) 取同組序列資料集中最小值。形式引數均與上同，不詳述

例如：

SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"

FROM tmp1

GROUP BY col

9、SUM([distinct|all] col) 取指定列所在分組序列的值的和並返回。

例如：SELECT col, SUM(value) "Sum Value" FROM tmp1 GROUP BY col

10、GROUPING(col) cube、rollup子句的輔助函式，並且必須配合cube或rollup才能使用。用來確定當前記錄行是否是由cube或rollup生成，如果是則返回1，不則返回0。

例如：

SELECT col, sum(value), grouping(value)

FROM tmp1

GROUP BY rollup(col, value)

**********************************************************************************

ORACLE函式介紹

第四篇 非著名函式之聚合函式 2007.8.23

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　聚合函式可被用於select,order by以及having子句中。其運算可以基於group by的結果，也可以直接對所有記錄進行運算。當然，這種型別語句相信大家平常也用的比較多，概念性的就不多介紹了，有一點需要注意的是，除了count和grouping之後，其它的統計運算均會忽略值為null的列。

　　注意，多數聚合函式都支援DISTINCT子句(distinct是什麼？過濾記錄集中重複記錄用的)，如果不指定則預設針對所有計錄，舉個最簡單的例子：select count(col),count(distinct col) from tmp1。

　　本類函式中部分函式同時也屬於分析函式。其做為分析函式時的具體用法將會在後面章節介紹，本節不做描述。

1、CUME_DIST(expr[,expr]...) WITHIN GROUP

(ORDER BY

expr [DESC | ASC] [NULLS {FIRST | LAST}]

[,expr [DESC | ASC] [NULLS {FIRST | LAST}]]...)非常有意思的一個函式。查詢指定數值在指定分組序列中的相對位置，返回值是介於 0 和 1 之間的小數值。我們後面還會再講幾個相關的函式，均是實現型別功能，只是值的表現形式不同。

例如：SELECT CUME_DIST(120) WITHIN GROUP (ORDER BY value) FROM TMP1

2、GROUP_ID() 該函式必須配合group by子句使用。主要是用來區分group by 生成的記錄集中是否是被重複生成的記錄，如果該條記錄是則返回1，否則返回0。

例如：SELECT t.col, group_id() FROM TMP1 t GROUP BY col, ROLLUP(col)

3、PERCENT_RANK ( expr [, expr]... ) WITHIN GROUP

( ORDER BY

expr [ DESC | ASC ] [NULLS { FIRST | LAST }]

[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)本函式語法及形式與cume_dist非常相似，也是返回指定資料在指定分組序列中所佔的相對位置，不過與cume_dist不同的是。不管指定值與分組序列中某值是否重複，均將此值視為序列一部分，而cume_dist則不同。如果指定值與分組序列中某值重複，則將二值視為一個值處理(上帝保佑，但願俺講明白了，廢話不說，大家透過示例來理解吧)。

例如：SELECT PERCENT_RANK(120) WITHIN GROUP( ORDER BY value) FROM TMP1

4、PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 根據輸入值返回該值對應的分組序列中數值。輸入值應該是介於0到1之間。原因請參照前面的PERCENT_RANK函式。

如果分組序列中沒有存在對應值的話，會根據如下規則來計算返回值：

if (CRN = FRN = RN) then

(value of expression from row at RN)

else

(CRN - RN) * (value of expression for row at FRN) +

(RN - FRN) * (value of expression for row at CRN)

例如：

SELECT col, max(value), min(value), sum(value),

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,

PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b

FROM TMP1

group by col;

5、PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 語法與形式與上例相同，不過在計算方法上略有差異。如果分組序列中沒有對應值的話，那麼將會返回該序列中最近最大的一個值。

相對而言，我認為某些情況下本函式被應用的機率還是相當大地，起碼比上述幾個函式的應用範圍會廣。

例如：

SELECT col, max(value), min(value),

PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,

PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b

FROM TMP1

group by col;

6、STDDEV([DISTINCT|ALL] n) 該函式返回樣本的標準偏差。

例如：SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;

7、STDDEV_SAMP(n) 語法與形式與上同，其與STDDEV函式最大的區別是，如果該分組序列只有一行的話，則STDDEV_SAMP函式返回空值，而STDDEV則返回0。

例如：SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;

8、STDDEV_POP(n) 返回該分組序列總體標準偏差。

例如：SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;

9、VAR_POP(n) 該函式返回分組序列的總體方差，VAR_POP進行如下計算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)。

例如：SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;

10、VAR_SAMP(n) 與上類似，該函式返回分組序列的樣本方差，，其計算公式為：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)。

例如：SELECT col, VAR_POP(value),VAR_SAMP(value) FROM TMP1 GROUP BY col;

11、VARIANCE(n) 該函式返回分組序列方差，Oracle計算該變數如下：

如果表示式中行數為1，則返回0，如果表示式中行數大於1，則返回VAR_SAMP

例如：SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUP BY col;

********************************************************************************

ORACLE函式介紹

第五篇 分析函式簡述 2007.8.29

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　分析函式計算基於group by的列，分組查詢出的行被稱為"比照(window)"，在根據over()執行過程中，針對每一行都會重新定義比照。比照為"當前行(current row)"確定執行計算的行的範圍。這點一定要理解清楚。它是分析函式生成資料的原理。如果此處模糊，那麼你在應用分析函式時恐就不會那麼得心應手了。

　　分析函式與前面章節中講到的聚合函式非常相似，不同於聚合函式的地方在於它們每個分組序列均返回多行。在本節示例中會同時應用兩種函式做對比，以更好體現二者的差異。透過本章節練習相信大家就會注意到，部分聚合函式和分析函式是同一個命令，事實確實如此。如果從語法格式上區分的話，沒加over()的即是聚合函式，加了over()即是分析函式：）

　　有一點需要注意喲，除了order by子句的運算外，分析函式在SQL語句中將會最後執行。因此，分析函式只能應用於select的列或order by子句中(記住嘍，千萬別扔到什麼where、group by、having之類的地方了)。也正因此，同名的函式在做為聚合函式和分析函式時得出的結果可能不相同，就是因為此處運算邏輯不同造成的。

　　同時，部分分析函式在選擇列時支援distinct，如果你指定了該引數，則over條件中就只能指定partition子句，而不能再指定order by 子句了。

　　分析函式的語法結構比較複雜，但多數函式都具有相同的語法結構，所以先在之前進行統一介紹，後續單個函式介紹時就不過多說明函式語法結構了。

　　基本上所有的分析函式均是這種格式：

　　函式名稱 ([引數]) OVER (analytic_clause)

analytic_clause包含：[partition 子句][ order 子句 [window子句]]

l Partition 子句：Partition by exp1[ ,exp2]...

Partition沒啥說的，功能強大引數少，主要用於分組，可以理解成select中的group by。不過它跟select語句後跟的group by 子句並不衝突。

l Order子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]。部分函式支援window子句。

Order by的引數基本與select中的order by相同。大家按那個理解就是了。Nulls first|last是用來限定nulls在分組序列中的所在位置的，我們知道oracle中對於null的定義是未知，所以預設ordery的時候nulls總會被排在最前面。如果想控制值為null的列的話呢，nulls first|last引數就能派上用場了。

l Window子句：En,貼個圖吧

　　看起來複雜其實簡單，而且應用的機率相當的低，不詳細介紹了。

******************************************************************************************

ORACLE函式介紹

第六篇 著名函式之分析函式 2007.8.28

1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 計算平均值。

例如：

--聚合函式

SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函式

SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col)

FROM tmp1

ORDER BY col;

2、SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )

例如：

--聚合函式

SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函式

SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col)

FROM tmp1

ORDER BY col;

3、COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查詢分組序列中各組行數。

例如：

--分組查詢col的數量

SELECT col,count(0) over(partition by col order by col) ct FROM tmp1;

4、FIRST() 從DENSE_RANK返回的集合中取出排在第一的行。

SELECT col,

MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",

MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"

FROM tmp1

GROUP BY col;

--分析函式

SELECT col,

MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col),

MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col)

FROM tmp1

ORDER BY col;

可以看到二者結果基本相似，但是ex1的結果是group by後的列，而ex2則是每一行都有返回。

5、LAST()與上同，不詳述。

例如：見上例。

6、FIRST_VALUE (col) OVER ( analytic_clause ) 返回over()條件查詢出的第一條記錄

例如：

insert into tmp1 values ('test6','287');

SELECT col,

FIRST_VALUE(value) over(partition by col order by value) "First",

LAST_VALUE(value) over(partition by col order by value) "Last"

FROM tmp1;

7、LAST_VALUE (col) OVER ( analytic_clause ) 返回over()條件查詢出的最後一條記錄

例如：見上例。

8、LAG(col[,n][,n]) over([partition_clause] order_by_clause) lag是一個相當有意思的函式，其功能是返回指定列col前n1行的值(如果前n1行已經超出比照範圍，則返回n2，如不指定n2則預設返回null)，如不指定n1，其預設值為1。

例如：

SELECT col,

value,

LAG(value) over(order by value) "Lag",

LEAD(value) over(order by value) "Lead"

FROM tmp1;

9、LEAD(col[,n][,n]) over([partition_clause] order_by_clause) 與上函式正好相反，本函式返回指定列col後n1行的值。

例如：見上例

10、MAX (col) OVER (analytic_clause) 獲取分組序列中的最大值。

例如：

--聚合函式

SELECT col,

Max(value) "Max",

Min(value) "Min"

FROM tmp1

GROUP BY col;

--分析函式

SELECT col,

value,

Max(value) over(partition by col order by value) "Max",

Min(value) over(partition by col order by value) "Min"

FROM tmp1;

11、MIN (col) OVER (analytic_clause) 獲取分組序列中的最小值。

例如：見上例。

12、RANK() OVER([partition_clause] order_by_clause) 關於RANK和DENSE_RANK前面聚合函式處介紹過了，這裡不廢話不，大概直接看示例吧。

例如：

insert into tmp1 values ('test2',120);

SELECT col,

value,

RANK() OVER(order by value) "RANK",

DENSE_RANK() OVER(order by value) "DENSE_RANK",

ROW_NUMBER() OVER(order by value) "ROW_NUMBER"

FROM tmp1;

13、DENSE_RANK () OVER([partition_clause] order_by_clause)

例如：見上例。

14、ROW_NUMBER () OVER([partition_clause] order_by_clause) 這個函式需要多說兩句，透過上述的對比相信大家應該已經能夠看出些端倪。前面講過，dense_rank在做排序時如果遇到列有重複值，則重複值所在行的序列值相同，而其後的序列值依舊遞增，rank則是重複值所在行的序列值相同，但其後的序列值從+重複行數開始遞增，而row_number則不管是否有重複行，(分組內)序列值始終遞增

例如：見上例。

************************************************************************************

ORACLE函式介紹

第七篇 非著名函式之分析函式 2007.8.27

1、CUME_DIST() OVER([partition_clause] order_by_clause) 返回該行在分組序列中的相對位置，返回值介於0到1之間。注意喲，如果order by的列是desc，則該分組內最大的行返回列值1，如果order by為asc，則該分組內最小的行返回列值1。

例如：SELECT col, value, CUME_DIST() OVER(ORDER BY value DESC) FROM tmp1;

2、NTILE(n) OVER([partition_clause] order_by_clause)

ntile是個很有意思的統計函式。它會按照你指定的組數(n)對記錄做分組

例如：SELECT t.*,ntile(5) over(order by value desc) FROM tmp1 t;

3、PERCENT_RANK() OVER([partition_clause] order_by_clause) 與CUME_DIST類似，本函式返回分組序列中各行在分組序列的相對位置。其返回值也是介於0到1之間，不過其起始值始終為0而終結值始終為1。

例如：SELECT col, value, PERCENT_RANK() OVER(ORDER BY value) FROM tmp1;

4、PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函式功能與前面聚合函式處介紹的完全相同，只是一個是聚合函式，一個是分析函式。

例如：

--聚合函式

SELECT col, max(value), min(value), sum(value),

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,

PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b

FROM TMP1

group by col;

--分析函式

SELECT col,

value,

sum(value) over(partition by col) "Sum",

PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",

PERCENTILE_CONT(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"

FROM TMP1;

5、PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函式功能與前面聚合函式處介紹的完全相同，只是一個是聚合函式，一個是分析函式。

例如：

--聚合函式

SELECT col, max(value), min(value), sum(value),

PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,

PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b

FROM TMP1

group by col;

--分析函式

SELECT col,

value,

sum(value) over(partition by col) "Sum",

PERCENTILE_DISC(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",

PERCENTILE_DISC(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"

FROM TMP1;

6、RATIO_TO_REPORT(col) over ([partition_clause]) 本函式計算本行col列值在該分組序列sum(col)中所佔比率。如果col列為空，則返回空值。

例如：

SELECT col, value,

RATIO_TO_REPORT(value) OVER(PARTITION BY col) "RATIO_TO_REPORT"

FROM TMP1

7、STDDEV ([distinct|all] col) OVER (analytic_clause) 返回列的標準偏差。

例如：

--聚合函式

SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV"

FROM TMP1;

8、STDDEV_SAMP(col) OVER (analytic_clause) 功能與上相同，與STDDEV不同地方在於如果該分組序列只有一行的話，則STDDEV_SAMP函式返回空值，而STDDEV則返回0。

例如：

--聚合函式

SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV",

STDDEV_SAMP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_SAMP"

FROM TMP1;

9、STDDEV_POP(col) OVER (analytic_clause) 返回該分組序列總體標準偏差

例如：

--聚合函式

SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

STDDEV_POP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_POP"

FROM TMP1;

10、VAR_POP(col) OVER (analytic_clause) 返回分組序列的總體方差，VAR_POP進行如下計算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

例如：

--聚合函式

SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

VAR_POP(value) OVER(PARTITION BY col ORDER BY value) "VAR_POP"

FROM TMP1;

11、VAR_SAMP(col) OVER (analytic_clause) 與上類似，該函式返回分組序列的樣本方差，，其計算公式為：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)

例如：

--聚合函式

SELECT col, VAR_SAMP(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP"

FROM TMP1;

12、VARIANCE(col) OVER (analytic_clause) 該函式返回分組序列方差，Oracle計算該變數如下：

如果表示式中行數為1，則返回0，如果表示式中行數大於1，則返回VAR_SAMP

例如：

--聚合函式

SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUP BY col;

--分析函式

SELECT col, value,

VAR_SAMP(value) OVER(PARTITION BY col ORDER BY value) "VAR_SAMP",

VARIANCE(value) OVER(PARTITION BY col ORDER BY value) "VARIANCE"

FROM TMP1;

********************************************************************************

ORACLE函式介紹

第八篇 綜述 2007.8.13

　　透過前面章節的練習我們可以對三類函式特徵做些描述。

l 單值函式多數處理單個或多個值但只返回單個值(SYS_CONNECT_BY_PATH除外)。

l 聚合函式多數處理多行並且各分組序列分別返回成一行。

l 分析函式多數處理多行並且每條記錄均會有返回。

　　需要注意不同型別函式可被應用的範圍。

l 單值函式可應用範圍最為廣泛，select語句中的每一處都可被嵌入。

l 聚合函式通常會配合group by被應用於select或having子句，

l 分析函式只能應用於select或order by子句。

　　雖然我們前面介紹了百十多個函式，可是仍然並非全部，並且即使是我們介紹到的，也不能說所有函式都已經介紹了其全部的特色，比如：對於substr，length，instr（like）等均有擴充套件形式。比如

l substr:以字元為單位

l substrb:以位元組為單位

l substrc:以unicode完全字元為單位

l substr2:以UCS2程式碼點為單位

l substr4:以UCS4程式碼點為單位

　　它們對於字元的處理方式都是一致的，只不過在我們看來相同的字元在它們看來可能就不同，這就是不同編碼的原因。關於unicode,ucs程式碼概念介紹可以看看這裡：

　　Oracle中函式功能強大，分類細緻，某些地方構思異常巧妙。

一、引數中的小數點

我們以CEIL為例

SELECT CEIL(12.8) FROM DUAL;

值是什麼？

二、負數也有學問

我們先以LPAD函式為例。一個非常簡單的補值單值函式。

SELECT LPAD('Let us go!',5,'-') FROM DUAL;

猜猜這個會返回什麼值呢？

SELECT LPAD('Let us go!',20,'-') FROM DUAL;

猜猜這個又會返回什麼值呢？

SELECT LPAD('Let us go!',-5,'-') FROM DUAL;

再猜猜這個會返回什麼值呢？

再以SUBSTR為例：

SELECT SUBSTR('Let us go!',-5,5) FROM DUAL;

這個將要返回什麼值呢？

SELECT SUBSTR('Let us go!',-5,-5) FROM DUAL;

這個又將會返回什麼值呢？

三、小心隱藏參

我們仍然以LPAD為例：

SELECT LPAD('Let us go!',20) FROM DUAL;

猜猜這個返回什麼值呢？

SELECT SUBSTR('Let us go!',-5) FROM DUAL;

這個將要返回什麼值呢？

SELECT TRUNC(12.8251) FROM DUAL;

這個又將會返回什麼值呢？

　　諸如此類在oracle的函式中是比比旨是，比如：substr,instr,trim等等~~~~箇中奧妙還需個自體會。

Oracle中還提供了一批函式用來操縱xml資料，比如：XMLAGG,XMLCOLATTVAL,XMLCONCAT,XMLFOREST,XMLSEQUENCE,XMLTRANSFORM,EXTRACT(XML),UPDATEXML等，功能強悍，不過演示環境搭建稍嫌複雜，三言二語也說不清楚，我這裡就不多做介紹了。感興趣的話可以檢視oracle官方文件或者直接跟我交流。

如果以上種種仍然不能滿足你的需求，你還可以透過自己建立function的方式來實現，建立的語法格式非常簡單，不屬於本章內容就不多做介紹了，如果你想找個示例的話，俺之前寫過一個oracle下的郵件傳送有相關示例可供你參考，地址如下：http://www.itpub.net/825426.html。

　　最後補充一句再強大的功能，還是需要能夠靈活使用才能夠體現其價值，所以瞭解並不是我們的目地，熟練應用才是我們的目標：）。

ORACLE函式介紹

相關文章