SQL> create table C$Line ( line_id integer not null primary key ) ;

Table created

SQL> create table C$Muff ( muff_id integer not null primary key,
   2     line_id integer not null references C$Line ( line_id ),
   3     ordering integer default  0  not null,
   4     unique ( line_id, ordering )) ;

Table created

SQL> create table C$Seg ( muff_id integer not null primary key
   2     references C$Muff ( muff_id )) ;

Table created

SQL> create sequence C$Line_seq ;

Sequence created

SQL> create sequence C$Muff_seq ;

Sequence created

SQL> create sequence C$Seg_seq ;

Sequence created

SQL> create or replace view c$line_v as
   2     select line_id, muff_id_before, seg_id, muff_id_after
   3       from (select m.line_id, m.muff_id muff_id_before, s.muff_id seg_id,
   4         lead ( m.muff_id ) over ( partition by m.line_id order by ordering ) muff_id_after,
   5         ordering
   6       from C$Muff m, C$Seg s
   7       where m.muff_id = s.muff_id (+))
   8     where seg_id is not null
   9     order by line_id, ordering ;

View created

SQL> create or replace function C$CreateLine return integer is
   2     v_line_id integer ;
   3     v_muff_id integer ;
   4   begin
   5     insert into C$Line ( line_id )
   6       values ( C$Line_seq.nextval )
   7       returning line_id into v_line_id ;
   8     insert into C$Muff ( line_id, muff_id )
   9       values ( v_line_id, C$Muff_seq.nextval )
  10       returning muff_id into v_muff_id ;
  11     insert into C$Seg ( muff_id )
  12       values ( v_muff_id ) ;
  13     insert into C$Muff ( line_id, muff_id, ordering )
  14       values ( v_line_id, C$Muff_seq.nextval,  10  ) ;
  15     return v_line_id ;
  16   end ;
  17   /

Function created

SQL> create or replace function C$AddLastSeg ( p_line_id integer ) return integer is
   2     max_muff_ordering number ;
   3     v_muff_id integer ;
   4   begin
   5     select max ( ordering ) into max_muff_ordering
   6       from C$Muff where line_id = p_line_id ;
   7     insert into C$Seg ( muff_id )
   8       select muff_id from C$Muff
   9       where line_id = p_line_id and ordering = max_muff_ordering ;
  10     insert into C$Muff ( muff_id, line_id, ordering )
  11       values ( C$Muff_seq.nextval, p_line_id, max_muff_ordering +  10  )
  12       returning muff_id into v_muff_id ;
  13     return v_muff_id ;
  14   end ;
  15   /

Function created

SQL> create or replace function C$AddSeg ( p_muff_id_after integer ) return integer is
   2     prev_ordering number ;
   3     next_ordering number ;
   4     v_line_id integer ;
   5     v_muff_id integer ;
   6   begin
   7     select line_id, ordering into v_line_id, prev_ordering
   8       from C$Muff where muff_id = p_muff_id_after ;
   9     select min ( ordering ) into next_ordering
  10       from C$Muff where line_id = v_line_id and ordering > prev_ordering ;
  11     insert into C$Muff ( muff_id, line_id, ordering )
  12       values ( C$Muff_seq.nextval, v_line_id, ( prev_ordering + next_ordering ) /  2  )
  13       returning muff_id into v_muff_id ;
  14     insert into C$Seg ( muff_id )
  15       values ( v_muff_id ) ;
  16     return v_muff_id ;
  17   end ;
  18   /

Function created

SQL> create or replace procedure C$DelSeg ( p_muff_id integer ) is
   2   begin
   3     delete from C$Seg where muff_id = p_muff_id ;
   4     delete from C$Muff where muff_id = p_muff_id ;
   5   end ;
   6   /

Procedure created

SQL> declare
   2     line_id_1 integer ;
   3     line_id_2 integer ;
   4     muff_id_10 integer ;
   5     muff_id_11 integer ;
   6     muff_id_12 integer ;
   7     muff_id_13 integer ;
   8   begin
   9     line_id_1 := C$CreateLine ;
  10     line_id_2 := C$CreateLine ;
  11     muff_id_10 := C$AddLastSeg ( line_id_1 ) ;
  12     muff_id_11 := C$AddLastSeg ( line_id_1 ) ;
  13     muff_id_12 := C$AddLastSeg ( line_id_1 ) ;
  14     muff_id_13 := C$AddSeg ( muff_id_11 ) ;
  15     C$DelSeg ( muff_id_11 ) ;
  16   end ;
  17   /

PL/SQL procedure successfully completed

SQL> select * from C$Line_V ;

   LINE_ID  MUFF_ID_BEFORE     SEG_ID   MUFF_ID_AFTER
---------- --------------- ---------- ---------------
          1                 1            1                 2 
          1                 2            2                 5 
          1                 5            5                 8 
          1                 8            8                 7 
          2                 3            3                 4 

SQL>