Pastein: DROP DATABASE IF EXISTS `mebel_factory`; CREATE DATABASE `mebel_factory` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `mebel_factory`; SET FOREIGN_KEY

Загрузка данных
DROP DATABASE IF EXISTS `mebel_factory`;
CREATE DATABASE `mebel_factory`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

USE `mebel_factory`;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `позиции_заказа`;
DROP TABLE IF EXISTS `состав_изделия`;
DROP TABLE IF EXISTS `заказы`;
DROP TABLE IF EXISTS `изделия`;
DROP TABLE IF EXISTS `материалы`;

SET FOREIGN_KEY_CHECKS = 1;

-- =========================
-- ТАБЛИЦЫ
-- =========================

CREATE TABLE `заказы` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `номер` VARCHAR(20) NOT NULL,
  `дата` DATE NOT NULL,
  `статус` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_заказы_номер` (`номер`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `изделия` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `название` VARCHAR(100) NOT NULL,
  `тип` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `материалы` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `название` VARCHAR(100) NOT NULL,
  `цена` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `состав_изделия` (
  `изделие_id` INT NOT NULL,
  `материал_id` INT NOT NULL,
  `количество` INT NOT NULL,
  PRIMARY KEY (`изделие_id`, `материал_id`),
  CONSTRAINT `fk_состав_изделия_изделия`
    FOREIGN KEY (`изделие_id`) REFERENCES `изделия` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_состав_изделия_материалы`
    FOREIGN KEY (`материал_id`) REFERENCES `материалы` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `позиции_заказа` (
  `заказ_id` INT NOT NULL,
  `изделие_id` INT NOT NULL,
  `количество` INT NOT NULL,
  PRIMARY KEY (`заказ_id`, `изделие_id`),
  CONSTRAINT `fk_позиции_заказа_заказы`
    FOREIGN KEY (`заказ_id`) REFERENCES `заказы` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_позиции_заказа_изделия`
    FOREIGN KEY (`изделие_id`) REFERENCES `изделия` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =========================
-- ЗАПОЛНЕНИЕ ДАННЫМИ
-- =========================

INSERT INTO `материалы` (`id`, `название`, `цена`) VALUES
(1, 'Дерево', 500),
(2, 'Металл', 800),
(3, 'Ткань', 300);

INSERT INTO `изделия` (`id`, `название`, `тип`) VALUES
(1, 'Стол', 'Корпусная'),
(2, 'Стул', 'Мягкая'),
(3, 'Шкаф', 'Корпусная');

INSERT INTO `состав_изделия` (`изделие_id`, `материал_id`, `количество`) VALUES
(1, 1, 5),
(1, 2, 2),
(2, 1, 2),
(2, 3, 3),
(3, 1, 10);

INSERT INTO `заказы` (`id`, `номер`, `дата`, `статус`) VALUES
(1, 'ORD1', '2025-05-01', 'новый'),
(2, 'ORD2', '2025-05-02', 'в работе');

INSERT INTO `позиции_заказа` (`заказ_id`, `изделие_id`, `количество`) VALUES
(1, 1, 2),
(1, 2, 4),
(2, 3, 1);

-- =========================
-- ПРОЦЕДУРЫ
-- =========================

DELIMITER $$

-- 1. Проверка существования заказа
DROP PROCEDURE IF EXISTS `sp_check_order_exists` $$
CREATE PROCEDURE `sp_check_order_exists`(
    IN p_order_number VARCHAR(20)
)
BEGIN
    DECLARE v_cnt INT DEFAULT 0;

    SELECT COUNT(*) INTO v_cnt
    FROM `заказы`
    WHERE `номер` = p_order_number;

    IF v_cnt > 0 THEN
        SELECT CONCAT('Заказ ', p_order_number, ' найден') AS message;
    ELSE
        SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
    END IF;
END $$

-- 2. Создание нового заказа с текущей датой и статусом "новый"
DROP PROCEDURE IF EXISTS `sp_create_order` $$
CREATE PROCEDURE `sp_create_order`(
    IN p_order_number VARCHAR(20)
)
BEGIN
    DECLARE v_cnt INT DEFAULT 0;

    SELECT COUNT(*) INTO v_cnt
    FROM `заказы`
    WHERE `номер` = p_order_number;

    IF v_cnt > 0 THEN
        SELECT CONCAT('Заказ ', p_order_number, ' уже существует') AS message;
    ELSE
        INSERT INTO `заказы` (`номер`, `дата`, `статус`)
        VALUES (p_order_number, CURDATE(), 'новый');

        SELECT CONCAT('Заказ ', p_order_number, ' создан') AS message;
    END IF;
END $$

-- 3. Добавить изделие в заказ, только если заказ существует
DROP PROCEDURE IF EXISTS `sp_add_item_to_order` $$
CREATE PROCEDURE `sp_add_item_to_order`(
    IN p_order_number VARCHAR(20),
    IN p_item_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_order_id INT DEFAULT NULL;
    DECLARE v_item_cnt INT DEFAULT 0;
    DECLARE v_dup INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR 1062 SET v_dup = 1;

    SELECT `id`
    INTO v_order_id
    FROM `заказы`
    WHERE `номер` = p_order_number
    LIMIT 1;

    SELECT COUNT(*) INTO v_item_cnt
    FROM `изделия`
    WHERE `id` = p_item_id;

    IF v_order_id IS NULL THEN
        SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
    ELSEIF v_item_cnt = 0 THEN
        SELECT CONCAT('Изделие ', p_item_id, ' не найдено') AS message;
    ELSE
        INSERT INTO `позиции_заказа` (`заказ_id`, `изделие_id`, `количество`)
        VALUES (v_order_id, p_item_id, p_quantity);

        IF v_dup = 1 THEN
            SELECT 'Такая позиция уже существует' AS message;
        ELSE
            SELECT 'Изделие добавлено в заказ' AS message;
        END IF;
    END IF;
END $$

-- 4. Добавить изделие в заказ без дублирования
DROP PROCEDURE IF EXISTS `sp_add_item_to_order_no_dup` $$
CREATE PROCEDURE `sp_add_item_to_order_no_dup`(
    IN p_order_number VARCHAR(20),
    IN p_item_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_order_id INT DEFAULT NULL;
    DECLARE v_item_cnt INT DEFAULT 0;
    DECLARE v_exists INT DEFAULT 0;

    SELECT `id`
    INTO v_order_id
    FROM `заказы`
    WHERE `номер` = p_order_number
    LIMIT 1;

    SELECT COUNT(*) INTO v_item_cnt
    FROM `изделия`
    WHERE `id` = p_item_id;

    IF v_order_id IS NULL THEN
        SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
    ELSEIF v_item_cnt = 0 THEN
        SELECT CONCAT('Изделие ', p_item_id, ' не найдено') AS message;
    ELSE
        SELECT COUNT(*) INTO v_exists
        FROM `позиции_заказа`
        WHERE `заказ_id` = v_order_id
          AND `изделие_id` = p_item_id;

        IF v_exists = 0 THEN
            INSERT INTO `позиции_заказа` (`заказ_id`, `изделие_id`, `количество`)
            VALUES (v_order_id, p_item_id, p_quantity);

            SELECT 'Позиция добавлена в заказ' AS message;
        ELSE
            SELECT 'Такая позиция уже есть в заказе, добавление не выполнено' AS message;
        END IF;
    END IF;
END $$

-- 5. Изменение статуса заказа
DROP PROCEDURE IF EXISTS `sp_change_order_status` $$
CREATE PROCEDURE `sp_change_order_status`(
    IN p_order_number VARCHAR(20),
    IN p_new_status VARCHAR(20)
)
BEGIN
    UPDATE `заказы`
    SET `статус` = p_new_status
    WHERE `номер` = p_order_number;

    IF ROW_COUNT() > 0 THEN
        SELECT CONCAT('Статус заказа ', p_order_number, ' изменён на "', p_new_status, '"') AS message;
    ELSE
        SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
    END IF;
END $$

-- 6. Перевод заказа в статус "в работе" только если текущий статус "новый"
DROP PROCEDURE IF EXISTS `sp_move_order_to_work_if_new` $$
CREATE PROCEDURE `sp_move_order_to_work_if_new`(
    IN p_order_number VARCHAR(20)
)
BEGIN
    UPDATE `заказы`
    SET `статус` = 'в работе'
    WHERE `номер` = p_order_number
      AND `статус` = 'новый';

    IF ROW_COUNT() > 0 THEN
        SELECT CONCAT('Заказ ', p_order_number, ' переведён в статус "в работе"') AS message;
    ELSE
        IF EXISTS (SELECT 1 FROM `заказы` WHERE `номер` = p_order_number) THEN
            SELECT CONCAT('Статус заказа ', p_order_number, ' не "новый", изменения не выполнены') AS message;
        ELSE
            SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
        END IF;
    END IF;
END $$

-- 7. Закрытие заказа в статус "завершен", если он не завершен
DROP PROCEDURE IF EXISTS `sp_close_order` $$
CREATE PROCEDURE `sp_close_order`(
    IN p_order_number VARCHAR(20)
)
BEGIN
    UPDATE `заказы`
    SET `статус` = 'завершен'
    WHERE `номер` = p_order_number
      AND `статус` <> 'завершен';

    IF ROW_COUNT() > 0 THEN
        SELECT CONCAT('Заказ ', p_order_number, ' закрыт') AS message;
    ELSE
        IF EXISTS (SELECT 1 FROM `заказы` WHERE `номер` = p_order_number) THEN
            SELECT CONCAT('Заказ ', p_order_number, ' уже завершён, изменения не выполнены') AS message;
        ELSE
            SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
        END IF;
    END IF;
END $$

-- 8. Удаление заказа только если статус "новый"
DROP PROCEDURE IF EXISTS `sp_delete_order_if_new` $$
CREATE PROCEDURE `sp_delete_order_if_new`(
    IN p_order_number VARCHAR(20)
)
BEGIN
    DELETE FROM `заказы`
    WHERE `номер` = p_order_number
      AND `статус` = 'новый';

    IF ROW_COUNT() > 0 THEN
        SELECT CONCAT('Заказ ', p_order_number, ' удалён') AS message;
    ELSE
        IF EXISTS (SELECT 1 FROM `заказы` WHERE `номер` = p_order_number) THEN
            SELECT CONCAT('Удалить можно только заказ со статусом "новый". Заказ ', p_order_number, ' не удалён') AS message;
        ELSE
            SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
        END IF;
    END IF;
END $$

-- 9. Удаление всех позиций заказа для определённого изделия
DROP PROCEDURE IF EXISTS `sp_delete_order_item` $$
CREATE PROCEDURE `sp_delete_order_item`(
    IN p_order_number VARCHAR(20),
    IN p_item_id INT
)
BEGIN
    DECLARE v_order_id INT DEFAULT NULL;

    SELECT `id`
    INTO v_order_id
    FROM `заказы`
    WHERE `номер` = p_order_number
    LIMIT 1;

    IF v_order_id IS NULL THEN
        SELECT CONCAT('Заказ ', p_order_number, ' не найден') AS message;
    ELSE
        DELETE FROM `позиции_заказа`
        WHERE `заказ_id` = v_order_id
          AND `изделие_id` = p_item_id;

        IF ROW_COUNT() > 0 THEN
            SELECT 'Позиции удалены' AS message;
        ELSE
            SELECT 'Позиции для удаления не найдены' AS message;
        END IF;
    END IF;
END $$

-- 10. Проверка существования изделия
DROP PROCEDURE IF EXISTS `sp_check_item_exists` $$
CREATE PROCEDURE `sp_check_item_exists`(
    IN p_item_id INT
)
BEGIN
    DECLARE v_cnt INT DEFAULT 0;

    SELECT COUNT(*) INTO v_cnt
    FROM `изделия`
    WHERE `id` = p_item_id;

    IF v_cnt > 0 THEN
        SELECT CONCAT('Изделие ', p_item_id, ' найдено') AS message;
    ELSE
        SELECT CONCAT('Изделие ', p_item_id, ' не найдено') AS message;
    END IF;
END $$

-- 11. Добавление материала в изделие в определённом количестве
DROP PROCEDURE IF EXISTS `sp_add_material_to_item` $$
CREATE PROCEDURE `sp_add_material_to_item`(
    IN p_item_id INT,
    IN p_material_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_item_cnt INT DEFAULT 0;
    DECLARE v_material_cnt INT DEFAULT 0;
    DECLARE v_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO v_item_cnt
    FROM `изделия`
    WHERE `id` = p_item_id;

    SELECT COUNT(*) INTO v_material_cnt
    FROM `материалы`
    WHERE `id` = p_material_id;

    IF v_item_cnt = 0 THEN
        SELECT CONCAT('Изделие ', p_item_id, ' не найдено') AS message;
    ELSEIF v_material_cnt = 0 THEN
        SELECT CONCAT('Материал ', p_material_id, ' не найден') AS message;
    ELSE
        SELECT COUNT(*) INTO v_exists
        FROM `состав_изделия`
        WHERE `изделие_id` = p_item_id
          AND `материал_id` = p_material_id;

        IF v_exists = 0 THEN
            INSERT INTO `состав_изделия` (`изделие_id`, `материал_id`, `количество`)
            VALUES (p_item_id, p_material_id, p_quantity);

            SELECT 'Материал добавлен в изделие' AS message;
        ELSE
            UPDATE `состав_изделия`
            SET `количество` = `количество` + p_quantity
            WHERE `изделие_id` = p_item_id
              AND `материал_id` = p_material_id;

            SELECT 'Количество материала в изделии увеличено' AS message;
        END IF;
    END IF;
END $$

DELIMITER ;
Больше возможностей при регистрации:
