Oracle常用的SQL方法总结

共计 1945 个字符，预计需要花费 5 分钟才能阅读完成。

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

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

merge into A
using B
on (A.key = B.key)
when matched then
  update set A.name = B.name
when 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_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
 
  (2). 查看引起死锁的会话
  select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where 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;

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/136193.htm