一枚有理想的女程序员：Oracle常用函数

Oracle常用函数

一、DECODE()函数

1.DECODE()函数的两种形式

第一种形式

含义解释：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

第二种形式

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

二、sign()函数

解释：
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

三、union和union all的用法

union all是将两个select语句的结果求并集。 union是将union all的结果下再去除重复数据

四、WM_CONCAT函数

wm_concat()函数是oracle中独有的,mysql中有一个group_concat()函数。

含义：实现行转列功能，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据

示例：

五、NVL()函数与NVL2()函数

nvl函数

nvl函数基本语法为nvl(E1,E2)，意思是E1为null就返回E2，不为null就返回E1。

nvl2函数

nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。

六、CONCAT()函数

concat只能连接两个字符，而“||”可以连接多个字符。

用concat时：

①.sql>select concat(‘aa’,‘bb’) from test;

 concat('aa','bb')

 aabb

②.sql>select concat(‘aa’,‘bb’,‘cc’) from test;

 0RA-00909:invalid number of arguments

 因此，用concat连接多个字符时，需要嵌套

 sql>select concat(concat('aa','bb'),'cc') from test;



 concat(concat('aa','bb'),'cc')

 aabbcc

用||时：

 sql>select 'aa'||'bb' from test

 'aa'||'bb'

 aabb

七、WITH AS用法

with as的作用其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了

语法：

针对一个别名
with tmp as (select * from tb_name)

针对多个别名
	with
 	tmp as (select * from tb_name),
	tmp2 as (select * from tb_name2),
 	tmp3 as (select * from tb_name3),

八、SELECT DISTINCT 语句

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

DISTINCT 关键词用于返回唯一不同的值。

语法：

SELECT DISTINCT 列名称 FROM 表名称

九、CONNECT BY PRIOR

Oracle中：从子节点查询，直到根节点的一个分支；或者从根节点查询，知道字节点的许多分支；可以用proir

总结：

1）prior放在子节点端，则表示扫描树是以start with指定的节点作为根节点从上往下扫描。可能对应一个或多个分支。 start with可以省略，如果省略，表示对所有节点都当成根节点分别进行遍历

2）prior放在父节点端，则表示扫描树是以start with指定的节点作为最低层子节点，从下往上扫描。顺序是子节点往父节点扫描，直到根节点为止，这种情况只能得到一个分支。 start with可以省略，如果省略，表示对所有节点都当成最低层子节点分别往根节点方向遍历