|
一、修改标识值
------示例
CREATE TABLE t1(ID int IDENTITY,A int)
INSERT t1 VALUES(1)
INSERT t1 VALUES(2)
INSERT t1 VALUES(3)
DELETE FROM t1 WHERE A=2
GO
------将ID=3的记录的ID值改为2
SET IDENTITY_INSERT t1 ON
INSERT t1(ID,A) SELECT 2,A FROM t1 WHERE ID=3
DELETE FROM t1 WHERE ID=3
SET IDENTITY_INSERT t1 OFF
SELECT * FROM t1
-------------结果
ID????????? A
-----------------
1?????????? 1
2?????????? 3
二、流水号
----------生成流水号
----------创建测试表
create table test(id varchar(18),? --流水号,日期(8位)+时间(4位)+流水号(4位)
?name varchar(10)? --其他字段
)
go
----------创建生成流水号的触发器
create trigger t_insert on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
?set @id1=0
else
?set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
?,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go
-------插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'
--------修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'
---------显示测试结果
select * from test
---------删除测试环境
drop table test
------------测试结果
id???????????????? name??????
------------------ ----------
2004022720430001?? aa
2004022720430002?? bb
2004022720430003?? cc
2004022720430004?? aa
2004022720430005?? bb
2004022720430006?? cc
（所影响的行数为 6 行）
?
三、查表法生成流水号
--------下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
----------得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
?RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
-------在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
---------插入资料
BEGIN TRAN
?INSERT tb(col) VALUES(1)
?INSERT tb(col) VALUES(2)
?INSERT tb(col) VALUES(3)
?DELETE tb WHERE col=3
?INSERT tb(col) VALUES(4)
?INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN
---------显示结果
SELECT * FROM tb
-------结果
BH???????? col
---------------- -----------
BH000001? 1
BH000002? 2
BH000003? 4
BH000004? 14
?
四、生成纯数字随机编号
---------取得随机数的视图
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=10
lb_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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
