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;