oracle使用中一些技巧写法

一年的时间过去了，日志却没有更新多少，哎，还是太懒了...

言归正传，在使用oracle写sql脚本时会有一些特殊写法来实现查询。

去除对称数据

select tab.g from (
with a as ( select a.id || b.id g,a.rowid arowid,b.rowid browid 
from test_dk a,test_dk b ) 
select g,a.arowid,a.browid from a
) tab 
where tab.arowid < tab.browid
order by rownum desc

行转列，列转行

select wm_concat(v.id) id from (
  with a as ( select  '1,2,3,4,'  id from dual )
 select regexp_substr(id,'[^,]+',1,rownum) id from a
 connect by rownum <= length(regexp_replace(id,'[^,]+'))
 ) v
 

oracle 递归调用

select id,substr(sys_connect_by_path(wjjmc,','),2) p_name,
substr(sys_connect_by_path(id,','),2) p_id
from zyk_pan where ssrid ='5B54FB4E41675CC7E0501C0AC40C7CAD'
start with fjwjj is null
connect by prior id =fjwjj
order by cjsj 

oracle截取最后一位'-'后面的内容

select reverse(substr(reverse('0-000-0000sss7'),1,
instr(reverse('0-000-000sss07'),'-') - 1)) aa from dual;

触发器--操作前后的记录

create or replace trigger logTfiger
 after update on test_dk
 for each row
 begin
 insert into TEST_log (oldid,newid) values(:old.id,:new.id);
 end ;

锁表解锁

SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid; 

alter system kill session '15,35137';