Oracle用法、函数备忘记录
Listagg
select * from emp
select LISTAGG(ename,'-') within group (order by deptno desc) from emp;
可以看到功能类似wm_concat，可以自定义连接符，区别：
LISTAGG : 11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000.
和wm_concat相比，listagg可以执行排序。例如
select deptno, listagg(ename,';')
within
group(order
by ename) enames from emp group
by deptno;
类似的语法还有：
--计算数值1000在记录集中的排序值。
select rank(1000) within group(order by sal),dense_rank(1000) within group(order by sal) from emp;
cume_dist()、PERCENT_RANK
cume_dist:计算结果为相对位置/总行数。返回值(0,1]。
percent_rank:计算方法为 (相对位置-1)/(总行数-1),
select cume_dist(20, 4000) within group(order by deptno, sal) cume_dist
from emp;
with table as
SQL Code
with temp as(
select 500 population, 'China' nation ,'Guangzhou' city from dual union all
select 1500 population, 'China' nation ,'Shanghai' city from dual union all
select 500 population, 'China' nation ,'Beijing' city from dual union all
select 1000 population, 'USA' nation ,'New York' city from dual union all
select 500 population, 'USA' nation ,'Bostom' city from dual union all
select 500 population, 'Japan' nation ,'Tokyo' city from dual
)
select population,
nation,
city,
listagg(city,',') within GROUP (order by city) over (partition by nation) rank
from temp
With table as 类似创建一个临时表，只可以查询一次，之后就被销毁，同时可以创建多个临时table，比如：
with sql1 as
(select to_char(a) s_name from test_tempa),
sql2 as
(select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum = 1))
select * from sql1 union all select * from sql2
pivot unpivot
行列转换，见
Minus
SQL中有一个MINUS关键字，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃，其语法如下：
[SQL Segment 1]
MINUS
[SQL Segment 2]
--------------------------------------------
--创建表1
create table test1
(
name varchar(10),
sex varchar(10),
);
insert into test1 values('test','female');
insert into test1 values('test1','female');
insert into test1 values('test1','female');
insert into test1 values('test11','female');
insert into test1 values('test111','female');
--创建表2
create table test2
(
name varchar(10),
sex varchar(10),
);
insert into test1 values('test','female');
insert into test1 values('test2','female');
insert into test1 values('test2','female');
insert into test1 values('test22','female');
insert into test1 values('test222','female');
-------------------------------------------
select * from test1 minus select * from test2;
结果：
NAME SEX
---------- ----------
test1 female
test11 female
test111 female
-----------------------------------------------------------
select * from test2 minus select * from test1;
结果:
NAME SEX
---------- ----------
test2 female
test22 female
test222 female
结论：Minus返回的总是左边表中的数据，它返回的是差集。注意：minus有剃重作用
==========================================================
下面是我做的实验，很明显能够看出MINUS的效率，made_order共23万笔记录，charge_detail共17万笔记录
性能比较:
SELECT order_id FROM made_order
MINUS
SELECT order_id FROM charge_detail
1.14 sec
select a.order_id
from made_order a
where not exists
(select order_id from charge_detail where order_id = a.order_id)
18.19 sec
select order_id
from made_order
where order_id not in (select order_id from charge_detail)
20.05 sec
nullslast(first)
排序，遇空排在前（后）
select * from emp order by comm desc nulls last
Over
select ename,max(sal) over() from emp;
rownum和row_number()
row_number() over (partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而row_number()在包含排序从句后是先排序再计算行号码。
select row_number() over (order by ename) rn,ename from emp ;
select rownum,ename from emp order by ename;
Partition by
select row_number() over(partition by job order by sal) row_number,
rank() over(partition by job order by sal) rank,
dense_rank() over(partition by job order by sal) dense_rank,
count(1) over(partition by job order by sal) count,
max(empno) over(partition by job order by sal) max,
sum(sal) over(partition by job order by sal) sum,
lag(ename) over(partition by job order by sal) lag,
lead(ename) over(partition by job order by sal) lead,
sum(sal) over(order by sal range between unbounded preceding and current row) range_unbound_sum,
sum(sal) over(order by sal rows between unbounded preceding and current row) rows_unbound_sum,
sum(sal) over(order by sal range between 1 preceding and 2 following) range_sum,
sum(sal) over(order by sal rows between 1 preceding and 2 following) rows_sum,
sal, mgr, job, empno, ename
from emp
注意： rank、dense_rank的区别，count的变化。
Dump
DUMP(expr[,return_fmt[,start_position][,length]])
基本参数时4个，最少可以填的参数是0个。当完全没有参数时，直接返回null。另外3个参数也都有各自的默认值：
expr：这个参数是要进行分析的表达式（数字或字符串等，可以是各个类型的值）
return_fmt：指返回参数的格式，有5种用法：
1）8：以8进制返回结果的值
2）10：以10进制返回结果的值（默认）
3）16：以16进制返回结果的值
4）17：以单字符的形式返回结果的值
5）1000：以上4种加上1000，表示在返回值中加上当前字符集
start_position：开始进行返回的字符位置
length：需要返回的字符长度
select dump('abc') from dual;
Type
typ表示当前的expr值的类型。如：2表示NUMBER，96表示CHAR。
CODE TYP
----- ------------------------------
1 VARCHAR2
2 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
112 CLOB
113 BLOB
114 BFILE
180 TIMESTAMP
181 TIMESTAMP WITH TIMEZONE
182 INTERVAL YEAR TO MONTH
183 INTERVAL DAY TO SECOND
208 UROWID
231 TIMESTAMP WITH LOCAL TIMEZONE
len
len表示该值所占用的字节数。
对于汉字，ZHS16GBK编码一个汉字需要2个字节，UTF8需要3个字节。
SQL> select dump('多多',1010) from dual;
DUMP('多多',1010)
-------------------------------------------------------
Typ=96 Len=6 CharacterSet=UTF8: 229,164,154,229,164,154
SQL> select dump('多多',1010) from dual;
DUMP('多多',1010)
---------------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: 182,224,182,224
Value
具体的存储值。返回的数值就是Oracle在自己内部对前面的这个expr值得存储形式。对于非汉字的普通字符串，可以理解为就是它的ASCII码。
Reverse
oracle 提供一个reverse函数,可以实现将一个对象反向转换.
比如:
SQL> select reverse('123456') from dual;
REVERSE('123456')
-----------------
654321
由于这个函数,是针对数据库内部存储的对象编码进行反转的,
因此,在比如:数字,中文等.因为实现存储的并不是直接编码后的结果,而是经过某种内部转换后实现的,因此,在转换完成后,并不一定是自己想要的结果.示例:
SQL> select reverse(123456) ,reverse('中华人民共和国') from dual;
REVERSE(123456) REVERSE('中华人民共和国')
--------------- -------------------------
-668706000000 ?秃补衩巳?兄
在这个地方,一个整数,一个中文内容,就被转换为其它的内容了.
我们dump看看,就明白oracle是如何reverse了
SQL> select reverse(123456),dump(123456),dump(reverse(123456)) from dual;
REVERSE(123456) DUMP(123456) DUMP(REVERSE(123456))
--------------- ------------------------- -------------------------
-668706000000 Typ=2 Len=4: 195,13,35,57 Typ=2 Len=4: 57,35,13,195
九九乘法表
Oracle相关函数总结：
select replace(reverse(sys_connect_by_path(reverse(rownum || '*' || lv || '=' ||
rpad(rownum * lv, 2)),
'/ ')),
'/')
from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv;
Group by
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。
Grouping sets(A,B)表示先对A普通分组，再对B普通分组，然后合并在一起。
Rollup
汇总数据到行尾。
select job,sum(sal) from emp group by rollup(job)
select deptno,job,sum(sal) from emp group by rollup(deptno,job)
Grouping
select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job)
如果显示"1"表示GROUPING函数对应的列（例如JOB字段）是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。
如果显示"0"表示此行对应的这列参未与ROLLUP函数分组汇总活动。
CUBE
select deptno,job,grouping(job),sum(sal) from emp group by cube(deptno,job);
groupingsets
select deptno,job,grouping(job),sum(sal) from emp group by grouping sets(deptno,job);
select deptno,job,grouping(job),sum(sal) from emp group by grouping sets((deptno,job),deptno);
Model
select deptno, job,ename, sal
from emp model return updated rows
partition by(deptno)
dimension by(job,ename)
measures(sal)
--rules(sal [ 'ANALYST','JAMES'] = 1)
rules(sal [ job in('ANALYST','CLERK'),length(ename)>1] = 1)
详细见：http://blog.csdn.net/huangyunzeng2008/article/details/5664172
Numtoyminterval和numtodsinterval
计算时间间隔的函数。
Numtoyminterval常用的单位有'year','month'
numtodsinterval常用的单位有 ('day','hour','minute','second')
select sysdate,
sysdate + numtoyminterval(3, 'month'),
sysdate + numtodsinterval(3, 'second') as res
from dual;
skip locked
在11gR1中，oracle增加了skip locked子句。此时，如果探测到了记录已经被锁，就不会进入等待队列或者抛出ORA-00054错误，而是跳过该条记录继续执行。
Select owner, table_name from t_test2 where owner='OUTLN' for update skip locked;
--first、last
select max(ename) keep(dense_rank first order by nvl(sal,0) nulls first) from emp a;
select max(ename) keep(dense_rank first order by nvl(sal,0) nulls first) over(partition by deptno),a.* from emp a;
--first_value、last_value
select first_value(ename) over(order by sal),a.* from emp a where deptno=10;
