oracle insert 语句

基于oracle数据库中自带的scott表emp

一、最基本用法

1、列出表中的所有字段及每个字段对应的值
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7333,'GEJUN','CLERK',7999,to_date('1980/12/17','yyyy-mm-dd'),900,200,10);

2、按表中字段顺序给出字段值
insert into emp values(7333,'GEJUN','CLERK',7999,to_date('1980/12/17','yyyy-mm-dd'),900,200,10);




二、insert into select

我的测试步骤是：
1.删除表中部门编号是7369的员工
delete emp where empno=7369;




2.查询出删除的数据
select * from emp as of timpstamp sysdate-1/12
where not exists(select 1 from emp e where emp.empno=e.empno);



3.将删除的数据再次插入到表中
insert into emp(
select * from emp as of timpstamp sysdate-1/12
where not exists(select 1 from emp e where emp.empno=e.empno));



三、insert into with

我的测试步骤是：
1.删除表中部门编号是7369的员工
delete emp where empno=7369;




2.查询出删除的数据
select * from emp as of timpstamp sysdate-1/12
where not exists(select 1 from emp e where emp.empno=e.empno);



3.将删除的数据再次插入到表中
insert into emp
with a as (select * from emp as of timestamp sysdate-1/12 where not exists(select 1 from emp e where emp.empno=e.empno))
select * from a;



值得注意的是第二和第三种方案的比较，insert into select 时要目标表后紧跟括号，而用with as（临时表）时，insert into emp后是不带括号的。（另外提一点，with as语句后要紧跟select 语句。）