Generating a unique 10 digit phone number-CodePudding

My goal is to generate a random 10 digit unique phone number function.

Is there a way to ensure the first digit isn't a zero(0)? Second, I want to wrap this in a function to do a test like NOT IN OR NOT EXISTS before adding the phone number.

I came up with the following so far but can use some help on implementing the other requirements.


CREATE TABLE PHONE_NUMBERS
(
   PHONE_NUMBER NUMBER
);

INSERT INTO PHONE_NUMBERS (PHONE_NUMBER)
SELECT listagg(ROWNUM-1,'') WITHIN GROUP ( 
order by (dbms_random.random )) mydigit FROM dual
CONNECT BY LEVEL <= 10

CodePudding user response：

To generate numbers from 1000000000 to 9999999999999, you can use:

FLOOR(DBMS_RANDOM.VALUE(1e9, 1e10))

If you want to insert values without inserting duplicates then use a MERGE statement:

MERGE INTO phone_numbers dst
USING (
  SELECT DISTINCT
         FLOOR(DBMS_RANDOM.VALUE(1e9, 1e10)) AS phone_number
  FROM   DUAL
  CONNECT BY LEVEL <= 10
) src
ON (src.phone_number = dst.phone_number)
WHEN NOT MATCHED THEN
  INSERT (phone_number)
  VALUES (src.phone_number);

db<>fiddle here