Can we continue a column with values using sequence in sql?-CodePudding

I have a table with a column sno having values (1,2,3,4,5). i have created a sequence seq which starts with 1 and increment by 1. now i want to insert the value 6 and so on in this column using this sequence seq but i dont want to change the start with value in the created sequence?

Different ways to do it if possible would be great. Thankyou.

CodePudding user response：

The only way I know is what you don't want.

-- find the last `sno` value; let's presume it returns 5
select max(sno) from that_table;

-- create a sequence which starts with max   1 (i.e. 6):
create sequence seq start with 6;

-- create a trigger which populates `sno`:
create or replace trigger trg_bi_tab
  before insert on that_table
  for each row
begin
  :new.sno := seq.nextval;
end;
/

Note that sequences aren't gapless, which means that it might (and will, sooner or later) produce gaps.

If you badly insist on continuing the sno sequence, then it is literally MAX 1. Can you do that? Sure, but that's a bad idea because you might (and will) get duplicates whenever two (or more) users fetch the same MAX value; only the first one who commits will store that value - all other users will create duplicates (and, possibly, violate uniqueness if sno column is supposed to be unique).

Yes, you could lock the table until the first user - who is working on that table - does the job and commits, but that's even worse because in a multi-user environment you'll have many not-that-happy users (who are waiting ... and waiting ...).

Or, you could create your own "sequence" - a table that holds the next value. You'd calculate it with an autonomous transaction function.

But, why would you want to do anything of that? Create a sequence that starts with max 1 and let Oracle do the rest.

CodePudding user response：

You could alter the sequence. Suppose the highest nr of your table is 35 so the sequence needs to start at 36 and you don't know what the current curval of your sequence is but you want to set it to have 36 as nextval then this is what you could do:

DECLARE
  l_new_curval NUMBER := 35;
  l_curval NUMBER;
  l_offset NUMBER;
BEGIN
  l_curval := myseq.nextval;
  l_offset := l_new_curval - l_curval;
  EXECUTE IMMEDIATE 'alter sequence myseq increment by '||l_offset;
  l_curval := myseq.nextval;
  EXECUTE IMMEDIATE 'alter sequence myseq increment by 1';
END;
/