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