create table soldier_groups (
id int not null primary key,
name varchar2(256)
)Table created.
create table planets (
id integer primary key,
name varchar(256)
)Table created.
create table planet_garrison (
soldier_group_id integer primary key,
planet_id integer not null,
constraint planet_visitor_fk_planet
foreign key ( planet_id )
references planets ( id ),
constraint planet_visitor_fk_group
foreign key ( soldier_group_id )
references soldier_groups ( id )
)Table created.
create or replace package soldier_group_action as
procedure move_to(
i_group_id simple_integer,
i_planet_id simple_integer );
function current_planet(
i_group_id simple_integer )
return planets.id%type;
end; Package created.
create or replace package body soldier_group_action as
procedure move_to(
i_group_id simple_integer,
i_planet_id simple_integer )
as
begin
insert into planet_garrison ( soldier_group_id, planet_id )
values ( i_group_id, i_planet_id );
exception
when dup_val_on_index then
update planet_garrison
set planet_id = i_planet_id
where soldier_group_id = i_group_id;
end;
function current_planet(
i_group_id simple_integer )
return planets.id%type
as
l_planet_id planets.id%type;
begin
select planet_id into l_planet_id
from planet_garrison
where soldier_group_id = i_group_id;
return l_planet_id;
exception when no_data_found then
return null;
end;
end; Package Body created.
insert into soldier_groups values ( 1, '1st Platoon of 2nd Brigade' )1 row(s) inserted.
insert into soldier_groups values ( 2, '2nd Platoon of 2nd Brigade' )1 row(s) inserted.
insert into planets values ( 1, 'Tatooine' )1 row(s) inserted.
insert into planets values ( 2, 'Hoth' )1 row(s) inserted.
begin
soldier_group_action.move_to(
i_group_id => 1,
i_planet_id => 1 );
dbms_output.put_line( 'Current planet: ' ||
to_char(soldier_group_action.current_planet( 1 )) );
end; Current planet: 1
create or replace view soldier_groups_stationed as
select
groups.id group_id,
groups.name group_name,
planets.id planet_id,
planets.name planet_name
from
soldier_groups groups
inner join planet_garrison garrison on groups.id = garrison.soldier_group_id
inner join planets on garrison.planet_id = planets.id View created.
create or replace view soldier_groups_all as
select
groups.id group_id,
groups.name group_name,
planets.id planet_id,
planets.name planet_name
from
soldier_groups groups
left outer join planet_garrison garrison on groups.id = garrison.soldier_group_id
left outer join planets on garrison.planet_id = planets.id View created.
select * from soldier_groups_stationed| GROUP_ID | GROUP_NAME | PLANET_ID | PLANET_NAME | 1 | 1st Platoon of 2nd Brigade | 1 | Tatooine |
|---|
select * from soldier_groups_all| GROUP_ID | GROUP_NAME | PLANET_ID | PLANET_NAME | 1 | 1st Platoon of 2nd Brigade | 1 | Tatooine | 2 | 2nd Platoon of 2nd Brigade | - | - |
|---|