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
Solution:1
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;
Solution:2
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
Solution:3
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.
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon