Oracle数据库实用函数

Oracle实用函数

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

--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数

CASE

WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END

 

decodecase when区别?

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

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 dualCONNECTBYrownum <=length('qwertyui')

 

 

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个月

相关推荐