Mysql cursor issue?
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.
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.
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
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties: Asensitive: The server may or may not make a copy of its result table.
Cursors are particularly useful in stored procedures. They allow you to use only one query to accomplish a task that would otherwise require several queries. However, all cursor operations must execute within a single procedure.
Cursor is a Database object which allows us to process each row and manipulate its data. A Cursor is always associated with a Select Query and it will process each row returned by the Select Query one by one.
There is no THEN
nor END IF
keywords, the procedure cannot compile.
Check this link for proper syntax of IF
statement: http://dev.mysql.com/doc/refman/5.7/en/if.html
Use EXIST
operator 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
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With