搜索
Oracle常用及非常用函数[收藏此页][打印]作者：IT168李丙洋2007-10-31内容导航：oracle函数介绍第1页：oracle函数介绍第2页：字符型函数返回字符值第3页：字符型函数返回数字值第4页：非常见函数之单值函数感于总有些网友提出一些非常基础的问题，比如有没有实现某某功能的函数啊，某某函数是做什么用的啊，格式是什么等等，同时也感受到自己对oracle函数认识的不足，于是集中月余时间专注于oracle函数，小有心得不敢私藏，发之与诸公共享。本文并不准备介绍全部的oracle函数，当前情势下，俺也还没这个时间，需要学习的东西太多了，要把多数时间花在学习经常能用上的技术方面:)，所以如果是准备深入了解所有oracle函数的朋友，还是去关注：OracleSQLReference官方文档更靠谱一些。本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思的一些函数。分二类介绍，分别是：著名函数篇－经常用到的函数非著名函数篇－即虽然很少用到，但某些情况下却很实用注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。单值函数在查询中返回单个值，可被应用到select，where子句，startwith以及connectby子句和having子句。(一).数值型函数(NumberFunctions)数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。ACOS,ASIN,ATAN,andATAN2支持30位小数点。1、MOD(n1,n2)返回n1除n2的余数，如果n2=0则返回n1的值。例如：SELECTMOD(24,5)FROMDUAL;2、ROUND(n1[,n2])返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracledocuments上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。例如：SELECTROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1)FROMDUAL;3、TRUNC(n1[,n2]返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。例如：SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROMDUAL;(二).字符型函数返回字符值(CharacterFunctionsReturningCharacterValues)该类函数返回与输入类型相同的类型。?返回的CHAR类型值长度不超过2000字节；?返回的VCHAR2类型值长度不超过4000字节；如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。?返回的CLOB类型值长度不超过4G；对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。1、LOWER(c)将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型例如：SELECTLOWER('WhaTistHis')FROMDUAL;2、UPPER(c)将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型例如：SELECTUPPER('WhaTistHis')FROMDUAL;3、LPAD(c1,n[,c2])返回指定长度=n的字符串，需要注意的有几点：?如果nc1.length则从右到左截取指定长度返回；?如果nc1.lengthandc2isnull，以空格从左向右补充字符长度至n并返回；?如果nc1.lengthandc2isnotnull，以指定字符c2从左向右补充c1长度至n并返回；例如：SELECTLPAD('WhaTistHis',5),LPAD('WhaTistHis',25),LPAD('WhaTistHis',25,'-')FROMDUAL;最后大家再猜一猜，如果n0，结果会怎么样4、RPAD(c1,n[,c2])返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；例如：SELECTRPAD('WhaTistHis',5),RPAD('WhaTistHis',25),RPAD('WhaTistHis',25,'-')FROMDUAL;5、TRIM([[LEADING||TRAILING||BOTH]c2FROM]c1)哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。看起来很复杂，理解起来很简单：?如果没有指定任何参数则oracle去除c1头尾空格例如：SELECTTRIM('WhaTistHis')FROMDUAL;?如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)例如：SELECTTRIM('W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了leading参数则会去掉c1头部c2例如：SELECTTRIM(leading'W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了trailing参数则会去掉c1尾部c2例如：SELECTTRIM(trailing'W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)例如：SELECTTRIM(both'W'FROM'WhaTistHiswW')FROMDUAL;注意：c2长度=16、LTRIM(c1[,c2])千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。例如：SELECTLTRIM('WWhhhhhaTistHiswW','Wh')FROMDUAL;7、RTRIM(c1,c2)与上同，不过方向相反例如：SELECTRTRIM('WWhhhhhaTistHiswW','Ww')FROMDUAL;8、REPLACE(c1,c2[,c3])将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。例如：SELECTREPLACE('WWhhhhhaTistHiswW','W','-')FROMDUAL;9、SOUNDEX(c)神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：?保留字符串首字母，但删除a、e、h、i、o、w、y。?将下表中的数字赋给相对应的字母：1：b、f、p、v2：c、g、k、q、s、x、z3：d、t4：l5：m、n6：R?如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；?只返回前4个字节，不够用0填充例如：SELECTSOUNDEX('dog'),soundex('boy')FROMDUAL;10、SUBSTR(c1,n1[,n2])截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。n1=开始长度；n2=截取的字符串长度，如果为空，默认截取到字符串结尾；?如果n1=0thenn1=1?如果n10，则oracle从左向右确认起始位置截取例如：SELECTSUBSTR('Whatisthis',5,3)FROMDUAL;?如果n10，则oracle从右向左数确认起始位置例如：SELECTSUBSTR('Whatisthis',-5,3)FROMDUAL;?如果n1c1.length则返回空例如：SELECTSUBSTR('Whatisthis',50,3)FROMDUAL;然后再请你猜猜，如果n21，会如何返回值呢11、TRANSLATE(c1,c2,c3)就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解，我们通过几具实例来说明：例如：SELECTTRANSLATE('Whatisthis','','-')FROMDUAL;SELECTTRANSLATE('Whatisthis','-','')FROMDUAL;结果都是空。来试试这个：SELECTTRANSLATE('Whatisthis','','')FROMDUAL;再来看这个：SELECTTRANSLATE('Whatisthis','ait','-*')FROMDUAL;是否明白了点呢？Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了？Replace是替换，而translate则像是过滤。1(三).字符型函数返回数字值(CharacterFunctionsReturningNumberValues)本类函数支持所有的数据类型1、INSTR(c1,c2[,n1[,n2]])返回c2在c1中位置?c1:原字符串?c2:要寻找的字符串?n1:查询起始位置，正值表示从左到右，负值表示从右到左(大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0?n2:第几个匹配项。大于0例如：SELECTINSTR('abcdefg','e',-3)FROMDUAL;2、LENGTH(c)返回指定字符串的长度。如果例如：SELECTLENGTH('A123中')FROMDUAL;猜猜SELECTLENGTH('')FROMDUAL;的返回值是什么(四).日期函数(DatetimeFunctions)本类函数中，除months_between返回数值外，其它都将返回日期。1、ADD_MONTHS()返回指定日期月份+n之后的值，n可以为任何整数。例如：SELECTADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12)FROMDUAL;2、CURRENT_DATE返回当前session所在时区的默认时间例如：SQLaltersessionsetnls_date_format='mm-dd-yyyy';SQLselectcurrent_datefromdual;3、SYSDATE功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好，深入浅出)的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。例如：SELECTSYSDATE,CURRENT_DATEFROMDUAL;4、LAST_DAY(d)返回指定时间所在月的最后一天例如：SELECTlast_day(SYSDATE)FROMDUAL;5、NEXT_DAY(d,n)返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session