没有THEN
nor END IF
关键字,程序无法编译。
检查此链接的正确语法IF
陈述:http://dev.mysql.com/doc/refman/5.7/en/if.html http://dev.mysql.com/doc/refman/5.7/en/if.html
Use EXIST
运算符而不是(SELECT count(*)... ) <=0
,
阅读此链接即可了解原因:http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx 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;
我建议对过程参数和变量名称使用一些前缀以避免歧义,例如使用p_
对于参数和v_
对于变量。看看这段代码,很难猜测哪个名称是列名、变量还是过程参数。这可能会导致错误和错误。
避免使用SELECT *
- 如果有人更改表结构,此代码将失败。在游标声明中显式列出所需的列:
declare cur1 cursor for
select name,Address,City,IdentityNO,clientNo
from temptable;
更正后的过程可能如下所示:
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