PL/SQL】DELETE IN PROCEDURE Analysis & Example | oracle数据库: 用procedure删除数据的案例分析

/* procedure practice */
--display original table 
select * from regions;
drop sequence s_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;
/

create sequence s_regions
    start with 12
    increment by 1
    cache 2;

execute add_ceshi (s_regions.nextval, 'wei');
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;

create or replace procedure de_ceshi(
    p_id regions.region_id%type) IS
begin
    delete from regions
    where regions.region_id > p_id;
end de_ceshi;
/


--test 0/ negative value
/*
execute de_ceshi(-2);
select * from regions;
*/

execute de_ceshi(9);
select * from regions;

OUTPUT

REGION_ID REGION_NAME

---

     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa   

Sequence S_REGIONS dropped.

Procedure ADD_CESHI compiled

Sequence S_REGIONS created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

REGION_ID REGION_NAME

---

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

8 rows selected.

Procedure DE_CESHI compiled

PL/SQL procedure successfully completed.

REGION_ID REGION_NAME

---

     1 Europe                   
     2 Americas                 
     3 Asia                     
     4 Middle East and Africa