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



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
Previous
Next Post »