How get next number some like sequence - Oracle PLSQL-CodePudding

Hello in table I have data not use sequence (Row where num = 4 is deleted), How get number 4 for next insert? After insert row where num = 4, next num is 6...

eg.

Select 1 as num from dual
union all
Select 2 as num from dual
union all
Select 3 as num from dual
union all
Select 5 as num from dual

CodePudding user response：

Try this one out:

WITH dat AS
(
SELECT 1 AS NUM
  FROM DUAL
 UNION ALL
SELECT 2 AS NUM
  FROM DUAL
 UNION ALL
SELECT 3 AS NUM
  FROM DUAL
 UNION ALL
SELECT 5 AS NUM
  FROM DUAL
)
SELECT d1.num   1 AS next_val
  FROM dat d1
 WHERE NOT EXISTS (SELECT NULL
                     FROM dat d2
                    WHERE d2.num = d1.num   1)
 ORDER BY next_val;