oracle之事务和truncate删除

--在事务中使用保存点，保存点只在事务之中有效
--事务结束后，所有保存点失效
create table dept_bak 
as 
select * from dept;

insert into dept_bak values(50, '开发', 1114);
savepoint a;
delete from dept_bak d where d.deptno>30;
savepoint b; --b点的状态是10,20,30
update dept_bak d set d.dname='人事部' where d.deptno = 10;
rollback to b;
commit;
rollback to a;

select * from dept_bak;


create table test1(
       t_id number primary key
);

insert into test1 values(1);
insert into test1 values(2);
insert into test1 values(3);
insert into test1 values(4);
insert into test1 values(5);
savepoint a;
insert into test1 values(5);
insert into test1 values(6);
rollback to a;
commit;

--用PL/SQL
declare

begin
  insert into test1 values(1);
  insert into test1 values(2);
  insert into test1 values(3);
  insert into test1 values(4);
  insert into test1 values(5);
  savepoint a;
  insert into test1 values(5);
  insert into test1 values(6);
exception --捕获异常
  when others then
    rollback to a;
    commit;
end;

select * from test1;




--使用子查询创建表
create table emp_2 
as
select * from emp;

select * from emp_2;

--使用delete语句删除   删除表中的所有内容
delete from emp_2;  --可以回滚


--使用truncate语句删除
truncate table emp_2;    --不可回滚   效率高  需慎用


--使用drop删除表
drop table emp_2;