oracle 常用函数整理

当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval ’7’ day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8*interval '7' hour from dual

实用函数
DECODE

语法如下：　　

DECODE(value, if1, then1, if2,then2,if3,then3, . . . else ) Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。　　需要注意的是，这里的if、then及else 都可以是函数或计算表达式。

实例

decode(tr.PERIOD_NUM,1,'P01',2,'P02',3,'P03','Err')

INSTR

返回要查找字符的位置

CASEWHEN

详见http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数

CASE

WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END

decode和case when区别？

decode只能做等值的，case when可以做区间的，使用范围来进行条件区分，decode能做到的，case when也能做到；效率方面，个人觉得两者效率差不多；但decode的语法要简单些，但它只能做等值的比较；case when end 能做条件的判断。

http://blog.sina.com.cn/s/blog_6da521f90100tcm4.html

SUBSTR

substr() 函数返回字符串的一部分。

语法：substr(string,start,length)

string - 指定的要截取的字符串。

start - 必需，规定在字符串的何处开始。

正数 - 在字符串的指定位置开始

负数 - 在从字符串结尾的指定位置开始

0 - 在字符串中的第一个字符处开始

length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。

例如：select substr('abcdefg',3,4) from dual;结果是cdef

select substr('abcdefg',-3,4) from dual;结果efg

Oracle正则表达式函数：

regexp_like、regexp_substr、regexp_instr、regexp_replace

REPLACE

replace(x,y,z)返回值为将串X中的Y串用Z串替换后的结果字符串。若省略Z参数，则将串X中为Y串的地方删除

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Changes

--------------

BLACK and BLUE

例子

有一个列导入的数据应该时‘2011-10-11’的格式，结果导入的数据为‘2011/10/11’格式的，5000多条记录要一条条改基本不可能。 后来想到了replace这个函数，具体用法如下：

update 表1 t set t.列1=replace((select 列1 from 表1 a where a.主键列=t.主键列) ,'/' , '-' ) 解决了我们问题。

replace 函数用法如下：

replace（'将要更改的字符串','被替换掉的字符串','替换字符串'）

select"PARENTNAME", "MEMBERNAME", "ALIAS"

from (select'Product' parentname,

'ProductTotal' membername,

'产品汇总'alias

from dual)

CONNECT BY

Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：

[ START WITH condition ]

CONNECTBY [ NOCYCLE ] condition

1． 可用来生成序列

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM<= 10;

2.可用来实现树状查询

CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

selectrownumfrom dual CONNECTBYrownum <= length('qwertyui')

详见http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

RPAD

rpad函数从右边对字符串使用指定的字符进行填充 　　rpad(string,padded_length,[pad_string])　　

string 表示：被填充的字符串 　　

padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符; 　　

pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。 　　

例如：　　

rpad('tech', 7); 将返回'tech ' 　　

rpad('tech', 2); 将返回'te' 　　

rpad('tech', 8, '0'); 将返回'tech0000'　　

rpad('tech on the net', 15, 'z'); 将返回 'tech onthe net' 　　

rpad('tech on the net', 16, 'z'); 将返回 'tech onthe netz'

TRUNC

TRUNC函数返回以指定元素格式截去一部分的日期值。

--Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual--123
10.select trunc(123.458,0) from dual--123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual--120
13.select trunc(123.458,-4) from dual--0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual--123
17.select trunc(123,-1) from dual--120

一下语句会经常用到，相当于不用创建表而可以插入几条数据

Select ‘Jan’ membername,’1月’ name from dual union select ‘Feb’ membername,’2月’ name from dual;

ADD_MONTHS

ADD_MONTHS(d,n)--时间点d再加上n个月

有两个简单例子，以说明"exists"和"in"的效率问题

1) select * from T1 where exists(select 1from T2 where T1.a=T2.a) ;

　　T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (selectT2.a from T2) ;

T1数据量非常大而T2数据量小时，T1>>T2时，2) 的查询效率高。

In适合内外表都很大的情况，exists适合外表结果集很小的情况

过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。

Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。

在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。

这也就是使用EXISTS比使用IN通常查询速度快的原因。

OVER函数的作用及用法

RANK ( ) OVER ( [query_partition_clause]order_by_clause )

DENSE_RANK ( ) OVER ([query_partition_clause] order_by_clause )

可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,

其中PARTITION BY 为分组字段，ORDER BY 指定排序字段。

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_idorder by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。

TO_CHAR TO_DATE

to_char和to_date经验分享

㈠比较下面两种写法：

to_char(start_time,'yyyy-mm-ddhh24:mi:ss')='2013-06-06 16:18:30'

和

start_time=to_date('2013-06-06 16:18:30','yyyy-mm-ddhh24:mi:ss')

任何时候都推荐使用后者！！

㈡数字转为英文的简单实现

sys@ORCL> selectto_char(to_date('1314','J'),'Jsp') as "I love 1314" from dual;

I love 1314

-----------------------------------

One Thousand Three Hundred Fourteen

TO_CHAR(aDate,'JSP')可以得到一个数字的拼写,例如:
SELECT TO_CHAR(SYSDATE, 'JSP') FROM DUAL;
这条SQL的查询结果是:
TWO MILLION FOUR HUNDRED FIFTY-THREE THOUSAND FIVE HUNDRED NINE
该数字为:20004530509
为什么?
因为TO_CHAR(aDate,'JSP')是指日期aDate距离JULIAN日期的第一天即4712-01-01BC的天数,也就是从公元前4712年的1月1号到aDate这个日期的天数的英文拼写.Cool!
JULIAN日期的范围是公元前4712-01-01到公元9999-01-01.期间的天数是我们能够拼写的最大值,最小值是1.
那么我们要想得到对一个数字的拼写,只要知道这些天数之后的那个日期就行,得到这个日期很简单:
TO_DATE(aDigital,'J') 其中aDigital就是你要表示的数字.
这样我们得到一个数字的拼写可以这样(例如101):
SELECT TO_CHAR(TO_DATE(101, 'J'), 'JSP') FROM dual
得到的结果是: ONE HUNDRED ONE

㈢一个 to_date 的陷阱

我们可以用to_date函数来构造自己想要的任何时间

当省略了HH,MI,SS时，oracle缺省置为0，即：取整到日

当省略了DD时，oracle缺省置为1，即：取整到月

当省略了MM时，oracle会取整到年吗？我们都会惯性的认为：会。然，非也！

Oracle不会取整到年，他会取整到当前月！

select to_date('1991','yyyy') from dual;

结果为1991-6-1


1.在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份