7種函數的詳細介紹及用法:
1、錯誤報告(error reporting)函數
sqlcode 返回oracle錯誤號
sqlerrm 返回oracle錯誤信息
主要用於異常處理
declare
err_num number;
err_msg varchar2(100);
begin
...
exception
...
when others then
/*當引發異常的時候,將SQLERRM和SQLCODE值取出,插入表ERRORS中*/
err_num:=sqlcode;
err_msg:=substr(sqlerrm,1,100);
insert into errors(errnum,errmsg) values(err_num,err_msg);
end;
2、數值(Number)函數
abs 返回絕對值
acos 返回以弧度為單位的反余弦值
asin 返回以弧度為單位的反正弦值
atan(m) 返回m的反正切值(以弧度為單位),即返回arctan(m)
atan2(m,n) 返回m與n的反正切值即返回aretan(m/n)
bitand 位與函數,即按位相與
ceil 返回大於等於特定值的最小整數
cos 返回以弧度為單位的余弦值
cosh 返回以弧度為單位的雙曲余弦值
exp 求e的x方(e=2.71828,如 exp(3)=e的3次方)
floor 返回小於等於特定值的最大整數
ln 返回自然對數
log(x,y) 返回以x為底的y的對數
mod(x,y) 返回x除以y的余數
power(x,y) 返回x的y次方
round 返回四捨五入的值
sign(x) 根據x的值決定返回1、0或-1(x>0,返回1;x=0,返回0;x<0,返回-1)
sin 返回以弧度為單位的正弦值
sinh 返回以弧度為單位的雙曲正弦值
sqrt(x) 返回x的平方根
tan 返回以弧度為單位的正切值
tanh 返回以弧度為單位的雙曲正切值
trunc(x,y) 返回剪裁到指定位置y後x的值(y可不寫默認為0,將x裁剪為整數)
****DUAL表: 是oracle工作表,是一個行和一個列的標准表,它被用做一個
虛擬表。要展示oracle內置函數的功能時,用到此表
1 bitand: select order_id,customer_id,decode(bitand(order_status,1),1,
'warehouse','postoffice') location;
2 ceil: select ceil(-7.38) from dual; -7
3 floor: select floor(-7.38) from dual; -8
4 sign: select sign(-23) from dual; -1
5 round: select round(123.456),round(123.789,-1),round(456.123,1) from dual;
注:其結果是四捨五入到保留指定位
6 trunc: select trunc(456.325,1),trunc(753.256,-1),trunc(5240.45) from dual;
注:其結果是保留到指定位但不執行四捨五入
3、字符(char)函數:下面為ORACLE中所有的字符函數。
ASCII 返回指定字符的ASCII碼
CHR 返回指定的ASCII碼的字符和ASCII函數互為反函數
CONCAT 連接兩個指定的字符(‘與“||”操作符作用相同)
INITCAP 返回拽定的字符中,該字符串每一個單詞的第一個字母為大寫,其他均為小寫,
不影響不是字母的字符串。
INSTR(X,Y[,Z]) 返回Y在X中的位置,如沒有則為0,如有Z則從Z開始找到的Y在X中的位置,與SUBSTR(X,Y[,Z])相似。
INSTRB(X,Y) 返回Y在X中位置,如沒有則為0,對於單字節字符系統,返回的值以字節為單位
LENGTH(X) 返回以字節為單位的X的長度,包括填充的字符,如果值是未知的,則返回NULL;
LENGTHB(x) 作用同LENGTH(X),對於單字節字符系統,返回值以字節為單位。
LOWER 將特定的字符串化為小寫,不影響不是字母的字符串
LPAD(X,I,Y) 用字符串Y按指定的填充I填充X字符串的左邊。
LTRIM(X,Y) 裁剪字符串和Y中字符相同的X左邊的字符。
RTRIM(X,Y) 裁剪字符串和Y中字符相同的X右邊的字符。
~~NLS_INITCAP 與INITCAP函數相同,它可以使用NLSSORT指定的分類方法。
~~NLS_LOWER 與LOWER函數相同,它可以使用NLSSORT指定的分類方法。
~~NLS_UPPER 與UPPER函數相同,它可以使用NLSSORT指定的分類方法。
NLSSORT(string) 返回用於排序string的字符串字節。
REPLACE(X,Y,Z) 用Z字符串取代X字符串中的Y字符串。
RPAD(X,I,Y) 用字符串Y按指定的填充數I填充X字符串的右邊。
LPAD(X,I,Y) 用字符串Y按指定的填充婁I填充X字符串的左邊。
SPIMDEX 返回字符串的語音表示。
SUBSTRB 同SUBSTR,以字節婁而百字符數返回字符串的一部分。
TRIM 裁剪字符串兩邊的字符,是LTRIM和RTRIM兩個函數的組合。
TRUNSLATE 同REPLACE,作用於字符基礎上而非字符串基礎上。
UPPER 將整個字符串轉換成大寫,不對非字母字符串產生作用。
祥解字符函數:
1 CONCAT : SELECT CONCAT('MY','ORACLE') "ORACLE" FROM DUAL;
2 LPAD : SELECT LPAD('THIS IS MY ORACLE',10,'XY') FROM DUAL;
結果是 THIS IS MY
SELECT LPAD('THIS IS MY ORACLE',20,'XY') FROM DUAL;
結果是 XYXTHIS IS MY ORACLE
SELECT LPAD('THIS IS MY ORACLE',18,'XY') ORACLE FROM DUAL;
結果是 XTHIS IS MY ORACLE
3 LTRIM : SELECT LTRIM('XXBBBBZZZZYXYXMYBOOK','XB') FROM DUAL;
結果是 ZZZZYXYXMYBOOK
4 NLS_INITCAP : SELECT NLS_INITACP('jdfhkj','NLS_SORT=Xdutch') result from dual;
結果是 Jdfhkj
5 NLS_LOUER: SELECT NLS_LOWER('JKJKJK','NLS_SORT=Xgerman') "result" from dual;
結果是 jkjkjk
6 NLS_UPPER: SELECT NLS_UPPER('jkjkjk','NLS_SORT=Xgerman') "result" from dual;
結果是 JKJKJK
7 INSTR : SELECT INSTR('AABBABCAB','AB') "result" from dual;
結果是 2
SELECT INSTR('AABBASCAB','AB',4) "result" from dual;
結果是 8
8 NLSSORT: SELECT NLSSORT ('MYORACLE_函數123') FROM DUAL;
結果是 4D794F524C434C455FBAAFCAFD31323300返回用於排序string字符串字節。
9 REPLACE : SELECT REPLACE('this is that','is','are') "result" from dual;
結果是 thare are that
SELECT REPLACE('this is that','is','are') "result" from dual;
結果是 th that
10 SOUNDEX: SELECT SOUNDEX('SCOTT') ONE,SOUND('SKIT') TWO FROM DUAL;
結果是 IST SET
---- -----
S300 S300
11 SUBSTR : SELECT SUBSTR('MYBOOKISORACLE','3,8) "RESULT" FROM DUAL;
結果是Result
------
BOOKISOR
SELECT SUBSTR('MybookIsOracle',-6,8) "Result" from dual;
結果是result
--------
Oracle
SELECT SUBSTR('MYBOOKISORACLE',6) "RESULT" FROM DUAL;
結果是result
--------
KISORACLE
12 TRANSLATE : SELECT TRANSLATE('ABCMYORACLEABC','ABC','**') "RESULT" FROM DUAL;
結果是result
--------
**myoracle**
** 13 TRIM : SELECT TRIM('A' FROM 'AAMYORACLEAA') "RESULT" FROM DUAL;
結果是 result
----------
MYORACLE
4、轉換 (Conversion) 函數
CHARTOROWID: 將包含外部格式的ROWID的CHAR或VARCHAR2數值轉換為內部的二進制格式
CONVERT : 從一個字符集轉換到另一個字符集
HEXTORAW: 從十六進制字符串值轉換為內部的原始值
ROWTOHEX: 將內部的原始值轉換為十六進制字符串值
ROWIDTOCHAR: 將ROW ID轉換為其外部的18-字節字符串表示
TO_BLOB : 將指定的值轉換為BLOB類型的值
TO_CHAR: 將DATE、MLSLABLE和NUMBER強制轉換為VARCHAR2字符串
TO_CLOB: 將指定的值轉換為CLOB類型的值
TO_DATE: 將CHAR或VARCHAR2字符串強制轉換為日期值
TO_LABEL : 將CHAR或varchar2字符串強制轉換為MLSABEL
TO_MULTI_BYTE: 將任何單字節字符串轉換為多字節字符串
TO_NUMBER: 將CHAR或VARCHAR2字符串強制轉換為NUMBER值
TO_SINGLE_BYTE: 將任何多字節字符串轉換為單字節字符串
常用字符集:
US7ASCII: U.S7位ASCII字符集。這是大多數Unix操作系統和運行於Unix上的Oracle數據庫所采用的字符集
WE8DEC: DEC西歐8位字符集
WE8HP: HP西歐LaserJet8位字符集
F7DEC: DEC法語7位字符集
WE8EBCDIC500 : IBM西歐EBCDIC Code Page 500字符集
WE8PC850 : IBM PC Code Page 500 字符集,這是大多數PC系統和運行於PC上的Oracle數據庫所采用的字符集
WE8ISO8859P1: ISO_8859-1西歐8位字符集
TO_CHAR的四種轉換情況:
1 TO_CHAR(DATE[,FORMAT[NLSPARAMS]])
1> 將日期期轉換為字符型
AD、A.D. AD 指示符。可帶句點也可不帶句點。
AM、A.M. 午前指示符。可帶句點也可不帶句點。
BC、B.C. BC指示符。可帶句點也可不帶句點。
CC、C.C. 世紀。SCC作為負數返回BC日期。(century:世紀)
*日期格式元素與描述:
D 一周中的星期幾,用1-7表示,(星期天為1)
DAY 天的全稱。使用空格填充9個字符,大小寫敏感。
DD 月中的第幾天(1-31)
DDD 年中的第幾天(1-366)
DY 天名稱的簡寫。大小寫敏感
FX 在數據元素語格式之間采用准確的匹配模式。
FM 填充模式。確定在輸出格式中是否用空格填充。
HH、HH12 一天中的時間(1-12)
HH24 一天中的時間(1-24)
IW 返回根據ISO標准的年中的第幾周(1-52、1-53)
IYY、IY、I 返回ISO年中最後三、二、一位數據。
IYYY 返回ISO年中標准的四位數。
J Julian日,從公元前4712年1月1日起到指定日期的天數。
MI 分(1-59),表時間
MM 月(1-12)
MONTH 月份的名稱。使用空格填充9個字符。大小寫敏感。
MON 月份名稱的簡寫,大小寫敏感。
PM、P.M. 午後的指示符。
Q 一年中的季度(1-4)
RM 農驪數字中的月份(I_XII)
RR 世紀中年份的最後兩位數字。
SS 秒(0-59)
SSSS 從午前開始過去的秒數。(0-86399)
WW 一年中的第幾個星期(1-53)
W 該月中的第幾個星期(1-5)
Y,YYY 年份,用逗號進行分位。
YYYY,SYYYY 四位的年份。SYYYY將BC日期作為負數返回。
YEAR、SYEAR 年份的名字,SYEAR將BC日期作為負數返回,大小寫敏感
YYY、YY、Y 年份的最後三、二、一位數。
停頓(Punctuation): 在結果字符串中產生所有必須的停頓間隔符。
“文本”: 在雙引號中的文本被重新產生出來。
SELECT TO_CHAR(SYSDATE,'A.D.YYYY"年"-MONTH-DD"日"-DAY') "Reault" from dual;
result
-------
公元2001年-10月-12日-星期五
2> TO_CHAR(NUMBER[,FORMAT[,NLSPARAMS]])
*數字格式元素:
9 如9999 : 每一個9表示一個有效位,轉換值的有效位應和9的各位相同,如要轉換的
是負數則應有前導的負號,前導如為0則視為空格。
0 如0999或9990 返回不包括空格的前導可後繼0。
$ 如$999 : 返回帶有前導貨幣符號的數值。
B 如B999 : 當整數為0時,將該小數的整數部分填充為空格。
MI如99MI : 該值如為負數,則加後繼負號,如非負數則加一後繼占位符。
S 如S999或999S S在前,為數據加前導+或-號、S在後,為數據加後繼+或-號。
PR 如999PR: 如為負值,用尖括號括起,如為正值,則前導後繼各加一空格。
D 如99D99: 返回小數點的位置,兩邊的9指定了最大位數。
G 如99G99: 返回千分位分隔符,G可出現多次
C 如C999: 在指定的位置上返回本地貨幣號。
L 如L999: 在指定位置上返回本地貨幣號
, 如99,99: 在指定的位置返回一個逗號,而不管指定的千分位分隔符。
. 如99.99 : 在指定的位置返回一個小數點,而不管指定的小數點分隔符。
V 如99V99: 返回與10的n次方相乘的值,n是V後面9的個數。
EEEE 如9.99EEEE : 以科學計數法返回值。
RM,rm如RM.rm: 用大寫(RM)或小寫(rm)的羅馬數字返回指定的數字。
*
NLS_NUMERIC_CHARACTERS:可簡寫為'NLS_NUMBER_CHARS'.其表示的意思是指定分
組分隔符和小數點使用的字符。
NLS_CURRENCY:指定Oracle默認的貨幣。
NLS_ISO_CURRENCY: 指定ISO貨幣符號的字符。
例子: SELECT TO_CHAR(123456789,'L999G999G999D99','NLS_CURRENCY=¥') "RESULT" FROM DUAL;
結果是RESULT
--------
¥123,456,789.00
TO_CHAR的另外兩種轉換情況:
3> TO_CHAR(label[,format]) :它用於將MLSLABEL類型的label值轉換為一個VARCHAR2類型的值。
4> TO_CHAR(character):它用於將NCHAR、NVARCHAR2、CLOB或NCLOB轉換成數據庫能夠認識的字符類型。
2、TO_CLOB 將給定的NCLOB值轉換成LOB字段或將別的字符串轉換成CLOB值
3、TO_DATE(STRING[,FORMAT[,NLSPARAMS]])函數將CHAR或VARCHAR2類型的值轉換為一個DATE類型的值。
('NLS_DATE_LANGUAGE=language'指定返回日期所使用的語言。)
select to_Date('1999 july 21','yyyy-mm-dd','nls_date_language=english') "result" from dual;
result
------
21-7月-99
select to_date('20010809','yyyy-mm-dd') "result" from dual;
result
-------
09-8月-01
4、TO_MULTI_BYTE: 函數將string中所有的單字節字符轉換為等價的多字節字符。只當數據庫字符集中同時包
含單字節和多字節時才有用,否則string不會進行任何處理而返回
select to_multi_byte('myoracle') "result" from dual;
result
------
myoracle
5、TO_SINGLE_BYTE(STRING) :函數將string中有的多字節字符轉換成單字節字符。和TO_MULTI_BYTE互為反函數。
select to_single_byte('myoracle9i') "result" from dual;
result
--------
myoracle9i
6、TO_NUMBER(STRING[,FORMAT[,NLSPARAMS]]):函數將CHAR或VARCHAR2類型的STRING轉換成NUMBER類型的數值。
select to_number('$1234.56','$9999.99') "result" from dual;
result
------
1234.56
6.5日期(Date)函數
add_months 給指定的日期加上若干個月。
current_date 返回當前會話期時區內的日期。
current_timestamp 返回當前會話期時區內的日期和時間。
dbtimezone 返回當前數據庫所在時區。
extract 從時間參數中取出並返回指定的時間字段。
from_tz 將一個含有時區的TEMESTAMP的值轉換成TIMESTAMP WITH TIME ZONE值。
last_day 返回給定月份的最後一天。
localtimestamp 以timestamp數據形式返回當前會話期時區內的日期和時間。
months_between 返回兩個日期之間的月份。
new_time 返回根據用戶指定的時區計算相應的時間/天數值。
next_day 返回指定日期後的下一個星期幾的日期。
numtodsinterval 將指定的值轉換成INTERVAL DAY TO SECOND 類型。
numtoyminterval 將指定的值轉換成INTERVAL YEAR TO MONTH 類型。
round 按指定的日期參數(年、月、日)四捨五入指定的日期。
sessiontimezone 返回當前會話期的時區。
sysdate 返回系統當前的日期和時間。
systimestamp 以timestamp with time zone 類型返回系統日期。
to_dsinterval 將指定的字符串轉換成INTERVAL DAY TO SECOND類型。
to_timestamp 將指定的字符串轉換成TIMESTAMP類型。
to_timestamp_tz 將指定的字符串轉換成TIMESTAMP WITH TIME ZONE類型。
to_yminterval 將指定的字符串轉換成INTERVAL YEAR TO MONTH類型。
tz_lffset 根據指定的值返回相應的時區偏移量。
trunc 按指定的日期參數(年、月、日)剪裁指定的日期。
1、sysdate函數
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') "now" from dual;
now
-------------------
2001-10-14,09:02:09
2、add_months(date,x)函數
select add_months(to_date('2001-01-30','yyyy-mm-dd'),1) "first" from dual;
first
--------
28-2月-01
3、current_date函數
alter session set time_zone='-5:0';
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sessiontimezone,current_date from dual;
sessintimezone current_date
-------------- ------------
-05:0016-10月-2001 21:16:09
4、current_timestamp函數
alter session set time_zone='-5:0';
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sessiontimezone,current_timestamp from dual;
sessiontimezone current_timestamp
--------------- -----------------------------
-05:00 16-10月-2001 21:26:04.715620 p.m. -5:00
5、dbtimezone函數
select dbtimezone from dual;
dbtime
-------
+08:00
6、extract函數
select extract(year from date'1998-03-07') from dual;
extract(year from date'1998-03-07')
-----------------------------------
1998
7、from_tz函數
select from_tz(timestamp'2000-03-28 08:00:00','3:00') from dual;
from_tz(timestamp'2000-03-28 08:00:00','3:00)
-----------------------------------------------
28-3月-00 08:00:00 am +03:00
8、localtimestamp函數
alter session set time_zone='-5:00';
select current_timestamp,localtimestamp from dual;
current_timestamp localtimestamp
---------------------------------------------------------
04-apr-00 01:27:18.999220 pm -05:00 04-aor-00 01:27:19 pm
9、months_between函數
select months_between('18-10月-01','18-10月-01') "first"
months_between('18-10月-01','19-4月-01') "second' from dual;
first second
------- ----------
-6 5.96774194
10、NEW_TIME函數 : 將ZONE1時區的日期DATE轉換成ZON2時區的日期。
AST 大西洋標准時間
ADT 大西洋夏令時
BST 白令標准時
BDT 白令夏令時
CST 中部夏令時
EDT 東部夏令時
EST 東部標准時
GMT 格林尼治標准時
HST 阿拉斯加-夏威夷標准時
HDT 阿拉斯加-夏威夷夏令時
MST 山地標准時
MDT 山地夏令時
NST 紐芬蘭標准時
PST 太平洋標准時
PDT 太平洋夏令時
YST 育空標准時
YDT 育空夏令時
select to_char(new_time(sysdate,'pst','gmt'),'yyyy-mm-dd,hh:mi:ss') "result" from dual;
result
-------------------
2001-10-14,06:28:10
11、NEXT_DAY函數
select next_day(sysdate,'星期五') from dual;
next_day(sysda
----------------
19-10月-01
12、NUMTODSINTERVAL(N,'CHAR_EXPR') 函數將指定的N轉換成INTERVAL DAY GO SECOND類型。
select numtodsinterval(100,'hour') from dual;
numtodsinterval(100,'hour')
--------------------------------
+000000004 04:00:00.000000000
13、NUMTOMINTERVAL(N,'CHAR_EXPR')函數將指定的值N轉換成INTERVAL YEAR TO MONTH類型。
select numtoyminterval(100,'month') from dual;
numtoyminterval(100,'month')
------------------------------
+0.000000008-04
14、ROUND(DATE,[,FORMAT])函數 將指定的值N轉換成INTERVAL YEAR TO MINTH 類型。
select round(to_date('12-6月-01'),'mm') "first"
round(to_date('18-6月-01'),'mm') "second" from dual;
first second
------- --------
01-6月-01 01-7月-01
15、SESSIONTIMEZONE函數返回當前會話期的時區
select sessiontimezone from dual;
session
---------
+08:00
16、SYSTIMESTAMP函數:以TIMESTAMP WITH TIME ZONE 類型返回當前系統日期。
select systimestamp from dual;
systimestamp
-------------
28-10月-01 12.38.55.538741 pm +8:00
17、TO_DSIONTERVAL(CHAR[,NLSPARAM])函數將指定的字符串 轉換成INTERVAL DAY TO SECOND類型。
select employee_id, alst_name from hr.employees
where hire_date-to_dsinterval('100 10:00:00')
>date '1985-01-01';
18、TO_TIMESTAMP函數將指定的字符串轉換成TIMESTAMP類型
select to_timestamp('1999-12-01 11:00:00','yyyy-mm-dd hh:mi:ss') "first" from dual;
first
---------
01-DEC-99 11.00.00.000000000 AM
19、TO_TIMESTAMP_TZ(char,[fmt[,nlsparam]])函數將指定的字符串轉換成TIMEST AMP WITH TIME ZONE 類型。
20、TO_YMINTERVAL(char)函數將指定的字符串轉換成INTERVAL YEAR TO MONTH類型。
21、TZ_OFFSET函數
select tz_offset('us/eastern') from dual;
tz_offs
----------
-4:00
22、TRUNC(DATE[,FORMAT])函數將日期DATE按照FORMAT指定的格式進行截取,缺省為'DD',截取日期為當月第一天。
SELECT TRUNC(TO_DATE('12-6月-01'),'MM') "FIRST"
TRUNC(TO_DATE('18-6月-01'),'MM') "SECOND" FROM DUAL;
FIRST SECOND
------- ----------
01-6月-01 01-6月-01
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss am') "result1",
to_char(trunc(sysdate),'yyyy-mm-dd hh:mi:ss am') "result2" from dual;
result1 result2
---------- --------------
2001-10-21 02:48:49 下午 2001-10-21 12:00:00 上午
6.6分組(GROUP) 函數
AVG 求返回的各列的平均值
COUNT 返回查詢的行的數目
GLB 返回MSLABLE的最大下界
LUB 返回MSLABEL的最小上界
MAX 返回查詢列中的最大值
STDDEV 返回查詢列的平均偏差
SUM 返回查詢列的總和
VARIANCE 返回查詢列的統計方差
1、STDDEV函數
select stddev(mgr) from scott.emp;
stddev(mgr)
------------
103.71466
2、VARIANGE([DISTINCTIALL] column)函數查詢列的統計方差
select variange(mgr) from scott.emp
variance(mgr)
--------------
10756.7308
6.7雜項(Misc)函數
BILENAME 返回一個BFILE位置指示符,指向一個存儲實際的LOB二進制文件的存儲文件
DECODE 類似於IF_THEN_ELSE語句
DUMP 返回有關指定數值的內部表示信息VARCHAR2類型的數值。
EMPTY_BLOB 返回一個空的二進制指示符
EMPTY_CLOB 返回一個空的字符指示符
GREATEST 返回給定的參數列表中最大的表達式
LEAST 返回給定的參數列表中最小的表達式
NLS_CHARSET_DECL_LEN 返回NCHAR列的定義長度(以字符長度表示)
NLS_CHARSET_ID 返回與NLS字符集名稱關聯的NLS集ID號
NLS_CHARSET_NAME 返回傳遞給函數的ID關聯的NLS字符名稱
NVL 從給定的表達式中選擇第一個不為空的值
SYS_CONTEXT 返回當前進程的屬性
SYS_GUID 產生和返回一個16字節的全局的惟一的標志符(ROW)值
UID 返回ORACLE中用戶的ID號
USER 返回當前用戶名稱
USERENV 返回當前工作環境的信息
VSIZE 返回指定值的內部字節數
1、錯誤報告(error reporting)函數
sqlcode 返回oracle錯誤號
sqlerrm 返回oracle錯誤信息
主要用於異常處理
declare
err_num number;
err_msg varchar2(100);
begin
...
exception
...
when others then
/*當引發異常的時候,將SQLERRM和SQLCODE值取出,插入表ERRORS中*/
err_num:=sqlcode;
err_msg:=substr(sqlerrm,1,100);
insert into errors(errnum,errmsg) values(err_num,err_msg);
end;
2、數值(Number)函數
abs 返回絕對值
acos 返回以弧度為單位的反余弦值
asin 返回以弧度為單位的反正弦值
atan(m) 返回m的反正切值(以弧度為單位),即返回arctan(m)
atan2(m,n) 返回m與n的反正切值即返回aretan(m/n)
bitand 位與函數,即按位相與
ceil 返回大於等於特定值的最小整數
cos 返回以弧度為單位的余弦值
cosh 返回以弧度為單位的雙曲余弦值
exp 求e的x方(e=2.71828,如 exp(3)=e的3次方)
floor 返回小於等於特定值的最大整數
ln 返回自然對數
log(x,y) 返回以x為底的y的對數
mod(x,y) 返回x除以y的余數
power(x,y) 返回x的y次方
round 返回四捨五入的值
sign(x) 根據x的值決定返回1、0或-1(x>0,返回1;x=0,返回0;x<0,返回-1)
sin 返回以弧度為單位的正弦值
sinh 返回以弧度為單位的雙曲正弦值
sqrt(x) 返回x的平方根
tan 返回以弧度為單位的正切值
tanh 返回以弧度為單位的雙曲正切值
trunc(x,y) 返回剪裁到指定位置y後x的值(y可不寫默認為0,將x裁剪為整數)
****DUAL表: 是oracle工作表,是一個行和一個列的標准表,它被用做一個
虛擬表。要展示oracle內置函數的功能時,用到此表
1 bitand: select order_id,customer_id,decode(bitand(order_status,1),1,
'warehouse','postoffice') location;
2 ceil: select ceil(-7.38) from dual; -7
3 floor: select floor(-7.38) from dual; -8
4 sign: select sign(-23) from dual; -1
5 round: select round(123.456),round(123.789,-1),round(456.123,1) from dual;
注:其結果是四捨五入到保留指定位
6 trunc: select trunc(456.325,1),trunc(753.256,-1),trunc(5240.45) from dual;
注:其結果是保留到指定位但不執行四捨五入
3、字符(char)函數:下面為ORACLE中所有的字符函數。
ASCII 返回指定字符的ASCII碼
CHR 返回指定的ASCII碼的字符和ASCII函數互為反函數
CONCAT 連接兩個指定的字符(‘與“||”操作符作用相同)
INITCAP 返回拽定的字符中,該字符串每一個單詞的第一個字母為大寫,其他均為小寫,
不影響不是字母的字符串。
INSTR(X,Y[,Z]) 返回Y在X中的位置,如沒有則為0,如有Z則從Z開始找到的Y在X中的位置,與SUBSTR(X,Y[,Z])相似。
INSTRB(X,Y) 返回Y在X中位置,如沒有則為0,對於單字節字符系統,返回的值以字節為單位
LENGTH(X) 返回以字節為單位的X的長度,包括填充的字符,如果值是未知的,則返回NULL;
LENGTHB(x) 作用同LENGTH(X),對於單字節字符系統,返回值以字節為單位。
LOWER 將特定的字符串化為小寫,不影響不是字母的字符串
LPAD(X,I,Y) 用字符串Y按指定的填充I填充X字符串的左邊。
LTRIM(X,Y) 裁剪字符串和Y中字符相同的X左邊的字符。
RTRIM(X,Y) 裁剪字符串和Y中字符相同的X右邊的字符。
~~NLS_INITCAP 與INITCAP函數相同,它可以使用NLSSORT指定的分類方法。
~~NLS_LOWER 與LOWER函數相同,它可以使用NLSSORT指定的分類方法。
~~NLS_UPPER 與UPPER函數相同,它可以使用NLSSORT指定的分類方法。
NLSSORT(string) 返回用於排序string的字符串字節。
REPLACE(X,Y,Z) 用Z字符串取代X字符串中的Y字符串。
RPAD(X,I,Y) 用字符串Y按指定的填充數I填充X字符串的右邊。
LPAD(X,I,Y) 用字符串Y按指定的填充婁I填充X字符串的左邊。
SPIMDEX 返回字符串的語音表示。
SUBSTRB 同SUBSTR,以字節婁而百字符數返回字符串的一部分。
TRIM 裁剪字符串兩邊的字符,是LTRIM和RTRIM兩個函數的組合。
TRUNSLATE 同REPLACE,作用於字符基礎上而非字符串基礎上。
UPPER 將整個字符串轉換成大寫,不對非字母字符串產生作用。
祥解字符函數:
1 CONCAT : SELECT CONCAT('MY','ORACLE') "ORACLE" FROM DUAL;
2 LPAD : SELECT LPAD('THIS IS MY ORACLE',10,'XY') FROM DUAL;
結果是 THIS IS MY
SELECT LPAD('THIS IS MY ORACLE',20,'XY') FROM DUAL;
結果是 XYXTHIS IS MY ORACLE
SELECT LPAD('THIS IS MY ORACLE',18,'XY') ORACLE FROM DUAL;
結果是 XTHIS IS MY ORACLE
3 LTRIM : SELECT LTRIM('XXBBBBZZZZYXYXMYBOOK','XB') FROM DUAL;
結果是 ZZZZYXYXMYBOOK
4 NLS_INITCAP : SELECT NLS_INITACP('jdfhkj','NLS_SORT=Xdutch') result from dual;
結果是 Jdfhkj
5 NLS_LOUER: SELECT NLS_LOWER('JKJKJK','NLS_SORT=Xgerman') "result" from dual;
結果是 jkjkjk
6 NLS_UPPER: SELECT NLS_UPPER('jkjkjk','NLS_SORT=Xgerman') "result" from dual;
結果是 JKJKJK
7 INSTR : SELECT INSTR('AABBABCAB','AB') "result" from dual;
結果是 2
SELECT INSTR('AABBASCAB','AB',4) "result" from dual;
結果是 8
8 NLSSORT: SELECT NLSSORT ('MYORACLE_函數123') FROM DUAL;
結果是 4D794F524C434C455FBAAFCAFD31323300返回用於排序string字符串字節。
9 REPLACE : SELECT REPLACE('this is that','is','are') "result" from dual;
結果是 thare are that
SELECT REPLACE('this is that','is','are') "result" from dual;
結果是 th that
10 SOUNDEX: SELECT SOUNDEX('SCOTT') ONE,SOUND('SKIT') TWO FROM DUAL;
結果是 IST SET
---- -----
S300 S300
11 SUBSTR : SELECT SUBSTR('MYBOOKISORACLE','3,8) "RESULT" FROM DUAL;
結果是Result
------
BOOKISOR
SELECT SUBSTR('MybookIsOracle',-6,8) "Result" from dual;
結果是result
--------
Oracle
SELECT SUBSTR('MYBOOKISORACLE',6) "RESULT" FROM DUAL;
結果是result
--------
KISORACLE
12 TRANSLATE : SELECT TRANSLATE('ABCMYORACLEABC','ABC','**') "RESULT" FROM DUAL;
結果是result
--------
**myoracle**
** 13 TRIM : SELECT TRIM('A' FROM 'AAMYORACLEAA') "RESULT" FROM DUAL;
結果是 result
----------
MYORACLE
4、轉換 (Conversion) 函數
CHARTOROWID: 將包含外部格式的ROWID的CHAR或VARCHAR2數值轉換為內部的二進制格式
CONVERT : 從一個字符集轉換到另一個字符集
HEXTORAW: 從十六進制字符串值轉換為內部的原始值
ROWTOHEX: 將內部的原始值轉換為十六進制字符串值
ROWIDTOCHAR: 將ROW ID轉換為其外部的18-字節字符串表示
TO_BLOB : 將指定的值轉換為BLOB類型的值
TO_CHAR: 將DATE、MLSLABLE和NUMBER強制轉換為VARCHAR2字符串
TO_CLOB: 將指定的值轉換為CLOB類型的值
TO_DATE: 將CHAR或VARCHAR2字符串強制轉換為日期值
TO_LABEL : 將CHAR或varchar2字符串強制轉換為MLSABEL
TO_MULTI_BYTE: 將任何單字節字符串轉換為多字節字符串
TO_NUMBER: 將CHAR或VARCHAR2字符串強制轉換為NUMBER值
TO_SINGLE_BYTE: 將任何多字節字符串轉換為單字節字符串
常用字符集:
US7ASCII: U.S7位ASCII字符集。這是大多數Unix操作系統和運行於Unix上的Oracle數據庫所采用的字符集
WE8DEC: DEC西歐8位字符集
WE8HP: HP西歐LaserJet8位字符集
F7DEC: DEC法語7位字符集
WE8EBCDIC500 : IBM西歐EBCDIC Code Page 500字符集
WE8PC850 : IBM PC Code Page 500 字符集,這是大多數PC系統和運行於PC上的Oracle數據庫所采用的字符集
WE8ISO8859P1: ISO_8859-1西歐8位字符集
TO_CHAR的四種轉換情況:
1 TO_CHAR(DATE[,FORMAT[NLSPARAMS]])
1> 將日期期轉換為字符型
AD、A.D. AD 指示符。可帶句點也可不帶句點。
AM、A.M. 午前指示符。可帶句點也可不帶句點。
BC、B.C. BC指示符。可帶句點也可不帶句點。
CC、C.C. 世紀。SCC作為負數返回BC日期。(century:世紀)
*日期格式元素與描述:
D 一周中的星期幾,用1-7表示,(星期天為1)
DAY 天的全稱。使用空格填充9個字符,大小寫敏感。
DD 月中的第幾天(1-31)
DDD 年中的第幾天(1-366)
DY 天名稱的簡寫。大小寫敏感
FX 在數據元素語格式之間采用准確的匹配模式。
FM 填充模式。確定在輸出格式中是否用空格填充。
HH、HH12 一天中的時間(1-12)
HH24 一天中的時間(1-24)
IW 返回根據ISO標准的年中的第幾周(1-52、1-53)
IYY、IY、I 返回ISO年中最後三、二、一位數據。
IYYY 返回ISO年中標准的四位數。
J Julian日,從公元前4712年1月1日起到指定日期的天數。
MI 分(1-59),表時間
MM 月(1-12)
MONTH 月份的名稱。使用空格填充9個字符。大小寫敏感。
MON 月份名稱的簡寫,大小寫敏感。
PM、P.M. 午後的指示符。
Q 一年中的季度(1-4)
RM 農驪數字中的月份(I_XII)
RR 世紀中年份的最後兩位數字。
SS 秒(0-59)
SSSS 從午前開始過去的秒數。(0-86399)
WW 一年中的第幾個星期(1-53)
W 該月中的第幾個星期(1-5)
Y,YYY 年份,用逗號進行分位。
YYYY,SYYYY 四位的年份。SYYYY將BC日期作為負數返回。
YEAR、SYEAR 年份的名字,SYEAR將BC日期作為負數返回,大小寫敏感
YYY、YY、Y 年份的最後三、二、一位數。
停頓(Punctuation): 在結果字符串中產生所有必須的停頓間隔符。
“文本”: 在雙引號中的文本被重新產生出來。
SELECT TO_CHAR(SYSDATE,'A.D.YYYY"年"-MONTH-DD"日"-DAY') "Reault" from dual;
result
-------
公元2001年-10月-12日-星期五
2> TO_CHAR(NUMBER[,FORMAT[,NLSPARAMS]])
*數字格式元素:
9 如9999 : 每一個9表示一個有效位,轉換值的有效位應和9的各位相同,如要轉換的
是負數則應有前導的負號,前導如為0則視為空格。
0 如0999或9990 返回不包括空格的前導可後繼0。
$ 如$999 : 返回帶有前導貨幣符號的數值。
B 如B999 : 當整數為0時,將該小數的整數部分填充為空格。
MI如99MI : 該值如為負數,則加後繼負號,如非負數則加一後繼占位符。
S 如S999或999S S在前,為數據加前導+或-號、S在後,為數據加後繼+或-號。
PR 如999PR: 如為負值,用尖括號括起,如為正值,則前導後繼各加一空格。
D 如99D99: 返回小數點的位置,兩邊的9指定了最大位數。
G 如99G99: 返回千分位分隔符,G可出現多次
C 如C999: 在指定的位置上返回本地貨幣號。
L 如L999: 在指定位置上返回本地貨幣號
, 如99,99: 在指定的位置返回一個逗號,而不管指定的千分位分隔符。
. 如99.99 : 在指定的位置返回一個小數點,而不管指定的小數點分隔符。
V 如99V99: 返回與10的n次方相乘的值,n是V後面9的個數。
EEEE 如9.99EEEE : 以科學計數法返回值。
RM,rm如RM.rm: 用大寫(RM)或小寫(rm)的羅馬數字返回指定的數字。
*
NLS_NUMERIC_CHARACTERS:可簡寫為'NLS_NUMBER_CHARS'.其表示的意思是指定分
組分隔符和小數點使用的字符。
NLS_CURRENCY:指定Oracle默認的貨幣。
NLS_ISO_CURRENCY: 指定ISO貨幣符號的字符。
例子: SELECT TO_CHAR(123456789,'L999G999G999D99','NLS_CURRENCY=¥') "RESULT" FROM DUAL;
結果是RESULT
--------
¥123,456,789.00
TO_CHAR的另外兩種轉換情況:
3> TO_CHAR(label[,format]) :它用於將MLSLABEL類型的label值轉換為一個VARCHAR2類型的值。
4> TO_CHAR(character):它用於將NCHAR、NVARCHAR2、CLOB或NCLOB轉換成數據庫能夠認識的字符類型。
2、TO_CLOB 將給定的NCLOB值轉換成LOB字段或將別的字符串轉換成CLOB值
3、TO_DATE(STRING[,FORMAT[,NLSPARAMS]])函數將CHAR或VARCHAR2類型的值轉換為一個DATE類型的值。
('NLS_DATE_LANGUAGE=language'指定返回日期所使用的語言。)
select to_Date('1999 july 21','yyyy-mm-dd','nls_date_language=english') "result" from dual;
result
------
21-7月-99
select to_date('20010809','yyyy-mm-dd') "result" from dual;
result
-------
09-8月-01
4、TO_MULTI_BYTE: 函數將string中所有的單字節字符轉換為等價的多字節字符。只當數據庫字符集中同時包
含單字節和多字節時才有用,否則string不會進行任何處理而返回
select to_multi_byte('myoracle') "result" from dual;
result
------
myoracle
5、TO_SINGLE_BYTE(STRING) :函數將string中有的多字節字符轉換成單字節字符。和TO_MULTI_BYTE互為反函數。
select to_single_byte('myoracle9i') "result" from dual;
result
--------
myoracle9i
6、TO_NUMBER(STRING[,FORMAT[,NLSPARAMS]]):函數將CHAR或VARCHAR2類型的STRING轉換成NUMBER類型的數值。
select to_number('$1234.56','$9999.99') "result" from dual;
result
------
1234.56
6.5日期(Date)函數
add_months 給指定的日期加上若干個月。
current_date 返回當前會話期時區內的日期。
current_timestamp 返回當前會話期時區內的日期和時間。
dbtimezone 返回當前數據庫所在時區。
extract 從時間參數中取出並返回指定的時間字段。
from_tz 將一個含有時區的TEMESTAMP的值轉換成TIMESTAMP WITH TIME ZONE值。
last_day 返回給定月份的最後一天。
localtimestamp 以timestamp數據形式返回當前會話期時區內的日期和時間。
months_between 返回兩個日期之間的月份。
new_time 返回根據用戶指定的時區計算相應的時間/天數值。
next_day 返回指定日期後的下一個星期幾的日期。
numtodsinterval 將指定的值轉換成INTERVAL DAY TO SECOND 類型。
numtoyminterval 將指定的值轉換成INTERVAL YEAR TO MONTH 類型。
round 按指定的日期參數(年、月、日)四捨五入指定的日期。
sessiontimezone 返回當前會話期的時區。
sysdate 返回系統當前的日期和時間。
systimestamp 以timestamp with time zone 類型返回系統日期。
to_dsinterval 將指定的字符串轉換成INTERVAL DAY TO SECOND類型。
to_timestamp 將指定的字符串轉換成TIMESTAMP類型。
to_timestamp_tz 將指定的字符串轉換成TIMESTAMP WITH TIME ZONE類型。
to_yminterval 將指定的字符串轉換成INTERVAL YEAR TO MONTH類型。
tz_lffset 根據指定的值返回相應的時區偏移量。
trunc 按指定的日期參數(年、月、日)剪裁指定的日期。
1、sysdate函數
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') "now" from dual;
now
-------------------
2001-10-14,09:02:09
2、add_months(date,x)函數
select add_months(to_date('2001-01-30','yyyy-mm-dd'),1) "first" from dual;
first
--------
28-2月-01
3、current_date函數
alter session set time_zone='-5:0';
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sessiontimezone,current_date from dual;
sessintimezone current_date
-------------- ------------
-05:0016-10月-2001 21:16:09
4、current_timestamp函數
alter session set time_zone='-5:0';
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sessiontimezone,current_timestamp from dual;
sessiontimezone current_timestamp
--------------- -----------------------------
-05:00 16-10月-2001 21:26:04.715620 p.m. -5:00
5、dbtimezone函數
select dbtimezone from dual;
dbtime
-------
+08:00
6、extract函數
select extract(year from date'1998-03-07') from dual;
extract(year from date'1998-03-07')
-----------------------------------
1998
7、from_tz函數
select from_tz(timestamp'2000-03-28 08:00:00','3:00') from dual;
from_tz(timestamp'2000-03-28 08:00:00','3:00)
-----------------------------------------------
28-3月-00 08:00:00 am +03:00
8、localtimestamp函數
alter session set time_zone='-5:00';
select current_timestamp,localtimestamp from dual;
current_timestamp localtimestamp
---------------------------------------------------------
04-apr-00 01:27:18.999220 pm -05:00 04-aor-00 01:27:19 pm
9、months_between函數
select months_between('18-10月-01','18-10月-01') "first"
months_between('18-10月-01','19-4月-01') "second' from dual;
first second
------- ----------
-6 5.96774194
10、NEW_TIME函數 : 將ZONE1時區的日期DATE轉換成ZON2時區的日期。
AST 大西洋標准時間
ADT 大西洋夏令時
BST 白令標准時
BDT 白令夏令時
CST 中部夏令時
EDT 東部夏令時
EST 東部標准時
GMT 格林尼治標准時
HST 阿拉斯加-夏威夷標准時
HDT 阿拉斯加-夏威夷夏令時
MST 山地標准時
MDT 山地夏令時
NST 紐芬蘭標准時
PST 太平洋標准時
PDT 太平洋夏令時
YST 育空標准時
YDT 育空夏令時
select to_char(new_time(sysdate,'pst','gmt'),'yyyy-mm-dd,hh:mi:ss') "result" from dual;
result
-------------------
2001-10-14,06:28:10
11、NEXT_DAY函數
select next_day(sysdate,'星期五') from dual;
next_day(sysda
----------------
19-10月-01
12、NUMTODSINTERVAL(N,'CHAR_EXPR') 函數將指定的N轉換成INTERVAL DAY GO SECOND類型。
select numtodsinterval(100,'hour') from dual;
numtodsinterval(100,'hour')
--------------------------------
+000000004 04:00:00.000000000
13、NUMTOMINTERVAL(N,'CHAR_EXPR')函數將指定的值N轉換成INTERVAL YEAR TO MONTH類型。
select numtoyminterval(100,'month') from dual;
numtoyminterval(100,'month')
------------------------------
+0.000000008-04
14、ROUND(DATE,[,FORMAT])函數 將指定的值N轉換成INTERVAL YEAR TO MINTH 類型。
select round(to_date('12-6月-01'),'mm') "first"
round(to_date('18-6月-01'),'mm') "second" from dual;
first second
------- --------
01-6月-01 01-7月-01
15、SESSIONTIMEZONE函數返回當前會話期的時區
select sessiontimezone from dual;
session
---------
+08:00
16、SYSTIMESTAMP函數:以TIMESTAMP WITH TIME ZONE 類型返回當前系統日期。
select systimestamp from dual;
systimestamp
-------------
28-10月-01 12.38.55.538741 pm +8:00
17、TO_DSIONTERVAL(CHAR[,NLSPARAM])函數將指定的字符串 轉換成INTERVAL DAY TO SECOND類型。
select employee_id, alst_name from hr.employees
where hire_date-to_dsinterval('100 10:00:00')
>date '1985-01-01';
18、TO_TIMESTAMP函數將指定的字符串轉換成TIMESTAMP類型
select to_timestamp('1999-12-01 11:00:00','yyyy-mm-dd hh:mi:ss') "first" from dual;
first
---------
01-DEC-99 11.00.00.000000000 AM
19、TO_TIMESTAMP_TZ(char,[fmt[,nlsparam]])函數將指定的字符串轉換成TIMEST AMP WITH TIME ZONE 類型。
20、TO_YMINTERVAL(char)函數將指定的字符串轉換成INTERVAL YEAR TO MONTH類型。
21、TZ_OFFSET函數
select tz_offset('us/eastern') from dual;
tz_offs
----------
-4:00
22、TRUNC(DATE[,FORMAT])函數將日期DATE按照FORMAT指定的格式進行截取,缺省為'DD',截取日期為當月第一天。
SELECT TRUNC(TO_DATE('12-6月-01'),'MM') "FIRST"
TRUNC(TO_DATE('18-6月-01'),'MM') "SECOND" FROM DUAL;
FIRST SECOND
------- ----------
01-6月-01 01-6月-01
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss am') "result1",
to_char(trunc(sysdate),'yyyy-mm-dd hh:mi:ss am') "result2" from dual;
result1 result2
---------- --------------
2001-10-21 02:48:49 下午 2001-10-21 12:00:00 上午
6.6分組(GROUP) 函數
AVG 求返回的各列的平均值
COUNT 返回查詢的行的數目
GLB 返回MSLABLE的最大下界
LUB 返回MSLABEL的最小上界
MAX 返回查詢列中的最大值
STDDEV 返回查詢列的平均偏差
SUM 返回查詢列的總和
VARIANCE 返回查詢列的統計方差
1、STDDEV函數
select stddev(mgr) from scott.emp;
stddev(mgr)
------------
103.71466
2、VARIANGE([DISTINCTIALL] column)函數查詢列的統計方差
select variange(mgr) from scott.emp
variance(mgr)
--------------
10756.7308
6.7雜項(Misc)函數
BILENAME 返回一個BFILE位置指示符,指向一個存儲實際的LOB二進制文件的存儲文件
DECODE 類似於IF_THEN_ELSE語句
DUMP 返回有關指定數值的內部表示信息VARCHAR2類型的數值。
EMPTY_BLOB 返回一個空的二進制指示符
EMPTY_CLOB 返回一個空的字符指示符
GREATEST 返回給定的參數列表中最大的表達式
LEAST 返回給定的參數列表中最小的表達式
NLS_CHARSET_DECL_LEN 返回NCHAR列的定義長度(以字符長度表示)
NLS_CHARSET_ID 返回與NLS字符集名稱關聯的NLS集ID號
NLS_CHARSET_NAME 返回傳遞給函數的ID關聯的NLS字符名稱
NVL 從給定的表達式中選擇第一個不為空的值
SYS_CONTEXT 返回當前進程的屬性
SYS_GUID 產生和返回一個16字節的全局的惟一的標志符(ROW)值
UID 返回ORACLE中用戶的ID號
USER 返回當前用戶名稱
USERENV 返回當前工作環境的信息
VSIZE 返回指定值的內部字節數