mysql切片遍历_mysql 12,23,4 list 的遍历处理

如下面的遍历

CREATE DEFINER = 'root'@'localhost'

PROCEDURE zita_wms.DeliveryOrderUtil_ConfirmByList(

OUT confirmed_no_ INT,

OUT not_confirmed_no_ INT,

IN company_id_ INT,

IN order_list_ VARCHAR(20000), -- 12,23,4

IN user_id_ VARCHAR(40) )

BEGIN

DECLARE order_id_ INT;

DECLARE is_confirmed_ INT;

DECLARE get_list CURSOR FOR

SELECT d.id

FROM delivery_order d, sys_array_tab a

WHERE d.id = a.content

AND d.logistics_company_id = company_id_;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET order_id_ = NULL;

SET confirmed_no_ = 0 ;

SET not_confirmed_no_ = 0 ;

CALL sys_array(order_list_, ',');

OPEN get_list;

order_list: LOOP

FETCH get_list INTO order_id_;

IF order_id_ IS NULL THEN

LEAVE order_list;

END IF;

--

CALL DeliveryOrderUtil_Confirm(is_confirmed_ , order_id_, user_id_ );

IF is_confirmed_ = 1 THEN -- 1:确认成功 0:确认失败

SET confirmed_no_ = confirmed_no_ + 1 ;

ELSE

SET not_confirmed_no_ = not_confirmed_no_ + 1 ;

END IF;

COMMIT; -- 业务需求, 确认一个订单就提交一个

END LOOP;

CLOSE get_list;

END

对应工具类, 创建临时表,删除数据,遍历数据插入

CREATE DEFINER = 'root'@'localhost'

PROCEDURE zita_wms.sys_array(IN content_ VARCHAR(20000), IN separate_ VARCHAR(1))

BEGIN

SET @i_ := 1;

SET @count_ := CHAR_LENGTH(content_) - CHAR_LENGTH(REPLACE(content_, separate_,'')) + 1;

SET @tmp_ := '';

-- DROP temporary table if exists sys_array_tab;

CREATE TEMPORARY TABLE IF NOT EXISTS sys_array_tab( seq_no INT(11),

content VARCHAR(2000) );

DELETE FROM sys_array_tab;

WHILE @i_ <= @count_ DO

SET @tmp_ := SUBSTRING_INDEX(SUBSTRING_INDEX(content_,separate_,@i_),separate_,-1);

INSERT INTO sys_array_tab(seq_no, content)

VALUES( @i_, @tmp_ );

SET @i_ := @i_ + 1;

END WHILE;

END