I've the next table using the value 501 inside it..

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

How do i write a saved proc about this which returns me 501 and batches Number to next in sequence (i.e. 502)? I'd like this behavior repeated each time the saved proc is known as.

(Also, how do i refer to this as saved proc from the query?)

A part of these question 3151056.

Thanks,

Voodoo

Make use of an IDENTITY column that takes proper care of numbering and incrementing for you personally.

Any came back number is likely to become already utilized by another connection/client/process

You are posting data from old tables, right? Let's say you import data from old tables with identity off and then you place the identity using the greatest number+1 and continue your existence using identity.

Other approach is applying a trigger at place that will see if NumberItem is null and it'll add the Max+1 whether it's null. Otherwise, do nothing at all.

I do not believe that SP is a great solution. And I am confident you do not need everything 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;