'select into'语句不能在带有游标的mysql存储过程中工作

Here is the issue: temprequest is the table where i fetch rows from. I look up the deliverableId column of deliverable table based on the temprequest table while fetching. therefore i use a select into statement. but the variable where I put the looked up value is only working for the first row fetched.

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

The 'Begin end' 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;

2 个解决方案

#1

To accomplish the same without cursor use (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

I've just realised that BuID and BuprodName are constants here and not used anywhere besides insert statement.

我刚刚意识到BuID和BuprodName是常量,除了insert语句之外没有使用过任何地方。

#2

The no_more_rows variable gets set to true if any query within the cursor doesn't fetch any result. In your case if the following query doesn't fetch any result, even in that case no_more_rows will evaluated as true.

如果游标中的任何查询未获取任何结果,则no_more_rows变量将设置为true。在您的情况下,如果以下查询未获取任何结果,即使在这种情况下,no_more_rows也将评估为true。

SELECT deliverableId  
FROM deliverable 
WHERE deliverable.Product_prodName =vtname AND deliverable.version = tversion

Solution for this is to set no_more_rows to false at the end of the cursor, so that even if it evaluates to true during cursor execution, it will be reset and will be exited only when there are no rows returned by the cursor query.

解决方法是在游标末尾将no_more_rows设置为false,这样即使在游标执行期间它的计算结果为true,它也会被重置,并且只有在游标查询没有返回任何行时才会退出。

he_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;
    SET no_more_row = false
END LOOP the_loop;

#1