oracle 删除 优化,Oracle优化查询改写（第四章-插入，更新与删除）

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;