mysql订单状态主要有哪些_mysql自定义函数统计订单状态：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$$