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

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

create table planet_x_sold_grp ( 
  sold_grp_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 ( sold_grp_id ) 
    references soldier_grp ( id ) 
)

create or replace package planet_util as 
  procedure update_grp_planet( 
    a simple_integer, 
    b simple_integer ); 
  
  function get_planet( 
    a simple_integer ) 
    return planets.id%type; 
end; 

create or replace package body planet_util as 
  procedure update_grp_planet( 
    a simple_integer, 
    b simple_integer ) 
  as 
  begin 
    insert into planet_x_sold_grp ( sold_grp_id, planet_id ) 
      values ( a, b ); 
  exception 
    when dup_val_on_index then 
      update planet_x_sold_grp 
        set planet_id = b 
        where sold_grp_id = a; 
  end; 
  
  function get_planet( 
    a simple_integer ) 
    return planets.id%type 
  as 
    l_planet_id planets.id%type; 
  begin 
    select planet_id into l_planet_id 
      from planet_x_sold_grp 
      where sold_grp_id = a; 
    return l_planet_id; 
  exception when no_data_found then 
    return null; 
  end; 
end; 

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

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

insert into planets values ( 1, 'Tatooine' )

insert into planets values ( 2, 'Hoth' )

begin 
    planet_util.update_grp_planet( 1,  1 ); 
  
    dbms_output.put_line( 'Current planet: ' || 
      to_char(planet_util.get_planet( 1 )) ); 
end; 

create or replace view soldier_groups_1 as 
    select  
        g.id group_id, 
        g.name group_name, 
        p.id planet_id, 
        p.name planet_name 
    from 
        soldier_grp g  
        inner join planet_x_sold_grp pxg on g.id = pxg.sold_grp_id 
        inner join planets p on pxg.planet_id = p.id 

create or replace view soldier_groups_2 as 
    select 
        g.id group_id, 
        g.name group_name, 
        p.id planet_id, 
        p.name planet_name 
    from 
        soldier_grp g  
        left outer join planet_x_sold_grp pxg on g.id = pxg.sold_grp_id 
        left outer join planets p on pxg.planet_id = p.id 

select * from soldier_groups_1

select * from soldier_groups_2