h

oracle11g学习笔记-基本函数

制作:高进波
时间:2009-10-10

Oracle的函数很多,可以分为基本函数和分析函数两大类.
在联机事务处理系统中,基本函数使用的很多,而在以数据仓库为基础的经营分析系统中,除了基本函数外,还要用

到分析函数.

1.字符函数

ascii(c1)
其中,c1是一个字符串,该函数返回第一个字母的ASCII码,它的逆函数是chr()
select ascii(‘A’) big_A,ascii(‘z) big_z from emp;

chr(i)
其中,i是一个数字,函数返回十进制表示的字符
select chr(65),chr(123),chr(233) from emp;

concat(c1,c2)
其中,c1,c2均为字符串,函数将c2连续到c1的后面
select concat(‘slobo ‘,’Svoboda’) username from dual;

initcap(c1)
其中,c1为一个字符串,函数将每个单词的第一个字母大写其他字母小写返回
select initcap(‘veni,vedi,vici’) ceasar from dual;

instr(c1,c2[,i[,j]])
其中,c1,c2均为字符串,i,j为整数.函数返回c2在c1中第j次出现的位置,搜索从c1的第i个字符开始,当没有发现

需要的字符时返回0,
如果i为负数,那么搜索将从右到左进行,但是位置的计算还是从左到右,i和j的默认值为1
select instr(‘Mississippi’,'i’,3,3) from dual;
select instr(‘Mississippi’,'i’,-2,3) from dual;

instrb(c1,c2,[,i[,j]])
与instr()函数一样,只是返回的是字节,对于单字节instrb()等于instr()

length(c1)
其中,c1为字符串,返回c1的长度
select length(‘Ipso Facto’) ergo from dual;

lengthb(c1)
与length()一样,返回字节

lower(c1)
返回c1的小写字符,经常出现在where子串中
select lower(colorname) from itemdetail where lower(colorname) like ‘%white%’;

lpad(c1,i,c2)
其中,c1,c2均为字符串,i为整数,在c1的左侧用c2字符串补足至长度i,可多次重复,如果i小于c1的长度,那么只返

回i那么长的c1字符,其他的将被截去,c2的默认值为单空格
select lpad(answer,7,”) padded,answer unpadded from question;

ltrim(c1,c2)
把c1最左边的字符去掉,使其第一个字符不在c2中,如果没有c2,那么c1就不会改变
select ltrim(‘Mississippi’,'Mis’) from dual;

rpad(c1,i,c2)
在c的右侧用c2字符串补足至长度i,可多数重复,如果i小于c1的长度,那么只返回i那么长的c1字符,其他的将被截

去,c2的默认值为单空格,和lpad相似

rtrim(c1[,c2])
把c1中最右边的字符去掉,使其最后一个字符不在c2中,如果没有c2,那么c1就不会改变

replace(c1,[c2,c3])
c1,c2,c3都是字符串,函数用c3代替出现在c1中的c2后返回
select replace(‘uptown’,'up’,'down’) from dual;

stbstr(c1,i[,j])
c1为一个字符串,i,j为整数,从c1的第i位开始返回长度为j的子字符串,如果j为空,则直到串的尾部
select substr(‘Message’,1,4) from dual;

substrb(c1,i[,j])
与substr大致相同,只是i,j是以字节计算

soundex(c1)
返回与c1发间相似的词
select soundex(‘dawes’) Dawes,soundex(‘daws’) Daws,soundex(‘dawson’) from dual;

translate(c1,c2,c3)
将c1中与c2相同的字符以c3代替
select translate(‘fumble’,'uf’,'ar’) test from dual;

trim(c1 from c2)
该函数用于从字符串的头部,尾部或两端截断特定字符,参数c1为要截去的字符,c2是源字符串
select trim(‘A’ from ‘ABCDEF’) from dual;

upper(c1)
返回c1的大写,常出现在where子串中
select upper(‘hi’) from dual;
select name from dual where upper(name) like ‘KI%’;

2.数字函数
abs(n)
返回n的绝对值

acos(n)
反全余弦函数,返回-1到1之间的数,n表示弧度
select acos(-1) pi,acos(1) zero from dual;

asin(n)
反正弦函数,返回-1到1之间的数,n表示弧度

atan(n)
反正切函数,返回n的反正切值,n表示弧度

ceil(n)
返回大于或等于n的最小整数

cos(n)
返回n的余弦值,n为弧度

cosh(n)
返回n的双曲余弦值,n为数字
select cosh(<1.4>) from dual;

exp(n)
返回e的n次幂,e=2.71828183

floor(n)
返回小于等于n的最大整数

ln(n)
返回n的自然对数,n必须大于0

log(n1,n2)
返回以n1为底n2的对数

mod(n1,n2)
返回n1除以n2的余数

power(n1,n2)
返回n1的n2次方

round(n1,n2)
返舍入小数点右边n2位的n1的值,n2的默认值为0,返回与小数点最接近的整数,如果n2为负数就舍入小数点左边相

应的位上,n2必须为整数
select round(123456,-2),round(12345.54321,2) from dual;

sign(n)
如果n为负数,返回-1,如果n为正数,返回1,如果n=0返回0

sin(n)
返回n的正弦值,n为弧度

sinh(n)
返回n的双曲正弦值,n为弧度

sqrt(n)
返回n的平方根,n为弧度

tan(n)
返回n的正切值,n为弧度

tanh(n)
返回n的双曲正切值,n为弧度

trunc(n1,n2)
返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小

数点左边相应的位上

3.日期函数

add_months(d,i)
返回日期d加上i个月后的结果,i可以是任意整数,如果i是一个小数,那么数据库将隐式将其他成整数,将会截去小

数点后面的部分

last_day(d)
函数返回包含日期d的月份的最后一天

months_between(d1,d2)
返回d1和d2之间月的数目,如果d1和d2的日期都相同,或者是该月的最后一天,那么将返回一个整数,否则返回的结

果将包含一个分数

new_time(d1,tz1,tz2)
d1是一个日期数据类型,当时区tz1中的日期和时间是d1时,返回时区tz2中的日期和时间,tz1和tz2是字符串

next_day(d,char)
该函数用于返回指定日期后的第一个工作日(由char指定)所对应的日期.

sysdate
函数没有参数,返回当前日期和时间

trunc(d,[fmt])
返回由fmt指定的单位的日期d

4.转换函数

chartorwid(c)
c是一个字符串,函数将c转换为rwid数据类型
select test_id from test_case where worid=chartorwid(‘AAAOSAACAAAALiAAA’);

convert(c,dset,sset)
C是字符串,dset,sset是两字符集,函数将字符串c由sset字符集转换为dset字符集,sset的缺省设置为数据库的字

符集

hextoraw(x)
x为16进制的字符串,函数将16进制的x转换为raw数据类型

rawtohex(x)
x为raw数据类型的字符串,函数将raw数据类型转换为16进制的

rowidtochar(rowid)
函数将rowid数据类型转换为char数据类型

o_char(x[,fmt[,nlsparam]])
x是一个data或number数据类型,函数将x转换成fmt指定格式的char数据类型,如果x为日期

nlsparm=NLS_DATE_LANGUAGE控制返回的月份和日所使用的语言,如果
x为数字nlsparm=NLS_NUMBERIC_CHARACTERS用来指定小数位和千分位的分隔符,以及货币符号

to_date(c,[fmt,nlsparam]])
c表示字符串,fmt表示一种特殊格式的字符串,返回按照fmt格式显示的c,nlsparm表示使用的语言

to_multi_byte(c)
c表示一个字符串,函数将c的单字节字符转换成多字节字符

to_number([c,[fmt[,nlsparam]])
c表示字符串,fmt表示一个特殊格式的字符串,函数返回值按照fmt指定的格式显示

to_single_byte(c)
将字符串c中的多字节字符转化成转换的单字节字符

5.组函数
oracle提供了丰富的基于组的,多行的函数,这些函数可以在select或select的having子名中使用,当用于select

子串时,常常和group by一起使用

avg([{distinct | all}])
返回数值的平均值,默认设置为ALL
select avg(sal),avg(distinct sal),avg(all sal) from scott.emp;

max([{distinct | all}])
返回选择列表项目的最大值

min([{distinct | all}])
返回选择列表项目的最小值

stddev([{distinct | all}])
返回选择列表项目的标准差,所谓标准差是方差的平方根

sum([{distinct | all}])
返回选择列表项目的数值的总和

variance([{distinct | all}])
返回选择列表项目的统计方差

基本函数完成!

相关日志：

• oracle11g学习笔记-select
• oracle11g学习笔记 – SQL语言基础