--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @r varchar(50)
IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
SET @BHLen=10lb_bh: --生成随机编号的处理
SELECT @r=re FROM v_RAND
WHILE LEN(@r)<@BHLen
SELECT @r=@r+re FROM v_RAND
SET @r=LEFT(@r,@BHLen) --检查编号在基础数据表中是否存在
IF EXISTS(SELECT * FROM tb WITH(XLOCK,PAGLOCK) WHERE BH=@r)
GOTO lb_bh RETURN(@r)
END
GO--创建引用生成随机编号的函数
CREATE TABLE tb(
BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10),
col int)--插放数据
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
COMMIT TRAN
SELECT * FROM tb
GO
/*--结果
BH col
------------------------------ -----------
6128177354 1
7378536177 3
8387186129 2
--*/
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @r varchar(50)
IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
SET @BHLen=10lb_bh: --生成随机编号的处理
SELECT @r=re FROM v_RAND
WHILE LEN(@r)<@BHLen
SELECT @r=@r+re FROM v_RAND
SET @r=LEFT(@r,@BHLen) --检查编号在基础数据表中是否存在
IF EXISTS(SELECT * FROM tb WITH(XLOCK,PAGLOCK) WHERE BH=@r)
GOTO lb_bh RETURN(@r)
END
GO--创建引用生成随机编号的函数
CREATE TABLE tb(
BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10),
col int)--插放数据
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
COMMIT TRAN
SELECT * FROM tb
GO
/*--结果
BH col
------------------------------ -----------
6128177354 1
7378536177 3
8387186129 2
--*/
解决方案 »
- 日期字符转换
- sql语句能否实现除了一个字段外全选?
- 存放空间大一点,会不会影响数据库查询的性能?
- 安装2005后没有服务端奇怪,我什么都选了,重新安装后还是没有
- 想MS-SQL数据库插入汉字乱码的问题。
- 高分求助:使用opendatasource 从Excel导入Sql Server如何指定Excel的列名?
- 这样的存储过程该怎么写啊?请大虾们指教,多谢!!!
- Sql Server 2005 数据库转换到Sql Server 2000,自动编号问题不可避免,有没有办法解决
- 如何把OLAP中的立方体导出成文件?
- 如何获取delphi中dbgrid的单元格的内容
- 关于查询某一时间段纪录的问题,急!
- 单机 SQLSERVER 不存在或者拒绝访问
insert @tb
select 1001 declare @start int,@num int
set @start=1000
set @num=3while @num>0
begin
set @start=@start+1
insert @tb select top 1 @start from @tb where not exists(select 1 from @tb where NumberList=@start)
set @num=@num-1
endselect * from @tb
/*
NumberList
-----------
1001
1002
1003
*/
Create PROC MakeRandom
@num int
AS
begin
Declare @RndNum varchar(10)
declare @s varchar(10)
Declare @Count INT
Set @Count = 0
while @Count <@num
begin
set @s='0123456789'
set @RndNum =
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
+substring(@s,cast(rand()*10 as int)%10+1,1)
--select @RndNum
IF (not Exists(Select * From RandomCode where code=@RndNum))
begin
Insert Into RandomCode(code) values(@RndNum)
set @Count = @Count + 1
end
end
end
go
create table tb(ID int primary key)
go
if object_id('pro_insert')is not null
drop proc pro_insert
go
create proc pro_insert
@kss int,
@gs int
as
declare @i int
set @i=1
while @i<= @gs
begin
insert tb select @kss+@i where @kss+@i not in (select id from tb)
set @i=@i+1
end
go
exec pro_insert 1000,3
select * from tb
/*ID
-----------
1001
1002
1003(影響 3 個資料列)*/