Oracle优化查询改写（第四章-插入，更新与删除）-优快云博客

本文介绍了使用SQL进行数据插入的各种技巧，包括如何插入新记录、限制数据插入条件、复制表结构及数据、利用WITH CHECK OPTION进行约束以及多表插入等高级操作。

2019独角兽企业重金招聘Python工程师标准>>>

4.1插入新记录

4.2阻止对某几列插入

4.3复制表的定义及数据

4.4用WITH CHECK OPTION限制数据录入

alter table emp add constraints ch_sal check(sal>0);
    alter table emp add constraints ch_hiredate check(hiredate >= sysdate);
    insert into (select empno,ename,hiredate from emp where hiredate <= SYSDATE with check option)
    values (9999,'test',sysdate-1)

4.5多表插入语句

 create  table emp1 as select empno,ename,job from emp where 1=2;
    create table emp2 as select empno,ename,deptno from emp 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);
    
    
    delete emp1;
    delete emp2;
    
    select * from emp1;
    select * from emp2;
  
    
    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;           
    
     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;

转载于:https://my.oschina.net/projerry/blog/1585930