Summary of common SQL methods for Oracle

Some of the data is typically processed in the project, and some basic SQL statements are provided below:

1. Conditional insertion and modification: you need to insert a record in the table, judging by key identification before inserting. If the identifier does not exist, a new record is inserted, and if the identifier exists, the field in the original record is updated according to the value given in the statement:

Merge into ausing Bon (A.key = b.key) While matched then update set a.name = B.namewhen not matched then insert into (a.ke Y, A.name) VALUES (B.key, B.name)

2. Statistics grouping in groups and collections: when it is necessary to count the members in a group, or how many sub-groups, and other collection-based statistics. When you are doing collection-based statistics, you also need to dynamically include or exclude records that meet certain criteria.

Examples are as follows: based on the number of employees in the enterprise promotion, to count the number of jobs each employee has experienced.

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. Recover data based on time stamp:

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. There is a field of type string whose value is migrated to the value Type field in the new system:

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) retur n ' Y '; exception when Val_err then return ' N '; end;

5. Resolve the deadlock problem:

(1). View Locked Table 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). View the session that caused the deadlock 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). Kill the deadlock alter system kill session ' sid,serial# ';

6. View the SQL statement that the current connection user is running:

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. Query for real-time statements that consume the most resources:

In Oracle 11g, query V$sql_monitor can be used to monitor SQL queries near real-time consumption of resources select * FROM (select A.sid session_id,a.sql_id,a.status,a.cpu_tim e/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;

This article is from the "Pengze 0902" blog, be sure to keep this source http://pengze0902.blog.51cto.com/7693836/1863181

Summary of common SQL methods for Oracle