SO-43416924-SP-v1, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP PROCEDURE IF EXISTS `insert_inventory`;
DROP TABLE IF EXISTS `warehouse`, `inventory`;

CREATE TABLE `warehouse` (
    `bin` VARCHAR(6) NOT NULL,
    `qty` INT DEFAULT 1,
    PRIMARY KEY(`bin`)
);

CREATE TABLE `inventory` (
    `item` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10),
    PRIMARY KEY(`item`),
    UNIQUE KEY(`name`)
);

CREATE TRIGGER `trg_ai_inventory` AFTER INSERT ON `inventory`
FOR EACH ROW
    INSERT INTO `warehouse` (`bin`)
    VALUES (NEW.`item`);

CREATE PROCEDURE `insert_inventory`(`_name` VARCHAR(10))
BEGIN
    DECLARE `_item` INT DEFAULT (SELECT `item`
                                 FROM `inventory`
                                 WHERE `name` = `_name`);
    IF `_item` IS NULL THEN
        INSERT INTO `inventory` (`name`)
        VALUES (`_name`);
    ELSE
        UPDATE `warehouse`
        SET `qty` = `qty` + 1
        WHERE `bin` = `_item`;
    END IF;
END\\

CALL `insert_inventory`('hammer');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

CALL `insert_inventory`('hammer');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

CALL `insert_inventory`('pliers');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

DROP PROCEDURE IF EXISTS `insert_inventory`;
DROP TABLE IF EXISTS `warehouse`, `inventory`;


absolute service time: 0,38 sec