Oracle SQL 查询优化.Part4

1. 复制表结构但不新增数据：

-- 复制表结构但不插入数据
create table emp_new as select * from emp where 1 = 2;
select * from emp_new;

2. 利用 with check option，配合视图，可以为插入数据操作做一些限制：

-- with check optiom 限制数据的插入
insert into (select empno, deptno, empname, empsalary, empdesc
               from emp
              where emp.deptno <> 'dept02'-- with check option
            )
values('emp008', 'dept02', 'Ross', 7000, '对行业发展趋势有较强的洞察力，有统筹全局能力');

执行报错：ORA-01402:视图 WITH CHECK OPTIDN违反 where子句

3. 多表插入：

这里讲三种多表插入：a. 无条件 insert；b. 有条件 insert；c. insert first。

• 无条件 insert all

先将 emp 和 emp_bak 清空，再执行下边 sql：

-- 无条件 insert all
insert all
into emp_new1
into emp_new2
select * from emp;

此语是将 emp 的数据同时插入到 emp_new1、emp_new2 表里，执行 select * from emp_new1 的结果如下（emp_new2 的数据集也是如此）：

• 有条件 insert all
  

-- 有条件 insert all
insert all
when empsalary < 5000 
  then into emp_new1
when empsalary > 3000 
  then into emp_new2
select * from emp;

执行上边 sql 后，工资（empsalary）小于 5000 的员工信息插入 emp_new1，工资（empsalary）大于 3000 的员工信息插入 dept_new2。其中 empsalary 为 4000 的同时插入了 emp_new1 和 emp_new2，有时候需要插入指定的表，这个在下边讲：

• insert first

insert first  如果前边有条件符合，后边的表就不会插入对应的行：

-- insert first
insert first
when empsalary < 5000 
  then into emp_new1
when empsalary > 3000 
  then into emp_new2
when empsalary > 3000 
  then into emp_new3
select * from emp;

emp_new2 不会插入 empsalary 为 4000 的这条记录，emp_new3 没有记录插入。

二、更新 update 操作：

1. 利用 select 子查询进行 update，需要注意避免全表更新：

数据准备，先将 emp_new1 中所有记录的 empdesc 置为 “未填写”

现在准备根据 emp_new1 表中的记录去更新 emp 表中 empno 对应记录的 empdesc，好多人会写成如下 sql：

-- 利用 select 进行 update 进行了全表更新
update emp
   set emp.empdesc = 
       (select empdesc
          from emp_new1
         where emp.empno = emp_new1.empno
       );

上边的结果说明，这个 update 操作的 sql 进行了全表扫描。对 empno 没有匹配到的记录，均被更新为 null。事实上应该加上 where 语句才是正确的：

update emp
   set emp.empdesc = 
       (select empdesc
          from emp_new1
         where emp.empno = emp_new1.empno
       )
 where exists (select 1
          from emp_new1
         where emp.empno = emp_new1.empno
       )

2. merge into 语句：

针对上边的语句，可以用 merge into 语句

-- merge into 的实现方法
merge into emp
using (select * 
         from emp_new1
      ) e
   on (e.empno = emp.empno)
when matched then
  update set emp.empdesc = e.empdesc;

运行结果和 1 中结果一样，如果做判断，推荐使用 merge into 方法。因为 merge into 只访问一次 emp_new1。

三、删除 delete 操作

1. 删除重复记录：

方法有很多种，这里只说一种，利用分析函数分组，判断 分组序号是否大于 1.

delete
  from emp
 where rowid in (select rid 
                   from (select rowid as rid,
                                row_number() over(partition by empsalary order by empno asc) as seq
                           from emp
                         )
                   where seq > 1
                 );

但是，如果需要删除完全相同的两条数据中的一条，需要在 partition by 后边加上所有列名，否则删除哪条数据并不确定，删掉记录是和 order by 语句相关的。

Oracle SQL 查询优化.Part4

标签：