mysql - 'select into' 语句在带有游标的 mysql 存储过程中不起作用

Question

这里是问题所在:temprequest 是我从中获取行的表。我在获取时根据 temprequest 表查找可交付表的 deliverableId 列。因此我使用 select into 语句。但是我放置查找值的变量仅适用于获取的第一行。

“开始结束” block :

DECLARE no_more_rows BOOLEAN;
DECLARE nr_rows INT DEFAULT 0;  
DECLARE loop_cntr INT DEFAULT 0;

declare delId int;

declare vtname varchar(200);
declare tversion varchar(200);
declare custId int;
declare prod varchar(200);

DECLARE c_temp CURSOR FOR select tname, version,BuID,BuprodName from temprequest;
SET no_more_rows = False;

OPEN c_temp;
select FOUND_ROWS() into nr_rows;

the_loop:LOOP
    FETCH c_temp into vtname,tversion,custId, prod;
    IF no_more_rows THEN            
        LEAVE the_loop;
    END IF;
    -- statements for each request record
    Set delId= (SELECT deliverableId  
                FROM deliverable 
                WHERE deliverable.Product_prodName =vtname AND deliverable.version = tversion);
    INSERT INTO request VALUES (delId, custId, prod);               
    SET loop_cntr = loop_cntr + 1;

END LOOP the_loop;
CLOSE c_temp;

Answer 1

要在没有光标的情况下完成相同的操作，请使用 (UPDATED):

INSERT INTO request 
SELECT 
    deliverable.deliverableId   as delId,
    qwe.BuID            as custId,
    qwe.BuprodName          as prod

FROM deliverable 
inner join 
(select tname, version, BuID,BuprodName 
from temprequest) 
    as qwe (tname, version, BuID,BuprodName) 
        on deliverable.Product_prodName = qwe.vtname 
       AND deliverable.version = qwe.tversion

我刚刚意识到 BuID 和 BuprodName 在这里是常量，除了插入语句之外没有在任何地方使用。