Oracle常用函数

1、nvl( ) 函数

从两个表达式返回一个非 null 值。

语法

NVL(eExpression1, eExpression2)

参数

eExpression1, eExpression2

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或null值。

说明

在不支持 null 值或 null 值无关紧要的情况下，可以使用NVL( )来移去计算或操作中的 null 值。

注意：两个参数的类型要匹配

2、ROW_NUMBER() OVER()函数

语法

 ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

参数

COLUMN1，COLUMN2

根据COLUMN1分组，在分组内部根据 COLUMN2排序。

举例

表employee的数据如下：

empid       deptid      salary
----------- ----------- -----------------------
1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

需求：根据部门分组，显示每个部门的工资等级

预期结果：

empid       deptid      salary                              rank
----------- ----------- -----------------------      --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                               1
6           40          14500.00                               2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

SQL脚本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee;

3、ORDER BY 子句

解释

此处主要说一下order by后面接多个字段的情况。

select * from tablename order by column1,column2;

等价于

select * from tablename order by column1 asc,column2 asc;

order by 的默认排序方式为asc,也就是说在排序字段后面不添加关键字也会默认asc排序。

多字段执行方式：先按照column1的值排序，当column1中有重复数据的时候、在按column2里面的值来排序，如果有column3、就按照column2里面的重复数据来排列,所以，order by 后面的字段顺序对排序结果是有影响的。

（注：column2的排序仅仅存在于column1的重复数据排序）

order by 子句按一个或多个（最多16个）字段排序查询结果，可以是升序（asc）也可以死降序（desc）,缺省是升序，order by子句通常放在SQL语句的最后，如果order by子句中定义了多个字段，则按照字段的先后顺序排序。

举例

select acct_month,prov_id,result  from ZBA_DM.ODS_EXECUTE_LOG T
where  PROCNAME='P_DM_C_M_BI_3G_ACCT_SNAP' and acct_month between '201301' and '201303' and prov_id in('010','011','017','091')
order by prov_id desc, acct_month asc;

查询结果如下：

                                                          

select acct_month,prov_id,result  from ZBA_DM.ODS_EXECUTE_LOG T
where  PROCNAME='P_DM_C_M_BI_3G_ACCT_SNAP' and acct_month between '201301' and '201303' and prov_id in('010','011','017','091')
order by acct_month asc,prov_id desc;

查询结果如下：

                                                          

 

 4、INSTR

  通过INSTR可以获取字段中某个特殊字符的位置，通过该位置可以对字段进行截取.

  例如：SELECT * FROM TABLE;

                                           

     通过INSTR截取 . 之后的字符。

  SELECT SUBSTR(MON_OBJ_ID,INSTR(MON_OBJ_ID,'.')+1) MON_OBJ_ID FROM TABLE;

                                                                              

5、LEAST

含义：返回值列表中最小值。

格式：LEAST(VALUE1，VALUE2，VALUE3,....)

注：value列表必须是相同类型，也可以是一张表的同一行，不同列的值进行比较。

    当value值列表中有一个为null，则返回null值。

例如：

SELECT LEAST(1,3,2) FROM DUAL;--返回1

SELECT LEAST('A','B','C') FROM DUAL;--返回A

SELECT LEAST(NULL,'B','C') FROM DUAL; --返回null

SELECT LEAST(T.EMPNO,T.MGR) FROM SCOTT.EMP;--返回empno和mgr的较小值