oracle更新（insert delete update truncate）

oracle更新（insert delete update）

①insert

create table people(
       id number primary key,
       name varchar2(10) not null,
       status varchar2(3)
);

create sequence people_seq minvalue 1 maxvalue 99999 start with 1 increment by 1;

--向people表插入数据
insert into people values(people_seq.nextval,'张三','ACT');
insert into people values(people_seq.nextval,'李四','ACT');

select * from people;
--使用子查询向people表插入数据
insert into people select employee_id,employee_name,status from t_employee where employee_id>2; 

alter table people modify status default 'ACT'

insert into people(id,name,status) values(people_seq.nextval,'测试默认',default);

②delete(oracle允许delete时省略from关键字)

delete people where id=11;

③update

④truncate 截断数据
特点：
1.truncate 属于DDL范畴，delete 属于DML范畴
2.truncate是删除表中所有的数据，delete可以根据条件删除一条或者多条记录
3.truncate截断后不能rollback，delete后可以回滚
适用场景：清空表中测试数据时。

--示例
create table people_emp as select * from people;
select * from people_emp;
truncate table people;

数据提交与回滚

回滚：数据库将DML执行的语句存储在回滚段中

1.执行DML操作后，手动rollback，回滚事务
2.事务执行失败后，自动rollback
注意：commit后，DML操作无法再回滚。

提交

1.手动commit
2.

select * from people;
insert into people values(people_seq.nextval,'测试回滚',default);