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 ;
/*
* 因为现在分类最多只有四级,所以最多循环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; /**结束*/
ELSE
SET i=i+1;
END IF;
END WHILE;
RETURN IFNULL(contraactor_id_temp,0);
END$$
DELIMITER ;
示例2
DELIMITER $$
/**
* 根据定单号获取发货截止日期的函数
*/
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 ;