您现在的位置： 网页制作教程网 >> 数据库教程 >> oracle 教程 >> 文章正文

-- 实现一条记录根据条件多表插入
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') ;

-- 不建议使用

上一页  [1] [2] [3] [4] 下一页