oracle產生不重複隨機數

1.建立FUNCTION fn_ifrandom（其中用到遞迴）

CREATE OR REPLACE FUNCTION fn_ifrandom (tmpallrandom IN clob,tmprandom IN VARCHAR2,allcount in number) RETURN varchar2IS  --產生不重複的隨機數  randomchar varchar2(5);  --最終返回的不重複的隨機數  randomchar1 varchar2(5);  ifrandom number;  --迴圈產生隨機數時，判斷是否是重複隨機數BEGIN     --判斷是否是重複隨機數，是則ifrandom>0，否則ifrandom=0    select instr(tmpallrandom,tmprandom,1,1) into ifrandom from dual;     if ifrandom>0 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 fn_ifrandom;

2.預存程序調用：

CREATE OR REPLACE PROCEDURE p_main2_mx(kouchucnt in number,allcount in number)as       tmprandom varchar2(5);  --隨機數，（最大20000）       tmpallrandom varchar2(32767);   --拼接好的不重複隨機數，滿足條件後寫入tmpclob並清空       tmpclob clob; --tmpallrandom拼接長度超過32767後，拼入此變數，後面都用此變數       v_cnt number(5);  --拼接clob計數時用begin      if kouchucnt is not null or kouchucnt<>0 then          --產生拼接的隨機數，範圍為1-allcount        for i in 1 ..kouchucnt loop                --產生隨機數                select to_char(round(dbms_random.value(1,allcount))) into tmprandom from dual;                tmprandom := fn_ifrandom(tmpclob||tmpallrandom,tmprandom,allcount);                tmpallrandom := 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;        --因為拼接的所有tmpallrandom最後是,'   所以使用時要去掉後2位,並且在第一位拼一個單引號        tmpclob :=replace(tmpclob,',,',',');        tmpclob :=substr(tmpclob,1,length(tmpclob)-1);       -----------------開始（下面將拼好的隨機數拆分了逗號變成一個table，where in table就沒有in 1000個的限制）       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  a.column_value cva FROM TABLE( fn_split(tmpclob,',') ) a )          )        ) loop        --......        end loop;end ;

知識點：

1.拼接長度超過32767時，就應該將大varchar2串拼到clob串再重新開始拼接varchar2串。

2.where in條件中有1000個的限制，如果in一個table，就沒有此限制。應該先將要in的條件用逗號隔開然後用自寫的split把他分成一個暫存資料表。