oracle的函數(shù)很多﹐特整理出來﹕
abs(m) m的絕對值
mod(m,n) m被n除后的余數(shù)
power(m,n) m的n次方
round(m[,n]) m四舍五入至小數(shù)點(diǎn)后n位的值（n缺省為0）
trunc(m[,n]) m截?cái)?/span>n位小數(shù)位的值（n缺省為0）
字符函數(shù)：
initcap(st) 返回st將每個(gè)單詞的首字母大寫，所有其他字母小寫
lower(st) 返回st將每個(gè)單詞的字母全部小寫
upper(st) 返回st將每個(gè)單詞的字母全部大寫
concat(st1,st2) 返回st為st2接st1的末尾（可用操作符"||"）
lpad(st1,n[,st2]) 返回右對齊的st,st為在st1的左邊用st2填充直至長度為n,st2的缺省為空格
rpad(st1,n[,st2]) 返回左對齊的st,st為在st1的右邊用st2填充直至長度為n,st2的缺省為空格
ltrim(st[,set]) 返回st,st為從左邊刪除set中字符直到第一個(gè)不是set中的字符。缺省時(shí)，指的是空格
rtrim(st[,set]) 返回st,st為從右邊刪除set中字符直到第一個(gè)不是set中的字符。缺省時(shí)，指的是空格
replace(st,search_st[,replace_st]) 將每次在st中出現(xiàn)的search_st用replace_st替換，返回一個(gè)st。缺省時(shí)，刪除search_st
{
SUBSTR(string,a[,b])返回string的一部分，a和b以字符為單位。
SUBSTRB(string,a[,b])返回string的一部分，a和b是以字節(jié)為單位。
SUBSTRC(string,a[,b])返回string的一部分，a和b是以UNICODE完全字符為單位。
SUBSTR2(string,a[,b])返回string的一部分，a和b是以UCS2代碼點(diǎn)為單位。
SUBSTR4(string,a[,b])返回string的一部分，a和b是以UCS4代碼點(diǎn)為單位。
以上函數(shù)都是返回string的一部分，從字符位置A開始，長為B個(gè)字符。如果A是0，那它就被認(rèn)為是1（字符串的開始位置）。如果A是正數(shù)，那么字符從左邊開始數(shù)。如果是負(fù)數(shù)，則從STRING的末尾開始，從右邊數(shù)。如果B不存在，那么缺省是整個(gè)字符串。如果B小于1，將返回NULL。如果A或B使用了浮點(diǎn)數(shù)，那么該數(shù)值首先被節(jié)取成一個(gè)整數(shù)，返回類型與STRING相同。
}
{
LENGTH(string)
LENGTHB(string)
LENGTHC(string)
LENGTH2(string)
LENGTH4(string)
以上函數(shù)返回string的長度。因?yàn)?/span>CHAR類型的值是填充空格的，所以如果string是CHAR數(shù)據(jù)類型，那么末尾的空格算在長度之內(nèi)。如果string是NULL，函數(shù)返回NULL。
}
{
INSTR(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b以字符為單位。
INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以字節(jié)為單位。
INSTRC(string1,string2[,a][,b])返回string1中包含string2的位置。a和b是以UNICODE完全字符為單位。
INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以UCS2代碼點(diǎn)為單位
INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置a和b是以UCS4代碼點(diǎn)為單位。
以上函數(shù)返回string1中包含string2的位置。從左邊開始掃描string1,起始位置是A。如果A為負(fù)數(shù)那么從右邊開始掃描。第B次出現(xiàn)的位置將被返回。A和B缺省都為1，即返回在string1中第一次出現(xiàn)string2的位置。如果string2在A和B的規(guī)定下沒有找到那么就返回0。位置的計(jì)算是相對于string1的開始位置的，而不關(guān)A和B的取值。
}
轉(zhuǎn)換函數(shù)：
nvl(m,n) 如果m值為null,返回n,否則返回m
to_char(m[,fmt]) m從一個(gè)數(shù)值轉(zhuǎn)換為指定格式的字符串fmt缺省時(shí)，fmt值的寬度正好能容納所有的有效數(shù)字
to_number(st[,fmt]) st從字符型數(shù)據(jù)轉(zhuǎn)換成按指定格式的數(shù)值，缺省時(shí)數(shù)值格式串的大小正好為整個(gè)數(shù)
附：
to_char()函數(shù)的格式：
---------------------------------
符號 說明
---------------------------------
9 每個(gè)9代表結(jié)果中的一位數(shù)字
0 代表要顯示的先導(dǎo)0
$ 美元符號打印在數(shù)的左邊
L 任意的當(dāng)?shù)刎泿欧?/span>
. 打印十進(jìn)制的小數(shù)點(diǎn)
, 打印代表千分位的逗號
---------------------------------
分組函數(shù)：
avg([distinct/all] n) 列n的平均值
count([all] *) 返回查詢范圍內(nèi)的行數(shù)包括重復(fù)值和空值
count([distinct/all] n) 非空值的行數(shù)
max([distinct/all] n) 該列或表達(dá)式的最大值
min([distinct/all] n) 該列或表達(dá)式的最小值
stdev([distinct/all] n) 該列或表達(dá)式的標(biāo)準(zhǔn)偏差，忽略空值
sum([distinct/all] n) 該列或表達(dá)式的總和
variance([distinct/all] n) 該列或表達(dá)式的方差，忽略空值
日期函數(shù)：
add_months(d,n) 日期d加n個(gè)月
last_day(d) 包含d的月份的最后一天的日期
month_between(d,e) 日期d與e之間的月份數(shù)，e先于d
new_time(d,a,b) a時(shí)區(qū)的日期和時(shí)間d在b時(shí)區(qū)的日期和時(shí)間
next_day(d,day) 比日期d晚，由day指定的周幾的日期
sysdate 當(dāng)前的系統(tǒng)日期和時(shí)間
greatest(d1,d2,...dn) 給出的日期列表中最后的日期
least(d1,k2,...dn) 給出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式轉(zhuǎn)變成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式轉(zhuǎn)成日期值，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截?cái)嗟阶罱娜掌?/span>
附：
日期格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代碼 說明 舉例或可取值的范圍
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD 該月某一天 1－3
DY 三個(gè)大寫字母表示的周幾 SUN，...SAT
DAY 完整的周幾，大寫英文 SUNDAY，...SATURDAY
MM 月份 1－12
MON 三個(gè)大寫字母表示的月份 JAN，...DEC
MONTH 完整 JANUARY,...DECEMBER
RM 月份的羅馬數(shù)字 I,...XII
YY或YYYY 兩位，四位數(shù)字年
HH:MI:SS 時(shí)：分：秒
HH12或HH24 以12小時(shí)或24小時(shí)顯示
MI 分
SS 秒
AM或PM 上下午指示符
SP 后綴SP要求拼寫出任何數(shù)值字段
TH 后綴TH表示添加的數(shù)字是序數(shù) 4th,1st
FM 前綴對月或日或年值，禁止填充
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
43.求兩日期某一部分的差（比如秒）
直接用兩個(gè)日期相減（比如d1-d2=12.3）
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.根據(jù)差值求新的日期（比如分鐘）
SELECT sysdate+8/60/24 vaule FROM DUAL;
45.求不同時(shí)區(qū)時(shí)間
SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
-----時(shí)區(qū)參數(shù),北京在東8區(qū)應(yīng)該是Ydt-------
AST ADT 大西洋標(biāo)準(zhǔn)時(shí)間
BST BDT 白令海標(biāo)準(zhǔn)時(shí)間
CST CDT 中部標(biāo)準(zhǔn)時(shí)間
EST EDT 東部標(biāo)準(zhǔn)時(shí)間
GMT 格林尼治標(biāo)準(zhǔn)時(shí)間
HST HDT 阿拉斯加—夏威夷標(biāo)準(zhǔn)時(shí)間
MST MDT 山區(qū)標(biāo)準(zhǔn)時(shí)間
NST 紐芬蘭標(biāo)準(zhǔn)時(shí)間
PST PDT 太平洋標(biāo)準(zhǔn)時(shí)間
YST YDT YUKON標(biāo)準(zhǔn)時(shí)間
當(dāng)前時(shí)間減去7分鐘的時(shí)間
select sysdate,sysdate - interval '7' MINUTE from dual
當(dāng)前時(shí)間減去7小時(shí)的時(shí)間
select sysdate - interval '7' hour from dual
當(dāng)前時(shí)間減去7天的時(shí)間
select sysdate - interval '7' day from dual
當(dāng)前時(shí)間減去7月的時(shí)間
select sysdate,sysdate - interval '7' month from dual
當(dāng)前時(shí)間減去7年的時(shí)間
select sysdate,sysdate - interval '7' year from dual
時(shí)間間隔乘以一個(gè)數(shù)字
select sysdate,sysdate - 8 *interval '2' hour from dual
<!--[if !supportLists]-->一、<!--[endif]-->關(guān)于NULL的幾點(diǎn)說明：
1、對空值做加、減、乘、除等運(yùn)算操作，結(jié)果仍為空。
2、對空值進(jìn)行比較時(shí)，只能使用“is null”和“is not null”判斷。
3、在對某字段進(jìn)行按照升序排序的時(shí)候，若此字段中有的數(shù)據(jù)為空，則為NULL的將排到最后。
4、在用 LIKE '%' 來匹配時(shí)，空值不會被匹配。因?yàn)?/span>‘%’表示“任意多個(gè)字符”，但不包括“沒有任何字符”。
5、在計(jì)算一個(gè)number型字段 A 的SUM時(shí)，若此字段中有數(shù)據(jù)為空，則用 SUM(A) 來求和時(shí)，會忽略這樣的記錄。
6、在計(jì)算一個(gè)number型字段 A 的COUNT時(shí)，若此字段中有數(shù)據(jù)為空，則用 COUNT(A) 來計(jì)數(shù)時(shí)，不會忽略這樣的記錄，也會把這樣的記錄統(tǒng)計(jì)進(jìn)來。
7、在where條件中，Oracle把結(jié)果為NULL的條件做為FALSE來處理，帶有這樣條件的select語句將不返回結(jié)果，并且不返回錯(cuò)誤信息。但NULL和FALSE還是不同的。
NLS函數(shù)
除了NCHR，這些函數(shù)都是以字符類型為參數(shù)返回字符類型值。
CONVERT(string,dest_charset[,source_charset])
將輸入string轉(zhuǎn)換為指定字符集dest_charset。source_charset是輸入值的字符集——如果它沒有被指定，則缺省為數(shù)據(jù)庫字符集。輸入值可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB和NCLOB類型。返回值為
VARCHAR2類型。如果dest_charset中沒有輸入字符串中的一個(gè)字符，將會使用一個(gè)代替字符（由dest_charset定義）
NCHR(X)
返回?cái)?shù)據(jù)庫國家字符集中值為X的字符。NCHR(X) 等價(jià)于CHR(x USING NCHAR_CS).
NLS_CHARSET_DECL_LEN(byte_width,charset)
返回一個(gè)NCHAR值的聲明寬度(以字符為單位).byte_width是該值以字節(jié)為單位的長度charset是該值的字符集ID。
NLS_CHARSET_ID(charset_name)
返回指定字符集charset_name的數(shù)字ID。為charset_name指定“CHAR_CS”將返回?cái)?shù)據(jù)庫字符集的ID，為charset_name指定“NCHAR_CS”將返回?cái)?shù)據(jù)庫國家字符集的ID。如果charset_name是一個(gè)無效字符集名，將返回
NULL。NLS_CHARSET_ID和NLS_CHARSET_NAME是互為反函數(shù)。
NLS_CHARSET_NAME([charset_id])
返回指定字符集ID charset_id 的名字。如果 charset_id 是一個(gè)無效字符集ID，將返回NULL;
NLS_INITCAP(string[,nlsparams])
以字符串中每個(gè)單詞第一個(gè)字符大寫而單詞中其余字母小寫的形式返回string.nlsparams指定了一個(gè)與該會話缺省的不同的排序次序。如果沒有指定該參數(shù)，NLS_INITCAP與INITCAP相同。nlsparams應(yīng)該采取下面的形式：
'NLS_SORT=sort',其中sort是一個(gè)語言排序序列。
NLS_UPPER(string[,nlsparams])
以大寫形式返回string,不是字母的字符不受影響。如果沒有指定nlsparams,NLS_UPPER與UPPER相同。
NLS_LOWER
以小寫形式返回string,不是字母的字符不受影響，如果沒有指定nlsparams，NLS_LOWER與LOWER相同。
NLSSORT(string[,nlsparams])
返回用于排序string的字符串字節(jié)。所有值都被轉(zhuǎn)換為字節(jié)字符串，這樣在不同數(shù)據(jù)庫之間就保持了一致性。
如果沒有指定nlsparams,那么就會使用會話中缺省排序序列。
TRANSLATE(string USING {CHAR_CSINCHAR_CS})
TRANSLATE...USING 將輸入string參數(shù)轉(zhuǎn)換為數(shù)據(jù)庫字符集（指定CHAR_CS）或數(shù)據(jù)庫國家字符集（指定NCHAR_CS）.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2類型。如果指定CHAR_CS，返回類型為VARCHAR2，如果指定NCHAR_CS,返回類型為NVARCHAR2。TRANSLATE...USING是CONVERT功能的子集。
userenv的內(nèi)容﹕返回當(dāng)前用戶環(huán)境的信息,opt可以是:
ENTRYID:返回會話人口標(biāo)志
SESSIONID:返回會話ID
TERMINAL:返回終端或機(jī)器的標(biāo)器
ISDBA:查看當(dāng)前用戶是否是DBA﹐如果是則返回true;
LANGUAGE﹕返回當(dāng)前的環(huán)境變量
CLIENT_INFO﹕客戶端信息
LANG﹕返回當(dāng)前環(huán)境的語言的縮寫 sys_context的內(nèi)容:
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
<!--[if !supportLineBreakNewLine]-->
case函授
(http://www.eygle.com/archives/2005/10/oracle_howto_user_case_function.html)
example
create sequence STUDENT_SEQUENCE;
CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
stu_name VARCHAR2(20),
score NUMBER(10,0),
grade varchar2(2));
INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Scott', 98,null);
INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Margaret', 88,null);
INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Joanne', 75,null);
INSERT INTO students (id, stu_name, score, grade)
VALUES (student_sequence.NEXTVAL, 'Manish', 66,null);
update students
set grade =
case when score > 90 then 'a'
when score > 80 then 'b'
when score > 70 then 'c'
else 'd' end
----------------------------------------------------------------------------------
update students
set grade = (
select grade from
(
select id,
case when current_credits > 90 then 'a'
when current_credits > 80 then 'b'
when current_credits > 70 then 'c'
else 'd' end grade
from students
) a
where a.id = students.id
)
<!--[endif]-->http://www.aygfsteel.com/lingy/archive/2009/05/26/277972.html
|