Oracle常用函数
1.decode用法，等同于case
decode(条件,值1,返回值1,值2,返回值2,...省缺值) 相当于判断if
decode取较小值
Select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;
sign()根据0,正数，负数返回0,1,-1
2.字符串连接 || 或 concat
3.row_number,rank,dense_rank和over的使用区别
select row_number() over(order by price) as rowid,* from a; 序列依次递增123456..
select row_number() over(partition by 分组字段 order by price) as rowid,* from a; 序列按组依次递增1234123
select rank() over(partition by 分组字段 order by price) as rowid,* from a; 同组排序相同为同一序列，下一个跳过同序列个数。12241224
select dense_rank() over(partition by 分组字段 order by price) as rowid,* from a; 序列按组有相同序列，依次递增。12231223
4.返回非空函数，参数个数不同
COALESCE(参数1,[..参数n])返回第一个非空表达式，如果都是空值，返回一个空值。相当于case判断，满足非空返回结果，停止判断。oracle9i版本后开始使用。
NVL(表达式1,表达式2)从两个表达式返回一个非空值。如果都是null则返回一个空值null。目的将空值换实际值。表达式1和2必须同一个类型。
NVL2(表达式1,表达式2,表达式3)表达式1为空返回表达式3，否则返回2.
5.trim(),ltrim(),rtrim()区别
trim()去除字符串两边的空格
ltrim()去除字符串左边的空格
rtrim()去除字符串右边的空格
6.TO_CHAR(),TO_NUMBER(),TO_DATE常见用法
(1)TO_CHAR()格式化数值，
FM9999990.009表示小数点左边最多7位，最少1为为0。小数点右边最少2位，最多3位，且第四位四舍五入。
select 0.12345 a0,to_char(0.12345) a1,
to_char(0.12345,’FM9999990.009’) a2,to_char(0.1236,’FM9999990.009’) a3 from dual;
(2)24小时显示用hh24。不加为12，超过12报错。
select sysdate,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
,to_date(’2012-03-21 18:56:12’,’yyyy-mm-dd hh24:mi:ss’) from dual;
(3)TO_NUMBER()将char或varchar变成数值类型
格式范围内可以转换，反之报错
select to_number(‘123.123’,’9999.999’) from dual;可以
select to_number(‘123.123’,’9999.99’) from dual;报错
7.EXECUTE IMMEDIATE存储过程中使用
(1)直接执行语句
EXECUTE IMMEDIATE ‘truncate table a’ ;
(2)执行参数语句
v_sql :=‘select count(1) from a’ ; EXECUTE IMMEDIATE v_sql;
(3)执行参数语句并复值给某个变量
EXECUTE IMMEDIATE v_sql into v_num;
(4)带参数sql
v_sql :=‘select * from a where a.t1= :1 and a.t2= :2 ’;
EXECUTE IMMEDIATE v_sql using 值1，值2;
8.行转列 适合10g之前
select x1,x2,
sum(decode(rn,1,值y)) 值y1,
sum(decode(rn,2,值2))值y2,
from(
select x1,x2,date,值1,
rank() over (partition by x1,x2 order by a.date desc) rn
from a where ..group by x1,x2
)
oracle 11g后行转列可以函数pivot()行转列，unpivot列转行
pivot(聚合函数 for 列名 in(类型) ), in(‘’)可以是指定列别名，也可以是
子查询。例如(select distinct 列名 from a)
select * from (
select x1,x2,值1行转列的值，
rank() over (partition by x1,x2 order by a.date desc) rn
from a where ..)b
pivot (sum(值1) for rn in( 1 别名1, 2别名2))
结果为x1,x2,别名,别名2
