--取得随机数的视图 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
--*/