MySQL 성능 개선 프로젝트 4

배송 주문이 생성될 때 해당 주문의 총 가격에 따라 보험상품을 가입해야 하는 기능이 있다.

어떤 배송건이 어떤 상품을 가입했는지와 해당 상품들에 대한 테이블을 새로 만들어야했다. 

 

그리고 지난번에 만든 배송 주문 통합 프로시저 안에 관련된 프로시저를 생성해 추가하면 될것이다.

delimiter //
DROP PROCEDURE if exists insert_delivery_insurance;
CREATE PROCEDURE insert_delivery_insurance(new_dv_order_id BIGINT)
BEGIN
	DECLARE selected_order_id BIGINT;
	DECLARE selected_total_price DECIMAL(10,2);
	DECLARE selected_product_id bigint;
	DECLARE selected_product_name VARCHAR(255);
	DECLARE selected_product_period INT;
	DECLARE selected_product_price INT;
	DECLARE selected_product_coverage_rate INT;
	
	
	SELECT order_id
	INTO selected_order_id
	FROM delivery_order
	WHERE dv_order_id = new_dv_order_id;
	
	
	SELECT total_price
	INTO selected_total_price
	FROM orders
	WHERE order_id = selected_order_id;
	
	SET selected_product_id =
		case
			when selected_total_price >= 1000000 then 4
			when selected_total_price >= 200000 then  3
			when selected_total_price >= 50000 then  2
			when selected_total_price >= 10000 then  1
			ELSE null
		END;
		
	if(selected_product_id IS not NULL) then
		SELECT name, expiration_period, price, coverage_rate
		INTO 
        	selected_product_name, 
            selected_product_period, 
            selected_product_price, 
            selected_product_coverage_rate
		FROM insurance_product
		WHERE product_id = selected_product_id;
		
	
		INSERT INTO delivery_insurance(
			dv_order_id,
			name,
			purchase_date,
			expiration_date,
			insurance_fee,
			coverage_amount,
			insurance_status
		) VALUES(
			new_dv_order_id,
			selected_product_name,
			NOW(),
			DATE_ADD(NOW(), INTERVAL selected_product_period DAY),
			selected_total_price * (selected_product_price/100),
			selected_total_price * (selected_product_coverage_rate/100),
			1
		);
	END if;
	
END//

delimiter ;

 

배송 주문  테이블이 가지고있는 order_id값을 통해 주문의 정보를 가져올 수 있다.

그리고 해당 주문의 가격을 비교해 어떠한 보험 상품에 가입할지 case문을 통해 정해준다.

보험 상품이 정해지면 상품의 이름, 기간, 보험료, 보장금액을 가져와 변수에 넣어놓는다.

그 후 오늘로부터 상품의 보험 기간과 주문의 가격 일정 비율 만큼의 보험료와 보장금액을 계산해서 배송 보험 테이블에 데이터를 입력한다.