你的位置:首页 > 数据库

[数据库]Oracle常用的SQL方法总结


   在项目中一般需要对一些数据进行处理,以下提供一些基本的SQL语句:   

1.基于条件的插入和修改:需要在表中插入一条记录,插入前根据key标识判断。如果标识符不存在,则插入新纪录,如果标识符存在,则根据语句中所给的新值对原纪录中的字段进行更新:

merge into Ausing Bon (A.key = B.key)when matched then update set A.name = B.namewhen not matched then insert into (A.key, A.name) values (B.key, B.name)

2.在分组和集合里统计分组:当需要统计一个分组里的成员,或有多少个子分组,及其他基于集合的统计。在进行基于集合的统计时,还需要动态包括或剔除满足特定条件的记录。

实例如下:基于职工在企业里的升职次数,来统计企业每个员工经历过的工作岗位次数。

select jh.JobsHeld, count(*) as StaffCount   from (select u.employee_id, count(*) as JobsHeld       from (select employee_id           from hr.employees          union all          select employee_id from hr.job_history) u       group by u.employee_id) jh   group by jh.JogsHeld

3.根据时间戳恢复数据:

ALTER TABLE tablename ENABLE row movement ;flashback table tablename to timestamp to_timestamp('2012-09-13 13:00:00','yyyy-mm-dd hh24:mi:ss');

4.有一个类型为字符串的字段,要讲其值迁移到新系统里的数值类型字段中:

create or replace function isnum(v_in varchar2) return varchar is val_err exception; pragma exception_init(val_err, -6502) --char to num conv.error scrub_num number;begin scrub_num := to_number(v_in) return 'Y';exception when val_err then  return 'N';end;

5.解决死锁问题:

(1).查看被锁的表 select b.owner,b.object_name,a.session_id,a.locked_modefrom v$locked_object a,dba_objects bwhere b.object_id = a.object_id;  (2).查看引起死锁的会话  select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session bwhere a.session_id = b.sid order by b.logon_time; (3).杀掉死锁alter system kill session ‘sid,serial#’; 

6.查看当前连接用户正在运行的SQL语句:

select a.SID, a.USERNAME, b.SQL_TEXT from v$session a, v$sqltext_with_newlines b where a.SQL_ADDRESS = b.ADDRESS  and a.SQL_HASH_VALUE = b.HASH_VALUE order by a.USERNAME, a.SID, b.piece

7.查询最消耗资源的实时语句:

oracle 11g中,可以通过查询V$SQL_MONITOR来监控SQL查询近乎实时的消耗资源  select * from (   select a.sid session_id,a.sql_id,a.status,a.cpu_time/1000000 cpu_sec,a.buffer_gets,a.disk_reads,b.sql_text sql_text   from v$sql_monitor a,v$sql_b   where a.sql_id=b.sql_id   order by a.cpu_time desc)   where rownum<=20;