oracle常用函数

Oracle函数(一).数值型函数(NumberFunctions)1、MOD(n1,n2)返回n1除n2的余数，如果n2=0则返回n1的值。例如：SELECTMOD(24,5)FROMDUAL;2、ROUND(n1[,n2])返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果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;(一).数值型函数(NumberFunctions)1、ABS(n)返回数字的绝对值SELECTABS(-1000000.01),ABS(1000000.01)FROMDUAL;2、CEIL(n)返回大于或等于n的最小的整数值SELECTceil(18.2),ceil(-18.2)FROMDUAL;3、FLOOR(n)返回小于等于n的最大整数值SELECTFLOOR(2.2),FLOOR(-2.2)FROMDUAL;4、POWER(n1,n2)返回n1的n2次方。n1,n2可以为任意数值，不过如果n1是负数，则n2必须为整数SELECTPOWER(2,-2)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;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长度=16、LTRIM(c1[,c2])千万不要以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。例如：SELECTLTRIM(‘WWhhhhhaTistHiswW’,‘Wh’)FROMDUAL;SELECTLTRIM(‘aWWhhhhhaTishhhaaaaaaaatHiswW’,‘wWhaThtHis’)FROMDUAL;c2的值没有顺序7、RTRIM(c1,c2)与上同，不过方向相反例如：SELECTRTRIM('WWhhhhhaTistHiswW','Ww')FROMDUAL;8、REPLACE(c1,c2[,c3])将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。例如：SELECTREPLACE('WWhhhhhaTistHiswW','W','-')FROMDUAL;11、TRANSLATE(c1,c2,c3)就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。Replace是替换，而translate则像是过滤SELECTTRANSLATE('acdebade','abc','123')FROMDUAL;selectTRANSLATE('AabBddccCD','ABCDabcd','12341234')fromdual;SUBSTR(c1,n1[,n2])截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。n1=开始长度；n2=截取的字符串长度，如果为空，默认截取到字符串结尾；如果n1=0thenn1=1如果n10，则oracle从左向右确认起始位置截取例如：SELECTSUBSTR('Whatisthis',5,3)FROMDUAL;如果n10，则oracle从右向左数确认起始位置例如：SELECTSUBSTR('Whatisthis',-5,3)FROMDUAL;如果n1c1.length则返回空例如：SELECTSUBSTR('Whatisthis',50,3)FROMDUAL;然后再请你猜猜，如果n21，会如何返回值呢1、INSTR(c1,c2[,n1[,n2]])返回c2在c1中位置c1:原字符串c2:要寻找的字符串n1:查询起始位置，正值表示从左到右，负值表示从右到左(大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0n2:第几个匹配项。大于0例如：SELECTINSTR(‘abcdefg’,‘e’,-3)FROMDUAL;可以用INSTR(c1,c2)0来代替wherec1like‘%c2%’(三).字符型函数返回数字值2、LENGTH(c)返回指定字符串的长度。如果例如：SELECTLENGTH('abc中国')FROMDUAL;SELECTLENGTH(‘')FROMDUAL;空3、VSIZE(c)返回c的字节数。例如：SELECTVSIZE('abc中国')FROMDUAL对比一下SELECTVSIZE('abc中国'),LENGTH('abc中国')FROMDUAL1、ADD_MONTHS()返回指定日期月份+n之后的值，n可以为任何整数。SELECTADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12)FROMDUAL;selectto_char(add_months(sysdate,-12),'yyyy')fromdual;--用此方法取得上一年度年份要比selectto_char(sysdate,'yyyy')-1fromdual快（尤其是当表分区的情况下selectto_char(sysdate,'yyyy')-3,to_char(add_months(sysdate,-36),'yyyy')fromdual;--对比一下下面两个sql的执行时间SELECTCOUNT(1)ASIDFROMNJ_QY_NJZTBZWHEREZ.VCHR_NJND=to_char(sysdate,'yyyy')-3;SELECTCOUNT(1)ASIDFROMNJ_QY_NJZTBZWHEREZ.VCHR_NJND=to_char(add_months(sysdate,-36),'yyyy');selectcount(1)fromdzsj_file_allWHEREprjcode=to_char(add_months(sysdate,-84),'yyyy');selectcount(1)fromdzsj_file_allWHEREprjcode=to_char(sysdate,'yyyy')-7;日期型--返回当前session所在时区的默认时间CURRENT_DATESYSDATESELECTSYSDATE,CURRENT_DATEFROMDUAL;--LAST_DAY(d)返回指定时间所在月的最后一天SELECTlast_day(SYSDATE)FROMDUAL;selectto_date('2012','yyyy')FROMDUAL;SELECTlast_day(to_date('2012','yyyy'))FROMDUAL;--某个月最后一天selectlast_day(to_date('201202','yyyy-mm'))fromdual;--某个月有多少天selectTO_CHAR(last_day(to_date('200106','yyyymm')),'DD')fromdual;SELECTcasewhenTO_CHAR(last_day(to_date('2001'||'02','yyyymm')),'DD')=29then'闰年'else'平年'endFROMDUAL;--MONTHS_BETWEEN(d1,d2)返回d1与d2间的月份差SELECTmonths_between(SYSDATE,sysdate),months_between(SYSDATE,add_months(sysdate,-1)),months_between(SYSDATE,add_months(sysdate,1))FROMDUAL;