ALTER PROCEDURE [dbo].[CREATE_PRODUCT_PER_ORDER] @SET_CNT INT AS BEGIN DECLARE @CNT INT = 1 DECLARE @MAX_ORDER_ID INT = 0 WHILE @SET_CNT > @CNT BEGIN DECLARE @TEMP TABLE ( order_id nvarchar(255) ,product_id nvarchar(255) ,add_to_cart_order nvarchar(255) ,reordered nvarchar(1) ) SET @MAX_ORDER_ID = (select max(cast(order_id as int)) FROM CI_products_per_order) --주문내역의 주문번호 Max ---------------------------------------------------------------------------------------- ------------------------------------적재루트 설정--------------------------------------- ---------------------------------------------------------------------------------------- DECLARE @START_NUM_ROOT INT -- 범위 시작 ,@END_NUM_ROOT INT -- 범위 마지막 ,@ROOT INT -- 수량 SET @START_NUM_ROOT = 1 SET @END_NUM_ROOT = 3 SET @ROOT = RIGHT('0' + CAST(ROUND( ( (@END_NUM_ROOT + 1) - @START_NUM_ROOT ) * RAND() + @START_NUM_ROOT , 0, 1) AS NVARCHAR), 2) IF (@ROOT = 1 ) GOTO PROC_1 ELSE IF (@ROOT = 2 ) GOTO PROC_2 ELSE IF (@ROOT = 3 ) GOTO PROC_3 ---------------------------------------------------------------------------------------- ------------------------------------적재루트 1번 --------------------------------------- ---------------------------------------------------------------------------------------- PROC_1: ---수량을 Rand으로 추출 DECLARE @START_NUM_PRODUCT INT -- 범위 시작 ,@END_NUM_PRODUCT INT -- 범위 마지막 ,@QTY INT -- 수량 --반복횟수 DECLARE @START_REPEAT INT -- 반복횟수 시작 ,@END_REPEAT INT -- 반복횟수 마지막 ,@REPEAT_CNT INT -- 반복횟수 COUNT ,@TOTAL_REPEAT_CNT INT -- 반복횟수 MAX /*1~10개의 물품 선택*/ SET @START_REPEAT = 1 SET @END_REPEAT = 10 SET @REPEAT_CNT = 0 SET @TOTAL_REPEAT_CNT = RIGHT('0' + CAST(ROUND( ( (@END_REPEAT + 1) - @START_REPEAT ) * RAND() + @START_REPEAT , 0, 1) AS NVARCHAR), 2) WHILE @TOTAL_REPEAT_CNT > @REPEAT_CNT BEGIN SET @START_NUM_PRODUCT = 1 SET @END_NUM_PRODUCT = 10 /*수량을 랜덤으로 선택*/ SET @QTY = RIGHT('0' + CAST(ROUND( ( (@END_NUM_PRODUCT + 1) - @START_NUM_PRODUCT ) * RAND() + @START_NUM_PRODUCT , 0, 1) AS NVARCHAR), 2) INSERT INTO @TEMP SELECT @MAX_ORDER_ID + 1 AS Order_id ,(SELECT TOP 1 T01.product_id FROM CI_products T01 ORDER BY NEWID()) AS Product_id ,@QTY AS add_to_cart_order ,0 AS reordered SET @REPEAT_CNT = @REPEAT_CNT + 1 END ---------------------------------------------------------------------------------------- ------------------------------------적재루트 2번 --------------------------------------- ---------------------------------------------------------------------------------------- PROC_2: SET @START_REPEAT = 1 SET @END_REPEAT = 10 SET @REPEAT_CNT = 0 SET @TOTAL_REPEAT_CNT = RIGHT('0' + CAST(ROUND( ( (@END_REPEAT + 1) - @START_REPEAT ) * RAND() + @START_REPEAT , 0, 1) AS NVARCHAR), 2) WHILE @TOTAL_REPEAT_CNT > @REPEAT_CNT BEGIN SET @START_NUM_PRODUCT = 1 SET @END_NUM_PRODUCT = 20 SET @QTY = RIGHT('0' + CAST(ROUND( ( (@END_NUM_PRODUCT + 1) - @START_NUM_PRODUCT ) * RAND() + @START_NUM_PRODUCT , 0, 1) AS NVARCHAR), 2) INSERT INTO @TEMP SELECT @MAX_ORDER_ID + 1 AS Order_id ,(SELECT TOP 1 T01.product_id FROM CI_products T01 ORDER BY NEWID()) AS Product_id ,@QTY AS add_to_cart_order ,0 AS reordered SET @REPEAT_CNT = @REPEAT_CNT + 1 END ---------------------------------------------------------------------------------------- ------------------------------------적재루트 3번 --------------------------------------- ---------------------------------------------------------------------------------------- PROC_3: SET @START_REPEAT = 1 SET @END_REPEAT = 15 SET @REPEAT_CNT = 0 SET @TOTAL_REPEAT_CNT = RIGHT('0' + CAST(ROUND( ( (@END_REPEAT + 1) - @START_REPEAT ) * RAND() + @START_REPEAT , 0, 1) AS NVARCHAR), 2) WHILE @TOTAL_REPEAT_CNT > @REPEAT_CNT BEGIN SET @START_NUM_PRODUCT = 1 SET @END_NUM_PRODUCT = 30 SET @QTY = RIGHT('0' + CAST(ROUND( ( (@END_NUM_PRODUCT + 1) - @START_NUM_PRODUCT ) * RAND() + @START_NUM_PRODUCT , 0, 1) AS NVARCHAR), 2) INSERT INTO @TEMP SELECT @MAX_ORDER_ID + 1 AS Order_id ,(SELECT TOP 1 T01.product_id FROM CI_products T01 ORDER BY NEWID()) AS Product_id ,@QTY AS add_to_cart_order ,0 AS reordered SET @REPEAT_CNT = @REPEAT_CNT + 1 END INSERT INTO CI_products_per_order SELECT order_id ,product_id ,add_to_cart_order ,reordered FROM @TEMP SET @CNT = @CNT + 1 END END