常用SQL语句-Oracle篇

数据库相关的文章很有一段时间没有写了。最近得拾起来，在这里贴一些自己常用的SQL：

SQL执行，事务提交一条龙：

begin #insert、update、delete commit ; exception when others then rollback ; end ;

数据库表锁死解决：

SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; ALTER system KILL session '828,31041'; ------------------------------------------------------------------ --批量解锁（存储过程方式方式） declare cursor mycur is select b.sid,b.serial# 　　from v$locked_object a,v$session b 　　where a.session_id = b.sid group by b.sid,b.serial#; begin for cur in mycur loop execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' '); end loop; end;

重复数据查询

select column_name1,count(column_name2) from table1 group by table1所有的字段 HAVING COUNT(column_name1) >= 2;

删除重复数据

DELETE 　　FROM table_name a 　　WHERE rowid > ( SELECT min(rowid) 　　FROM table_name b 　　WHERE b.id = a.id and b.name=a.name);

递归查询

select t.* from DEMO_TREE t where t.pkid in (select c.pkid from DEMO_TREE c start with c.pkid = 'B' connect by prior c.pkid=c.fpkid)

备注：同一张表，c.pkid=c.fpkid没错

字段值替换 (批量替换某字段前俩字符)

update table_name y set y.companycode =REPLACE(chr(0)||y.companycode,chr(0)||substr(y.companycode ,0,2) ,'JR') where substr(y.companycode, 0, 2) like 'QT'