Oracle常用的SQL方法總結

標籤：oracle

   在項目中一般需要對一些資料進行處理，以下提供一些基本的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;

本文出自 “彭澤0902” 部落格，請務必保留此出處http://pengze0902.blog.51cto.com/7693836/1863181

Oracle常用的SQL方法總結