Number Sequence With Gaps

oraclesequence

In an Oracle PLSQL function I want to generate a special kind of sequence number. It starts with 00000001, but when 00899999 is reached, I want to have 01000001 as its next number. This pattern repeats when 01899999 is reached: The next number then should be 02000001.

To put it another way: My goal is to skip the numbers in the sequence in the range between ##900000 and ##999999, where ## denote two digits.

How can this be done on an Oracle database?

Best Answer

If you're looking for an algorithm:

  • Add 1 to previous number;
  • if (current number + 100000) % 1000000 = 0, add 100001 to current number.

So:

  • 899998 + 1 = 899999; (100000 + 899999) % 1000000 = 999999; return 899999.
  • 899999 + 1 = 900000; (100000 + 900000) % 1000000 = 0; 900000 + 100001 = 1000001; return 1000001
  • 1000001 + 1 = 1000002; (100000 = 1000002) % 1000000 = 100002; return 1000002 ...
  • 1899998 + 1 = 1899999; (100000 + 1899999) % 1000000 = 999999; return 1899999
  • 1899999 + 1 = 1900000; (100000 + 1900000) % 1000000 = 0; 1900000 + 100001 = 2000001; return 2000001
  • and so on.

One implementation:

  • CREATE TABLE myNumSeq (lastNum int); INSERT INTO myNumSeq (0);

  • Create a function, fn_GetNextNum(), that works something like this:

    DECLARE @theNum int;
BEGIN TRANSACTION;
SELECT TOP 1 @theNum = lastNum + 1 FROM myNumSeq FOR UPDATE;
IF (@theNum + 100000) % 1000000 = 0
BEGIN
    SET @theNum = @theNum + 100001;
END;
UPDATE myNumSeq SET lastNum = @theNum;
COMMIT TRANSACTION;
RETURN @theNum;

Add error handling as appropriate. I haven't used Oracle regularly in about 18 years, so please consider the above pseudocode. I leave the Oracle-specific details (can't recall if it has both procedures and functions, or the return mechanism used for something like this if it only has procedures) to someone familiar with that.

I'm sure this could be written more concisely, but for single-record additions it should work fine. If you're adding records in bulk, using a variation on a number table would probably be quicker than a function.

Related Question