有不调用游标的手段吗，下面这段代码

Question

CREATE PROCEDURE generate_fyjl(IN startTime DATETIME, IN endTime DATETIME)
BEGIN

DECLARE ID VARCHAR(50);

DECLARE _STOP INT DEFAULT 0;

-- 游标
DECLARE cur CURSOR FOR SELECT IFNULL(tlk_qdcp.ID,'null') FROM tlk_qdcp WHERE tlk_qdcp.ITEM_CPJG='放款' AND NOT EXISTS (SELECT tlk_fyjl.ID FROM tlk_fyjl WHERE tlk_qdcp.ID=tlk_fyjl.ITEM_QDCPID);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=1;

OPEN cur;
-- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur INTO ID;
   -- 声明结束的时候
    IF _STOP=1 THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件

    call Insert_FYJL(ID);

  END LOOP;
-- 关闭游标
CLOSE cur;

END;

Answer 1

CREATE PROCEDURE generate_fyjl(IN startTime DATETIME, IN endTime DATETIME)
BEGIN
    DECLARE ID VARCHAR(50);
    
    -- 使用循环替代游标
    WHILE (SELECT COUNT(*) FROM tlk_qdcp WHERE ITEM_CPJG='放款' AND NOT EXISTS (SELECT tlk_fyjl.ID FROM tlk_fyjl WHERE tlk_qdcp.ID=tlk_fyjl.ITEM_QDCPID)) > 0 DO
        SELECT IFNULL(tlk_qdcp.ID,'null') INTO ID FROM tlk_qdcp WHERE ITEM_CPJG='放款' AND NOT EXISTS (SELECT tlk_fyjl.ID FROM tlk_fyjl WHERE tlk_qdcp.ID=tlk_fyjl.ITEM_QDCPID) LIMIT 1;
        
        CALL Insert_FYJL(ID);
    END WHILE;
END;