oracle使用中一些技巧写法

技术·学习 · 2018-01-17 · 89 人浏览

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

言归正传,在使用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';
oracle sql
Theme Jasmine by Kent Liao