时间戳

首页 > 解决方案 > 将存储过程的每一行插入一个表中

mysql - 将存储过程的每一行插入一个表中

问题描述

我在 MySql 中有两个存储过程。其中一个进行计算并分组返回所有内容。对于另一个存储过程，我有一个每个月运行的 WHILE 语句并调用第一个存储过程。我想得到每个组的结果并将它们保存在一个表中。

代码类似于：

CREATE PROCEDURE `first`(IN `start` date, **it should be list of items** )
begin 
    SET result = 0;
    
SELECT 
    item.name,
    sum(amount) *INTO result*

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(start) 
group by something;
end

跑步者是这样的：

CREATE PROCEDURE `runner`(IN `from` date, IN `to` date)
BEGIN
    set dateTo = date(to);

    set dateFrom = date(from);

    WHILE DATE(dateFrom) <= DATE(dateTo) DO

        call first(dateFrom, @res);

// here I need another loop through all results of the first procedure to insert each one of them in the following line.

        insert into table_x (**some values which have been returned**);
                
        SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 month);
    END WHILE;
END

标签： mysqlstored-procedures

解决方案

我不-'认为循环和第二个程序真的很有必要

MySQL 不能返回表数组或类似的东西，但你可以使用临时表

DELIMITER $$
CREATE PROCEDURE `first`(IN `start` date )
begin 
DROP TEMPORARY TABLE IF EXISTS myTable;
CREATE TEMPORARY TABLE myTABLE
SELECT 
    item.name,
    sum(amount) 

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(start) 
group by something;
end$$
DELIMITER ;

外部过程

DELIMITER $$
CREATE PROCEDURE `runner`(IN `_from` date, IN `_to` date)
BEGIN
    set dateTo = date(_to);

    set dateFrom = date(_from);

    WHILE DATE(dateFrom) <= DATE(dateTo) DO

        call first(dateFrom, @res);



        insert into table_x (SELECT * FROM myTable);
                
        SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 month);
    END WHILE;
END$$
DELIMITER ;

您可以使动态 sql 使用不同的变量

DELIMITER $$
CREATE PROCEDURE `first`(IN `_start` date , IN _group varchar(100))
begin 
DROP TEMPORARY TABLE IF EXISTS myTable;
SET @sql := CONCAT("
CREATE TEMPORARY TABLE myTABLE
SELECT 
    item.name,
    sum(amount) 

FROM
    FOO
        INNER JOIN
    BAR ON id = id
WHERE
        date(someDate) < date(",_gRoup,") 
group by",_goup,";");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 
end$$
DELIMITER ;

推荐阅读