SQL stored proc - help me write this one, please! (part 2)

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 store开发者_StackOverflow中文版d proc is called.

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

Part of my previous question 3151056.

Thanks,

Voodoo

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

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.

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;