oracle常用函数.docx
《oracle常用函数.docx》由会员分享,可在线阅读,更多相关《oracle常用函数.docx(14页珍藏版)》请在冰豆网上搜索。
oracle常用函数
oracle常用函数
一、字符函数
函数
说明
ASCII
返回对应字符的十进制值
CHR
给出十进制返回字符
CONCAT
拼接两个字符串,与||相同
INITCAT
将字符串的第一个字母变为大写
INSTR
找出某个字符串的位置
INSTRB
找出某个字符串的位置和字节数
LENGTH
以字符给出字符串的长度
LENGTHB
以字节给出字符串的长度
LOWER
将字符串转换成小写
LPAD
使用指定的字符在字符的左边填充
LTRIM
在左边裁剪掉指定的字符
RPAD
使用指定的字符在字符的右边填充
RTRIM
在右边裁剪掉指定的字符
REPLACE
执行字符串搜索和替换
SUBSTR
取字符串的子串
SUBSTRB
取字符串的子串(以字节)
SOUNDEX
返回一个同音字符串
TRANSLATE
执行字符串搜索和替换
TRIM
裁剪掉前面或后面的空格
UPPER
将字符串变为大写
ASCII()
是字符串。
返回与指定的字符对应的十进制数。
selectascii('A')A,ascii('a')"a",ascii('0')zero,ascii('')spacefromdual;
AaZEROSPACE
----------------------------------------
65974832
selectascii('赵')zhao,length('赵')lengfromdual;
ZHAOLENG
--------------------
547401
CHR([NCHAR])
给出整数,返回对应字符。
如:
selectchr(54740),chr(65)fromdual;
CHC
---
赵A
CONCAT(,)
selectconcat('010-','88018159')||'转23'电话fromdual;
电话
-----------------
010-88018159转23
INITCAP()
返回字符串c1并第一个字母变为大写。
例如:
selectinitcap('simth')uppfromdual;
UPP
-----
Simth
INSTR(,[,[,]])
在一个字符串中搜索指定的字符,返回发现指定的字符的位置。
C1:
被搜索的字符串
C2:
希望搜索的字符串
I:
搜索的开始位置,缺省是1
J:
出现的位置,缺省是1。
SELECTINSTR('OracleTraining','ra',1,2)"Instring"FROMDUAL;
Instring
----------
9
INSTRB(,[,[,]])
除了返回的字节外,与INSTR相同,
LENGTH()
返回字符串c的长度。
selectlength('oracle')fromdual;
LENGTH('ORACLE')
--------------------------
6
LENGTHB()
以字节返回字符串的字节数。
selectlengthb('中'),length('中')fromdual;
LENGTHB('中')LENGTH('中')
-------------------------
21
LOWER()
返回字符串并将所有字符变为小写。
selectlower('AaBbCcDd')"AaBbCcDd"fromdual;
AaBbCcDd
-------------
aabbccdd
UPPER()
与LOWER相反,将给出字符串变为大写。
如:
SQL>selectupper('AaBbCcDd')"AaBbCcDd"fromdual;
AABBCCDD
--------
AABBCCDD
RPAD和LPAD(粘贴字符)
RPAD(string,Length[,'set'])
LPAD(string,Length[,'set'])
RPAD在列的右边粘贴字符;
LPAD在列的左边粘贴字符。
selectRPAD('City',10,'.')cityfromdual;
CITY
----------
City......
(即不够10个字符用'.'填满)
LTRIM(左截断)RTRIM(右截断)函数
LTRIM(string[,’set’])
LeftTRIM(左截断)删去左边出现的任何set字符,set缺省为空格。
RTRIM(string[,’set’])
RightTRIM(右截断)删去右边出现的任何set字符,set缺省为空格。
SELECTRTRIM('MotherTheresa,The','The')ExampleFROMDUAL;
EXAMPLE
----------------
MotherTheresa,
SUBSTRSubstr(string,start[,Count])
取子字符串中函数
对字串(或字段),从start字符开始,连续取count个字符并返回结果,如果没有指count则一直取到尾。
selectsubstr('ABCDEFG',2,3)fromdual;
SUB
---
BCD
REPLACE(‘string’[,’string_in’,’string_out’])
String:
希望被替换的字符串或变量。
String_in:
被替换字符串。
String_out:
要替换字符串。
SELECTREPLACE('Oracle','Or','Mir')ExampleFROMDUAL;
EXAMPLE
-------
Miracle
TRIM(string)
去掉前面及尾面的字符;
SELECTTRIM('98723489')ExampleFROMDUAL;
EXAMPLE
--------
98723489
二、数值函数
函数
说明
ABS(value)
CEIL(value)
COS(value)
COSH(value)
EXP(value)
FLOOR(value)
LN(value)
LOG(value)
MOD(value,divisor)
POWER(value,exponent)
ROUND(value,precision)
SIGN(value)
SIN(value)
SINH(value)
SQRT(value)
TAN(value)
TANH(value)
TRUNC(value,按precision)
VSIZE(value)
绝对值
大于或等于value的最小整数
余弦
反余弦
e的value次幂
小于或等于value的最大整数
value的自然对数
value的以10为底的对数
求模
value的exponent次幂
按precision精度4舍5入
value为正返回1;为负返回-1;为0返回0.
余弦
反余弦
value的平方根
正切
反正切
按照precision截取value
返回value在ORACLE的存储空间大小
CEIL()
返回大于或等于给出数字的最小整数。
如:
SQL>selectceil(3.14159)fromdual;
CEIL(3.14159)
-------------
4
FLOOR()
对给定的数字取整数,如:
selectfloor(123.45),floor(45.56)fromdual;
FLOOR(123.45)FLOOR(45.56)
-------------------------
12345
ROUND(value,precision)
按照指定的精度进行舍入;
selectround(55.5),round(-55.5),trunc(55.5),trunc(-55.5)
fromdual;
round(55.5)round(-55.5)trunc(55.5)trunc(-55.5)
-----------------------------------------------
56-5655-55
TRUNC(value,precision)
按照指定的截取一个数。
如:
SELECTround(124.16666,-2)round1,round(124.16666,2)round2,TRUNC(124.16666,-2)trunc1,trunc(124.16666,2)trunc2fromdual;
ROUND1ROUND2TRUNC1TRUNC2
----------------------------------------
100124.17100124.16
SIGN()
取数字n的符号,大于0返回1;小于0返回-1;等于0返回0。
如:
例:
selectsign(123),sign(-100),sign(0)fromdual;
sign(123)sign(-100)sign(0)
------------------------------
1-10
TRUNC(value,precision)
按照指定的截取一个数。
如:
SELECTround(124.16666,-2)round1,round(124.16666,2)round2,TRUNC(124.16666,-2)trunc1,trunc(124.16666,2)trunc2fromdual;
ROUND1ROUND2TRUNC1TRUNC2
----------------------------------------
100124.17100124.16
三、日期函数
Oracle用到的日期函数是:
函数
描述
ADD_MONTH
在日期date上增加count个月
GREATEST(date1,date2,...)
从日期列表中选出最晚的日期
LAST_DAY(date)
返回日期date所在月的最后一天
LEAST(date1,date2,...)
从日期列表中选出最早的日期
MONTHS_BETWEEN(date2,date1)
给出Date2-date1的月数(可以是小数)
NEXT_DAY(date,’day’)
给出日期date之后下一天的日期,这里的day为星期,如:
MONDAY,Tuesday等。
ADD_MONTHS(,)
增加月份和减去月份,如:
selectadd_months(SYSDATE,2)Examplefromdual;
EXAMPLE
----------
05-12月-08
selectto_char(add_months(to_date('19971201','yyyymmdd'),-1),'yyyy-mm-dd')Examplefromdual;
EXAMPLE
----------
1997-11-01
LAST_DAY(date)
返回日期date所在月的最后一天,如:
selectsysdate,LAST_DAY(sysdate)Examplefromdual;
SYSDATEEXAMPLE
--------------------
05-10月-0831-10月-08
MONTHS_BETWEEN(date2,date1)
给出Date2-date1的月数(可以是小数);
selectmonths_between('19-12月-1999','19-3月-2000')Examplefromdual;
EXAMPLE
----------
-3
selectmonths_between(to_date('2005.05.20','yyyy.mm.dd'),
to_date('2000.05.20','yyyy.mm.dd'))Examplefromdual;
EXAMPLE
----------
60
NEXT_DAY(date,’day’)
给出日期date和星期x之后计算下一星期x的日期,这里的day为星期,如:
MONDAY,Tuesday等。
但在中文环境下,要写成’星期x’这样的格式,如:
selectSYSDATE,next_day(SYSDATE,'星期五')fromdual;
SYSDATENEXT_DAY(S
--------------------
05-10月-0810-10月-08
SYSDATE
用来得到系统的当前日期,如:
selectto_char(sysdate,'dd-mon-yyyyday')todayfromdual;
TODAY
-------------------
05-10月-2008星期日
四、转换函数
函数
描述
TO_CHAR
转换日期格式到字符串
TO_DATE
按照指定的格式将字符串转换到日期型
TO_NUMBER
将数字字串转换到数字
TO_CHAR(date,’format’)
根据format重新格式日期date的格式。
如:
selectto_char(sysdate,'yyyy/mm/ddhh24:
mi:
ss')fromdual;
TO_CHAR(SYSDATE,'YY
-------------------
2001/05/1823:
05:
36
日期格式比较多,详细内容请参考原版资料。
下面给出常用的日期格式代码:
日期格式代码表
日期代码
格式说明
例子
AD或BC
AD=AnnoDomini公元,BC=BeforeChrist公元前。
不带点的公元或公元前
‘YYYYAD’=1999AD
A.D.或B.C.
带点的公元或公元前
‘YYYYA.D.’=1999A.D.
AM或PM
AM=antemeridiem上午,PM=postmeridiem下午。
不带点的上午或下午
‘HH12AM’=09AM
A.M.或P.M.
带点的上午或下午
‘HH12A.M.’=09A.M.
DY
星期几的缩写
Mon,Tue,...
DAY
星期几的全拼
Monday,Tuesday,...
D
一周的星期几,星期天=1,星期六=7
1,2,3,4,5,6,7
DD
一月的第几天,131
1,2,...31
DDD
一年的第几天,1366
1,2,3,...366
J
公元前的第几天(从公元前4712起?
)
2451514,2451515,...
W
一个月的第几周,15
1,2,3,4,5
WW,IW
一年的第几周,一年的ISO的第几周
1,2,3,4,...52
MM
两为数的月
01,02,03,...12
MON
月份的缩写
Jan,Feb,Mar,...Dec
MONTH
月份的全拼
January,February,...
RM
罗马数字的月份,IXII
I,II,III,IV,...XII
YYYY,YYY,YY,Y
四位数的年,三位数的年
1999,999,99,9
YEAR
年的全拼
NineteenNinety-nine
SYYYY
如果是公元前(BC),年份前负号
-1250
RR
当前年份的后两位数字
01代表2001年
HH,HH12
12小时制,112
1,2,3,...12
HH24
24小时制,023
0,1,2,3,...23
MI
一小时中的第几分,059
0,1,2,3...59
SS
一分中的第几秒,059
0,1,2,3,...59
SSSSS
一天中的第几秒,086399
0,1,2,3,...86399
../-;:
标点符号表示法
文字显示
‘text’
引号表示法
文字显示
TO_DATE(string,’format’)
将一和字串转换为ORACLE的日期。
如:
Insertintodemo(demo_key,date_col)
Values(1,to_date(’04-Oct-1999’,‘DD-Mon-yyyy’));
TO_NUMBER()
将给出的字符转换为数字,如:
SELECTTO_NUMBER(‘1947’)FROMDUAL;
五、其它函数
GREATEST()
返回一组表达式中的最大值,即比较字符的编码大小。
如:
SQL>selectgreatest('AA','AB','AC')fromdual;
GR
--
AC
SQL>selectgreatest('啊','安','天')fromdual;
GR
--
天
即“天”的编码比“安”和“啊”都大。
LEAST()
返回一组表达式中的最小值,即比较字符的编码大小。
如:
SQL>selectleast('啊','安','天')fromdual;
LE
--
啊
UID函数
返回标识当前用户的唯一整数,如:
USER函数
返回当前用户的名字,如:
system
selectuser,uidfromdual;
USERUID
----------------------------------------
SYSTEM5
NVL函数
NVL(value,substitute)value为空时以substitute代替
selectempno,sal,comm,sal+comm,sal+nvl(comm,0)fromemp;
EMPNOSALCOMMSAL+COMMSAL+NVL(COMM,0)
-------------------------------------------------------
7369800800
7499160030019001900
7521125050017501750
756629752975
76541250140026502650
769828502850
778224502450
778830003000
783950005000
78441500015001500
787611001100
7900950950
790230003000
793413001300