mysql存储过程怎么接收select结果?

DELIMITER ;;
CREATE PROCEDURE total_num(
IN p_begin INT,
IN p_end INT
)  
BEGIN  
    DECLARE v_begin INT DEFAULT 0;
    DECLARE v_end INT DEFAULT 0;
    DECLARE v_total INT DEFAULT 0;
    DECLARE item_count INT DEFAULT 0;
    
    SET v_begin = p_begin;
    SET v_end = p_end;
    
    WHILE v_begin<=v_end DO 
    SET @table_name = CONCAT('rd_feed_relation_',v_begin);
    SELECT COUNT(1) INTO item_count FROM @table_name;
    SET v_total = v_total + item_count;
    SET v_begin = v_begin+1;
    END WHILE;
    SELECT v_total;
END ;;
DELIMITER ;

CALL total_num(0,3);

这个存储过程为了解决我有几百张表,通过循环count找到这些表记录的总和这个问题。

以上是理想的状态,但是这样的语法是不成立的,找了半天的资料,发现通过CONCAT字段没问题,通过CONCAT后的表名是需要EXECUTE一遍的,这就让我遍历的结果集会是返回多个的,达不到求总数的要求。我该怎么修改这段代码?

阅读 9.6k
3 个回答

已经解决,还是使用了EXECUTE去执行sql,之前多个结果集可能因为没有通过INTO到item_count来获取总数。

DROP PROCEDURE IF EXISTS total_num;  

DELIMITER ;;
CREATE PROCEDURE total_num(
IN p_begin INT,
IN p_end INT
)  
BEGIN  
    DECLARE v_begin INT DEFAULT 0;
    DECLARE v_end INT DEFAULT 0;
    DECLARE v_total INT DEFAULT 0;
    DECLARE item_count INT DEFAULT 0;
    
    SET v_begin = p_begin;
    SET v_end = p_end;
    
    WHILE v_begin<=v_end DO 
    SET @table_name = CONCAT('rd_feed.rd_feed_relation_',v_begin);
    SET @sql = CONCAT("SELECT COUNT(1) INTO @item_count FROM ",@table_name);
    
    PREPARE selectIT FROM @sql;
    EXECUTE selectIT ;
    
    SET v_total = v_total + @item_count;
    SET v_begin = v_begin+1;
    END WHILE;
    DEALLOCATE PREPARE selectIT;
    SELECT v_total;
END ;;
DELIMITER ;

CALL total_num(0,10);

最好用函数接受返回结果

DROP PROCEDURE IF EXISTS total_num;

CREATE PROCEDURE total_num(
IN p_begin INT,
IN p_end INT
)  
BEGIN  
    DECLARE v_begin INT DEFAULT 0;
    DECLARE v_end INT DEFAULT 0;
    DECLARE v_total INT DEFAULT 0;
    
    SET v_begin = p_begin;
    SET v_end = p_end;
    
    WHILE v_begin<=v_end DO 
      SET @sql = CONCAT('SELECT COUNT(1) INTO @item_count FROM rd_feed_relation_', v_begin);

          PREPARE stmt FROM @sql;
        EXECUTE stmt;
      SET v_total = v_total + @item_count;

      SET v_begin = v_begin+1;
    END WHILE;
    DEALLOCATE PREPARE stmt;

    SELECT v_total;
END
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题