先说一下这两个命令的用法格式:
--起一个名字为A的savepoion
savepoint A(这个A是savepoint的名字)
--跳转到savepoint A处
rollback to A
savepoint A(这个A是savepoint的名字)
--跳转到savepoint A处
rollback to A
DECLARE
v_number number;
BEGIN
v_number := 1;
insert into DEPT values(deptno_seq.nextval,v_number, '');
savepoint A;
insert into DEPT values(deptno_seq.nextval,v_number+1, '');
savepoint B;
insert into DEPT values(deptno_seq.nextval,v_number+2, '');
savepoint C;
rollback to A;
--如果这行不加,那么数据并没有写入到数据库中,只是写到了缓存中,直接查询数据库是没有插入的值的
commit;
END;
v_number number;
BEGIN
v_number := 1;
insert into DEPT values(deptno_seq.nextval,v_number, '');
savepoint A;
insert into DEPT values(deptno_seq.nextval,v_number+1, '');
savepoint B;
insert into DEPT values(deptno_seq.nextval,v_number+2, '');
savepoint C;
rollback to A;
--如果这行不加,那么数据并没有写入到数据库中,只是写到了缓存中,直接查询数据库是没有插入的值的
commit;
END;
deptno_seq.nextval
这个是事先在数据中生成好的序列,代码为
CREATE SEQUENCE "SUCRE"."DEPTNO_SEQ" MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT
BY 10 START
WITH 201 CACHE 20 NOORDER NOCYCLE ;