存储过程随机数的产生

以下转自http://xinxuan3038.blog.163.com/blog/static/70346209201071125558292/，用的应该是MS SQL吧，没有进行验证。

以下是产生1000个10位的随机数的代码方法一：

alter PROC proc_randCardID_LYJ2

AS
declare @i int
DECLARE @randCardID char(19)
DECLARE @r numeric(15,8)
set @i=1
while @i<=1000
begin
SELECT @r=RAND(
(DATEPART(mm,GETDATE())*100000)+
(DATEPART(ss,GETDATE())*1000)+
 DATEPART(ms,GETDATE())
)

SET @randCardID=SUBSTRING(convert(varchar(15),@r),3,5)+SUBSTRING(convert(varchar(15),@r),5,5)
 if not exists(select * from rand_CardID where CardID=@randCardID)
 begin
 insert rand_CardID select @randCardID
set @i=@i+1
 end

end

 

方法二：

create  procedure randCardID_xcq
as
begin
declare @i int
declare @n bigint
set @i=1
while @i<=10000
begin
  set @n=(SELECT  CAST(CEILING(RAND() * 10000000000) AS bigint))
  if @n<1000000000 or  exists(select num from #tmp where num=@n)
   continue
  else
   begin
    insert into #tmp values(@n)
    set @i=@i+1
   end
 end
end

GO

假设五位的随机代码select   cast(10000+90000*rand()   as   int)也可以这样产生

方法三：

drop table #t
create table #t(aa varchar(10))
----------
declare @i int
declare @n varchar(10)
set @i=0
set @n=0
while @i<1000
begin
 set @n=cast(cast(rand()*9999999999 as bigint) as varchar(10))  
 set @n=REPLICATE('0',10-LEN(@n))+@n
 while exists(select * from #t where aa=@n)--如果相同，则重取随机数
 begin
  set @n=cast(cast(rand()*9999999999 as bigint) as varchar(10))
  set @n=REPLICATE('0',10-LEN(@n))+@n
 end
 insert into #t values(@n)
 set @i=@i+1
end
--------
select cast(aa as varchar(10)) from #t

 

具体进行：declare @i int
declare @d int
declare @n bigint
set @i=1
while @i<=10000
begin
  set @n=(SELECT  CAST(CEILING(RAND() * 10000000000) AS bigint))
  set @d=left(@n,1)
  if @d<>8
    continue
 
  if @n<1000000000 or  exists(select num from #tmp where num=@n) or right(@n,1) in (4,7)
   continue
  else
   begin
    insert into #tmp values(@n)
    set @i=@i+1
   end
 end

===========================================================
以下为个人根据第一种方法编写的，使用的数据库为MySql
/* 可获取指定长度(最大长度13)的随机数*/
/* out_ret内部返回执行结果 */
/* out_rand返回随机数 */
/* in_num指定位数，最大13 */
DROP PROCEDURE IF EXISTS sp_pub_get_rand;;
CREATE PROCEDURE sp_pub_get_rand
(
    OUT out_ret INT,
    OUT out_rand VARCHAR(15),
    IN  in_num    INT
)
label_top:BEGIN
   
    DECLARE randID VARCHAR(19) DEFAULT '';
    DECLARE r NUMERIC(15, 8);
    DECLARE nMaxLen INT UNSIGNED DEFAULT(13);

    /* secondPos must greater than startPos */
    DECLARE nStartPos INT UNSIGNED DEFAULT(3);
    DECLARE nSecondPos INT UNSIGNED DEFAULT(5);
    DECLARE nMaxWhile INT UNSIGNED DEFAULT(100);
    DECLARE nIndex INT UNSIGNED DEFAULT(0);
    DECLARE nLen SMALLINT UNSIGNED DEFAULT(0);
    DECLARE nSubLen SMALLINT UNSIGNED DEFAULT(0);

    IF(nMaxLen >= in_num and 0 < in_num) THEN
        repeat1_top:REPEAT
            SET nSubLen = in_num/2;        /* 子字符串的长度 */
            SET r = RAND(
                EXTRACT(MINUTE FROM CURTIME())*100000 +
                EXTRACT(SECOND FROM CURTIME())*1000 +
                EXTRACT(MICROSECOND FROM CURTIME()));
        
            /* r为浮点数，0.xxx，所以必须从第三位开始，example:连接"23"，"35" = "2335"*/
            SET randID = CONCAT(SUBSTRING(CONVERT(r, CHAR(15)), nStartPos, nSubLen), SUBSTRING(CONVERT(r, CHAR(15)), nSecondPos, nSubLen));
            SET out_ret = 0;
            SET out_rand = randID;
            SET nIndex = nIndex + 1;
        UNTIL (nMaxWhile < nIndex OR !EXISTS(SELECT * FROM r_param WHERE id=0 and type=0 and int_value=randID))
        END REPEAT repeat1_top;

        /* 预防超出长度 */
        SET nLen = LENGTH(randID);
        IF(nLen > in_num) THEN
            SET randID = SUBSTRING(randID, 1, in_num);
        END IF;

        UPDATE r_param SET int_value=randID WHERE id=0 and type=0;
    ELSE
        SET out_ret = -1;
    END IF;

END label_top;;