mysql自定义函数统计订单状态：GET_ORDER

DELIMITER $$

USE `local_hnyz`$$

DROP FUNCTION IF EXISTS `GET_ORDER_STATUS`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_ORDER_STATUS`(orderId VARCHAR(64)) RETURNS TINYINT-- varchar(64) CHARSET utf8
BEGIN
DECLARE seal_status VARCHAR(1) DEFAULT '';
DECLARE seal_total INT DEFAULT 0;-- 印章总数
DECLARE statu_cj INT DEFAULT 0;-- 承接数
DECLARE statu_zz INT DEFAULT 0;-- 制作数
DECLARE statu_jf INT DEFAULT 0;-- 交付数
DECLARE done INT DEFAULT 0;
DECLARE order_cancel_status TINYINT DEFAULT 0;-- 订单撤销状态
-- declare _err int default 0;
DECLARE cur_order_seals CURSOR FOR SELECT ls.status FROM se_seal_order_seal sos INNER JOIN lv_seal ls ON(sos.SEAL_ID=ls.id AND sos.SEAL_ORDER_ID = orderId);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- declare continue handler for not found set done=1;
-- declare continue handler for sqlexception,sqlwarning
SELECT COUNT(ssos.`ID`) AS total INTO seal_total FROM se_seal_order_seal ssos WHERE ssos.`SEAL_ORDER_ID`=orderId;
SELECT so.`CANCEL_STATUS` INTO order_cancel_status FROM se_seal_order so WHERE so.`ID`=orderId;

-- 订单状态we取消状态，直接返回为撤回
IF order_cancel_status=1 THEN
RETURN 4;
END IF;
-- 订单中印章数量为0，直接返回为承接中
IF seal_total=0 THEN
RETURN 1;
END IF;

OPEN cur_order_seals;
-- repeat
sealLoop: LOOP
FETCH cur_order_seals INTO seal_status;
IF done=1 THEN
LEAVE sealLoop;
END IF;
-- select seal_status;
IF done!=1 THEN
IF seal_status = '2' THEN
SET statu_cj = statu_cj+1;
END IF;
IF seal_status = '3' THEN
SET statu_zz = statu_zz+1;
END IF;
IF (seal_status='1' OR seal_status='4' OR seal_status='5' OR seal_status='6' OR seal_status='7') THEN
SET statu_jf = statu_jf+1;
END IF;
END IF;
-- set done=0
-- UNTIL done end repeat;
END LOOP sealLoop;
CLOSE cur_order_seals;

IF statu_jf=seal_total THEN
RETURN 3;
ELSEIF (statu_cj=seal_total OR ((statu_cj+statu_zz)=seal_total) OR (statu_zz=seal_total) OR ((statu_zz+statu_jf)=seal_total) OR (statu_zz!=0) OR ((statu_cj+statu_jf)=seal_total)) THEN
RETURN 2;
ELSE
RETURN 1;
END IF;

END$$

DELIMITER ;

巴特西

mysql自定义函数统计订单状态：GET_ORDER_STATUS()

最新文章

热门文章