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 utf8BEGIN 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 ;
转载于:https://www.cnblogs.com/sung1024/p/11175236.html
