ORACLE函数介绍

 

 

ORACLE函数介绍

第一篇 著名函数之单值函数   2007.8.13

 

 

注：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)

　　该类函数返回与输入类型相同的类型。

l         返回的CHAR类型值长度不超过2000字节；

l         返回的VCHAR2类型值长度不超过4000字节；

如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。

 

l         返回的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的字符串，需要注意的有几点：

l         如果n<c1.length则从右到左截取指定长度返回；

l         如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；

l         如果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)  哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。

<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1025" style="width: 405.75pt; height: 96.75pt; mso-wrap-style: square; mso-position-horizontal-relative: page; mso-position-vertical-relative: page" type="#_x0000_t75"><imagedata o:title="functions126" src="file:///C:%5CDOCUME~1%5CsoftTest%5CLOCALS~1%5CTemp%5Cmsohtml1%5C02%5Cclip_image001.gif"></imagedata></shape>

看起来很复杂，理解起来很简单：

l         如果没有指定任何参数则oracle去除c1头尾空格

例如：SELECT TRIM('   WhaT is tHis   ') FROM DUAL;

l         如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)

例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;

l         如果指定了leading参数则会去掉c1头部c2

例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;

l         如果指定了trailing参数则会去掉c1尾部c2

例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;

l         如果指定了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) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：

l         保留字符串首字母，但删除a、e、h、i、o、w、y。

l         将下表中的数字赋给相对应的字母：

1：b、f、p、v

2：c、g、k、q、s、x、z

3：d、t

4：l

5：m、n

6：R

l         如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；

l         只返回前4个字节，不够用0填充

例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;

 

10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。

n1=开始长度；

n2=截取的字符串长度，如果为空，默认截取到字符串结尾；

l         如果n1=0 then n1=1

l         如果n1>0，则oracle从左向右确认起始位置截取

例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;

l         如果n1<0，则oracle从右向左数确认起始位置

例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;

l         如果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中位置

l         c1:原字符串

l         c2:要寻找的字符串

l         n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0

l         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) 返回指定时间所在月的最后一天

margin-left: 21p