以下是产生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;;