Pastein: -- ============================================ -- СОЗДАНИЕ БАЗЫ ДАННЫХ -- ============================================ DROP DATABASE IF EXISTS mebel

Загрузка данных
-- ============================================
-- СОЗДАНИЕ БАЗЫ ДАННЫХ
-- ============================================

DROP DATABASE IF EXISTS mebel_factory;
CREATE DATABASE mebel_factory;
USE mebel_factory;

-- ============================================
-- УДАЛЕНИЕ ТАБЛИЦ ЕСЛИ СУЩЕСТВУЮТ
-- ============================================

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

-- ============================================
-- СОЗДАНИЕ ТАБЛИЦ
-- ============================================

CREATE TABLE заказы (
    id INT AUTO_INCREMENT PRIMARY KEY,
    номер VARCHAR(20) UNIQUE,
    дата DATE,
    статус VARCHAR(20)
);

CREATE TABLE изделия (
    id INT AUTO_INCREMENT PRIMARY KEY,
    название VARCHAR(100),
    тип VARCHAR(50)
);

CREATE TABLE материалы (
    id INT AUTO_INCREMENT PRIMARY KEY,
    название VARCHAR(100),
    цена DECIMAL(10,2)
);

CREATE TABLE состав_изделия (
    изделие_id INT,
    материал_id INT,
    количество INT,
    PRIMARY KEY (изделие_id, материал_id),

    FOREIGN KEY (изделие_id)
        REFERENCES изделия(id)
        ON DELETE CASCADE,

    FOREIGN KEY (материал_id)
        REFERENCES материалы(id)
);

CREATE TABLE позиции_заказа (
    заказ_id INT,
    изделие_id INT,
    количество INT,
    PRIMARY KEY (заказ_id, изделие_id),

    FOREIGN KEY (заказ_id)
        REFERENCES заказы(id)
        ON DELETE CASCADE,

    FOREIGN KEY (изделие_id)
        REFERENCES изделия(id)
);

-- ============================================
-- ЗАПОЛНЕНИЕ ТАБЛИЦ
-- ============================================

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

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

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

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

INSERT INTO позиции_заказа VALUES
(1,1,2),
(1,2,4),
(2,3,1);

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

DELIMITER $$

-- ============================================
-- 1. Проверка существования заказа
-- ============================================

CREATE PROCEDURE sp_check_order_exists(
    IN p_order_number VARCHAR(20)
)
BEGIN
    DECLARE cnt INT;

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

    IF cnt > 0 THEN
        SELECT 'Заказ найден' AS Результат;
    ELSE
        SELECT 'Заказ не найден' AS Результат;
    END IF;
END $$

-- ============================================
-- 2. Создание нового заказа
-- ============================================

CREATE PROCEDURE sp_create_order(
    IN p_order_number VARCHAR(20)
)
BEGIN
    INSERT INTO заказы(номер, дата, статус)
    VALUES(p_order_number, CURDATE(), 'новый');

    SELECT 'Заказ создан' AS Результат;
END $$

-- ============================================
-- 3. Добавление изделия в заказ
-- ============================================

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;

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

    IF v_order_id IS NOT NULL THEN

        INSERT INTO позиции_заказа
        VALUES(v_order_id, p_item_id, p_quantity);

        SELECT 'Изделие добавлено' AS Результат;

    ELSE

        SELECT 'Заказ не существует' AS Результат;

    END IF;
END $$

-- ============================================
-- 4. Добавление изделия без дублирования
-- ============================================

CREATE PROCEDURE sp_add_item_no_duplicate(
    IN p_order_number VARCHAR(20),
    IN p_item_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_order_id INT;
    DECLARE cnt INT;

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

    SELECT COUNT(*)
    INTO cnt
    FROM позиции_заказа
    WHERE заказ_id = v_order_id
    AND изделие_id = p_item_id;

    IF cnt = 0 THEN

        INSERT INTO позиции_заказа
        VALUES(v_order_id, p_item_id, p_quantity);

        SELECT 'Позиция добавлена' AS Результат;

    ELSE

        SELECT 'Такая позиция уже существует' AS Результат;

    END IF;
END $$

-- ============================================
-- 5. Изменение статуса заказа
-- ============================================

CREATE PROCEDURE sp_change_status(
    IN p_order_number VARCHAR(20),
    IN p_status VARCHAR(20)
)
BEGIN
    UPDATE заказы
    SET статус = p_status
    WHERE номер = p_order_number;

    SELECT 'Статус изменен' AS Результат;
END $$

-- ============================================
-- 6. Перевод в статус "в работе"
-- только если статус "новый"
-- ============================================

CREATE PROCEDURE sp_start_order(
    IN p_order_number VARCHAR(20)
)
BEGIN
    UPDATE заказы
    SET статус = 'в работе'
    WHERE номер = p_order_number
    AND статус = 'новый';

    SELECT 'Проверка завершена' AS Результат;
END $$

-- ============================================
-- 7. Закрытие заказа
-- ============================================

CREATE PROCEDURE sp_close_order(
    IN p_order_number VARCHAR(20)
)
BEGIN
    UPDATE заказы
    SET статус = 'завершен'
    WHERE номер = p_order_number
    AND статус <> 'завершен';

    SELECT 'Заказ закрыт' AS Результат;
END $$

-- ============================================
-- 8. Удаление заказа
-- только если статус "новый"
-- ============================================

CREATE PROCEDURE sp_delete_order(
    IN p_order_number VARCHAR(20)
)
BEGIN
    DELETE FROM заказы
    WHERE номер = p_order_number
    AND статус = 'новый';

    SELECT 'Удаление выполнено' AS Результат;
END $$

-- ============================================
-- 9. Удаление позиций заказа
-- определенного товара
-- ============================================

CREATE PROCEDURE sp_delete_order_item(
    IN p_order_number VARCHAR(20),
    IN p_item_id INT
)
BEGIN
    DECLARE v_order_id INT;

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

    DELETE FROM позиции_заказа
    WHERE заказ_id = v_order_id
    AND изделие_id = p_item_id;

    SELECT 'Позиция удалена' AS Результат;
END $$

-- ============================================
-- 10. Проверка существования изделия
-- ============================================

CREATE PROCEDURE sp_check_item_exists(
    IN p_item_id INT
)
BEGIN
    DECLARE cnt INT;

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

    IF cnt > 0 THEN
        SELECT 'Изделие существует' AS Результат;
    ELSE
        SELECT 'Изделие не существует' AS Результат;
    END IF;
END $$

-- ============================================
-- 11. Добавление материала в изделие
-- ============================================

CREATE PROCEDURE sp_add_material_to_item(
    IN p_item_id INT,
    IN p_material_id INT,
    IN p_quantity INT
)
BEGIN
    INSERT INTO состав_изделия
    VALUES(p_item_id, p_material_id, p_quantity);

    SELECT 'Материал добавлен' AS Результат;
END $$

DELIMITER ;

-- ============================================
-- ПРОВЕРКА РАБОТОСПОСОБНОСТИ
-- ============================================

-- Просмотр данных

SELECT * FROM заказы;
SELECT * FROM позиции_заказа;
SELECT * FROM изделия;
SELECT * FROM материалы;
SELECT * FROM состав_изделия;

-- ============================================
-- 1. Проверка существования заказа
-- ============================================

CALL sp_check_order_exists('ORD1');
CALL sp_check_order_exists('ORD999');

-- ============================================
-- 2. Создание заказа
-- ============================================

CALL sp_create_order('ORD3');

SELECT * FROM заказы;

-- ============================================
-- 3. Добавление изделия в заказ
-- ============================================

CALL sp_add_item_to_order('ORD3', 1, 5);

SELECT * FROM позиции_заказа;

-- ============================================
-- 4. Добавление без дублирования
-- ============================================

CALL sp_add_item_no_duplicate('ORD3', 2, 3);

CALL sp_add_item_no_duplicate('ORD3', 2, 3);

SELECT * FROM позиции_заказа;

-- ============================================
-- 5. Изменение статуса
-- ============================================

CALL sp_change_status('ORD3', 'в работе');

SELECT * FROM заказы;

-- ============================================
-- 6. Перевод в статус "в работе"
-- ============================================

CALL sp_create_order('ORD4');

CALL sp_start_order('ORD4');

SELECT * FROM заказы;

-- ============================================
-- 7. Закрытие заказа
-- ============================================

CALL sp_close_order('ORD4');

SELECT * FROM заказы;

-- ============================================
-- 8. Удаление заказа
-- ============================================

CALL sp_create_order('ORD5');

SELECT * FROM заказы;

CALL sp_delete_order('ORD5');

SELECT * FROM заказы;

-- ============================================
-- 9. Удаление позиции заказа
-- ============================================

SELECT * FROM позиции_заказа;

CALL sp_delete_order_item('ORD1', 1);

SELECT * FROM позиции_заказа;

-- ============================================
-- 10. Проверка существования изделия
-- ============================================

CALL sp_check_item_exists(1);
CALL sp_check_item_exists(100);

-- ============================================
-- 11. Добавление материала в изделие
-- ============================================

CALL sp_add_material_to_item(3, 2, 7);

SELECT * FROM состав_изделия;
Больше возможностей при регистрации:
