不使用 SQL 游标插入行和更新表
我正在尝试创建一个 MS SQL 2005 存储过程来为订单中的特定组件分配仓库中的库存。有多个批次的可用库存,并且必须按特定顺序使用。我可以循环遍历可用库存并进行分配,直到订单完成,但我试图更多地考虑基于集合而不是顺序,并避免使用游标。
这是我的查询,用于获取特定订单组件可用的仓库库存;
SELECT
STOCK.ComponentId,
STOCK.StockId,
STOCK.ExpiryDate,
STOCK.BatchNo,
STOCK.StockQty,
ORDER_ITEMS.OrderQty
FROM
STOCK
JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId
WHERE
STOCK.WarehouseId = @WarehouseId
AND STOCK.StockQty > 0
AND ORDER_ITEMS.OrderItemId = @OrderItemId
我已将其放入临时表中或使用查询创建 CTE 并应用 ORDER BY 子句来对需要用完的库存进行排序。这给了我一个如下的结果集:
ComponentId | StockId | ExpiryDate | BatchNo | StockQty | OrderQty
-------------------------------------------------------------------
359 | 3107 | 2013-10-01 | 132435-1 | 20 | 50
359 | 3215 | 2013-10-01 | 154558-1 | 100 | 50
359 | 3216 | 2014-01-01 | 154689-1 | 100 | 50
我需要做的是使用满足订单数量所需的尽可能多的库存批次将记录插入到 STOCK_ALLOCATED
表中。在上面的示例中,我将使用第一行中的全部 20 个,然后需要第二行中的 30 个。
这需要将两条记录插入到 STOCK_ALLOCATED
表中,其中包含两个已用批次的 OrderItemId
、StockId
以及数量(20 和 30)并适当减少STOCK
表中的库存数量。
假设必要的事务已到位以一致地维护库存表,是否有一种方法可以在不使用游标循环和跟踪我已经分配了多少库存以及我还需要多少库存的情况下进行插入和更新?
I'm trying to create a MS SQL 2005 stored procedure to allocate stock from a warehouse for a particular component in an order. There are multiple batches of stock available and these have to be used in a particular order. I can loop through the available stock and allocate until the order is fulfilled but I'm trying to think more set based than sequential and avoid using a CURSOR.
Here is my query that gets the warehouse stock available for a particular order component;
SELECT
STOCK.ComponentId,
STOCK.StockId,
STOCK.ExpiryDate,
STOCK.BatchNo,
STOCK.StockQty,
ORDER_ITEMS.OrderQty
FROM
STOCK
JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId
WHERE
STOCK.WarehouseId = @WarehouseId
AND STOCK.StockQty > 0
AND ORDER_ITEMS.OrderItemId = @OrderItemId
I've been putting this into a temp table or creating a CTE with the query and applying an ORDER BY clause to get the stock sorted as it needs to be used up. This gives me a result set like:
ComponentId | StockId | ExpiryDate | BatchNo | StockQty | OrderQty
-------------------------------------------------------------------
359 | 3107 | 2013-10-01 | 132435-1 | 20 | 50
359 | 3215 | 2013-10-01 | 154558-1 | 100 | 50
359 | 3216 | 2014-01-01 | 154689-1 | 100 | 50
What I need to do is insert records into a STOCK_ALLOCATED
table using as many of the batches of stock as necessary to fulfil the Order quantity. In the example above I would use all 20 from the first row and then need 30 from the second row.
This would need to insert two records into a STOCK_ALLOCATED
table with the OrderItemId
, StockId
and the quantity (20 and 30) for the two used batches and also decrease the stock quantity in the STOCK
table appropriately.
Assuming that the necessary transactions are in place to maintain the stock tables consistently is there a way to do the inserts and updates without using a CURSOR to loop through and track how much stock I've already allocated and how much I still require?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这是一个适合您的测试示例:
基本上,您需要将自己获得的结果结合起来,以便您可以将先前保留的数量相加,然后从 StockQty 中减去该数量以确定您还需要哪些数量需要满足。
不过,为了做到这一点,您将需要一个唯一的 OrderByField,以便您可以准确地过滤掉以前的 StockQty 值。如果您没有可以使用的存储值,则可以使用
ROW_NUMBER() OVER
派生它。如果是这样,请告诉我您是否需要帮助。This is a test sample that will work for you:
Basically, you will need to join the results you are getting on itself, so that you can sum the quantities of the previous reserved quantities, and subtract this from the StockQty to determine what you still need to fulfill.
In order to do that, though, you will need a unique OrderByField so that you can accurately filter out the previous StockQty values. If you don't have a stored value you can use, you can derive it using
ROW_NUMBER() OVER
. If that's the case, let me know if you need help with that.