Oracle中常用的函数

1.NVL(expr1，expr2)

 解释：如果参数一expr1为空（包括null和‘’），则结果为expr2的值，如果expr1不为空，则结果为expr1的值。

SQL> SELECT NVL(NULL,'1') FROM DUAL;

NVL(NULL,'1')
-------------
1

SQL> SELECT NVL('','1') FROM DUAL;

NVL('','1')
-----------
1

SQL> SELECT NVL('a','1') FROM DUAL;

NVL('A','1')
------------
a

SQL> 

2.NVL2(expr1，expr2，expr3)

解释：如果参数expr1值为空，则结果为expr3的值，如果expr1不为空，则结果为expr2的值。

SQL> SELECT NVL2(null,'1','2') FROM DUAL;

NVL2(NULL,'1','2')
------------------
2

SQL> SELECT NVL2('','1','2') FROM DUAL;

NVL2('','1','2')
----------------
2

SQL> SELECT NVL2('a','1','2') FROM DUAL;

NVL2('A','1','2')
-----------------
1

SQL> 

3.LPAD(string,padded_length,[ pad_string])

解释：参数1：目标字符串，参数2：最终生成字符串的长度，参数3：填充字符（可以没有）。

此方法表示从左开始截取字符串，截取的长度为padded_length的值。如果有第三个参数，则表示在字符串（第一个参数）左边填充字符（pad_string的值）。如果字符串的长度小于参数padded_length的值，则在字符串前面补充空格。注意最后一个SQL例子。

SQL> SELECT LPAD('123',10) FROM DUAL;

LPAD('123',10)
--------------
       123

SQL> SELECT LPAD('1234567890',10) FROM DUAL;

LPAD('1234567890',10)
---------------------
1234567890

SQL> SELECT LPAD('1234567890',5) FROM DUAL;

LPAD('1234567890',5)
--------------------
12345

SQL> SELECT LPAD('123',10,'0') FROM DUAL;

LPAD('123',10,'0')
------------------
0000000123

SQL> SELECT LPAD('123',10,'x') FROM DUAL;

LPAD('123',10,'X')
------------------
xxxxxxx123

SQL> SELECT LPAD('123',10,'ABC') FROM DUAL;

LPAD('123',10,'ABC')
--------------------
ABCABCA123

SQL> 

4.RPAD(string,padded_length,[ pad_string])

解释：此方法和上面的LPAD()一样，不过这个是从右边开始的。

SQL> SELECT RPAD('ABC',5) FROM DUAL;

RPAD('ABC',5)
-------------
ABC

SQL> SELECT RPAD('ABCDEFG',5) FROM DUAL;

RPAD('ABCDEFG',5)
-----------------
ABCDE

SQL> SELECT RPAD('ABC',5,'0') FROM DUAL;

RPAD('ABC',5,'0')
-----------------
ABC00

SQL> SELECT RPAD('ABC',10,'123') FROM DUAL;

RPAD('ABC',10,'123')
--------------------
ABC1231231

SQL> 

5.SIGN(expr1-expr2)

解释：此函数用来比较两个值的大小，如果expr1>expr2,返回1，如果expr1<expr2，返回-1，如果相等返回0。

SQL> select sign(5-3) from dual;

 SIGN(5-3)
----------
         1

SQL> select sign(5-10) from dual;

SIGN(5-10)
----------
        -1

SQL> select sign(5-5) from dual;

 SIGN(5-5)
----------
         0

SQL> 

6.DECODE(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

解释：如果条件满足值1，则结果为返回值1，如果条件满足值2，则结果为返回值2，以此类推，如果都不满足，则结果为缺省值。

具体含义如下，

SQL> select decode('符合条件','符合条件','正确','不符合条件','错误','未知') from dual;

DECODE('符合条件','符合条件','正确','不符合条件','错误','未知')
--------------------------------------------
正确

SQL> select decode('不符合条件','符合条件','正确','不符合条件','错误','未知') from dual;

DECODE('不符合条件','符合条件','正确','不符合条件','错误','未知')
---------------------------------------------
错误

SQL> select decode('条件','符合条件','正确','不符合条件','错误','未知') from dual;

DECODE('条件','符合条件','正确','不符合条件','错误','未知')
------------------------------------------
未知

SQL> 

比如我有一张表T_HAO_COALA_ACCOUNT（客户账户表）：

ID	CUSTOMER_NAME	SEX	BIRTHDAY	PRINCIPAL	BALANCE	ARREARAGE	BILL_DATE
1	张三	1	2010/12/1	5000.00	1000.00	4000.00	10
3	王五	1	1987/6/11	30000.00	1500.00	28500.00	5
2	李四	1	2007/7/18	10000.00	8000.00	2000.00	12
4	你	2	2019/12/1	2000.00	0.00	2000.00	20
5	哈哈	2	2019/12/1	20000.00	20000.00	0.00	15
6	拉拉	1	1985/2/13	8000.00	6000.00	2000.00	8

1.将性别转换成汉字：PRINCIPAL：本金，BALANCE：余额，ARREARAGE：欠款，BILL_DATE：账单日。

select t.id,t.customer_name,decode(t.sex,'1','男','2','女') from t_hao_coala_account t

2.比较大小。找出欠款大于余额的：

select t.id,t.customer_name,decode(sign(t.arrearage-t.balance),0,'持平','1','欠款大于余额','-1','欠款较少') as result 
from t_hao_coala_account t 

3.本金等于10000放在最前面，其次是本金大于10000的，同时按本金升序排列

select t.id,t.customer_name,t.principal from t_hao_coala_account t  
order by decode(sign(t.principal-10000),0,1,1,2,-1,3),t.principal asc