Shulou(Shulou.com)06/01 Report--

Some data needs to be processed in a project. Here are some basic SQL statements:

1. Condition-based insertion and modification: a record needs to be inserted into the table and judged according to the key logo before insertion. If the identifier does not exist, a new record is inserted, and if the identifier exists, the fields in the original record are updated according to the new value given in the statement:

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)

two。 Statistical grouping in groups and sets: when you need to count the members of a group, or how many subgroups there are, and other set-based statistics. When carrying out set-based statistics, it is also necessary to dynamically include or eliminate records that meet specific conditions.

Examples are as follows: based on the number of promotions of employees in the enterprise, statistics of the number of jobs experienced by each employee in the enterprise.

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 timestamp:

ALTER TABLE tablename ENABLE row movement; flashback table tablename to timestamp to_timestamp ('2012-09-13 13-13-12-12-12-12-12-12-13-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-14-12-12-12-12-12-14-12-12-12-12-12-12-12-12-12-12-12-14-14-14-12-2012-09-13 13-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12

4. There is a field of type string whose value is migrated to the numeric 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) return'Yeshift exception when val_err then return'Nabilityend

5. Resolve deadlock issues:

(1)。 Look at the locked table select b. Ownerbook b. Objectkeeper name. Session codes. Lockedcards mode from v$locked_object a dbathing objects bwhere b.object_id = a. Objectboxes. Look at the session that caused the deadlock, select b. Username from v$locked_object b. Sidret b. Serializedtheconverting session bwhere a.session_id = b.sid order by b.logoncake time.3). Kill the deadlock alter system kill session 'sid,serial#'

6. View the SQL statements that the currently connected 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 the real-time statements that consume the most resources:

In oracle 11g, you can use query V$SQL_MONITOR to monitor SQL query near real-time consumption resource 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-dint vainsqlconsumb where a.sql_id=b.sql_id order by a.cpu_time desc) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.