下一篇 » « 上一篇

小议主子表INT自增主键插入记录的方法

作者:    时间:2008-01-22    来源:    点击:34864    本文共1篇文章 字体:[ ]

小议主子表INT自增主键插入记录的方法

主子表最常见的大概就是用在进销存、MRP、ERP里面,比如一张销售订单,订单Order(ID,OrderDate),订单明细OrderDetail(OrderID, ProductID, Num,Price)这个大概就是最简单的主子表了,两个表通过ID与OrderID建立关联,这里主键ID是自增的INT类型,OrderID是表OrderDetail的外键。当然,键的选择方法很多,现在我们选择的是在sql里面最简单的方法。

www.444p.com版权所有

对于这样的表结构,我们最常见的问题就是保存的时候怎样处理键值的问题,因为两个表关联非常的紧密,我们进行保存的时候需要把它们放在一个事务里面,这时问题就会出现,Order表中的ID是自动增长型的字段。现在需要我们录入一张订单,包括在Order表中插入一条记录以及在OrderDetail表中插入若干条记录。因为Order表中的ID是自动增长型的字段,那么我们在记录正式插入到数据库之前无法事先得知它的取值,只有在更新后才能知道数据库为它分配的是什么值,然后再用这个ID作为OrderDetail表的OrderID的值,最后更新OderDetail表。但是,为了确保数据的一致性,Order与OrderDetail在更新时必须在事务保护下同时进行,即确保两表同时更行成功,这个就会有点困扰。

www.444p.com版权所有


解决这类问题常见的主要有两类方法:

php学习之家http://www.444p.com


一种是微软在网上书店里使用的方法,使用了四个存储过程。改装一下,使之符合现在的例子

php学习之家


--存储过程一

php学习之家http://www.444p.com


CREATE PROCEDURE InsertOrder php学习之家

www.444p.com php学习之家

@Id INT = NULL OUTPUT, 本文来自 www.444p.com

php学习之家http://www.444p.com

@OrderDate DATETIME = NULL,

本文来自 www.444p.com

本文来自 www.444p.com

@ProductIDList NVARCHAR(4000) = NULL, 本文来自 www.444p.com

www.444p.com

@NumList NVARCHAR(4000) = NULL, www.444p.com

php学习之家

@PriceList NVARCHAR(4000) = NULL

php学习之家

www.444p.com

AS 本文来自 www.444p.com

php学习之家

SET NOCOUNT ON 本文来自 www.444p.com

本文来自 www.444p.com

SET XACT_ABORT ON

www.444p.com

本文来自 www.444p.com

BEGIN TRANSACTION php学习之家http://www.444p.com

www.444p.com版权所有

--插入主表

www.444p.com版权所有

www.444p.com

INSERT Orders(OrderDate) select @OrderDate

php学习之家http://www.444p.com

www.444p.com

SELECT @Id = @@IDENTITY

php学习之家

php学习之家

-- 插入子表

www.444p.com

php学习之家http://www.444p.com

IF @ProductIDList IS NOT NULL

www.444p.com php学习之家

EXECUTE InsertOrderDetailsByList @Id, @ProductIdList, @numList, @PriceList php学习之家

www.444p.com

COMMIT TRANSACTION

php学习之家

RETURN 0 www.444p.com

php学习之家

--存储过程二

php学习之家

www.444p.com

CREATE PROCEDURE InsertOrderDetailsByList

www.444p.com

www.444p.com版权所有

@Id INT,

本文来自 www.444p.com

www.444p.com版权所有

@ProductIDList NVARCHAR(4000) = NULL, php学习之家http://www.444p.com

本文来自 www.444p.com

@NumList NVARCHAR(4000) = NULL, 本文来自 www.444p.com

php学习之家

@PriceList NVARCHAR(4000) = NULL www.444p.com php学习之家

本文来自 www.444p.com

AS php学习之家

www.444p.com

SET NOCOUNT ON

www.444p.com

www.444p.com

DECLARE @Length INT

本文来自 www.444p.com

php学习之家http://www.444p.com

DECLARE @FirstProductIdWord NVARCHAR(4000)

php学习之家

本文来自 www.444p.com

DECLARE @FirstNumWord NVARCHAR(4000) www.444p.com php学习之家

www.444p.com

DECLARE @FirstPriceWord NVARCHAR(4000) php学习之家

php学习之家

DECLARE @ProductId INT www.444p.com版权所有

www.444p.com php学习之家

DECLARE @Num INT

php学习之家http://www.444p.com

DECLARE @Price MONEY php学习之家

php学习之家http://www.444p.com

SELECT @Length = DATALENGTH(@ProductIDList) www.444p.com

www.444p.com

WHILE @Length > 0

本文来自 www.444p.com

BEGIN php学习之家http://www.444p.com

www.444p.com版权所有

EXECUTE @Length = PopFirstWord @@ProductIDList OUTPUT, @FirstProductIdWord OUTPUT

php学习之家

php学习之家

EXECUTE PopFirstWord @NumList OUTPUT, @FirstNumWord OUTPUT www.444p.com

php学习之家

EXECUTE PopFirstWord @PriceList OUTPUT, @FirstPriceWord OUTPUT

www.444p.com

本文来自 www.444p.com

IF @Length > 0

www.444p.com

www.444p.com版权所有

BEGIN

php学习之家http://www.444p.com

SELECT @ProductId = CONVERT(INT, @FirstProductIdWord) 本文来自 www.444p.com

www.444p.com版权所有

SELECT @Num = CONVERT(INT, @FirstNumWord)

php学习之家

php学习之家

SELECT @Price = CONVERT(MONEY, @FirstPriceWord)

www.444p.com

www.444p.com

EXECUTE InsertOrderDetail @Id, @ProductId, @Price, @Num www.444p.com版权所有

END

本文来自 www.444p.com

END 本文来自 www.444p.com

php学习之家http://www.444p.com

--存储过程三

www.444p.com

本文来自 www.444p.com

CREATE PROCEDURE PopFirstWord

www.444p.com

本文来自 www.444p.com

@SourceString NVARCHAR(4000) = NULL OUTPUT,

php学习之家http://www.444p.com

php学习之家

@FirstWord NVARCHAR(4000) = NULL OUTPUT

www.444p.com

php学习之家

AS php学习之家

www.444p.com版权所有

SET NOCOUNT ON

本文来自 www.444p.com

php学习之家

DECLARE @Oldword NVARCHAR(4000) php学习之家

www.444p.com

DECLARE @Length INT www.444p.com版权所有

php学习之家

DECLARE @CommaLocation INT

www.444p.com

www.444p.com版权所有

SELECT @Oldword = @SourceString

www.444p.com版权所有

本文来自 www.444p.com

IF NOT @Oldword IS NULL

php学习之家

www.444p.com版权所有

BEGIN

www.444p.com

SELECT @CommaLocation = CHARINDEX(',',@Oldword)

www.444p.com版权所有

php学习之家http://www.444p.com

SELECT @Length = DATALENGTH(@Oldword) www.444p.com版权所有

php学习之家

IF @CommaLocation = 0

www.444p.com php学习之家

php学习之家

BEGIN php学习之家

www.444p.com

SELECT @FirstWord = @Oldword

www.444p.com

www.444p.com php学习之家

SELECT @SourceString = NULL

php学习之家

php学习之家

RETURN @Length

本文来自 www.444p.com

www.444p.com

END www.444p.com

本文来自 www.444p.com

SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)

php学习之家http://www.444p.com

php学习之家

SELECT @SourceString = SUBSTRING(@Oldword, @CommaLocation 1, @Length - @CommaLocation) www.444p.com php学习之家

www.444p.com版权所有

RETURN @Length - @CommaLocation www.444p.com版权所有

php学习之家

END www.444p.com版权所有

本文来自 www.444p.com

RETURN 0 php学习之家

www.444p.com

------------------------------------------------

www.444p.com php学习之家

--存储过程四

php学习之家

php学习之家http://www.444p.com

CREATE PROCEDURE InsertOrderDetail www.444p.com

@OrderId INT = NULL,

www.444p.com php学习之家

@ProductId INT = NULL,

php学习之家

@Price MONEY = NULL, www.444p.com版权所有

www.444p.com

@Num INT = NULL www.444p.com

AS

SET NOCOUNT ON www.444p.com

INSERT OrderDetail(OrderId,ProductId,Price,Num) php学习之家http://www.444p.com

SELECT @OrderId,@ProductId,@Price,@Num

www.444p.com版权所有

RETURN 0 本文来自 www.444p.com

插入时,传入的子表数据都是长度为4000的NVARCHAR类型,各个字段使用“,”分割,然后调用PopFirstWord分拆后分别调用InsertOrderDetail进行保存,因为在InsertOrder中进行了事务处理,数据的安全性也比较有保障,几个存储过程设计的精巧别致,很有意思,但是子表的几个数据大小不能超过4000字符,恐怕不大保险。

php学习之家

第二种方法是我比较常用的,为了方便,就不用存储过程了,这个例子用的是VB.NET。

‘处理数据的类 本文来自 www.444p.com

www.444p.com php学习之家

Public class DbTools 本文来自 www.444p.com

www.444p.com版权所有

private Const _IDENTITY_SQL As String = "SELECT @@IDENTITY AS ID"

php学习之家http://www.444p.com

www.444p.com

private Const _ID_FOR_REPLACE As String = "_ID_FOR_REPLACE" www.444p.com

php学习之家

‘对主子表插入记录

www.444p.com

www.444p.com版权所有

Public Function InsFatherSonRec(ByVal main_sql As String, ByVal ParamArray arParam() As String) As Integer www.444p.com

Dim conn As New SqlConnection(StrConn) php学习之家http://www.444p.com

Dim ID AS INTEGER www.444p.com

php学习之家

conn.Open()

www.444p.com版权所有

php学习之家http://www.444p.com

Dim trans As SqlTransaction = conn.BeginTransaction

www.444p.com版权所有

php学习之家http://www.444p.com

Try

本文来自 www.444p.com

www.444p.com版权所有

'主记录

php学习之家

php学习之家http://www.444p.com

myDBTools.SqlData.ExecuteNonQuery(trans, CommandType.Text, main_sql) www.444p.com php学习之家

www.444p.com版权所有

'返回新增ID号

www.444p.com版权所有

php学习之家

ID = myDBTools.SqlData.ExecuteScalar(trans, CommandType.Text, _IDENTITY_SQL)

www.444p.com

本文来自 www.444p.com

'从记录

php学习之家

www.444p.com

If Not arParam Is Nothing Then www.444p.com

For Each sql In arParam php学习之家

php学习之家http://www.444p.com

'将刚获得的ID号代入

www.444p.com

sql = sql.Replace(_ID_FOR_REPLACE, ID)

www.444p.com版权所有

www.444p.com版权所有

myDBTools.SqlData.ExecuteNonQuery(trans, CommandType.Text, sql) php学习之家http://www.444p.com

本文来自 www.444p.com

Next

php学习之家

www.444p.com

End If

本文来自 www.444p.com

php学习之家http://www.444p.com

trans.Commit()

www.444p.com

www.444p.com php学习之家

Catch e As Exception

php学习之家

php学习之家

trans.Rollback() php学习之家

www.444p.com

Finally www.444p.com php学习之家

php学习之家

conn.Close() www.444p.com

php学习之家http://www.444p.com

End Try www.444p.com php学习之家

本文来自 www.444p.com

Return ID 本文来自 www.444p.com

本文来自 www.444p.com

End Function www.444p.com php学习之家

End class

php学习之家

上面这段代码里有myDBTools,是对常见的数据库操作封装后的类,这个类对数据库进行直接的操作,有经验的.NET数据库程序员基本上都会有,一些著名的例子程序一般也都提供。

www.444p.com

本文来自 www.444p.com

上面的是通用部分,下面是对具体单据的操作 php学习之家

本文来自 www.444p.com

Publid class Order 本文来自 www.444p.com

php学习之家http://www.444p.com

Public _OrderDate as date ‘主表记录 本文来自 www.444p.com

Public ChildDt as datatable ‘子表记录,结构与OrderDetail一致

php学习之家

php学习之家http://www.444p.com

Public function Save() as integer

本文来自 www.444p.com

本文来自 www.444p.com

Dim str as string php学习之家

本文来自 www.444p.com

Dim i as integer php学习之家

www.444p.com

Dim arParam() As String

本文来自 www.444p.com

www.444p.com php学习之家

Dim str as string=”insert into Order(OrderDate) values(‘” & _OrderDate & “’)”

php学习之家

If not Childdt is nothing then

www.444p.com php学习之家

arParam = New String(ChildDT.Rows.Count - 1) {} 本文来自 www.444p.com

for i=0 to Childdt.rows.count-1

arparam(i)= ”insert into OrderDetail(OrderID,ProductID,Num,Price) Values(_ID_FOR_REPLACE,” & drow(“ProductID) & “,” & drow(“Num”) & “,” drow(“price”) & “)” www.444p.com版权所有

php学习之家

next i 本文来自 www.444p.com

End if

Return (new dbtools). InsFatherSonRec(str,arparam) www.444p.com

End class php学习之家


上面的两个例子为了方便解释,去掉了一些检验验证过程,有兴趣的朋友可以参照网上书店的例子研究第一种方法,或者根据自己的需要对第二种方法进行修改。

www.444p.com版权所有

责任编辑:semirock
发表评论
密码: (游客不需要密码)
记住我【Alt+S 或 Ctrl+Enter 快速提交】

搜索工具


《PHP与MYsql》点击排行