mysql 12,23,4 list 的遍历处理

2019独角兽企业重金招聘Python工程师标准>>>

如下面的遍历

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

 

转载于:https://my.oschina.net/u/3556610/blog/3049246