Oracle语法

整理文档很辛苦,赏杯茶钱您下走!

免费阅读已结束,点击下载阅读编辑剩下 ...

阅读已结束,您可以下载文档离线阅读编辑

资源描述

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

1 / 15
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功