강의 DB데이터 질문

--order 프로시저

DELIMITER $$

CREATE PROCEDURE sp_generate_orders(IN p_count INT)

BEGIN

DECLARE v_i INT DEFAULT 0;

DECLARE v_batch_size INT DEFAULT 2000;

DECLARE v_member_id BIGINT;

DECLARE v_item_count INT;

DECLARE v_product_id BIGINT;

DECLARE v_unit_price INT;

DECLARE v_total INT;

DECLARE v_order_id BIGINT;

DECLARE v_j INT;

DECLARE v_status VARCHAR(20);

DECLARE v_rand DOUBLE;

DECLARE v_days_ago INT;

DECLARE v_max_member BIGINT;

DECLARE v_max_product BIGINT;

DECLARE v_base_date DATETIME DEFAULT '2026-04-01 00:00:00';

SELECT MAX(member_id) INTO v_max_member FROM member;

SELECT MAX(product_id) INTO v_max_product FROM product;

SET autocommit = 0;

WHILE v_i < p_count DO

SET v_rand = RAND();

IF v_rand < 0.12 THEN

SET v_member_id = FLOOR(RAND() FLOOR(v_max_member 0.02))

+ FLOOR(v_max_member * 0.98) + 1;

ELSEIF v_rand < 0.35 THEN

SET v_member_id = FLOOR(RAND() FLOOR(v_max_member 0.08))

+ FLOOR(v_max_member * 0.90) + 1;

ELSEIF v_rand < 0.59 THEN

SET v_member_id = FLOOR(RAND() FLOOR(v_max_member 0.20))

+ FLOOR(v_max_member * 0.70) + 1;

ELSE

SET v_member_id = FLOOR(RAND() FLOOR(v_max_member 0.70)) + 1;

END IF;

SET v_rand = RAND();

IF v_rand < 0.50 THEN

SET v_days_ago = FLOOR(RAND() * 180);

ELSE

SET v_days_ago = 180 + FLOOR(RAND() * 915);

END IF;

SET v_rand = RAND();

SET v_status = CASE

WHEN v_rand < 0.60 THEN 'DELIVERED'

WHEN v_rand < 0.70 THEN 'SHIPPED'

WHEN v_rand < 0.80 THEN 'PAID'

WHEN v_rand < 0.90 THEN 'ORDERED'

ELSE 'CANCELLED'

END;

INSERT

INSERT INTO orders (member_id, order_status, total_price, ordered_at)

VALUES (v_member_id, v_status, 0,

DATE_SUB(v_base_date, INTERVAL v_days_ago DAY)

+ INTERVAL FLOOR(RAND() * 86400) SECOND);

SET v_order_id = LAST_INSERT_ID();

SET v_item_count = 1 + FLOOR(RAND() RAND() 5)

SET v_total = 0;

SET v_j = 0;

WHILE v_j < v_item_count DO

SET v_rand = RAND();

IF v_rand < 0.30 THEN

SET v_product_id = FLOOR(RAND() (v_max_product 0.01)) + 1;

ELSE

SET v_product_id = FLOOR(RAND() * v_max_product) + 1;

END IF;

SET v_unit_price = GREATEST(1000, ROUND(EXP(RAND() 3.0 + 8.0) / 100) 100);

INSERT INTO order_item (order_id, product_id, quantity, unit_price)

VALUES (v_order_id, v_product_id, 1 + FLOOR(RAND() * 3), v_unit_price);

SET v_total = v_total + v_unit_price;

SET v_j = v_j + 1;

END WHILE;

UPDATE orders SET total_price = v_total WHERE order_id = v_order_id;

SET v_i = v_i + 1;

IF v_i % v_batch_size = 0 THEN

COMMIT;

END IF;

END WHILE;

COMMIT;

SET autocommit = 1;

END$$

DELIMITER ;

--리뷰 프로시저

DELIMITER $$

CREATE PROCEDURE sp_generate_reviews(IN p_count INT)

BEGIN

DECLARE v_i INT DEFAULT 0;

DECLARE v_batch_size INT DEFAULT 5000;

DECLARE v_product_id BIGINT;

DECLARE v_member_id BIGINT;

DECLARE v_rating TINYINT;

DECLARE v_rand DOUBLE;

DECLARE v_max_member BIGINT;

DECLARE v_max_product BIGINT;

DECLARE v_base_date DATETIME DEFAULT '2026-04-01 00:00:00';

SELECT MAX(member_id)

INTO v_max_member

FROM member;

SELECT MAX(product_id)

INTO v_max_product

FROM product;

SET autocommit = 0;

WHILE v_i < p_count DO

SET v_rand = RAND();

IF v_rand < 0.40 THEN

SET v_product_id =

FLOOR(RAND() (v_max_product 0.05)) + 1;

ELSE

SET v_product_id =

FLOOR(RAND() * v_max_product) + 1;

END IF;

SET v_member_id =

FLOOR(RAND() * v_max_member) + 1;

SET v_rand = RAND();

SET v_rating = CASE

WHEN v_rand < 0.30 THEN 5

WHEN v_rand < 0.60 THEN 4

WHEN v_rand < 0.75 THEN 3

WHEN v_rand < 0.90 THEN 2

ELSE 1

END;

INSERT INTO review (

product_id,

member_id,

rating,

content,

created_at

)

VALUES (

v_product_id,

v_member_id,

v_rating,

CASE

WHEN RAND() < 0.5

THEN CONCAT('리뷰 내용 ', v_i)

ELSE NULL

END,

DATE_SUB(

v_base_date,

INTERVAL FLOOR(RAND() * 1095) DAY

)

);

SET v_i = v_i + 1;

IF v_i % v_batch_size = 0 THEN

COMMIT;

END IF;

END WHILE;

COMMIT;

SET autocommit = 1;

END$$

DELIMITER ;

위 상태에서 멈춰있어요

지금 order관련된 프로시저랑 review 관련된 프로시저가 너무 오래걸려서 안되는데 확인 부탁드립니다.