使用存储过程作为子查询 [英] Using a stored procedure as subquery

问题描述

我已经像这样在MySQL中创建了存储过程

I've create a stored procedure in MySQL like the this

CREATE DEFINER=`root`@`localhost` PROCEDURE `my_proc`(IN var1 VARCHAR(25))
BEGIN
select (sum(er)*9)/(out/3) as era from table1 where id = var1 group by id;
END

我还有一张想要从中获取此信息的表.我想做这样的事情(伪代码)

I have another table that I'd like to get this information from. I'd like to do something like this (pseudo code)

select id, column1, column2, (call my_proc(table1.id)) as era from table1

很容易让我的存储过程计算出一些信息，并将其作为列返回到该查询中.

Basiclly I'm having my stored procedure calculate some information and return it as a column into that query.

这里存储过程是正确的解决方案吗?

Is a stored procedure the right solution here?

**请注意，在伪查询中，表名应与存储过程中的表名相同.

**Note in the pseudo-query the table name is supposed to be the same as in the stored procedure.

推荐答案

您可以定义存储函数而不是存储过程.

You can define a stored function instead of a stored procedure.

CREATE DEFINER=`root`@`localhost` FUNCTION `my_func`(IN var1 VARCHAR(25))
  RETURNS NUMERIC(9,2)
BEGIN
  RETURN (SELECT (SUM(er)*9)/(out/3) AS era FROM table1 WHERE id = var1);
END

然后，您可以像调用函数一样简单地调用它:

Then you can call it simply as you would call a function:

SELECT id, column1, column2, my_func(table1.id) AS era FROM table1

必须确保所存储的函数返回单个标量，以便在选择列表中使用.

The stored function must be guaranteed to return a single scalar to be usable in your select-list.

我删除了GROUP BY，因为它是多余的.

I removed the GROUP BY, since it's superfluous.

上面的示例有点可疑，因为没有理由调用这样的函数来计算单行的SUM.但是我想您会想到一些更复杂的东西.

The example above is kind of suspicious, because there's no reason to call a function like this to calculate the SUM over a single row. But I guess you have something more complex in mind.

这篇关于使用存储过程作为子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！