OracleSQL精妙SQL语句讲解_精妙sql语句收集

OracleSQL精妙SQL语句讲解由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“精妙sql语句收集”。

Oracle: SQL精妙SQL语句讲解

一、重复操作查询

--where条件得distinct systemdicid作为唯一标识 select *

from dmis_zd_systemdic t WHERE typeid = '06012'

and t.systemdicid in(select min(systemdicid)from dmis_zd_systemdic where typeid = '06012'

group by name)order by orderno;

二、检查表是否存在select count(tname)from tab where tname = upper('表名');

三、日期函数

--返回当前日期年度的第一天

select trunc(sysdate,'year')from dual;--返回当前日期月份的第一天

select trunc(sysdate,'month')from dual;--上月最后一天

select last_day(add_months(sysdate,-1))from dual;--给定日期后最近星期几得日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一')next_day from dual;

四、同一张表中，根据一个字段更新另一个字段

update(select t.fgenerationtime as ftime, t.fgeneratedateall as str from dmis_fs_approvebook t where t.fgenerationtime is not null)set str = TO_CHAR(ftime, 'yyyy-mm-dd')where str is null;

五、重复数据查询 select * FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的数据（merge into）merge into student s using(select id, name, tel from test001)x on(s.s_id = x.id)when matched then

update set s_name = x.name when not matched then

insert(s_id, s_name, s_age)values(x.id, x.name, x.tel);commit;

七、查询执行sql（v$sql）

select t.module, t.first_load_time, t.sql_text from v$sql t order by first_load_time desc;

2、数据库精度修改处理--Create table /*drop table temp_data;*/ create table temp_data(FID VARCHAR2(40)not null, USEHOURS NUMBER(10)default 0, FVOLTAGE NUMBER(10)default 0, INVOLTAGE NUMBER(10)default 0);alter table TEMP_DATA add constraint tempfid primary key(FID);

insert into temp_data select a.fid, a.usehours, a.fvoltage, a.involtage from dmis_fs_factorymonthdetail a;

update dmis_fs_factorymonthdetail t set t.usehours = '', t.fvoltage = '', t.involtage = '';

alter table DMIS_FS_FACTORYMONTHDETAIL modify USEHOURS NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify FVOLTAGE NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify INVOLTAGE NUMBER(10,1);

update(select a.usehours as tusehours, b.usehours as fusehours, a.fvoltage as tfvoltage, b.fvoltage as ffvoltage, a.involtage as tinvoltage, b.involtage as finvoltage, a.fid as ffid, b.fid as tfid from dmis_fs_factorymonthdetail a, temp_data b where a.fid = b.fid)tt set tt.tusehours = tt.fusehours, tt.tfvoltage = tt.ffvoltage, tt.tinvoltage = tt.finvoltage where ffid = tfid;drop table temp_data;commit;

3、恢复drop掉的存储过程 用sys用户登陆，执行如下的查询：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD

HH24:MI:SS')

where

owner='IPRA'

and

name=

'P_IPACCHECK_NC' order by line;

4、删除某个用户下的对象--删除某个用户下的对象 set heading off;set feedback off;spool c:dropobj.sql;prompt--Drop constraint select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';prompt--Drop tables select 'drop table '||table_name ||';' from user_tables;

prompt--Drop view select 'drop view ' ||view_name||';' from user_views;

prompt--Drop sequence select

--行列转换 行转列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0))q1, SUM(decode(a.q, 2, a.bal, 0))q2, SUM(decode(a.q, 3, a.bal, 0))q3, SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn FROM t_change_cl a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM

--行列转换 行转列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code, a.q, row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn FROM t_change_lc_comma a)t1 START WITH t1.rn = 1

CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code, substr(t.q, instr(';' || t.q, ';', 1, rn), instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM 0)t ORDER BY 1, 2;

--实现一条记录根据条件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM

WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE

INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));SELECT * FROM t_mg;MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/删除重复记录 DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);SELECT b.code, b.NAME FROM(SELECT a.code, a.NAME, row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn FROM t_dup a)b WHERE b.rn > 1;--IN/EXISTS的不同适用环境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1 FROM t_orders b WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10 AND EXISTS

(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS

SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;SELECT * FROM t_loop;--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;COMMIT;END LOOP;END;

--模拟批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;v_count := v_count + 1;IF v_count >= 100 THEN COMMIT;END IF;END LOOP;COMMIT;END;

--真正的批量提交 DECLARE CURSOR cur IS

SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP FETCH cur BULK COLLECT INTO recs LIMIT 100;

--forall 实现批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

--悲观锁定/乐观锁定 DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;SELECT * FROM t_lock;

--常见的实现逻辑，隐含bug DECLARE v_cnt NUMBER;BEGIN

--这里有并发性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

--高并发环境下，安全的实现逻辑 DECLARE v_cnt NUMBER;BEGIN

--对指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有lock的情况下继续下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且释放lock END;

--硬解析/软解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);SELECT * FROM t_hard;DECLARE

sql_1 VARCHAR2(200);BEGIN

--hard parse--java中的同等语句是 Statement.execute()FOR i IN 1..1000 LOOP sql_1 := 'insert into t_hard(id)values(' || i || ')';EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;--soft parse--java中的同等语句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP EXECUTE IMMEDIATE sql_1 USING i;END LOOP;COMMIT;END;

--正确的分页算法 SELECT *

FROM(SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM 480;

--分页算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM 480;

--分页算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM 480;--OLAP

--小计合计 SELECT CASE

WHEN a.deptno IS NULL THEN '合计'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN '小计' ELSE

'' || a.deptno END deptno, a.empno, a.ename, SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分组排序 SELECT a.deptno, a.empno, a.ename, a.sal,--可跳跃的rank rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1,--密集型rank dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2,--不分组排序

rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--当前行数据和前/后n行的数据比较 SELECT a.empno, a.ename, a.sal,--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1,--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;