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.
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;