ITPub博客

首页 > 数据库 > Oracle > Oracle 12个精简常用函数

Oracle 12个精简常用函数

原创 Oracle 作者:忆江南soy 时间:2014-03-01 14:58:58 0 删除 编辑

(1)trunc(for date)

这里注意 to_date date是日期值 所以转换后是不含有时间

    TRUNC(for date) TRUNC函数为指定元素而截去的日期值。

  其具体的语法格式如下: TRUNCdate[,fmt]

  其中:date一个日期值,fmt日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

下面是该函数的使用情况:(mi minute 分钟的意思)

  select trunc(to_date('2012-03-23 23:59:59','yyyy-mm-dd hh24:mi:ss')) from dual 

系统定义表,只有一笔记录.可以用来返回函数值. select user from dual;返回当前用户. dual为了完善语义,因为没有from oracle中是报错的

-- return date : 2012-3-23

trunc(sysdate,'yyyy') --返回当年第一天.

trunc(sysdate,'mm') --返回当月第一天.

trunc(sysdate,'d') --返回当前星期的第一天(2012-07-11所在星期的第一天是2012-07-08)

思考:?如何获取当月的最后一天(提示:根据系统时间)

 

(2)trunc(number)

  TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。

  其具体的语法格式如下TRUNCnumber[,decimals]

  其中:number待做截取处理的数值,decimals指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分

  下面是该函数的使用情况:

  TRUNC89.9852=89.98

  TRUNC89.985=89

  TRUNC89.985-1=80

  注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0。与取整类似,比如参数为1即取整到十分位,如果是-1,则是取整到十位,以此类推;如果所设置的参数为负数,且负数的位数大于整数的字节数的话,则返回为0。如:TRUNC(89.985,-3)=0.

 

(3)to_char

把日期或数字转换为字符串;to_date是把字符串转换为数据库中得日期类型

使用TO_CHAR函数处理数字:TO_CHAR(number, '格式')TO_CHAR(salary,’$99,999.99’);

使用TO_CHAR函数处理日期:  TO_CHAR(date,格式);

 

(4)to_date

使用TO_DATE函数将字符转换为日期:TO_DATE(char[, '格式'])

e.g   select to_date('2011-11-5 4:39:57','yyyy-mm-dd hh24:mi ss') as col from dual

 

(5)to_number

使用TO_NUMBER函数将字符转换为数字:TO_NUMBER(char[, '格式'])

各种格式:

数字格式格式

日期格式

9代表一个数字

0强制显示0

$放置一个$

L放置一个浮动      本地货币符

.显示小数点

,显示千位指示符

 

格式控制描述

YYYYYYYYY分别代表4位、3位、2位的数字年,YEAR年的拼写

MM数字月,MONTH月的全拼,MON月的缩写

DD数字日,DAY星期的全拼,DY星期的缩写,AM表示上午或者下午

HH24HH12 12小时制或24小时制,MI分钟,SS秒钟

SP数字的拼写,TH数字的序数词

特殊字符假如特殊字符

HH24:MI:SS AM 15:43:20 PM

DD “OF” MONTH 12 OF OCTOBER

DDSPTH fourteenth

Date的格式 18-5-84

 

(6)instr

INSTR方法的格式为:INSTR(源字符串,目标字符串,起始位置,匹配序号)

查询是否匹配目标字符串

例如:INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR',目标字符串为'OR',起始位置为3,取第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候,从右边开始查找。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1)  "Instring" FROM DUAL的显示结果是:14

 别名Instring

 

(7)substr

取得字符串中指定起始位置和长度的字符串substr( string, start_position, [ length ] )

:

substr('This is a test', 6, 2)       return 'is'

substr('This is a test', 6)         return 'is a test'

substr('TechOnTheNet', -3, 3)   return 'Net'

substr('TechOnTheNet', -6, 3)    return 'The'select substr('Thisisatest', -4, 2) value from dual

举个例子更容易区分这两个函数:

 

(8)trim

Oracle中的trim函数是用来删除给定字符串或者给定数字中的头部或者尾部的给定字符。

trim函数具有如下的形式trim([leading/trailing/both][匹配字符串或数值][from][需要被处理的字符串或数值])这里如果

   指明了leading表示从删除头部匹配的字符串

   如果指明了trailing表示从删除尾部匹配的字符串

   如果指明了both,或者不指明任何位置,则两端都将被删除

如果不指明任何匹配字符串或数值则认为是空格,即删除前面或者后面的空格。

trim函数返回的类型是varchar2下面是一些例子:

各种情况

例子

结果

指明leading表示从删除头部匹配的字符串

select trim(leading '1' from '12321Tech11') from dual;

2321Tech11

指明trailing表示从删除尾部匹配的字符串

select trim(trailing '1' from '12321Tech11') from dual;

12321Tech

如果指明了both,或者不指明任何位置,则两端都将被删除

select trim(from中秋八月中) asfrom dual;   

select trim(both '1' from '12321Tech 111') from dual;

 

秋八月

2321Tech

如果不指明任何匹配字符串或数值则认为是空格,即删除前面或者后面的空格

select trim('   tech   ')  asfrom dual;

select trim(' '  from  '   tech   ')  asfrom dual;

select trim(0 from 7500) from dual;   

tech

tech

75

 LTRIM, RTRIM 不但可以去掉空格,还可以去掉指定字符, LTRIM(',aaaa',',')

 ltrim  rtrim

 

(9)translate

 语法:TRANSLATE(char, from, to)  

 用法:返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。

 fromto字符串长,那么在from中比to中多出的字符将会被删除。

三个参数中有一个是空,返回值也将是空值。

 举例:SQL> select translate('abcdefga','abc','wo')返回值from dual;

返回值------- wodefgw 

 分析:该语句要将'abcdefga'中的'abc'转换为'wo',

由于'abc''a'对应'wo'中的'w',故将'abcdefga'中的'a'全部转换成'w';

'abc''b'对应'wo'中的'o',故将'abcdefga'中的'b'全部转换成'o';

'abc'中的'c''wo'中没有与之对应的字符,故将'abcdefga'中的'c'全部删除;

简单说来,就是将from中的字符转换为to中与之位置对应的字符,若to中找不到与之对应的字符,返回值中的该字符将会被删除。

在实际的业务中,可以用来删除一些异常数据,比如表a中的一个字段t_no表示电话号码,而电话号码本身应该是一个由数字组成的字符串,

为了删除那些含有非数字的异常数据,就用到了translate函数:

SQL> delete from a  where length(translate(trim(a.t_no), '0123456789' || a.t_no,                                             '0123456789')) <> length(trim(a.t_no));

 

(10)replace

 语法:REPLACE(char, search_string,replacement_string)

 用法:将char中的字符串search_string全部转换为字符串replacement_string,没有匹配的字符串就都不变。       

 举例:

SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') from dual;返回值from dual;

结果是fgsgswsgs

       SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt')返回值from dual;

结果是fgeeerrrtttsweeerrrttts

分析:第一个例子中由于'fgsgswsgs'中没有与'fk'匹配的字符串,故返回值仍然是'fgsgswsgs'

第二个例子中将'fgsgswsgs'中的字符串'sg'全部转换为'eeerrrttt'

总结:综上所述,replacetranslate都是替代函数,只不过replace针对的是字符串,而translate针对的是单个字符。

 

(11)decode()

DECODE函数,它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。

区别于SQL的其它函数,DECODE函数还能识别和操作空值。 

语法:DECODE(control_value,value1,result1[,value2,result2…][,default_result]); 

试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较,以决定返回值。

value1   是一组成序偶的数值。如果输入数值与之匹配成功,则相应的结果将被返回。对应一个空的返回值,可以使用关键字NULL于之对应。

result1  是一组成序偶的结果值。

default_result  未能与任何一个值匹配时,函数返回的默认值。 

例如:

select decode(x,1,'x is 1',2,'x is 2','others') from dual

x等于1时,则返回‘x is 1’

x等于2时,则返回‘x is 2’

否则,返回others’ 

需要,比较2个值的时候,可以配合sign()函数一起使用。

SELECT DECODE( SIGN(5-6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero')from dual

同样,也可以用CASE实现:

SELECT CASE SIGN(5-6) 

   WHEN  1  THEN  'Is Positive'

   WHEN -1 THEN  'Is Nagative'

   ELSE 'Is Zero' END

FROM DUAL 

此外,还可以在Order by中使用Decode

例如:表subject,有subject_name列。要求按照:语、数、外的顺序进行排序。这时,就可以非常轻松的使用Decode完成要求了。

select * from subject order by decode(subject_name, '语文',1, '数学',2, '外语',3) 

(12)nvl

nvl( ) 函数(类似于SQLSERVERisnull)

语法:  1.     NVL(eExpression1, eExpression2)

参数:  1.     eExpression1, eExpression2

如果eExpression1的计算结果为null值,则NVL( )返回eExpression2

如果eExpression1的计算结果不是null值,则返回eExpression1

eExpression1eExpression2可以是任意一种数据类型。

如果eExpression1eExpression2的结果皆为null值,则NVL( )返回.NULL.

1.    select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID 

注意:两个参数得类型要匹配

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28982975/viewspace-1097637/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-18

  • 博文量
    16
  • 访问量
    64983