create table soldier_groups ( 
  id int not null primary key, 
  name varchar2(256) 
)

create table planets ( 
  id integer primary key, 
  name varchar(256) 
)

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 ) 
)

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; 

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; 

insert into soldier_groups values ( 1, '1st Platoon of 2nd Brigade' )

insert into soldier_groups values ( 2, '2nd Platoon of 2nd Brigade' )

insert into planets values ( 1, 'Tatooine' )

insert into planets values ( 2, 'Hoth' )

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; 

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 

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 

select * from soldier_groups_stationed

select * from soldier_groups_all