oracle只舍不入,Oracle常用函数 - 不以物喜-不以己悲的个人空间 - OSCHINA - 中文开源技术交流社区...

1、decode

decode(value,if1,then1,if2,then2,if3,then3,...,[default])；

如果不匹配，返回default值；如果未定义default值，则返回空值。

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表

举例：

现有一个商品销售表sale，表结构为:

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

现有数据为:

200001　　1000

200002　　1100

200003　　1200

200004　　1300

200005　　1400

200006　　1500

200007　　1600

200101　　1100

200202　　1200

200301　　1300

想要转化为以下结构的数据:

year　　　char(4)　　　　　 --年份

month1　　number(10,2)　　　--1月销售金额

month2　　number(10,2)　　　--2月销售金额

month3　　number(10,2)　　　--3月销售金额

month4　　number(10,2)　　　--4月销售金额

month5　　number(10,2)　　　--5月销售金额

month6　　number(10,2)　　　--6月销售金额

month7　　number(10,2)　　　--7月销售金额

month8　　number(10,2)　　　--8月销售金额

month9　　number(10,2)　　　--9月销售金额

month10　　number(10,2)　　　--10月销售金额

month11　　number(10,2)　　　--11月销售金额

month12　　number(10,2)　　　--12月销售金额

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

decode(substrb(month,5,2),'01',sell,0),

decode(substrb(month,5,2),'02',sell,0),

decode(substrb(month,5,2),'03',sell,0),

decode(substrb(month,5,2),'04',sell,0),

decode(substrb(month,5,2),'05',sell,0),

decode(substrb(month,5,2),'06',sell,0),

decode(substrb(month,5,2),'07',sell,0),

decode(substrb(month,5,2),'08',sell,0),

decode(substrb(month,5,2),'09',sell,0),

decode(substrb(month,5,2),'10',sell,0),

decode(substrb(month,5,2),'11',sell,0),

decode(substrb(month,5,2),'12',sell,0)

from A_Test;

结果：显然不符合要求

方法二：

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)),

sum(decode(substrb(month,5,2),'02',sell,0)),

sum(decode(substrb(month,5,2),'03',sell,0)),

sum(decode(substrb(month,5,2),'04',sell,0)),

sum(decode(substrb(month,5,2),'05',sell,0)),

sum(decode(substrb(month,5,2),'06',sell,0)),

sum(decode(substrb(month,5,2),'07',sell,0)),

sum(decode(substrb(month,5,2),'08',sell,0)),

sum(decode(substrb(month,5,2),'09',sell,0)),

sum(decode(substrb(month,5,2),'10',sell,0)),

sum(decode(substrb(month,5,2),'11',sell,0)),

sum(decode(substrb(month,5,2),'12',sell,0))

from A_Test

group by substrb(month,1,4);

结果：bingo

举例2：

id

score

1

100

2

95

3

85

4

84

5

70

6

60

id

standard

1

优秀

2

优秀

3

优秀

4

良好

5

良好

6

及格

select id, decode(sign(score-85),1,'优秀',0,'优秀',-1,

decode(sign(score-70),1,'良好',0,'良好',-1,

decode(sign(score-60),1,'及格',0,'及格',-1,'不及格'))) standard

from A_Test;

select id,

case

when score>=85 then '优秀'

when score>=70 then '良好'

when score>=60 then '合格'

when score<60 then '不及格'

end standard

from A_Test;

2、sign

sign(value)，某个值是0、正数还是负数，分别返回0、1、-1

3、字符函数

1)截取

substr(str, start, length)，按字算

substrb，按字节算，一个中文2个字节，当所取长度为奇数时，则自动舍弃最后一位字节

substrc:按Unicode编码

substr2:按UCS2编码

substr4:按UCS4编码

同理，字符串查找 instr(str, findStr[, start][, 第几个匹配]) 与 instrb。

length(str)和lengthb

2)ASCII(X)  --返回字符X的ASCII码

chr(x) --CHR和ASCII是一对反函数

CONCAT(X,Y) --连接字符串X和Y

LOWER(X) --X转换成小写

UPPER(X) --X转换成大写

LTRIM(X[,TRIM_STR]) --把X的左边截去trim_str字符串，缺省截去空格

RTRIM(X[,TRIM_STR])

TRIM([TRIM_STR  FROM]X)

REPLACE(X,old,new)

TRANSLATE(string,from_str,to_str)

--TRANSLATE是REPLACE所提供的功能的一个超集.如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.

RPAD(string1,x[,string2])

--返回在X 字符长度的位置上插入一个string2中的字符的string1。如果string2的长度要比X字符少，就按照需要进行复制。如果string2多于 X字符，则仅string1前面的X各字符被使用。如果没有指定string2，那么使用空格进行填充。X是使用显示长度可以比字符串的实际长度要长。 RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。

INITCAP(String) --返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string

NLS_INITCAP

NLS_LOWER

NLS_UPPER

NLSSORT(string[,nlsparams])

--得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.

4、数字

ABS(X)

CEIL(X)

FLOOR(X)

ROUND(X[,Y]) --X在第Y位四舍五入

TRUNC(X[,Y]) --X在第Y位截断

MOD(X,Y) --取余

POWER(X,Y) --X的Y次幂

LOG(X,Y) --X为底Y的对数

SQRT(X) --X的平方根

5、NVL(X,VALUE)

如果X为空，返回value，否则返回X

NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

6、聚合函数

sum、avg、count、min、max