Sequence id in oracle

oracle-11gplsql

I am using Oracle 11g.

I have a senario like:

I need to increase auto increment id like below

00000001
00000002
00000003
00000004

For example, my table looks like below

id       empid name    sal
000001   10    raju    5555
000002   20    ramesh  6666
000003   30    rahul   55555

Now if I insert a record into that table the id needs to increase automatically.

How can I achieve this in a stored procedure? I have an insert statement in a stored procedure.

Best Answer

This is usually achieved by using a sequence, often in combination with a trigger that fires for each row, before an insert.

CREATE SEQUENCE my_seq;

CREATE OR REPLACE TRIGGER trg_my_table_bir
BEFORE INSERT ON MyTable FOR EACH ROW
BEGIN
   IF :new.my_id IS NULL THEN
      :new.my_id := my_seq.NEXTVAL;
   END IF;
END;

But of course you're free to get that next value in your own stored procedure and insert it from there; if you use a trigger like the one above, it won't get another value from the sequence if one is already provided.


Please note that while the numbers from the sequence are guaranteed to be unique — you will not get the same number twice unless you reset the sequence — they are not guarateed to be dense — gaps in the numbering can and will occur.