use [test]
go
/****** object: userdefinedfunction [dbo].[randtennums] script date: 2019/7/23 15:40:16 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create function [dbo].[randtennums]()
returns varchar(10)
as
begin
declare @i int
declare @flag int
declare @serialnumber nvarchar(20)
declare @rands numeric(38,38)
–初始化设定
set @i=1
set @serialnumber = ”
–生成10位随机码
while @i<11
begin
–设置随机，这个随机会选择字母（大小写）还是数字
select @rands = rand_a from [dbo].[vw_rand]
set @flag=ceiling(@rands *3)
if @flag=1
begin
–随机字母（大写去除大写的o）
select @rands = rand_a from [dbo].[vw_rand]
while char(65+ceiling(@rands * 25)) = ‘o’
begin
select @rands = rand_a from [dbo].[vw_rand]
end
select @serialnumber=@serialnumber+char(65+ceiling(@rands * 25))
end
else if @flag=2
begin
–随机字母（小写去除小写的o）
select @rands = rand_a from [dbo].[vw_rand]
while char(97+ceiling(@rands * 25)) = ‘o’
begin
select @rands = rand_a from [dbo].[vw_rand]
end
select @serialnumber=@serialnumber+char(97+ceiling(@rands * 25))
end
else begin
–随机数字 1至9的随机数字(整数)
while cast(ceiling(@rands * 9) as varchar(1)) = ‘0’
begin
select @rands = rand_a from [dbo].[vw_rand]
end
select @rands = rand_a from [dbo].[vw_rand]
select @serialnumber=@serialnumber+cast(ceiling(@rands * 9) as varchar(1))
end
–进行下一个循环
set @i=@i+1
end
return @serialnumber;
end