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 <=
4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE
*
100) bal FROM dual CONNECT
BY ROWNUM <=
4;
SELECT *
FROM t_change_lc;
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;
--行列转换 列转行
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 <=
4) b) t
ORDER BY
1, 2;
--行列转换 行转列 合并
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.rn -
1
= PRIOR t1.rn
GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma
AS
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.rn -
1
= 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 <=
100) b
WHERE instr(';'
|| a.q, ';',
1, rn)
> 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<=5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1(flag
VARCHAR2(10) , c
VARCHAR2(10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2(flag
VARCHAR2(10) , c
VARCHAR2(10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3(flag
VARCHAR2(10) , c
VARCHAR2(10));
SELECT *
FROM t_ia_src;
SELECT *
FROM t_ia_dest_1;
SELECT *
FROM t_ia_dest_2;
SELECT *
FROM t_ia_dest_3;
INSERT ALL
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<=10;
INSERT INTO t_dup
SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)
NAME FROM dual CONNECT
BY ROWNUM<=2;
SELECT *
FROM t_dup;
SELECT *
FROM t_dup a WHERE a.ROWID
<> (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 <=10;
CREATE INDEX idx_nonfbi
ON t_fbi(dt);
DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1
ON t_fbi(trunc(dt));
SELECT *
FROM t_fbi WHERE trunc(dt)
= to_date('2006-09-21','yyyy-mm-dd') ;
-- 不建议使用
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 <=
500)
WHERE rn >
480 ;
-- 分页算法(why not this one)
SELECT a.*, ROWNUM rn
FROM (SELECT
* FROM t_employees
ORDER BY first_name) a
WHERE ROWNUM <=
500
AND ROWNUM >
480;
-- 分页算法(why not this one)
SELECT b.*
FROM (SELECT a.*, ROWNUM rn
FROM t_employees a
WHERE ROWNUM <
=
500
ORDER BY first_name) b
WHERE b.rn >
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;