【PL/SQL】 UPDATE IN PROCEDURE Analysis & Example | oracle数据库: 用procedure修改数据的案例分析

/* 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');
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;

---------------------------------------------
--update procedure practice
create or replace procedure up_ceshi(
    p_id regions.region_id%type,
    p_name regions.region_name%type) IS
    
    v_id regions.region_id%type;
    v_name regions.region_name%type;

begin
/* meaning less block
    select *
    into v_id, v_name
    from regions
    where region_id = p_id;
*/
    update regions
    set region_name = p_name
    where region_id = p_id;
    
end up_ceshi;
/

execute up_ceshi(11, 'zhao-zhan');
select * from regions;

--reset zhao-zhan to zhao
rollback;
/
select * from regions;

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