MySQL 带游标的Mysql存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20865592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql Stored procedure with cursor
提问by user3122164
Mysql cursor issue?
mysql游标问题？
I have written a stored procedure which will travel's record from one table and insert those into 2-3 different tables using insert statements.
我编写了一个存储过程，它将从一个表中传输记录，并使用插入语句将它们插入到 2-3 个不同的表中。
Problem is that i am checking if record is not exists in table1 then I am inserting record from temptable to table1 ,table2 sequentially ,but the condition is having some problem i don't know it its always going into else part.
问题是我正在检查 table1 中是否不存在记录，然后我将记录从 temptable 依次插入到 table1、table2，但条件有一些问题，我不知道它总是进入其他部分。
Code sample is as follows:
代码示例如下：
CREATE PROCEDURE `insertData`(In clientNo INT,In usedID INT)
BEGIN
declare mame varchar(100);
declare address varchar(100);
declare city varchar(50);
declare IdentityNO1 varchar(20)
declare cur1 cursor for select * from temptable;
declare continue handler for not found set done=1;
SET @clientNo = clientNO;
SET @userID = userID;
set done = 0;
open cur1;
igmLoop: loop
fetch cur1 into Name,Address,City,IdentityNO1,clientNo;
if done = 1 then leave igmLoop; end if;
//If no record exists in some records table1,table2.
IF ( (SELECT COUNT(*) FROM table1
WHERE IndentityNo=IdentityNo1
AND clientNo=@clientNo) < = 0)
INSERT INTO table1 (Name,IdentityNO) VALUES (name,IdentityNO1);
INSERT INTO table2 (Address,City) VALUES(address,city);
ELSE
INSERT INTO tblexceptional(Name,Address,City,IdentityNo)
VALUES(name,address,city,IdentityNo1);
end loop igmLoop;
close cur1;
END
回答by krokodilko
There is no THENnor END IFkeywords, the procedure cannot compile.
Check this link for proper syntax of IFstatement: http://dev.mysql.com/doc/refman/5.7/en/if.html
Use EXISToperator instead of (SELECT count(*)... ) <=0,
read this link to know the reason: http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx
没有THENnorEND IF关键字，程序无法编译。
检查此链接以获取正确的IF语句语法：http: //dev.mysql.com/doc/refman/5.7/en/if.html
使用EXIST运算符代替(SELECT count(*)... ) <=0，
阅读此链接以了解原因：http: //sqlblog.com /blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx
IF EXISTS(
SELECT null FROM table1
WHERE IndentityNo=IdentityNo1
AND clientNo=@clientNo
)
THEN
INSERT INTO table1 (Name,IdentityNO) VALUES (name,IdentityNO1);
INSERT INTO table2 (Address,City) VALUES(address,city);
ELSE
INSERT INTO tblexceptional(Name,Address,City,IdentityNo)
VALUES(name,address,city,IdentityNo1);
END IF;
I recommend using some prefixes for procedure arguments and variable names to avoid ambiguity, for example use p_for parameters and v_for variables. It's hard to guess, looking at this code, which name is a column name, a variable or a procedure parameter. This can lead to mistakes and errors.
Avoid using SELECT *- this code will fail if someone will change the table structure. Explicitely list required columns in the cursor declaration:
我建议为过程参数和变量名称使用一些前缀以避免歧义，例如p_用于参数和v_变量。看这段代码很难猜到，哪个名字是列名、变量还是过程参数。这可能会导致错误和错误。
避免使用SELECT *- 如果有人更改表结构，此代码将失败。在游标声明中明确列出所需的列：
declare cur1 cursor for
select name,Address,City,IdentityNO,clientNo
from temptable;
The corrected procedure might look like this:
更正后的程序可能如下所示：
CREATE PROCEDURE `insertData`(In p_clientNo INT,In p_usedID INT)
BEGIN
declare v_name varchar(100);
declare v_address varchar(100);
declare v_city varchar(50);
declare v_IdentityNO varchar(20)
declare v_clientNo int
declare cur1 cursor for
select name,Address,City,IdentityNO,clientNo
from temptable;
declare continue handler for not found set done=1;
set done = 0;
open cur1;
igmLoop: loop
fetch cur1 into v_name,v_Address,v_City,v_IdentityNO,v_clientNo;
if done = 1 then leave igmLoop; end if;
//If no record exists in some records table1,table2.
IF EXISTS( SELECT 1 FROM table1
WHERE IndentityNo = v_IdentityNo
AND clientNo = v_clientNo)
INSERT INTO table1 (Name,IdentityNO) VALUES (v_name,v_IdentityNO);
INSERT INTO table2 (Address,City) VALUES(v_address,v_city);
ELSE
INSERT INTO tblexceptional(Name,Address,City,IdentityNo)
VALUES(v_name,v_address,v_city,v_IdentityNo);
END IF;
end loop igmLoop;
close cur1;
END
