oracle生成不重复随机数

1.创建FUNCTION fn_ifrandom（其中用到递归）

CREATE OR REPLACE FUNCTION fn_ifrandom (tmpallrandom IN clob,tmprandom IN VARCHAR2,allcount in number)
 RETURN varchar2
IS  --生成不重复的随机数
  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把他分成一个临时表。