小残曦

love曦

小残曦

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';

 

 

评论回复

  1. 回复 野兔

    失踪人口回归

4 + 6 =

回到顶部