数据库自动生成自定义订单号
SQL Server数据库自动生成自定义订单号
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
/*以下代码生成的编号长度为14,前6位为日期信息,格式为YYYYMMDD,后6位为流水号。创建得到当前日期的视图,因为在用户定义函数不能使用不确定函数,而getdate就是不确定函数,必须建立一个视图才能获得getdate的值。*/
CREATE VIEW dbo.v_GetDate
AS
SELECT CONVERT(CHAR(8), GETDATE(), 112) AS dt
//创建SQL Server数据库中的自定义函数
CREATE FUNCTION f_NextBH()
RETURNS char(14)
AS
BEGIN
DECLARE @dt CHAR(8)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM test_orderid WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
//测试表中应用自定义函数
CREATE TABLE tb(
BH char(14) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
access数据库自动生成自定义订单号(access中的模块自定义函数)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
'生成订单序号---格式20090630000001(年月日+6位的流水号)
Function nextBH()
Dim strResult, temp
strResult = ""
temp = "000001"
Dim thedb As Database
Set thedb = DBEngine.Workspaces(0).Databases(0)
SQL1 = "select count(BH) AS bhCount from testOrderId WHERE BH like '" & Format(Date, "yyyymmdd") & "*'"
SQL2 = "select MAX(BH) AS MaxBH from testOrderId WHERE BH like '" & Format(Date, "yyyymmdd") & "*'"
Set rs1 = thedb.OpenRecordset(SQL1)
If Not rs1.EOF Then '先查找当天是否有流水号
If rs1.Fields("bhCount") > 0 Then
Set rs2 = thedb.OpenRecordset(SQL2)
If Not rs2.EOF Then '从数据库中得到当天的流水号的最大值
strResult = Format(Date, "yyyymmdd") & Right((CLng("1000001") + Right(temp, 6)), 6) '流水号加1后从右截取6位字符串
End If
Else
strResult = Format(Date, "yyyymmdd") & "000001"
End If
End If
nextBH = strResult
End Function
使用方法:
INSERT INTO testOrderId ( BH, description ) VALUES (nextBH(), '1118');