MySQL调用另一个过程并将值插入临时表

I have two procedures say proc1 and proc2. I am calling one mysql procedure within another.

我有两个程序说proc1和proc2。我在另一个内部调用一个mysql程序。

In proc1 I want to insert values from proc2 into proc1 temporary table.The proc2 returns two columns but i want to insert only one column into proc1 temporary table.

在proc1中我想将proc2中的值插入到proc1临时表中.proc2返回两列,但我只想在proc1临时表中插入一列。

The Output of Proc2 is as Below

Proc2的输出如下

Hrs(Timestamp)    Status
09:30             IN,OUT,IN,OUT 
04:30             IN,OUT
07:30             IN,OUT,IN,OUT
04:25             IN,OUT

Proc1 Code

CREATE PROCEDURE Proc1()
BEGIN
   DROP TABLE IF EXISTS TempWorkedHrs ;
   CREATE TEMPORARY TABLE TempWorkedHrs(WorkedHrs TIMESTAMP); 

   INSERT INTO TempWorkedHrs(WorkedHrs)
   CALL Proc2(); 

   SELECT SUM(WorkedHrs) INTO @TotalHrs
     FROM TempWorkedHrs;        
END //

The second column in proc2 is of no importance to me when I insert values into temporary table in proc1.

当我将值插入proc1中的临时表时,proc2中的第二列对我来说并不重要。

Q1. How to insert a particular column returned from procedure into temporary table? In my case first column from proc2.

Q1。如何将从procedure返回的特定列插入临时表?在我的案例中,来自proc2的第一列。

2 个解决方案

#1

Short answer: This is not possible.

简短的回答:这是不可能的。

Long answer: This is not possible, but there are workarounds to achieve the same effect.

答案很长:这是不可能的,但有一些解决方法可以达到同样的效果。

Option 1: Add a dummy column to your temporary table. Insert all columns from within Proc2 into your temporary table. Then drop the dummy column. Dirty.

选项1:向临时表添加虚拟列。将Proc2中的所有列插入临时表。然后放下虚拟列。脏。

Option 2: Add a parameter to Proc2, a BOOLEAN is probably a good choice. Insert more or less columns depending on the parameter value. Less dirty.

选项2:向Proc2添加参数,BOOLEAN可能是一个不错的选择。根据参数值插入更多或更少的列。不那么脏。

Option 3: Do you really need Proc2 be a procedure? In other words, does it really modify the data (or more generally, the environment) before selecting data? In other words, wouldn't a view be more suitable in this case?

选项3:您真的需要Proc2作为程序吗?换句话说,在选择数据之前,它是否真的会修改数据(或更一般地说,环境)?换句话说,在这种情况下,视图不会更合适吗?

[edit]

Thanks to James Holderness' pointer, I realized that you may not be aware that a stored procedure has no return value (there is no way around it). I took it for granted that by "Proc2 returns data", you actually meant "Proc2 writes into TempWorkedHrs some data that Proc1 will read back". If you can, please avoid this approach (either option 1. or 2. is dirty anyways). You most probably need a view here.

感谢James Holderness的指针,我意识到你可能没有意识到存储过程没有返回值(没有办法绕过它)。我理所当然地认为,通过“Proc2返回数据”,你实际上意味着“Proc2向TempWorkedHrs写入一些Proc1将读回的数据”。如果可以,请避免这种方法(选项1.或2.无论如何都是脏的)。你最有可能需要一个视图。

#2

This is not a good practice but you could use triggers to execute your procedures.

这不是一个好习惯,但您可以使用触发器来执行您的程序。

#1