Oracle查询优化改写_4

对应的第四章”插入，更新与删除“

---

1.阻止对某几列插入

向数据库某张表【包含字段A,B,C,D】中插入数据时，这张表内的一个或多个字段【D】不允许手动录入。此时，可以建立一个不包含“D”字段的VIEW，新增数据时通过这个VIEW插入即可。

creat table TEST (A vachar2(20) default '默认1',B vachar2(20) default '默认2',C vachar2(20) default '默认3',D date default sysdate);

creat or replace view v_test as select A,B,C from test;

/*插入数据*/
insert into v_test (A,B,C) values ('我的输入',null,'别动 d');

执行这条语句，生成一条新的记录，该记录的“D”字段为默认值。

/*通过VIEW新增数据，不能使用 关键字 default*/
 insert into v_test (A,B,C) values (default, null, '别动 d');

2.复制表的定义及数据

1）复制表

    CREATE TABLE test_zyh2 AS SELECT * FROM TEST_ZYH; 

2）复制表的定义

    CREATE TABLE test_zyh2 AS SELECT * FROM TEST_ZYH  WHERE 1 = 2;

注意：复制的表不包含默认值等约束信息，使用这种方式复制表后，需要重建默认值及索引和约束等信息。

3.插入数据字段限制

1）对插入的数据，做限制

    ALTER TABLE TEST_ZYH
ADD CONSTRAINT chech_num CHECK (NUM > 5);


ALTER TABLE TEST_ZYH
ADD CONSTRAINT check_C4 CHECK (C4 > SYSDATE);

/*报错：日期或系统变量在 CHECK 约束条件中指定错误*/

2）使用 WITH CHECK OPITION限制驶入

insert into (select empno,empmame,hiredete from emp where hiredete<= SYSDATE WITH CHECK OPTION) VALUES (9999,'',SYSDATE +1)

(select empno,empmame,hiredete from emp where hiredete<= SYSDATE WITH CHECK OPTION) 被当做一个视图处理。

当规则比较复杂，可以用 2） 实现约束。

4.多表插入

1）无条件 INSERT

CREATE TABLE EMP1 AS SELECT EMPNO,ENAME,JOB FROM EMP WHERE 1=2;
CREATE TABLE EMP2 AS SELECT EMPNO,ENAME,DEPTNO FROM WHERE 1=2;

INSERT ALL
    INTO EMP1(EMPNO,ENAME,JOB) VALUES (EMPNO,ENAME,JOB)
    INTO EMP2(EMPNO,ENAME,DEPTNO) VALUES (EMPNO,ENAME,DEPTNO)
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE DEPTNO IN (10,20);

因为没有加条件，所以会同时向两张表中插入数据，且两个表中，插入的数据条数一样。

2）有条件 INSERT

INSERT ALL
    WHEN JOB IN ('SALESMAN','MANAGER') THEN 
        INTO EMP1(EMPNO,ENAME,JOB) VALUES (EMPNO,ENAME,JOB)
    WHEN DEPTNO IN ('20','30') THEN
        INTO EMP2(EMPNO,ENAME,DEPTNO) VALUES (EMPNO,ENAME,DEPTNO)
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP;

根据设置的条件，只有满足条件的数据才会插入对应的表,如果某条数据，满足多个条件，则这条数据会被插入到多张表中。

3）有条件 INSERT FIRST

INSERT FIRST
    WHEN JOB IN ('SALESMAN','MANAGER') THEN 
        INTO EMP1(EMPNO,ENAME,JOB) VALUES (EMPNO,ENAME,JOB)
    WHEN DEPTNO IN ('20','30') THEN
        INTO EMP2(EMPNO,ENAME,DEPTNO) VALUES (EMPNO,ENAME,DEPTNO)
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP;

INSERT FIRST语句中，当第一个表符合条件后，第二个表将不再插入对应的行，表emp2中不再有与emp1相同的数据，也就是INSERT FIRST 与 INSERT ALL的不同之处。

4）转置 INSERT

转置 INSERT 与其说是一个分类，不如算作“INSERT ALL”的一个用法。

CREAT TABLE T2(d VACHAR2(10),des VACHAR2(50));

CREAT TABLE T1 AS 
SELECT 
    '熊样，精神不佳' AS d1,
    '猫样，温驯听话' AS d2,
    '狗样，神气活现' AS d3,
    '鸟样，向往明天' AS d4,
    '花样，像花儿一样快乐' as d5
FROM dual;    

/* 转置 INSERT */
INSERT ALL
    INTO t2(d,des) VALUES ('周一',d1)
    INTO t2(d,des) VALUES ('周二',d2)
    INTO t2(d,des) VALUES ('周三',d3)
    INTO t2(d,des) VALUES ('周四',d4)
    INTO t2(d,des) VALUES ('周五',d5)
SELECT d1,d2,d3,d4,d5 FROM T1;

/*查询结果*/
SELECT * FROM T2;

D           DES
----------------
周一        熊样，精神不佳
周二        猫样，温驯听话 
周三        狗样，神气活现
周四        鸟样，向往明天
周五        花样，像花儿一样快乐
5 rows selected

可以看到，转置 INSERT 的实质就是把不同列的数据插入到同一表的不同行中。

转置 INSERT 的等价语句如下：

INSERT INTO T2(d,des)
    SELECT '周一',d1 FROM T1 UNION ALL
    SELECT '周二',d2 FROM T1 UNION ALL
    SELECT '周三',d3 FROM T1 UNION ALL
    SELECT '周四',d4 FROM T1 UNION ALL
    SELECT '周五',d5 FROM T1 ;

5.合并记录【MERGE INTO】

MERGE INTO 语法
MERGE INTO table_name A 
USING (table|view|query_sql) B
ON (A.col = B.col) 
WHEN MATCHED THEN 
    UPDATE SET A.col1 = B.col_val1 [where 条件1]
    /* where 条件只出现一次，这里添加where条件1，那么 delete 后面的where条件2就无效*/
    [DELETE] [where 条件2]
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values) [ where 条件3]
    /* 这里是 A表中不存在对应的数据时，才会新增*/

注意：
    1）只能更改 A，不可以更新B。
    2）更新，删除，插入这三个操作同时进行，不分先后。
    3）where 条件只出现一次，这里添加where条件，那么 delete 后面的where就无效。此时 update的范围是：(A.col = B.col)减去范围[where 条件2]

6.删除名称重复的记录

create table dupes (id integer,name vachar(10));
insert into dupes values(1,'ZYH');
insert into dupes values(2,'ZYH');
insert into dupes values(3,'YYR');
insert into dupes values(4,'YYR');
insert into dupes values(5,'YYR');
insert into dupes values(6,'YNN');
insert into dupes values(7,'YNN');

1）通过name相同。id不同的方式来判断。

delete from dupes a 
    where exists (select null from dupes b where b.name = a.name and b.id > a.id) 

/* 利用这种方式删除数据时，需要建组合索引 */

create index idx_name_id on dupes(name,id);

2）用 ROWID 来代替其中的 id。

delete from dupes a
    where exists (select /*+ hash_sj */ null from dupes b where b.name = a.name and b.rowid > a.rowid);

/* 因为不需要关联id列，只需要建立单列索引*/

create  index idx_name on dupes(name);

3）通过分析函数根据 name 分组生成序号，然后删除序号大于1的数据。

delete from dupes where rowid in 
    (
    select rid from
        (
        select rowid as rid,ROW_NUMBER() OVER(PARTITION BY NAME order by id) as seq from dupes
        )
    where  seq > 1  
    );