[SQLServer]ストアドプロシージャの作成_3

0. はじめに

基本的なMRP(Material Requirements Planning)のストアドプロシージャのサンプルです。
この例では、製品構成表（BOM: Bill of Materials）と在庫情報を使って、必要な部品の発注量を計算します。

1. サンプルテーブルの作成

CREATE_TABLE_BOM.sql

-- 製品構成表（BOM: Bill of Materials）
CREATE TABLE BOM (
    ProductID INT,                  -- 製品ID
    ComponentID INT,                -- 部品ID
    QuantityRequired DECIMAL(10, 2) -- 必要数量
);

-- 在庫情報
CREATE TABLE Inventory (
    ComponentID INT PRIMARY KEY,    -- 部品ID
    QuantityOnHand DECIMAL(10, 2)   -- 在庫数量
);

-- 注文情報
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY, -- 注文ID
    ProductID INT,                         -- 製品ID
    QuantityOrdered DECIMAL(10, 2),        -- 注文数量
    OrderDate DATE                         -- 注文日
);

2. サンプルデータの挿入

INSERT_BOM.sql

-- 製品構成表（BOM）のデータ
INSERT INTO BOM (ProductID, ComponentID, QuantityRequired) VALUES (1, 101, 2);
INSERT INTO BOM (ProductID, ComponentID, QuantityRequired) VALUES (1, 102, 1);
INSERT INTO BOM (ProductID, ComponentID, QuantityRequired) VALUES (2, 101, 3);
INSERT INTO BOM (ProductID, ComponentID, QuantityRequired) VALUES (2, 103, 2);

-- 在庫情報のデータ
INSERT INTO Inventory (ComponentID, QuantityOnHand) VALUES (101, 5);
INSERT INTO Inventory (ComponentID, QuantityOnHand) VALUES (102, 2);
INSERT INTO Inventory (ComponentID, QuantityOnHand) VALUES (103, 1);

-- 注文情報のデータ
INSERT INTO Orders (ProductID, QuantityOrdered, OrderDate) VALUES (1, 10, '2024-06-01');
INSERT INTO Orders (ProductID, QuantityOrdered, OrderDate) VALUES (2, 5, '2024-06-02');

3. ストアドプロシージャの作成

CREATE_PROCEDURE_usp_CalculateMRP.sql

CREATE PROCEDURE usp_CalculateMRP
AS
BEGIN
    SET NOCOUNT ON;

    -- テンポラリテーブルの作成
    CREATE TABLE #MRPResults (
        ComponentID INT,                -- 部品ID
        QuantityRequired DECIMAL(10, 2),-- 必要数量
        QuantityOnHand DECIMAL(10, 2),  -- 在庫数量
        QuantityToOrder DECIMAL(10, 2)  -- 発注数量
    );

    -- MRP計算
    INSERT INTO #MRPResults (ComponentID, QuantityRequired, QuantityOnHand, QuantityToOrder)
    SELECT 
        bom.ComponentID, 
        SUM(o.QuantityOrdered * bom.QuantityRequired) AS QuantityRequired, 
        ISNULL(i.QuantityOnHand, 0) AS QuantityOnHand, 
        CASE 
            WHEN SUM(o.QuantityOrdered * bom.QuantityRequired) > ISNULL(i.QuantityOnHand, 0) 
            THEN SUM(o.QuantityOrdered * bom.QuantityRequired) - ISNULL(i.QuantityOnHand, 0) 
            ELSE 0 
        END AS QuantityToOrder
    FROM 
        Orders o
        JOIN BOM bom ON o.ProductID = bom.ProductID
        LEFT JOIN Inventory i ON bom.ComponentID = i.ComponentID
    GROUP BY 
        bom.ComponentID, 
        i.QuantityOnHand;

    -- 結果の表示
    SELECT * FROM #MRPResults;

    -- テンポラリテーブルの削除
    DROP TABLE #MRPResults;
END;

4. ストアドプロシージャの使用例

EXEC_usp_CalculateMRP.sql

-- MRPの計算を実行
EXEC usp_CalculateMRP;

実行結果

以下の結果が出力されます。

ComponentID	QuantityRequired	QuantityOnHand	QuantityToOrder
101	35.00	5.00	30.00
102	10.00	2.00	8.00
103	10.00	1.00	9.00