## Processing a list

Sep 26 2007 — edited Sep 28 2007
I have a FIFO list that is stored in a table which I maintain via a stored procedure.

eg. create or replace table my_favourite_totty_list (list_no number primary key, list_value varchar2(30) unique key);

The LIST_NO column is a number that allows me to order the list, the stored procedure maintains this number at all times.

eg. 1, Ginger
2, Posh
3, Baby
4, Scary

We've imposed a limit of 5 on the list for starters, so if you add "Sporty" say, it will be added to the list as "5, Sporty".

If you try to add another to the list, then it will be assigned as no.5, and every value will move up 1 and "Ginger" disappear off the list.

eg. 1. Posh
2. Baby
3. Scary
4. Sporty
5. Beyonce

If however you add a value which already exist on the list, then it has to move to the end of the list and every value below it has to move up 1.

eg. if we try to add "Scary" again, the list becomes

1. Posh
2. Baby
3. Sporty
4. Beyonce
5. Scary

My stored procedure contains pseudo logic along these lines;

procedure upd_fav_totty_list(p_val varchar2)
begin
insert into my_favourity_totty_list
where not exist and rows <= 5
if 0 rows inserted
update my_favourity_totty_list
set list_value = nvl(list_value from next number on my_favourity_totty_list, p_val)
if dup_index
then
update my_favourity_totty_list
set list_value = nvl(value from next number on my_favourity_totty_list, p_val)
where list_no >= (select list_no from my_favourity_totty_list where list_value = p_val)
end if
elsif dup_index
update my_favourity_totty_list
set list_value = nvl(value from next number on my_favourity_totty_list, p_val)
where list_no >= (select list_no from my_favourity_totty_list where list_value = p_val)
end if;
end;

I've got exception handlers to handle the dup_index bit.

At the most, it takes 3 DML to maintain the list. Can anyone think of a way to reduce this any further? The only caveat is I can't use MERGE because there's VPD in place and MERGE doesn't work with VPD.