Oracle生成随机码

首先建一个临时表，用于存储生成的活动码
            create table T_RANDOM_CODE
      (
       code varchar2(20)
      );
      -- Create/Recreate primary, unique and foreign key constraints
      alter table T_RANDOM_CODE
        add constraint PK_T_RANDOM_CODE primary key (code);

     建立存储过程，根据输入的前缀和活动码数量，生成不重复的活动码
 

   PROCEDURE create_random_code
   (
      p_number in number  --活动码生成数量
     ,p_prefix in varchar2   --活动码前缀
   ) as
      v_count  number;
      v_random number;
      v_num    number;
      v_code   varchar2(20);
   begin
          v_count := 0;
      WHILE v_count < p_number
      LOOP

       --活动码的位数可以在这里调整
         select dbms_random.value(10000000, 99999999)
           into v_random
           from dual;
         v_code := p_prefix || trunc(v_random);
         select count(*)
           into v_num
           from t_random_code
          where code = v_code;
         if v_num = 0 then
            insert into t_random_code (code) values (v_code);
            v_count := v_count + 1;
         end if;

      END LOOP;
      null;
   END create_random_code;