MySQL从多个存储过程中创建一个存储过程 [英] Mysql make a stored procedure from multiple stored procedures

问题描述

我有三个存储过程，这些存储过程针对不同的条件并连接不同的表提供相同的结果集.

I have three stored procedures which gives same result set for different conditions and joining different tables.

ex

procedure_one(StartDate,EndDate);
procedure_two(StartDate,EndDate);
procedure_three(StartDate,EndDate);

我需要另一个存储过程，该存储过程将给出上述三个存储过程的结果.只需合并以上三个存储过程即可.我不希望3个查询的联合，而是我希望3个存储过程的联合.我用谷歌搜索，但是找不到好的解决方案.

I need an another stored procedure which will gives the result from above three stored procedures. simply union of above three stored procedures. I don't want the union of 3 queries instead i want union of 3 stored procedures. I googled but unable to find good solution.

我做了以下尝试.

第一次尝试

DELIMITER //
CREATE PROCEDURE `MasterProcedure` (IN StartDate DATE,IN EndDate DATE)
    BEGIN
        SELECT @CALL procedure_one (StartDate,EndDate) UNION
        SELECT @CALL procedure_two (StartDate,EndDate) UNION
        SELECT @CALL procedure_three (StartDate,EndDate);
    END //
DELIMITER ; 

第二次尝试

DELIMITER //
DECLARE var_val1 TEXT;
DECLARE var_val2 TEXT;
DECLARE var_val3 TEXT;
CREATE PROCEDURE `MasterProcedure` (IN StartDate DATE,IN EndDate DATE)
    BEGIN
        SELECT @CALL procedure_one (StartDate,EndDate) INTO @var_val1;
        SELECT @CALL procedure_two (StartDate,EndDate) INTO @var_val2;
        SELECT @CALL procedure_three (StartDate,EndDate) INTO @var_val3;
        SELECT @var_val1,@var_val2,@var_val3;
    END //
DELIMITER ;       

是否有任何解决方法来实现这一目标.预先感谢.

Is there any workaround to achieve this. Thanks in advance.

推荐答案

我认为上述功能未在MySQL中实现.有关更多信息，请参见此处.

As I think above feature is not implemented in MySQL. See here for more information.

我想出了以下解决方案.但不确定这是否是解决此问题的最佳方法.

I came up with following solution. But not sure it is the best approach to this problem.

DELIMITER //
CREATE PROCEDURE pro_1(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp1 AS SELECT * FROM table_name WHERE $where1;
COMMIT;
END //
DELIMITER ;

如上所述，您可以按照以下步骤创建接下来的两个过程.

as above you can create next two procedures as follows.

DELIMITER //
CREATE PROCEDURE pro_2(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp2 AS SELECT * FROM table_name WHERE $where2;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE pro_3(IN param1 data_type,IN param2 data_type);
BEGIN
   CREATE TEMPORARY TABLE temp3 AS SELECT * FROM table_name WHERE $where3;
COMMIT;
END //
DELIMITER ;

因此，以下是上述三个过程的并集的主过程.

So following is the master procedure which is union of above 3 procedures.

DELIMITER //
CREATE PROCEDURE master_pro(IN param1 data_type,IN param2 data_type);
BEGIN
   CALL pro_1(param1,param2);
   CALL pro_2(param1,param2);
   CALL pro_3(param1,param2);
   CREATE TEMPORARY TABLE master_temp AS (SELECT * FROM temp1) UNION ALL (SELECT *  FROM temp2) UNION ALL (SELECT * FROM temp3);
COMMIT;
END //
DELIMITER ;

最后，如果您想从主过程中获得任何其他结果，则可以执行以下操作.

SO finally if you want to get any other results from master procedure you can do as follows.

DELIMITER //
CREATE PROCEDURE another_pro(IN param1 data_type,IN param2 data_type);
BEGIN
   CALL master_pro(param1,param2);
   SELECT columns_from_master_temp FROM master_temp WHERE where_clause;
COMMIT;
END //
DELIMITER ;

这篇关于MySQL从多个存储过程中创建一个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！