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 返回指定值的內部字節數
pl/sql函数学习
最新推荐文章于 2023-10-11 10:59:20 发布
扫一扫
1002
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
