首页 学习学习我爱学习 数据库

# 数据说明

  • 商品表（ ProductProduct ）
    • productId，charproductId，char，商品编号
    • productName，varcharproductName，varchar，商品名称
    • model，varcharmodel，varchar，型号
    • unit，varcharunit，varchar，计量单位
    • stockNum，intstockNum，int，库存数量
  • 员工表（ WorkerWorker ）
    • workerNo，charworkerNo，char，员工编号
    • workerName，varcharworkerName，varchar，员工姓名
    • gender，chargender，char，性别
    • phone，charphone，char，电话
    • livePlace，varcharlivePlace，varchar，住址
  • 供货商表（ SupplierSupplier ）
    • supplyNo，charsupplyNo，char，供货商编号
    • supplyName，varcharsupplyName，varchar，供货商姓名
    • phone，charphone，char，电话
    • address，varcharaddress，varchar，地址
  • 采购表（ PurchasePurchase ）
    • purchaseNo，charpurchaseNo，char，采购单编号
    • purchaseDate，datepurchaseDate，date，采购日期
    • money，decimalmoney，decimal，采购金额
    • supplyNo，charsupplyNo，char，供货商编号
    • workerNo，charworkerNo，char，员工编号
  • 采购明细表（ Purchase_detailedPurchase\_detailed）
    • purchaseNo，charpurchaseNo，char，采购单编号
    • productNo，charproductNo，char，商品编号
    • quantity，decimalquantity，decimal，采购数量
    • unitPrice，decimalunitPrice，decimal，单价

# 建库与建表

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 建库
CREATE DATABASES SellDb;

-- 建表
CREATE TABLE PurchaseOrder (
  purchaseId CHAR(10) PRIMARY KEY, -- 主码约束
  FOREIGN KEY (supplyNo) REFERENCES supplier(supplyNo), -- 外码约束
  FOREIGN KEY (workerNo) REFERENCES worker(workerNo), -- 外码约束
  
  purchaseDate DATE NULL,
  money DECIMAL(10, 2) NULL,
  
  CHECK (
    purchaseId LIKE 'P___________'
    AND SUBSTRING(purchaseId, 2, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    AND SUBSTRING(purchaseId, 10, 3) LIKE '[0-9][0-9][0-9]'
  )
);

# 存储过程

请你创建存储过 querySupply ，输入一个供货商编号，按照如下格式输出供货商名称，供货总金额，以及该供货商供应的每一个采购单及其采购明确信息，要求用游标实现输出

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
/*
=====================================================================
供货商名称：xxxxx                    供货总金额：xxxxxx
=====================================================================
采购单编号          商品名称                 数量            单价        
---------------------------------------------------------------------   
xxxxxxxxxxxx        xxxxxxxxx                xxxxx          xxxxxxx
......
---------------------------------------------------------------------   
*/

-- 存储过程
CREATE PROCEDURE querySupplier(@supplyNo CHAR(8))
AS
BEGIN
    DECLARE 
        @supplyName VARCHAR(100),
        @totalMoney DECIMAL(18, 2),
        @purchaseNo CHAR(20),
        @productNo CHAR(20),
        @quantity DECIMAL(10, 2),
        @unitPrice DECIMAL(10, 2),
        @productName VARCHAR(100)

    -- 获取供货商名称
    SELECT @supplyName = supplyName 
    FROM Supplier 
    WHERE supplyNo = @supplyNo

    -- 计算供货总金额
    SELECT @totalMoney = ISNULL(SUM(money), 0)
    FROM Purchase
    WHERE supplyNo = @supplyNo

    -- 输出供货商信息
    PRINT '============================================================'
    PRINT '供货商名称：' + @supplyName + '    供货总金额：' + CAST(@totalMoney AS VARCHAR)
    PRINT '============================================================'
    PRINT '采购单编号     商品名称     数量     单价'
    PRINT '------------------------------------------------------------'
    
    -- 游标：获取所有该供货商的采购单中的采购明细
    DECLARE cur CURSOR FOR
        SELECT purchaseNo, productNo, quantity, unitPrice
        FROM Purchase_detailed
        WHERE purchaseNo IN (
            SELECT purchaseNo
            FROM Purchase
            WHERE supplyNo = @supplyNo
        )

    OPEN cur
    FETCH NEXT FROM cur INTO @purchaseNo, @productNo, @quantity, @unitPrice

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 获取商品名称（不使用 JOIN）
        SELECT @productName = productName 
        FROM Product 
        WHERE productId = @productNo

        -- 输出明细数据，字段间用5个空格分隔
        PRINT @purchaseNo + '     ' + @productName + '     ' + 
              CAST(@quantity AS VARCHAR) + '     ' + 
              CAST(@unitPrice AS VARCHAR)

        FETCH NEXT FROM cur INTO @purchaseNo, @productNo, @quantity, @unitPrice
    END

    CLOSE cur
    DEALLOCATE cur

    PRINT '------------------------------------------------------------'
END

# 触发器

请你设计并实现一个针对采购明细表 Purchase_detailed 的更新触发器，名为 trgUpdate 。该触发器应在任何采购明细记录的数量发生变更时自动触发，确保商品表 Product 中对应商品的库存数量 stockNum 与采购明细中的采购数量保持实时同步。
具体来说，触发器需要比较更新前后的采购数量差值，然后将这个差值加到商品的当前库存数量中，实现库存的动态调整。为了处理批量更新的情况，触发器必须使用游标逐条遍历 inserted 和 deleted 两个系统表中的数据，分别代表更新后的新数据和更新前的旧数据，通过匹配采购单编号和商品编号的方式对应同一条记录，计算数量差并更新库存。

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE TRIGGER trgUpdate
ON Purchase_detailed
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @purchaseNo CHAR(20),
        @productNo CHAR(20),
        @oldQty DECIMAL(10, 2),
        @newQty DECIMAL(10, 2),
        @deltaQty DECIMAL(10, 2)

    -- 游标：逐行处理 inserted 和 deleted 中对应记录
    DECLARE cur CURSOR FOR
        SELECT i.purchaseNo, i.productNo, i.quantity, d.quantity
        FROM inserted i, deleted d
        WHERE i.purchaseNo = d.purchaseNo AND i.productNo = d.productNo

    OPEN cur
    FETCH NEXT FROM cur INTO @purchaseNo, @productNo, @newQty, @oldQty

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @deltaQty = @newQty - @oldQty

        -- 更新 Product 库存
        UPDATE Product
        SET stockNum = stockNum + @deltaQty
        WHERE productId = @productNo

        FETCH NEXT FROM cur INTO @purchaseNo, @productNo, @newQty, @oldQty
    END

    CLOSE cur
    DEALLOCATE cur
END