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);

[img]http://dl2.iteye.com/upload/attachment/0096/6975/94971158-273e-3c25-8d53-b0cda1560719.png[/img]


二、insert into select

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

[img]http://dl2.iteye.com/upload/attachment/0096/6979/1a35062a-b0b9-3e0d-82cf-2598501b2e94.png[/img]


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

[img]http://dl2.iteye.com/upload/attachment/0096/6981/159f5cd6-ba09-32c2-8e7e-ee1d22b0894c.png[/img]

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));

[img]http://dl2.iteye.com/upload/attachment/0096/6983/ca4ecf83-69e3-31b2-9042-63039a0f91a7.png[/img]

三、insert into with

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

[img]http://dl2.iteye.com/upload/attachment/0096/6979/1a35062a-b0b9-3e0d-82cf-2598501b2e94.png[/img]


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

[img]http://dl2.iteye.com/upload/attachment/0096/6981/159f5cd6-ba09-32c2-8e7e-ee1d22b0894c.png[/img]

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;

[img]http://dl2.iteye.com/upload/attachment/0096/6985/49e6d71f-0f3a-3299-ae7a-2d0583ffdee5.png[/img]

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