欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【PL/SQL】 ROLLBACK WHEN EXECUTE A PROCEDURE Analysis & Output | oracle数据库: 回滚案例的输出与分析

程序员文章站 2022-07-04 23:34:56
...
/* procedure practice */
--display original table 
select * from regions;

--ceshi procedure
create or replace procedure add_ceshi(
    p_id regions.region_id%type,
    p_name regions.region_name%type) IS

begin
    insert into regions
    values (p_id, p_name);  
end add_ceshi;
/

drop sequence s_regions;
create sequence s_regions
    start with 12
    increment by 1
    cache 2;
    
execute add_ceshi (11, 'zhao');
select * from regions;

rollback;
/
select * from regions;

execute add_ceshi (s_regions.nextval, 'wei');
select * from regions;

rollback;
/
select * from regions;

execute add_ceshi (s_regions.nextval, 'qi');    
execute add_ceshi (s_regions.nextval, 'yan');
execute add_ceshi (s_regions.nextval, 'han');

--display table after add-ceshi
select * from regions;

select * from regions;

--manual delete
delete from regions
where region_id > 9;

select * from regions;

OUTPUT:

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   

Procedure ADD_CESHI compiled

Sequence S_REGIONS dropped.

Sequence S_REGIONS created.

PL/SQL procedure successfully completed.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   
    11 zhao                     

Rollback complete.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   

PL/SQL procedure successfully completed.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   
    12 wei                      

Rollback complete.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   
    13 qi                       
    14 yan                      
    15 han                      

7 rows selected.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   
    13 qi                       
    14 yan                      
    15 han                      

7 rows selected.

3 rows deleted.

REGION_ID REGION_NAME


     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa