MySQL函數(shù)創(chuàng)造

DELIMITER $$

CREATE FUNCTION funGetSKUContractorId(param_sku_number VARCHAR(32)) RETURNS INT(11)
BEGIN
DECLARE contraactor_id_temp,parent_id_temp, i INT;
SET i=0;
SELECT MAX(contractor_id) , MAX(parent_id) INTO contraactor_id_temp, parent_id_temp FROM 
erp$pro$product_sku ps 
JOIN erp$pro$product p ON ps.product_id = p.product_id
JOIN erp$pro$product_category pc ON p.product_id = pc.product_id
JOIN erp$pro$category c ON pc.category_id = c.category_id
WHERE ps.sku_number = param_sku_number ;

/*
* 因?yàn)楝F(xiàn)在分類最多只有四級(jí)，所以最多循環(huán)4次
*/
WHILE (i<4 AND contraactor_id_temp IS NULL ) DO
	
 SELECT contractor_id,parent_id INTO contraactor_id_temp, parent_id_temp FROM erp$pro$category WHERE category_id = parent_id_temp;
	IF(contraactor_id_temp IS NOT NULL) THEN
	SET i = 5; /**結(jié)束*/
	ELSE
	SET i=i+1;
	END IF;
 END WHILE;
RETURN IFNULL(contraactor_id_temp,0);
END$$

DELIMITER ;

DELIMITER $$

/**
* 根據(jù)定單號(hào)獲取發(fā)貨截止日期的函數(shù)
*/
CREATE FUNCTION funGetOrderDeliverDeadline(order_id INT) 

RETURNS VARCHAR(32)

BEGIN

RETURN (SELECT MAX(DATE_ADD(so.create_time ,INTERVAL (SELECT IFNULL(MAX(produce_period),0) FROM erp$sal$order_sku os1
JOIN erp$pro$product_sku ps1 ON os1.product_sku_id = ps1.product_sku_id
JOIN erp$sal$order_shipment osp1 ON os1.order_shipment_id = osp1.order_shipment_id
WHERE osp1.sales_order_id = order_id) DAY)) 'deadline_deliver_time'
FROM erp$sal$sales_order so WHERE so.sales_order_id = order_id);
END$$

DELIMITER ;