case 1 在执行update insert delete将commit写在语句之后。
SELECT COUNT(1) FROM T;
COUNT(1)
--------------
299
---创建存储过程
create or replace procedure pppp
as
begin
for i in 123..190000 loop
insert into t
select * from t;
commit;
end loop;
end;
--调用存储过程
call pppp();
执行一段过程之后,取消,查询t表结果如下:
SQL> select count(1) from system.t;
COUNT(1)
----------
19595264
case 2 在执行update insert delete将commit写最后。可以对执行的过程进行终止。
SELECT COUNT(1) FROM T;
COUNT(1)
----------
299
---创建存储过程
create or replace procedure pppp
as
begin
for i in 123..190000 loop
insert into t
select * from t;
commit;
end loop;
end;
--调用存储过程
call pppp();
执行一段过程之后,取消,查询t表结果如下:
SQL> select count(1) from system.t;
COUNT(1)
----------
299
----总结
1 如果对于比较大的存储过程,想要在中途停止,则启动的dml操作的commit;必须要放在最后。(对于未提交的insert操作只有同session能够查询以及其他dml操作能操作到)
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system
SQL> insert into t select * from t;
299 rows inserted
SQL> select count(1) From t;
COUNT(1)
----------
598
SQL> update t set object_name='123';
598 rows updated
SQL> select distinct object_name from t;
OBJECT_NAME
--------------------------------------------------------------------------------
123
SQL> delete from t;
598 rows deleted
SQL> select count(1) from t;
COUNT(1)
----------
0
SQL>