Non-repeated random numbers generated by Oracle

1. create function fn_ifrandom (recursion is used)

Create or replace function fn_ifrandom (tmpallrandom in clob, tmprandom in varchar2, allcount in number) return varchar2is -- generate random number randomchar varchar2 (5 ); -- The final returned random number randomchar1 varchar2 (5); ifrandom number; -- when a random number is generated cyclically, determine whether it is a repeated random number begin -- determine whether it is a repeated random number, if it is, ifrandom> 0, otherwise, ifrandom = 0 select instr (tmpallrandom, tmprandom,) into ifrandom from dual; If ifrandom> 0 then -- repeats, then select to_char (round (dbms_random.value (1, allcount) into randomchar1 from dual; randomchar: = fn_ifrandom (tmpallrandom, randomchar1, allcount); else randomchar: = tmprandom; end if; return randomchar; end random;

2. Stored Procedure Call:

Create or replace procedure p_main2_mx (kouchucnt in number, allcount in number) as tmprandom varchar2 (5); -- random number, (max. 20000) tmpallrandom varchar2 (32767 ); -- The spliced random number does not repeat. After the conditions are met, write the tmpclob and clear the tmpclob clob. -- after the tmpallrandom splicing length exceeds 32767, splice the variable into it. The variable v_cnt number (5) is used later ); -- begin if kouchucnt is not null or kouchucnt <> 0 then is used to generate a random number for splicing during clob counting. The value range is 1-allcount for I in 1 .. kouchucnt loop -- generate a random number select to_char (round (random (1, allcount) into tmprandom from dual; tmprandom: = fn_ifrandom (tmpclob | tmpallrandom, tmprandom, allcount: = tmpallrandom | tmprandom | ','; v_cnt: = v_cnt + 1; if v_cnt = 5461 then -- 5461 = 32767/6 v_cnt: = 0; tmpclob: = tmpclob | tmpallrandom; tmpallrandom: = ''; end if; end loop; If v_cnt <> 0 then tmpclob: = tmpclob | tmpallrandom; end if; -- because all tmpallrandom is, 'Remove the last two bits for use, and put a single quotation mark tmpclob: = Replace (tmpclob, '); tmpclob: = substr (tmpclob, 1, length (tmpclob)-1); ----------------- (the following splits the spelled random number into a table, and the where in table does not have the limit of 1000 in) for ifbmd_record in (select ID, mobilecode from t_busi_presend_mx where mainid = main2id and rowid in (select T. row_id from (select rownum RN, rowid row_id from t_busi_presend_mx where mainid = main2id) t where T. rn in (select. column_value CVA from table (fn_split (tmpclob, ',') loop --...... end loop; end;

Knowledge point:

1. When the splicing length exceeds 32767, you should splice the large varchar2 string into the clob string and then re-splice the varchar2 string.

2. The where in condition has 1000 restrictions. If in is a table, there is no such restriction. We should first separate the conditions in with commas and then split them into a temporary table using a self-written split.