SQL generates a unique random numeric string

Oracle enables fast batch generation of random numeric strings:

--TableCreate Tablerandom_number_temp (row_num Number default 0  not NULL, Random_numVARCHAR2( -) not NULL )--typeType number_array_t is Table  of  Number Index  byBinary_integer;--implementation generates a unique random numberPROCEDUREInit_random_numbre (p_quantityinch  Number,--number of buildsP_leninch  Number,--Random number lengthSTATUS outVARCHAR2, Reeor_mes outVARCHAR2            ) isNumber_array number_array_t; V_number_array_len Number ; V_random_index Number;    New_number_array number_array_t; New_num_arr_indexinteger:=1; V_row_numinteger:= 0; V_max_index Number; V_empty_count Number:= 0;--number of NULL values detected    --n integer: =1;--for test, count to Number_array array CountTag_number Number:= 0.25;--allow to detect a scale of null valuesV_mac_empty_count Number:=  -;--maximum number of times a null value is allowed to be detected       BEGINSTATUS:=const_true; V_number_array_len:= POWER(Ten, P_len); --The number of generated bars is greater than the maximum number of random numbers.         ifP_quantity>V_number_array_len ThenV_number_array_len:=p_quantity; --return ' N ';        End if;  forKinch 0.. V_number_array_len-1Loop Number_array (k+1) :=K; EndLoop; V_max_index:=Number_array.Count; V_mac_empty_count:=Ceil (V_max_index*tag_number); LoopExit  whenV_row_num=P_quantityorNumber_array.Count = 0;--V_row_num starting from 0V_random_index:= round(Dbms_random.value (0.5, V_max_index)); IFNumber_array.EXISTS(V_random_index) ThenV_row_num:=V_row_num+ 1; INSERT  intoSfc.random_number_temp (row_num, Random_num)VALUES(V_row_num,lpad (To_char (Number_array (V_random_index)), P_len,'0')); Number_array.DELETE(V_random_index); ELSEV_empty_count:=V_empty_count+1; IFV_empty_count>V_mac_empty_count Then                   ifNumber_array.Count <=V_max_index ThenNew_num_arr_index:=1;  forTinch 1.. V_max_index LoopifNumber_array.EXISTS(t) ThenNew_number_array (new_num_arr_index):=Number_array (t); New_num_arr_index:=New_num_arr_index+ 1; End if; EndLoop; End if; Number_array:=New_number_array; New_number_array.Delete();--Empty ArrayV_max_index:=Number_array.Count; V_mac_empty_count:=Ceil (V_max_index*Tag_number);--The number of times to take the empty                  --N: = n+1; V_empty_count:= 0; END IF; END IF; EndLoop; COMMIT; EXCEPTION whenOTHERS ThenSTATUS:=Const_false; Reeor_mes:=SQLCODE|| '::' ||SUBSTR (SQLERRM,1, -); END;

Code implementation:

1. Place the minimum value to the maximum value into a temporary array.

2. Randomly fetch a value from the array into the table and delete the value.

3. Repeat step 2 until the number of null values is removed to the specified number of times, regenerate the temporary array, remove the null values from the temporary array, and then repeat step 2.

SQL generates a unique random numeric string