时间戳

首页 > 解决方案 > 用于向表中添加新行的存储过程，它检查值以验证它们在外部表上的值

mysql - 用于向表中添加新行的存储过程，它检查值以验证它们在外部表上的值

问题描述

我已经为葡萄酒爱好者收藏创建了一个基本数据库。我正在尝试编写核心存储过程，即将葡萄酒添加到 Wine 表中。

鉴于 Wine 表中的许多值都有外键约束，我需要编写一个过程来完成这些任务：

/* 检查类型和/或葡萄是否已经在各自的表中。如果不添加到 Type 和/或 Grape */

/* 大多数人在进入存储位置时，不会知道 locationID，他们会知道 rack number 和 cellID。所以它需要检查机架号和cellID，并返回LocationID assoc。用它。此外，将该位置的布尔 StorageLocation.Empty 更新为 false。*/

/* 大多数人不会知道 vintnerID，他们只会知道 vintnerName。因此，如果它存在于 Vintner 表中，则返回 ID assoc。用它，如果不将它添加到 Vintner 并返回分配给它的 ID */

/* 大多数人不知道 OriginID，他们会知道国家，也许还有地区。所以它需要检查它是否存在于 Origin 表中并返回 ID assoc。用它，如果不将它添加到 Origin 并返回分配给它的 ID*/

这些是我对这个问题的主要目标。任何愿意花时间帮助我的人都非常感谢任何见解和建议！这是我的第一个数据库，所以我确信存在明显的结构问题，我只是希望你能解释所需的逻辑以及如何去做，我能弄清楚。再次感谢你！

复制并粘贴以下所有内容：

DROP DATABASE IF EXISTS wino;

CREATE DATABASE IF NOT EXISTS wino CHARSET utf8 COLLATE utf8_unicode_ci;

USE wino;

CREATE TABLE Member
(
    MemberID        INT             UNIQUE AUTO_INCREMENT       PRIMARY KEY,
    FirstName       varchar(25)     NOT NULL,
    LastName        varchar(30)     NOT NULL,
    Address         varchar(50)     NULL,
    City            varchar(50)     NULL,
    StateID         varchar(2)      NULL,
    Zip             varchar(15)     NOT NULL,
    Phone           varchar(15)     NOT NULL
);

INSERT INTO MEMBER
    (FirstName, LastName, Address, City, StateID, Zip, Phone)
VALUES('Guy', 'Faux', '1542 5th St', 'Monte Cristo', NULL, '8204931', '2-424-515-2412');

CREATE TABLE StorageLocation
(
    LocationID      INT             AUTO_INCREMENT  PRIMARY KEY,
    LocationName    varchar(30)     NOT NULL,
    RackNumber      varchar(5)      NOT NULL,
    CellID          varchar(5)      NOT NULL,
    Empty           Boolean         DEFAULT     TRUE
);

CREATE UNIQUE INDEX rackNumberCellID
    ON StorageLocation (RackNumber, CellID);
ALTER TABLE StorageLocation
    ADD CONSTRAINT uq_LocationID UNIQUE(LocationName, RackNumber, cellID);

INSERT INTO StorageLocation
    (LocationName, RackNumber, cellID)
VALUES
    ('Main','1', 'A1'),
    ('Main','1', 'A2'),
    ('Main','1', 'A3'),
    ('Main','1', 'A4'),
    ('Main','1', 'A5'),
    ('Main','1', 'B1'),
    ('Main','1', 'B2'),
    ('Main','1', 'B3'),
    ('Main','1', 'B4'),
    ('Main','1', 'B5'),
    ('Main','1', 'C1'),
    ('Main','1', 'C2'),
    ('Main','1', 'C3'),
    ('Main','1', 'C4'),
    ('Main','1', 'C5'),
    ('Main','1', 'D1'),
    ('Main','1', 'D2'),
    ('Main','1', 'D3'),
    ('Main','1', 'D4'),
    ('Main','1', 'D5'),
    ('Main','1', 'E1'),
    ('Main','1', 'E2'),
    ('Main','1', 'E3'),
    ('Main','1', 'E4'),
    ('Main','1', 'E5'),
    ('Main','1', 'F1'),
    ('Main','1', 'F2'),
    ('Main','1', 'F3'),
    ('Main','1', 'F4'),
    ('Main','1', 'F5'),
    ('Main','2', 'A1'),
    ('Main','2', 'A2'),
    ('Main','2', 'A3'),
    ('Main','2', 'A4'),
    ('Main','2', 'A5'),
    ('Main','2', 'A6'),
    ('Main','2', 'A7'),
    ('Main','2', 'A8'),
    ('Main','2', 'A9'),
    ('Main','2', 'B1'),
    ('Main','2', 'B2'),
    ('Main','2', 'B3'),
    ('Main','2', 'B4'),
    ('Main','2', 'B5'),
    ('Main','2', 'B6'),
    ('Main','2', 'B7'),
    ('Main','2', 'B8'),
    ('Main','2', 'B9'),
    ('Main','2', 'C1'),
    ('Main','2', 'C2'),
    ('Main','2', 'C3'),
    ('Main','2', 'C4'),
    ('Main','2', 'C5'),
    ('Main','2', 'C6'),
    ('Main','2', 'C7'),
    ('Main','2', 'C8'),
    ('Main','2', 'C9'),
    ('Main','2', 'D1'),
    ('Main','2', 'D2'),
    ('Main','2', 'D3'),
    ('Main','2', 'D4'),
    ('Main','2', 'D5'),
    ('Main','2', 'D6'),
    ('Main','2', 'D7'),
    ('Main','2', 'D8'),
    ('Main','2', 'D9'),
    ('Main','2', 'E1'),
    ('Main','2', 'E2'),
    ('Main','2', 'E3'),
    ('Main','2', 'E4'),
    ('Main','2', 'E5'),
    ('Main','2', 'E6'),
    ('Main','2', 'E7'),
    ('Main','2', 'E8'),
    ('Main','2', 'E9'),
    ('Main','2', 'F1'),
    ('Main','2', 'F2'),
    ('Main','2', 'F3'),
    ('Main','2', 'F4'),
    ('Main','2', 'F5'),
    ('Main','2', 'F6'),
    ('Main','2', 'F7'),
    ('Main','2', 'F8'),
    ('Main','2', 'F9'),
    ('Main','2', 'G1'),
    ('Main','2', 'G2'),
    ('Main','2', 'G3'),
    ('Main','2', 'G4'),
    ('Main','2', 'G5'),
    ('Main','2', 'G6'),
    ('Main','2', 'G7'),
    ('Main','2', 'G8'),
    ('Main','2', 'G9'),
    ('Main','2', 'H1'),
    ('Main','2', 'H2'),
    ('Main','2', 'H3'),
    ('Main','2', 'H4'),
    ('Main','2', 'H5'),
    ('Main','2', 'H6'),
    ('Main','2', 'H7'),
    ('Main','2', 'H8'),
    ('Main','2', 'H9'),
    ('Main','2', 'I1'),
    ('Main','2', 'I2'),
    ('Main','2', 'I3'),
    ('Main','2', 'I4'),
    ('Main','2', 'I5'),
    ('Main','2', 'I6'),
    ('Main','2', 'I7'),
    ('Main','2', 'I8'),
    ('Main','2', 'I9'),
    ('Main','2', 'J1'),
    ('Main','2', 'J2'),
    ('Main','2', 'J3'),
    ('Main','2', 'J4'),
    ('Main','2', 'J5'),
    ('Main','2', 'J6'),
    ('Main','2', 'J7'),
    ('Main','2', 'J8'),
    ('Main','2', 'J9'),
    ('Main','3', 'A1'),
    ('Main','3', 'A2'),
    ('Main','3', 'A3'),
    ('Main','3', 'A4'),
    ('Main','3', 'A5'),
    ('Main','3', 'A6'),
    ('Main','3', 'A7'),
    ('Main','3', 'A8'),
    ('Main','3', 'A9'),
    ('Main','3', 'B1'),
    ('Main','3', 'B2'),
    ('Main','3', 'B3'),
    ('Main','3', 'B4'),
    ('Main','3', 'B5'),
    ('Main','3', 'B6'),
    ('Main','3', 'B7'),
    ('Main','3', 'B8'),
    ('Main','3', 'B9'),
    ('Main','3', 'C1'),
    ('Main','3', 'C2'),
    ('Main','3', 'C3'),
    ('Main','3', 'C4'),
    ('Main','3', 'C5'),
    ('Main','3', 'C6'),
    ('Main','3', 'C7'),
    ('Main','3', 'C8'),
    ('Main','3', 'C9'),
    ('Main','3', 'D1'),
    ('Main','3', 'D2'),
    ('Main','3', 'D3'),
    ('Main','3', 'D4'),
    ('Main','3', 'D5'),
    ('Main','3', 'D6'),
    ('Main','3', 'D7'),
    ('Main','3', 'D8'),
    ('Main','3', 'D9'),
    ('Main','3', 'E1'),
    ('Main','3', 'E2'),
    ('Main','3', 'E3'),
    ('Main','3', 'E4'),
    ('Main','3', 'E5'),
    ('Main','3', 'E6'),
    ('Main','3', 'E7'),
    ('Main','3', 'E8'),
    ('Main','3', 'E9'),
    ('Main','3', 'F1'),
    ('Main','3', 'F2'),
    ('Main','3', 'F3'),
    ('Main','3', 'F4'),
    ('Main','3', 'F5'),
    ('Main','3', 'F6'),
    ('Main','3', 'F7'),
    ('Main','3', 'F8'),
    ('Main','3', 'F9'),
    ('Main','3', 'G1'),
    ('Main','3', 'G2'),
    ('Main','3', 'G3'),
    ('Main','3', 'G4'),
    ('Main','3', 'G5'),
    ('Main','3', 'G6'),
    ('Main','3', 'G7'),
    ('Main','3', 'G8'),
    ('Main','3', 'G9'),
    ('Main','3', 'H1'),
    ('Main','3', 'H2'),
    ('Main','3', 'H3'),
    ('Main','3', 'H4'),
    ('Main','3', 'H5'),
    ('Main','3', 'H6'),
    ('Main','3', 'H7'),
    ('Main','3', 'H8'),
    ('Main','3', 'H9'),
    ('Main','3', 'I1'),
    ('Main','3', 'I2'),
    ('Main','3', 'I3'),
    ('Main','3', 'I4'),
    ('Main','3', 'I5'),
    ('Main','3', 'I6'),
    ('Main','3', 'I7'),
    ('Main','3', 'I8'),
    ('Main','3', 'I9'),
    ('Main','3', 'J1'),
    ('Main','3', 'J2'),
    ('Main','3', 'J3'),
    ('Main','3', 'J4'),
    ('Main','3', 'J5'),
    ('Main','3', 'J6'),
    ('Main','3', 'J7'),
    ('Main','3', 'J8'),
    ('Main','3', 'J9'),
    ('Main','4', 'A1'),
    ('Main','4', 'A2'),
    ('Main','4', 'A3'),
    ('Main','4', 'A4'),
    ('Main','4', 'A5'),
    ('Main','4', 'A6'),
    ('Main','4', 'A7'),
    ('Main','4', 'A8'),
    ('Main','4', 'A9'),
    ('Main','4', 'B1'),
    ('Main','4', 'B2'),
    ('Main','4', 'B3'),
    ('Main','4', 'B4'),
    ('Main','4', 'B5'),
    ('Main','4', 'B6'),
    ('Main','4', 'B7'),
    ('Main','4', 'B8'),
    ('Main','4', 'B9'),
    ('Main','4', 'C1'),
    ('Main','4', 'C2'),
    ('Main','4', 'C3'),
    ('Main','4', 'C4'),
    ('Main','4', 'C5'),
    ('Main','4', 'C6'),
    ('Main','4', 'C7'),
    ('Main','4', 'C8'),
    ('Main','4', 'C9'),
    ('Main','4', 'D1'),
    ('Main','4', 'D2'),
    ('Main','4', 'D3'),
    ('Main','4', 'D4'),
    ('Main','4', 'D5'),
    ('Main','4', 'D6'),
    ('Main','4', 'D7'),
    ('Main','4', 'D8'),
    ('Main','4', 'D9'),
    ('Main','4', 'E1'),
    ('Main','4', 'E2'),
    ('Main','4', 'E3'),
    ('Main','4', 'E4'),
    ('Main','4', 'E5'),
    ('Main','4', 'E6'),
    ('Main','4', 'E7'),
    ('Main','4', 'E8'),
    ('Main','4', 'E9'),
    ('Main','4', 'F1'),
    ('Main','4', 'F2'),
    ('Main','4', 'F3'),
    ('Main','4', 'F4'),
    ('Main','4', 'F5'),
    ('Main','4', 'F6'),
    ('Main','4', 'F7'),
    ('Main','4', 'F8'),
    ('Main','4', 'F9'),
    ('Main','4', 'G1'),
    ('Main','4', 'G2'),
    ('Main','4', 'G3'),
    ('Main','4', 'G4'),
    ('Main','4', 'G5'),
    ('Main','4', 'G6'),
    ('Main','4', 'G7'),
    ('Main','4', 'G8'),
    ('Main','4', 'G9'),
    ('Main','4', 'H1'),
    ('Main','4', 'H2'),
    ('Main','4', 'H3'),
    ('Main','4', 'H4'),
    ('Main','4', 'H5'),
    ('Main','4', 'H6'),
    ('Main','4', 'H7'),
    ('Main','4', 'H8'),
    ('Main','4', 'H9');



CREATE TABLE Origin
(
    OriginID                INT             UNIQUE AUTO_INCREMENT       PRIMARY KEY,
    Country                 varchar(30)     NOT NULL,
    StateProvince           varchar(30)     NULL,
    Region                  varchar(50)     NULL,
    Vineyard                varchar(50)     NULL
);
ALTER TABLE Origin
    ADD CONSTRAINT uq_OriginID UNIQUE (Country, StateProvince, Region, Vineyard);

INSERT INTO Origin
    (Country, StateProvince, Region, Vineyard)
VALUES
    ('United States', 'CA', 'Santa Rita Hills', 'Fiddlestix'),
    ('Spain', 'Northern Spain', 'Rioja', 'Campo Viejo'),
    ('United States', 'CA', 'Adelaida', 'Tablas Creek'),
    ('United States', 'CA', 'Napa Valley Carneros', 'M2 Carneros'),
    ('United States','CA', 'Napa Valley', NULL),
    ('United States','CA', 'Lodi Mokelumne River', 'Maley-Wegat'),
    ('Italy', 'Tuscany',NULL, NULL),
    ('Italy', 'Tuscany','Cortona', NULL),
    ('Spain', NULL,'Burgos', NULL),
    ('Italy', NULL, 'Montalcino', NULL);

CREATE TABLE Vintner
(
    VintnerID       INT             UNIQUE AUTO_INCREMENT       PRIMARY KEY,
    VintnerName     varchar(50)     UNIQUE      NOT NULL,
    YearEstablished YEAR    NULL
);

INSERT INTO Vintner
    (VintnerName, YearEstablished)
VALUES
    ('Fess Parker', 2016),
    ('Campo Viejo', 1964),
    ('Tablas Creek', NULL),
    ('M2', 2006),
    ('Lindeman', NULL),
    ('Renwood', NULL),
    ('Emilio Lustau', NULL),
    ('Robert Biale', NULL),
    ('Carpineto',NULL),
    ('Santa Christina', 1946),
    ('Ribera del Duero', NULL), 
    ('Caparzo', NULL);

CREATE TABLE WineType
(
    TypeName        varchar(40)     UNIQUE      PRIMARY KEY
);

INSERT INTO WineType
    (TypeName)
VALUES
    ('Pinot Noir'),
    ('Red Blend'),
    ('Vermentino'),
    ('Cream Sherry'),
    ('Chardonnay'),
    ('Port'),
    ('Rose'),
    ('Chenin Blanc'),
    ('White Blend'),
    ('Savignon Blanc'),
    ('Pecorino'),
    ('Prosecco'),
    ('Voigner'),
    ('Syrah'),
    ('Shiraz'),
    ('Petite Syrah'),
    ('Zinfandel'),
    ('Toscano'),
    ('Tempranillo'),
    ('Sangiovese');

CREATE TABLE Grape
(
    GrapeName       varchar(40)     UNIQUE      PRIMARY KEY
);

INSERT INTO Grape
    (GrapeName)
VALUES
    ('Pinot Noir'),
    ('Tempranillo'),
    ('Vermentino'),
    ('Chardonnay'),
    ('Syrah'),
    ('Savignon Blanc'),
    ('Zinfandel'),
    ('Cabernet Sauvignon'),
    ('Voigner'),
    ('Grenache'),
    ('Sangiovese');

CREATE TABLE FoodType
(
    FoodType        varchar(40)     UNIQUE      PRIMARY KEY
);

INSERT INTO FoodType
    (FoodType)
VALUES
    ('Fish'),
    ('Beef'),
    ('Pork'),
    ('Chicken'),
    ('Duck'),
    ('Lamb'),
    ('Italian'),
    ('Indian'),
    ('Asian'),
    ('Cheese');


CREATE TABLE Food
(
    FoodName        varchar(40)     UNIQUE      PRIMARY KEY
);


CREATE TABLE Wine
(
    WineID          INT             UNIQUE AUTO_INCREMENT       PRIMARY KEY,
    MemberID        INT             NOT NULL,
    WineName        varchar(40)     NOT NULL,
    TypeName        varchar(40)     NOT NULL,
    GrapeName       varchar(40)     NULL,
    VintnerID       INT             NOT NULL,
    OriginID        INT             NOT NULL,
    LocationID      INT             NOT NULL,
    Vintage         YEAR            NULL,
    Rating          varchar(10)     NULL,
    BottleSize      varchar(15)     NULL    DEFAULT '750ml',
    ABV             FLOAT           NULL,
    Price           FLOAT           NULL,
    PurchasedFrom   varchar(30)     NULL,
    DatePurchased   DATE            NULL,
    DrinkByDate     DATE            NULL,
    TastingNotes    varchar(500)    NULL,
    Pairing         varchar(40)     NULL,
    CONSTRAINT fk_typeName foreign key (TypeName)
        REFERENCES WineType (typeName)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_grapeName foreign key (GrapeName)
        REFERENCES Grape (grapeName)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_vintnerID foreign key (VintnerID)
        REFERENCES Vintner (vintnerID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_originID foreign key (OriginID)
        REFERENCES Origin (originID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_locationID foreign key (LocationID)
        REFERENCES StorageLocation (LocationID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_Pairing foreign key (Pairing)
        REFERENCES FoodType (foodType)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_MemberID foreign key (MemberID)
        REFERENCES Member (memberID)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

INSERT INTO Wine
    (WineName, TypeName, GrapeName, VintnerID, OriginID, LocationID,
    Vintage, Rating, BottleSize, ABV, Price, PurchasedFrom, DatePurchased, 
    DrinkByDate, TastingNotes)
VALUES
    ('Fess Parker Pinot Noir', 'Pinot Noir', 'Pinot Noir', 1, 1, 214,
    2016, NULL, DEFAULT, 14.3, NULL, NULL, NULL, NULL, NULL),
    ('Campo Viejo Rioja', 'Red Blend', 'Tempranillo', 2, 2, 142,
    2013, NULL, DEFAULT, 13.5, 13, NULL, NULL, NULL, 'Aroma of wild cherries, pepper, and oak' ),
    ('Tablas Creek Vermentino', 'Vermentino', 'Vermentino', 3, 3, 119,
    2016, NULL, DEFAULT, 12.9, NULL, NULL, NULL, NULL, 
    'The 2016 Vermentino has a classic 
    Vermentino nose of grapefruit pith, citrus leaf, 
    fresh herbs and sea spray, but with an extra level 
    of creaminess beyond what we usually see, like key lime pie. 
    The palate is zingy with flavors of lemon, nectarine, 
    and a briny mineral note that comes out on the long, clean, bright finish. 
    Drink now and over the next few years.'),
    ('M2 Pinot Noir los carneros','Pinot Noir', 'Pinot Noir', 4, 4, 182, 
    2015, NULL, DEFAULT, 13.8, NULL, NULL, NULL, NULL, NULL),
    ('Black Chicken', 'Zinfandel', 'Zinfandel', 8, 5, 126,
    2015, NULL, DEFAULT, 14.8, 31, 'Vineria', '2018-05-01', NULL, NULL),
    ('M2 Zinfandel', 'Zinfandel', 'Zinfandel', 4, 6, 183,
    2015, NULL, DEFAULT, 14.6, 25, 'M2 Wines', NULL, '2020-05-01', 'Peppery'),
    ('Dogajolo', 'Toscano', 'Sangiovese', 9, 7, 269,
    2015, NULL, DEFAULT, 13, NULL, NULL, NULL, NULL, 
    'Fruity, intense, berries, coffee, vanilla and spice'),
    ('Santa Cristina Toscano', 'Toscano', 'Sangiovese', 10,8, 123,
    2016, NULL, DEFAULT, 13, 15, 'Binnys', '2018-06-01', '2020-01-01', 
    'Ripe berry fruit, floral notes'),
    ('Valderiz', 'Tempranillo', 'Tempranillo', 11, 9, 245,
    2014, NULL, DEFAULT, 15, NULL, NULL, NULL, NULL, NULL),
    ('Brunello di Montalcino', 'Sangiovese', 'Sangiovese', 12, 10, 261,
    2012, NULL, DEFAULT, 14, NULL, NULL, NULL, NULL, NULL);


/* Is there an easier way to update StorageLocation.Empty for every entry in Wine?  */

UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 214;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 142;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 119;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 182;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 126;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 183;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 269;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 123;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 245;
UPDATE StorageLocation
SET Empty = FALSE
WHERE LocationID = 261;

标签： mysqlsqlstored-proceduresrelational-database

解决方案

虽然很长，但您的帖子只包含一个问题：

/* 有没有更简单的方法来为 Wine 中的每个条目更新 StorageLocation.Empty？*/

是的，不要指定 where 子句：

UPDATE StorageLocation SET Empty = FALSE;

其他的东西是 stackoverflow 没有必要设计来帮助的。我们不会告诉您如何构建或实现整个应用程序功能，我们会帮助您克服在此过程中遇到的问题。也就是说，您拥有的指令非常具体，并且有助于告知您特定的编程模式。我将就如何在一般意义上实现所需功能提供一些指导，但大部分设计和实现选择是由您自己做出的。请记住，没有人在这里为您做功课，您应该始终尝试编写一些代码，然后将其发布给我们查看 - 它不仅表明愿意，而且有助于我们了解您是如何想一想，这对于掌握你正在苦苦挣扎的地方至关重要

作为一项学术练习，您被要求编写一个存储过程，该过程接收人类相关值并查找它们以将它们转换为数据库在维护其关系时使用的 ID 值

大多数人不会知道vintnerID，他们只会知道vintnerName。因此，如果它存在于 Vintner 表中，则返回 ID assoc。用它，如果不将它添加到 Vintner 并返回分配给它的 ID

暗示您的存储过程可能如下所示：

CREATE STORED PROCEDURE blah (
  blah parameters, 
  vintnerName VARCHAR,
  more blah parameters
)
AS
BEGIN

  blah code

  DECLARE vintID INT;
  SELECT ID INTO vintID FROM vintners WHERE name = vintnerName;

  IF vintID IS NULL THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Unknown vintner name';
  END IF;

  blahblah code...

END;

推荐阅读