SQL stored proc - help me write this one, please!

Question

I have the following table with the value 501 in it..

CREATE TABLE _Numbers(
    Number numeric(20,0) NOT NULL PRIMARY KEY
)
INSERT INTO _Numbers VALUES(501)

How can I write a stored proc on this which returns me 501 and increments Number to next in sequence (i.e. 502)? I would like this behaviour repeated every time the stored proc is called.

(Also, how can I call this stored proc from any query?)

Part of my previous question 3151056.

Thanks,

Voodoo

Answer 1

Use an IDENTITY column which takes care of numbering and incrementing for you.

Any returned number is liable to be already used by another connection/client/process

Answer 2

You're importing data from old tables, right? What if you import data from old tables with identity off and after that you set the identity with the highest number+1 and continue your life using identity.

Other approach is using a trigger at insert that would check if NumberItem is null and it will add the Max+1 if it's null. If not, do nothing.

I don't think that SP is a good solution. And I'm pretty sure you don't need all that stuff.

Answer 3

CREATE OR REPLACE PROCEDURE read_and_increment (number_just_read OUT NUMBER)
IS
BEGIN
   DECLARE
      stored_number   NUMBER DEFAULT NULL;
   BEGIN
      SELECT number
        INTO stored_number
        FROM _numbers
       WHERE ROWNUM = 1;

      number_just_read := stored_number;

      UPDATE _numbers
         SET number = number + 1;

      COMMIT;
   END;
END read_and_increment;