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

/* 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