首页 > 数据库 > Oracle > 正文
ORACLE常用数值函数、转换函数、字符串函数介绍
2011-06-23       个评论      
收藏    我要投稿

 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。

  单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
    数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。

1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。
    例如:SELECT MOD(24,5) FROM DUAL;

2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。
例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;

3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。
例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;

(二).字符型函数返回字符值(Character Functions Returning Character Values)
  该类函数返回与输入类型相同的类型。
 返回的CHAR类型值长度不超过2000字节;
 返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。

 返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。

1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
    例如:SELECT LOWER(WhaT is tHis) FROM DUAL;

2、UPPER(c) 将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
    例如:SELECT UPPER(WhaT is tHis) FROM DUAL;

3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点:
 如果n<c1.length则从右到左截取指定长度返回;
 如果n>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回;
 如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回;
例如:SELECT LPAD(WhaT is tHis,5),LPAD(WhaT is tHis,25),LPAD(WhaT is tHis,25,-) FROM DUAL;
最后大家再猜一猜,如果n<0,结果会怎么样

4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反;
    例如:SELECT RPAD(WhaT is tHis,5),RPAD(WhaT is tHis,25),RPAD(WhaT is tHis,25,-) FROM DUAL;

5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。
看起来很复杂,理解起来很简单:
 如果没有指定任何参数则oracle去除c1头尾空格
例如:SELECT TRIM( WhaT is tHis ) FROM DUAL;
 如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟)
例如:SELECT TRIM(W FROM WhaT is tHis w W) FROM DUAL;
 如果指定了leading参数则会去掉c1头部c2
例如:SELECT TRIM(leading W FROM WhaT is tHis w W) FROM DUAL;
 如果指定了trailing参数则会去掉c1尾部c2
例如:SELECT TRIM(trailing W FROM WhaT is tHis w W) FROM DUAL;
 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!)
例如:SELECT TRIM(both W FROM WhaT is tHis w W) FROM DUAL;

注意:c2长度=1

6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如:SELECT LTRIM(WWhhhhhaT is tHis w W,Wh) FROM DUAL;

7、RTRIM(c1,c2)与上同,不过方向相反
    例如:SELECT RTRIM(WWhhhhhaT is tHis w W,W w) FROM DUAL;

8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。
    例如:SELECT REPLACE(WWhhhhhaT is tHis w W,W,-) FROM DUAL;

9、SOUNDEX(c) 神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。计算语音的算法如下:
 保留字符串首字母,但删除a、e、h、i、o、w、y。
 将下表中的数字赋给相对应的字母:
1:b、f、p、v
2:c、g、k、q、s、x、z
3:d、t
4:l
5:m、n
6:R
 如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个;
 只返回前4个字节,不够用0填充
例如:SELECT SOUNDEX(dog),soundex(boy) FROM DUAL;

10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度;
n2=截取的字符串长度,如果为空,默认截取到字符串结尾;
 如果n1=0 then n1=1
 如果n1>0,则oracle从左向右确认起始位置截取
例如:SELECT SUBSTR(What is this,5,3) FROM DUAL;
 如果n1<0,则oracle从右向左数确认起始位置
例如:SELECT SUBSTR(What is this,-5,3) FROM DUAL;
 如果n1>c1.length则返回空
例如:SELECT SUBSTR(What is this,50,3) FROM DUAL;
然后再请你猜猜,如果n2<1,会如何返回值呢

11、TRANSLATE(c1,c2,c3) 就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明:
例如:
SELECT TRANSLATE(What is this,,-) FROM DUAL;
SELECT TRANSLATE(What is this,-,) FROM DUAL;
结果都是空。来试试这个:
SELECT TRANSLATE(What is this, , ) FROM DUAL;
再来看这个:
SELECT TRANSLATE(What is this,ait,-*) FROM DUAL;
是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤

(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型

1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
 c1:原字符串
 c2:要寻找的字符串
 n1:查询起始位置,正值表示从左到右,负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑,如果为0的话,则返回的也是0
 n2:第几个匹配项。大于0
例如:SELECT INSTR(abcdefg,e,-3) FROM DUAL;

2、LENGTH(c) 返回指定字符串的长度。如果
例如:SELECT LENGTH(A123中) FROM DUAL;
猜猜SELECT LENGTH() FROM DUAL;的返回值是什么

(四).日期函数(Datetime Functions)
本类函数中,除months_between返回数值外,其它都将返回日期。

1、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。
例如:SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;

2、CURRENT_DATE 返回当前session所在时区的默认时间
例如:
SQL> alter session set nls_date_format = mm-dd-yyyy ;
SQL> select current_date from dual;

3、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。
例如:SELECT SYSDATE,CURRENT_DATE FROM DUAL;

4、LAST_DAY(d) 返回指定时间所在月的最后一天
例如:SELECT last_day(SYSDATE) FROM DUAL;

5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。
例如:三思用的中文nt,nls_language值为SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,星期四) FROM DUAL;
两种方式都可以取到正确的返回,但是:
SELECT NEXT_DAY(SYSDATE,Thursday) FROM DUAL;

点击复制链接 与好友分享!回本站首页
上一篇:oracle查看实例EM端口号、SID设制与一些命令
下一篇:oracle触发器详细
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站