0

I'm trying to create a table where one of the columns will contain autoincrementing numbers. Is there some other way of doing that instead of creating sequence for each row? The rows will be dynamically added/removed and the sequence needs to be resetable.

Table should look like this:

[Foreign_Key]   [Sequence_State]
    A               0
    B               0
    C               0

And when I call something like this(pseudocode):

getNextNumber('A');
getNextNumber('A');
getNextNumber('A');
getNextNumber('B');
getNextNumber('B');

the state of the table should change to this:

[Foreign_Key]   [Sequence_State]
    A               3
    B               2
    C               0

I guess I could go with PL/SQL function which would create the sequence for the Foreign_Key if it doesn't exist, increment and update the Sequence_State. But this all seems a bit clunky and I'm wondering if there's some better way to do that.

NeplatnyUdaj
  • 6,052
  • 6
  • 43
  • 76
  • what bout a trigger? like : http://stackoverflow.com/questions/16388576/oracle-auto-increment-trigger – Enoque Duarte Aug 07 '14 at 15:34
  • But I would need to have this trigger before select(which I think doesn't exist in Oracle). The sequences need to be created on the fly, because rows can change. – NeplatnyUdaj Aug 07 '14 at 15:41
  • I don't understand the question. What do you mean "creating sequence for each row"? Your sample data doesn't look like it has an auto-incremented anything in it. Can you clarify the question by providing sample data, before and after the update? – Gordon Linoff Aug 07 '14 at 16:41
  • Why do they need to change? are you looking at a 'Sequence' for the rows returned in a select? – bowlturner Aug 07 '14 at 16:47
  • I've updated the question. It should behave like if every record in the table had independent sequence. If the rows were static, then I would simply create sequence for each row, but it will change. For example by some user action, a new record 'D' will appear. Right now I'm trying to put together some function using execute immediate to create and increment sequences on the fly – NeplatnyUdaj Aug 07 '14 at 16:48
  • You will need an update statement for that. That is not what sequences are for. – bowlturner Aug 07 '14 at 16:49

1 Answers1

0

I think I've figured it out. As bowlturner pointed out, sequences aren't necessary(although I tried that they could be used). Simple selects and updates are enough:

create or replace function getNextNumber(otherid IN VARCHAR2)
return number
is 
  nextval NUMBER;
  entryexists NUMBER(1);
begin
  select case
    when exists(select * from CURRENT_STATE where OTHER_ID=otherid)
      then 1
      else 0
    end into entryexists
  from dual;  
  if entryexists = 0
    then      
      insert into CURRENT_STATE (OTHER_ID, SEQUENCE_STATE) values (otherid, 1);
      nextval := 1;
    else  
      update CURRENT_STATE set SEQUENCE_STATE = SEQUENCE_STATE + 1 where     OTHER_ID=otherid
        RETURNING SEQUENCE_STATE into nextval;
  end if;
  return nextval;
end;
/
NeplatnyUdaj
  • 6,052
  • 6
  • 43
  • 76