oracle 函數集錦

標籤：des   style   http   使用   os   資料   io   for   

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
)