【PL/SQL】 INSERT IN PROCEDURE Analysis & Example | oracle数据库: 用procedure插入数据的案例分析

/* procedure practice */
--display original table 
select * from regions;

--test sequence before declare the procedure
drop sequence s_regions;
create sequence s_regions
    start with 12
    increment by 1
    cache 2;

--ceshi procedure
create or replace procedure ceshi(
    p_id regions.region_id%type,
    p_name regions.region_name%type) IS
    
    --temp to store the value from sequence 1
    --not working
    tem number;
--ceshia
begin
    insert into regions 
    values (10, 'qin');
    
    --v2
    --all or following
    insert into regions
    values (p_id, p_name);
    
    --v3
    --try sequence after
    insert into regions
    values (s_regions.nextval, 'wei');
    
    insert into regions
    values (s_regions.nextval, 'qi');
    
    insert into regions
    values (s_regions.nextval, 'yan');
    
    insert into regions
    values (s_regions.nextval, 'han');
    
    --tem := S_REGIONS.nextval;
    --try to creat a sequence inside of block
    /* adding the following block will cause line 36 error
    execute immediate 'create sequence s2';-- start with 20 increment by 1';
    
    select s2.nextval 
    into tem
    from dual;
    
    tem := tem + 50;
    
    insert into regions
    values (tem, 'qi');
    
    */
end ceshi;
/

--test 1 
--not working
--nothing chnages
select * from regions;

--the folloing line is not working
--since the prroedure is not executing
--the rollback is meaning less
--rollback;
--/

--test 4
--whether rollback working
--no
--select * from regions;

--also
--the sequence does not work here
--coz this is used in the definition body of procedure
--move it before the procedure
/*
create sequence s_regions
    start with 12
    increment by 1
    nocache;
*/

--te
--test 2
--working
--qin is added
--zhao is added
execute ceshi(11, 'zhao');
select * from regions;

--reset table to init
--and test whether it works
--works
rollback;
/

select * from regions;

--manualy delete in case does not write rollback process
--test the table again
/*
delete from regions
where region_id in (10,11);
select * from regions;
*/

--try sequence
--not working if processed after execute the procedure
--deleted this and move it before procedure
--drop sequence s_regions;
/*
create sequence s_regions
    start with 12
    increment by 1
    nocache;
*/