Oracle常用函数-白红宇

Oracle常用函数

阅读量：6865 次

发布时间：2019-06-26

本文共 4107 字，大约阅读时间需要 13 分钟。

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,'不及格'))) standardfrom A_Test;

select id,case when score>=85 then '优秀'when score>=70 then '良好'when score>=60 then '合格'when score<60 then '不及格'end standardfrom 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位截断