从select语句和参数中插入值的mysql存储过程。

I'm writing a stored procedure to select multiple values from a table, and then insert one each value into a new row in a different table along with two parameters which are passed into the function.

我正在编写一个存储过程，从表中选择多个值，然后将每个值插入到不同表中的新行中，并将两个参数传递给函数。

All the documentation I can find shows how to insert results selected, not insert results selected along with parameters, it's that syntax I want to know.

我能找到的所有文档都显示了如何插入已选择的结果，而不是插入随参数选择的结果，这是我想知道的语法。

function (a, b)

select c from table
 for each result, 
    insert (a,b,c) into newtable (a,b,c)

In the above pseudo code the value c is retrieved from the select statement, but the values for a and b come from the stored procedures arguments. The select statement might return multiple values.

在上面的伪代码中，值c从select语句中检索，但是a和b的值来自存储过程参数。select语句可能返回多个值。

So lets say in table we have:

在表格中，我们有

id c
1 apple
2 orange
3 pear

id 1 apple 2 orange 3 pear

Then I call the stored procedure:

然后我调用存储过程:

procedure(recipe, book)

then we would see in newtable:

然后我们会在newtable中看到:

id a b c
1 recipe book apple
2 recipe book orange
3 recipe book pear

id: a b c 1食谱书苹果2食谱书橘子3食谱书梨。

1 个解决方案

#1

Ok I'll rewrite it as a procedure so you understand a and b are not values from table table:

我把它重写成一个过程，这样你就知道a和b不是表中的值:

CREATE PROCEDURE procedure(IN paramA VARCHAR(50), IN paramB VARCHAR(50)) 
     BEGIN 
        INSERT INTO newtable
        SELECT paramA, paramB, c
        FROM table
     END;

So, as in your example, you would then call it like this:

所以，在你的例子中，你会这样称呼它:

CALL procedure('recipe','book');

#1