oracle的函數很多﹐特整理出來﹕
abs(m) m的絕對值
mod(m,n) m被n除后的余數
power(m,n) m的n次方
round(m[,n]) m四舍五入至小數點后n位的值（n缺省為0）
trunc(m[,n]) m截斷n位小數位的值（n缺省為0）
字符函數：
initcap(st) 返回st將每個單詞的首字母大寫，所有其他字母小寫
lower(st) 返回st將每個單詞的字母全部小寫
upper(st) 返回st將每個單詞的字母全部大寫
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中字符直到第一個不是set中的字符。缺省時，指的是空格
rtrim(st[,set]) 返回st,st為從右邊刪除set中字符直到第一個不是set中的字符。缺省時，指的是空格
replace(st,search_st[,replace_st]) 將每次在st中出現的search_st用replace_st替換，返回一個st。缺省時，刪除search_st
{
SUBSTR(string,a[,b])返回string的一部分，a和b以字符為單位。
SUBSTRB(string,a[,b])返回string的一部分，a和b是以字節為單位。
SUBSTRC(string,a[,b])返回string的一部分，a和b是以UNICODE完全字符為單位。
SUBSTR2(string,a[,b])返回string的一部分，a和b是以UCS2代碼點為單位。
SUBSTR4(string,a[,b])返回string的一部分，a和b是以UCS4代碼點為單位。
以上函數都是返回string的一部分，從字符位置A開始，長為B個字符。如果A是0，那它就被認為是1（字符串的開始位置）。如果A是正數，那么字符從左邊開始數。如果是負數，則從STRING的末尾開始，從右邊數。如果B不存在，那么缺省是整個字符串。如果B小于1，將返回NULL。如果A或B使用了浮點數，那么該數值首先被節取成一個整數，返回類型與STRING相同。
}
{
LENGTH(string)
LENGTHB(string)
LENGTHC(string)
LENGTH2(string)
LENGTH4(string)
以上函數返回string的長度。因為CHAR類型的值是填充空格的，所以如果string是CHAR數據類型，那么末尾的空格算在長度之內。如果string是NULL，函數返回NULL。
}
{
INSTR(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b以字符為單位。
INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以字節為單位。
INSTRC(string1,string2[,a][,b])返回string1中包含string2的位置。a和b是以UNICODE完全字符為單位。
INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以UCS2代碼點為單位
INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置a和b是以UCS4代碼點為單位。
以上函數返回string1中包含string2的位置。從左邊開始掃描string1,起始位置是A。如果A為負數那么從右邊開始掃描。第B次出現的位置將被返回。A和B缺省都為1，即返回在string1中第一次出現string2的位置。如果string2在A和B的規定下沒有找到那么就返回0。位置的計算是相對于string1的開始位置的，而不關A和B的取值。
}
轉換函數：
nvl(m,n) 如果m值為null,返回n,否則返回m
to_char(m[,fmt]) m從一個數值轉換為指定格式的字符串fmt缺省時，fmt值的寬度正好能容納所有的有效數字
to_number(st[,fmt]) st從字符型數據轉換成按指定格式的數值，缺省時數值格式串的大小正好為整個數
附：
to_char()函數的格式：
---------------------------------
符號 說明
---------------------------------
9 每個9代表結果中的一位數字
0 代表要顯示的先導0
$ 美元符號打印在數的左邊
L 任意的當地貨幣符號
. 打印十進制的小數點
, 打印代表千分位的逗號
---------------------------------
分組函數：
avg([distinct/all] n) 列n的平均值
count([all] *) 返回查詢范圍內的行數包括重復值和空值
count([distinct/all] n) 非空值的行數
max([distinct/all] n) 該列或表達式的最大值
min([distinct/all] n) 該列或表達式的最小值
stdev([distinct/all] n) 該列或表達式的標準偏差，忽略空值
sum([distinct/all] n) 該列或表達式的總和
variance([distinct/all] n) 該列或表達式的方差，忽略空值
日期函數：
add_months(d,n) 日期d加n個月
last_day(d) 包含d的月份的最后一天的日期
month_between(d,e) 日期d與e之間的月份數，e先于d
new_time(d,a,b) a時區的日期和時間d在b時區的日期和時間
next_day(d,day) 比日期d晚，由day指定的周幾的日期
sysdate 當前的系統日期和時間
greatest(d1,d2,...dn) 給出的日期列表中最后的日期
least(d1,k2,...dn) 給出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式轉變成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式轉成日期值，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截斷到最近的日期
附：
日期格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代碼 說明 舉例或可取值的范圍
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD 該月某一天 1－3
DY 三個大寫字母表示的周幾 SUN，...SAT
DAY 完整的周幾，大寫英文 SUNDAY，...SATURDAY
MM 月份 1－12
MON 三個大寫字母表示的月份 JAN，...DEC
MONTH 完整 JANUARY,...DECEMBER
RM 月份的羅馬數字 I,...XII
YY或YYYY 兩位，四位數字年
HH:MI:SS 時：分：秒
HH12或HH24 以12小時或24小時顯示
MI 分
SS 秒
AM或PM 上下午指示符
SP 后綴SP要求拼寫出任何數值字段
TH 后綴TH表示添加的數字是序數 4th,1st
FM 前綴對月或日或年值，禁止填充
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
43.求兩日期某一部分的差（比如秒）
直接用兩個日期相減（比如d1-d2=12.3）
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.根據差值求新的日期（比如分鐘）
SELECT sysdate+8/60/24 vaule FROM DUAL;
45.求不同時區時間
SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
-----時區參數,北京在東8區應該是Ydt-------
AST ADT 大西洋標準時間
BST BDT 白令海標準時間
CST CDT 中部標準時間
EST EDT 東部標準時間
GMT 格林尼治標準時間
HST HDT 阿拉斯加—夏威夷標準時間
MST MDT 山區標準時間
NST 紐芬蘭標準時間
PST PDT 太平洋標準時間
YST YDT YUKON標準時間
當前時間減去7分鐘的時間
select sysdate,sysdate - interval '7' MINUTE from dual
當前時間減去7小時的時間
select sysdate - interval '7' hour from dual
當前時間減去7天的時間
select sysdate - interval '7' day from dual
當前時間減去7月的時間
select sysdate,sysdate - interval '7' month from dual
當前時間減去7年的時間
select sysdate,sysdate - interval '7' year from dual
時間間隔乘以一個數字
select sysdate,sysdate - 8 *interval '2' hour from dual
<!--[if !supportLists]-->一、<!--[endif]-->關于NULL的幾點說明：
1、對空值做加、減、乘、除等運算操作，結果仍為空。
2、對空值進行比較時，只能使用“is null”和“is not null”判斷。
3、在對某字段進行按照升序排序的時候，若此字段中有的數據為空，則為NULL的將排到最后。
4、在用 LIKE '%' 來匹配時，空值不會被匹配。因為‘%’表示“任意多個字符”，但不包括“沒有任何字符”。
5、在計算一個number型字段 A 的SUM時，若此字段中有數據為空，則用 SUM(A) 來求和時，會忽略這樣的記錄。
6、在計算一個number型字段 A 的COUNT時，若此字段中有數據為空，則用 COUNT(A) 來計數時，不會忽略這樣的記錄，也會把這樣的記錄統計進來。
7、在where條件中，Oracle把結果為NULL的條件做為FALSE來處理，帶有這樣條件的select語句將不返回結果，并且不返回錯誤信息。但NULL和FALSE還是不同的。
NLS函數
除了NCHR，這些函數都是以字符類型為參數返回字符類型值。
CONVERT(string,dest_charset[,source_charset])
將輸入string轉換為指定字符集dest_charset。source_charset是輸入值的字符集——如果它沒有被指定，則缺省為數據庫字符集。輸入值可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB和NCLOB類型。返回值為
VARCHAR2類型。如果dest_charset中沒有輸入字符串中的一個字符，將會使用一個代替字符（由dest_charset定義）
NCHR(X)
返回數據庫國家字符集中值為X的字符。NCHR(X) 等價于CHR(x USING NCHAR_CS).
NLS_CHARSET_DECL_LEN(byte_width,charset)
返回一個NCHAR值的聲明寬度(以字符為單位).byte_width是該值以字節為單位的長度charset是該值的字符集ID。
NLS_CHARSET_ID(charset_name)
返回指定字符集charset_name的數字ID。為charset_name指定“CHAR_CS”將返回數據庫字符集的ID，為charset_name指定“NCHAR_CS”將返回數據庫國家字符集的ID。如果charset_name是一個無效字符集名，將返回
NULL。NLS_CHARSET_ID和NLS_CHARSET_NAME是互為反函數。
NLS_CHARSET_NAME([charset_id])
返回指定字符集ID charset_id 的名字。如果 charset_id 是一個無效字符集ID，將返回NULL;
NLS_INITCAP(string[,nlsparams])
以字符串中每個單詞第一個字符大寫而單詞中其余字母小寫的形式返回string.nlsparams指定了一個與該會話缺省的不同的排序次序。如果沒有指定該參數，NLS_INITCAP與INITCAP相同。nlsparams應該采取下面的形式：
'NLS_SORT=sort',其中sort是一個語言排序序列。
NLS_UPPER(string[,nlsparams])
以大寫形式返回string,不是字母的字符不受影響。如果沒有指定nlsparams,NLS_UPPER與UPPER相同。
NLS_LOWER
以小寫形式返回string,不是字母的字符不受影響，如果沒有指定nlsparams，NLS_LOWER與LOWER相同。
NLSSORT(string[,nlsparams])
返回用于排序string的字符串字節。所有值都被轉換為字節字符串，這樣在不同數據庫之間就保持了一致性。
如果沒有指定nlsparams,那么就會使用會話中缺省排序序列。
TRANSLATE(string USING {CHAR_CSINCHAR_CS})
TRANSLATE...USING 將輸入string參數轉換為數據庫字符集（指定CHAR_CS）或數據庫國家字符集（指定NCHAR_CS）.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2類型。如果指定CHAR_CS，返回類型為VARCHAR2，如果指定NCHAR_CS,返回類型為NVARCHAR2。TRANSLATE...USING是CONVERT功能的子集。
userenv的內容﹕返回當前用戶環境的信息,opt可以是:
ENTRYID:返回會話人口標志
SESSIONID:返回會話ID
TERMINAL:返回終端或機器的標器
ISDBA:查看當前用戶是否是DBA﹐如果是則返回true;
LANGUAGE﹕返回當前的環境變量
CLIENT_INFO﹕客戶端信息
LANG﹕返回當前環境的語言的縮寫 sys_context的內容:
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.tkk7.com/lingy/archive/2009/05/26/277972.html
|