Oracle auto increment

Question

Possible Duplicate:
Autoincrement in Oracle

I have a table in oracle and I want to make userid auto increment.. Once I make it auto increment by using sequence and try to insert into table using procedure where do i put my sample.seq_userid How do i insert userid? do i have to declare it in my procedure?

 PROCEDURE insertExample
  (
    name_in IN sample.name%TYPE,
    age_in IN sample.age%TYPE
  )
  IS

  BEGIN

    INSERT INTO sample
    (name, age)
    VALUES
    (name_in, age_in);

  END insertExample;

here is my update one

PROCEDURE updateExample
  (
    userid_in IN sample.userid%TYPE,
    name_in IN sample.name%TYPE,
    age_in IN sample.age%TYPE
  )
  IS

  BEGIN

    UPDATE sample
       SET name = name_in,
           age = age_in
    WHERE userid = userid_in;


  END updateExample;

score 4 · Answer 1 · answered May 25 '11 at 16:33

4

you need a sequence:

create sequence seq_user_id start with 1 increment by 1;

and a trigger on a table

CREATE TRIGGER user_id_trg 
   BEFORE insert 
   ON sample 
   FOR EACH ROW
BEGIN
      SELECT seq_user_id.NEXTVAL INTO :new.user_id FROM dual;

END;
/

answered May 25 '11 at 16:33

schurik

7,798
2
23
29

7

Strictly speaking, you don't _need_ a trigger if you specify `seq_user_id.NEXTVAL` in the INSERT statement, such as `INSERT INTO sample (user_id, name, age) VALUES (seq_user_id.NEXTVAL, name_in, age_in)` in the example given. There can be performance advantages to specifying the sequence in the INSERT statement instead of relying on the trigger, but these might be more significant with `INSERT INTO...SELECT` than with `INSERT INTO...VALUES`. – Brian Camire May 25 '11 at 17:48

Oracle auto increment

1 Answers1