数据库自动生成自定义订单号

SQL  Server数据库自动生成自定义订单号

代码

/* 以下代码生成的编号长度为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中的模块自定义函数)

代码

' 生成订单序号---格式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');

 

转载于:https://www.cnblogs.com/raychn/archive/2010/07/23/1783708.html