Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Saturday, October 22, 2005

Nesting MySQL Cursor Loops

Handling cursor loops in MySQL keeps puzzling people.

Single Cursor Loops


The common case is to have a simple cursor loop. Each record is fetched from the cursor until there are now more records, and for each record some work is performed.
Let's take a look at this snippet:

01 begin
02 declare v_col1 int; -- define the data to grab from the cursor
03 declare no_more_rows boolean := FALSE; -- define the loop control variable
04 declare cursor1 cursor for -- define the set to iterate through
05 select col1
06 from MyTable;
07 declare continue handler for not found -- Capture cursor exhaustion event
08 set no_more_rows := TRUE; -- modify the loop control variable, then return
09
10 open cursor1; -- execute query, and save the result so we can iterate it
11 LOOP1: loop -- start repeatable region of code
12 fetch cursor1 -- grab data from current cursor record
13 into v_col1 -- this will raise NOT FOUND if the cursor is exhausted
14 ;
15 if no_more_rows then -- check the loop control variable, the handler might've modified it
16 close cursor1; -- free resources
17 leave LOOP1; -- stop iterations
18 end if;
19 --
20 -- Process data here
21 --
22 end loop LOOP1;
23 end;


This is a fragment of a MySPL Procedure.
To summarize, the CURSOR is processed by iteration through an ordinary LOOP that happens to FETCH rows from the cursor. When the cursor's exhausted, the NOT FOUND condition is raised, transferring control to a HANDLER that sets a flag. This flag is checked inside the loop, right after the fetch, and when it is set, iteration is terminated.

Handling cursor exhaustion


An important point to make right away is that, unlike the OPEN (line 10), FETCH (line 12) and CLOSE (line 16) statements, there is no formal relationship between the HANDLER declaration (lines 07..08) and the CURSOR declaration (lines 04..06). For instance, the HANDLER declaration never refers to cursor1, the name of the cursor. It just 'happens' to be the case that FETCH-ing eventually leads to a situation in wich the cursor will have reached it's last record. Such an exhausted cursor will raise the NOT FOUND condition on attempt to FETCH from it again. This condition can then be captured by the, scopewise, nearest appropriate HANDLER.

Nested Cursor Loops


Sometimes you need to nest two cursors. I don't want to discuss what problems are appropriately solved with this device right now. Let me just say that most of the time someone is using this device, they are doing that because they don't know how to write a join properly.
The problem with nesting curors is this: because we can't associate a HANDLER with a particular CURSOR we have to think of a way to know wich one of the cursors is exhausted once the NOT FOUND condition is raised. We really need to know that, because our loop controls depend on it. We definitely want to terminate the corrsponding loop when one of the cursors will be exhausted, don't we?

Using a separate block


Perhaps one of cleanest ways to do this is to rely on the scopewise propagation of the condition. By putting the inner LOOP into it's own BEGIN..END block, we have created the possibility to give that block it's own NOT FOUND handler. A NOT FOUND condition raised by a FETCH in the inner LOOP will thus be captured by the appropriate HANDLER declared in the inner BEGIN..END block. That's because that inner block's handler is nearer than the handler declared in the outer block. From there, the inner loop can easily be terminated:

01 BLOCK1: begin
02 declare v_col1 int;
03 declare no_more_rows boolean1 := FALSE;
04 declare cursor1 cursor for
05 select col1
06 from MyTable;
07 declare continue handler for not found
08 set no_more_rows1 := TRUE;
09 open cursor1;
10 LOOP1: loop
11 fetch cursor1
12 into v_col1;
13 if no_more_rows1 then
14 close cursor1;
15 leave LOOP1;
16 end if;
17 BLOCK2: begin
18 declare v_col2 int;
19 declare no_more_rows2 boolean := FALSE;
20 declare cursor2 cursor for
21 select col2
22 from MyOtherTable
23 where ref_id = v_col1;
24 declare continue handler for not found
25 set no_more_rows2 := TRUE;
26 open cursor2;
27 LOOP2: loop
28 fetch cursor2
29 into v_col2;
30 if no_more_rows then
31 close cursor2;
32 leave LOOP2;
33 end if;
34 end loop LOOP2;
35 end BLOCK2;
36 end loop LOOP1;
37 end BLOCK1;

Note that the inner BEGIN..END block's handler can never capture the NOT FOUND condition raised by the FETCH in the outer block, because conditions are propagated from the inside to the outside.
Another advantage of this approach is that the inner loop is almost entirely self-contained. The only thing that binds the inner BEGIN..END block to the outer one is a reference to the variable v_col1 in the decalaration of cursor2. Everything that has to do with the inner loop is defined in just one place, BLOCK2.

Resetting the loop control variable


The disadvantage of the previous method is of course the duplication of code. Apart from the cursor itself, we have to declare the loop control variable and the handler twice too. Sometimes, this is way too much trouble. Lucky for us, there's a way to evade all that, by resetting the loop control variable just before the inner loop terminates. Check it out:


00 begin
01 declare no_more_rows boolean default false;
02 declare v_col1 int;
03 declare v_col2 int;
04 declare cursor1 cursor for
05 select col1
06 from MyTable
07 ;
08 declare cursor2 cursor for
09 select col2
10 from MyOtherTable
11 where ref_id = v_col1;
12 declare continue handler for not found
13 set no_more_rows := true;
14
15 open cursor1;
16 LOOP1: loop
17 fetch cursor1 into v_col1;
18 if no_more_rows then
19 close cursor1;
20 leave LOOP1;
21 end if;
22 open cursor2;
23 LOOP2: loop
24 fetch cursor2 into v_col2;
25 if no_more_rows then
26 set no_more_rows := false;
27 close cursor2;
28 leave LOOP2;
29 end if;
30 end loop LOOP2;
31 end loop LOOP1;
32 end;


Observe te difference between the handling of the loop termination. For the outer loop, we see the usual sequence of statements to close the cursor and to leave the loop (lines 18..21). For the inner loop, we use an additional assignment to reset the loop control variable (line 26). This ensures the outer loop gets a chance to roll off untill it's exhausted too.

Packing cursor loops into stored procedures


Another approach would be to pack all the code for the dependant, inner cursor loop into a separate stored procedure and have the outer loop CALL that. This approach resembles the first one where we put the inner loop in it's own BEGIN..END block:


01 create procedure p_inner_loop(
02 IN p_col1 int
03 )
04 begin
05 declare v_col int;
06 declare no_more_rows boolean := FALSE;
07 declare cursor cursor1 for
08 select col2
09 from MyOtherTable
10 where ref_id = p_col1;
11 declare continue handler for not found
12 set no_more_rows := TRUE;
13 open cursor1;
14 LOOP1: loop
15 fetch cursor1
16 into v_col;
17 if no_more_rows then
18 close cursor1;
19 leave LOOP1;
20 end if;
21 end loop LOOP1;
22 end;


And


01 BLOCK1: begin
02 declare v_col1 int;
03 declare no_more_rows boolean1 := FALSE;
04 declare cursor1 cursor for
05 select col1
06 from MyTable;
07 declare continue handler for not found
08 set no_more_rows1 := TRUE;
09 open cursor1;
10 LOOP1: loop
11 fetch cursor1
12 into v_col1;
13 if no_more_rows1 then
14 close cursor1;
15 leave LOOP1;
16 end if;
17 call p_inner_loop(v_col1);
18 end loop LOOP1;
19 end BLOCK1;


This approach is especially useful when you want to be able to use that inner loop from other contexts as well. When cursor processing is becoming complex, it's probably a good idea to use this technique as a "divide and conquer" strategy to keep development managable. For example, this technique allows you test the different cursor loops separate from each other, wich can be a great aid in debugging.

104 comments:

Anonymous said...

I found it very useful, as compared to mysql online documentation.
Milind Paralkar

Roland Bouman said...

Hi Milind,

that's great, I'm so glad it helped.

Cursor loops, and nesting them still seems to be misunderstood by quite some people. I'm just glad this little article helped you using them.

kind regards,

Roland

Anonymous said...

declare no_more_rows boolean1 := FALSE;

should be no_more_row1 boolean := FALSE;?

-K

Roland Bouman said...

Yes -K (?)

you are absolutely right. Thanks for the spot.

Anyone wondering were the hack this comes from, check out the second listing in the blog. There, the declaration should indeed be in accordance with the correction.

Thanks again!

Odimar said...

for me the cursor only work when a put set v_done := false in all loops

OPEN cxReport; -- Open cursor
LOOP2: loop -- do until
fetch cxReport into vReportName; -- read record
if v_done then -- if found end of cursor
set v_done := false; -- set found as false
close cxReport; -- close cursor
leave LOOP2; -- leave loop
end if;

OPEN cxEnv;
LOOP3: loop
fetch cxEnv into vEnvName;
if v_done then
set v_done := false;
close cxEnv;
leave LOOP3;
end if;

SET vCount = 0;

OPEN cxBill;
LOOP4:loop
fetch cxBill into vBill;

if v_done then
set v_done := false;
close cxBill;
leave LOOP4;
end if;


SET vLineDuplicate = 0;
-- check bill period and build the correct start and end date for cbss
IF (MONTH(vDate) = 1 AND vBill = 28) THEN
SELECT CONCAT(YEAR(vDate)-1,'-','12','-',vBill,' 00:00:00') INTO vBillPeriod;
ELSEIF (MONTH(vDate) = 1 AND vBill <> 28) THEN
SELECT CONCAT(YEAR(vDate),'-',MONTH(vDate),'-',vBill,' 00:00:00') INTO vBillPeriod;
ELSEIF (MONTH(vDate) <> 1 AND vBill = 28) THEN
SELECT CONCAT(YEAR(vDate),'-',MONTH(vDate)-1,'-',vBill,' 00:00:00') INTO vBillPeriod;
ELSE
SELECT CONCAT(YEAR(vDate),'-',MONTH(vDate),'-',vBill,' 00:00:00') INTO vBillPeriod;
END IF;

SELECT CONCAT("CBSOI",vReportName) INTO vReportID;
SET vCount = vCount + 1;

Insert Into xRet(line1,line2,line3,line4)
Select vReportID,vBillPeriod,vEnvName,vCount;
-- Select records to see if there are duplicate recors
SELECT Count(*) as LineDuplicate INTO vLineDuplicate
FROM cbss_psp_total
WHERE ReportID = vReportID
AND BillingDate = vBillPeriod
AND DistCode = vEnvName
GROUP BY ReportID, ReportName, TaxType, Ref, ProcessingDateTime, ReportType, DistCode, DistDesc, BillingDate, State, PSP, TaxLevel, Location, Description, TaxRate, CurrentBilling, ExemptRevenue, TaxableRevenue, TotalTaxesBilled, AdjustmentTax, WORecoveryTax, NetTax
HAVING LineDuplicate > 1
LIMIT 1;

-- SELECT IFNULL(vLineDuplicate,0) INTO vLineDuplicate;
-- if has duplicate records
IF vLineDuplicate > 1 THEN
select "entre";
-- Insert the duplicate records into tmp_duplicate_records_report table
INSERT INTO tmp_duplicate_records_report(System, DuplicateAmount,DuplicateTimes,Impact,LineInfo,Id_User,UUID,DeletedTime)
SELECT vReportID,CurrentBilling,Count(*) as LineDuplicate,(CurrentBilling * (Count(*)-1)),CONCAT_WS(' | ',ReportID, ReportName, TaxType, Ref, ProcessingDateTime, ReportType, DistCode, DistDesc, BillingDate, State, PSP, TaxLevel, Location, Description, TaxRate, CurrentBilling, ExemptRevenue, TaxableRevenue, TotalTaxesBilled, AdjustmentTax, WORecoveryTax, NetTax),vId_User,vUUID, NOW()
FROM cbss_psp_total
WHERE ReportID = vReportID
AND BillingDate = vBillPeriod
AND DistCode = vEnvName
GROUP BY ReportID, ReportName, TaxType, Ref, ProcessingDateTime, ReportType, DistCode, DistDesc, BillingDate, State, PSP, TaxLevel, Location, Description, TaxRate, CurrentBilling, ExemptRevenue, TaxableRevenue, TotalTaxesBilled, AdjustmentTax, WORecoveryTax, NetTax
HAVING LineDuplicate > 1
LIMIT 1;

-- update records duplicated in tmp_checklist table changing the Image System Index
UPDATE tmp_checklist SET Status = 'DUPLICATE',
IdImage = vDUPLICATE
WHERE System = 'CBSS' And Environment = vEnvName And ReportID = vReportName
And BillPeriod = vBill And Month = MONTH(vBillPeriod) And Year = YEAR(vBillPeriod)
and Id_User = vId_User and UUID = vUUID and tablename = vTableName;
END IF;

end loop LOOP4;
end loop LOOP3;
end loop LOOP2;


but I still have problem with the 3 loop it fetch only the first record what can I do to solve the problem?

Roland Bouman said...

Hi Odimar,

you say that it only works for you if you reset the loop variable v_done for *ALL* loops.

If your LOOP2 really is the outmost loop, it shouldn't matter whether you reset the loop control variable or not. Either way, once the leave LOOP2 statement is executed, control is transferred to the point beyond the end LOOP2 statement. Assuming v_done is not used after that, it doesn't make a difference whether you reset it or leave it as it is.

However, your comment did make me realize that it is indeed OK to reset it also for outmost loop; this means all loops can be set up identically, which I like. Thanks for the insight!

As for your remaining problem...Ouch, difficult to say. Usually, problems are caused by an orphan SELECT or SELECT..INTO statement inside the loop. You see, if such a statement does not return any rows, the NOT FOUND condition is raised too, unintentionally firing the handler.

In your particular case, I can see one statement that could be causing this:

SELECT Count(*) as LineDuplicate
INTO vLineDuplicate
FROM cbss_psp_total
WHERE ...
GROUP BY ...
HAVING ...
LIMIT 1;

Normally, COUNT(*) queries always return a row, but in your case, the HAVING condition can be responsible for filtering out rows.

So, you could try and see of your problem persists if you remove the HAVING clause.

I hope that helps, good luck!

vaibogam said...

Really a wonderful article. It helped me lot.

vaibogam said...

Really a wonderful article. It helped me lot. Keep posting... Thanks.

Anonymous said...

By Trying the Demo given its again showing syntax error.

Roland Bouman said...

Hi Anonymous,

what demo, what syntax error? Me no understandie...

Anonymous said...

Excellent guys. Keep it going.

Thanks and Regards,
Ganesh L

Anonymous said...

this is a very helpful article. thanks for sharing your knowledge. ^_^

Anonymous said...

Thank you for great tutorial. It's helped me a lot. I couln't find anything good on mysql site. You are the best!!!!!

Roland Bouman said...

Thanks all for you kind comments.

Please do note that I think nesting cursors is a practice that really should be avoided at any cost.

In most of the cases the nested loops can be successfully refactored into a simple join. Please google my blog on "Refactoring Cursors" for a hint into this direction.

best regards,

Roland Bouman

Chris said...

Your the fucking man! I wish more MySql tutorials were like yours.

Roland Bouman said...

Hi Chris,

glad you liked the tutorial, thanks for your comments.

Having said that - please refrain from using the f-word, it might offend the other readers - TIA.

If you are looking for more tutorials on cursors, just use the "Search Blog" feature on this blog to locate more articles about cursors or MySQL stored procedures.

Kind regards,

Roland.

Puraz said...

This page has been INVALUABLE. It covered all the topics I was looking for, and explained everything extremely well. Thanks a million.

Alex said...

Thank you. It was very helpful to me.
Unfortunately mysql documentation is poor.
Great examples.

Ricardo Uhalde said...

Awesome article Roland, solved my problem with just a SET statement!!

BTW, could you elaborate a little bit more on how to avoid cursors? what one can do to traverse all records of a table without using cursors?

In my case, I use a cursor to read a temporary table while I update some rows. I've readed in MYSQL help that is not wise to use a cursor and update the same table that cursor points to, I don't know the implicacies of that technique but I followed their advice and used a temporary table.

Thanks again for you help!

Perdigão said...

Hi Roland.

I was searching a tutorial/article/faq/any-kind-of-explanation to this.

I'm a brazilian developer and started with PL/SQL recently.

Thank you.

Zeft said...

sweeeeeet!!!
thx a lot roland!

Anonymous said...

Hi Roland,

Thanks for the article, it was just the thing I was looking for. Helped a lot.

cheers

Anonymous said...

I have this stored procedure

CREATE PROCEDURE `spCar`()
begin
declare exit1 int default 0;
declare rec1 char(50);
declare rec2 char(50);
declare vgas int;
declare cur1 cursor for select distinct car,driver from dates;
declare exit handler for not found set exit1=1;
open cur1;
repeat
fetch cur1 into rec2,rec1;

-- select gas into vgas from someTable where car=rec2 and driver=rec1;
select rec2,rec1,c_rest;

until exit1=1 end repeat;
close cur1;
end


if I uncommnet the line above ... the sp will not show anything if at some point the vgas variable is null

if I leave it commented will display the right number of records but I need my sp with vgas variable ... and this variable might be null at some point

Roland Bouman said...

Hi anonymous, the problem is most likely that the NOT FOUND condition is triggered due to your select into fetching no rows.

You should actually wonder why you need that statement there at all - why are't you simply joining that? (Read my refactoring mysql cursors article.)

There are other problems with your approach to - the REPEAT construct is the worst possible device for a cursor loop for many reasons.

I have an article about that on this very blog too. Read it I am sure you will get an idea on how to rewrite this.

Let me know if you still have trouble with that.

kind regards,

Roland

Wisdom Hunter said...

Hi Roland
I know this is an old blog but I just found it - I am an Oracle developer teaching myself MySQL, and just making my first foray into stored procedures and cursors in MySQL. Contrary to the first poster, I found your detailed analysis of the 3 looping options quite useful. So - thank you !

Anonymous said...

EXCELLENT!!!
EXCELLENT!!!EXCELLENT!!!
EXCELLENT!!!EXCELLENT!!!EXCELLENT!!!

Thank you Roland!

Kannan said...

hai,
how to get the cursor records count ?

like oracle statement..

cursorName.count

like the above, is it possible to find the cursor records count in MySQL?

Thanks,

Roland Bouman said...

Hi Kannan,

No, MySQL does no support cursor attributes. Of course, it's a slight effort to add a local variable to do the counting yourself.

Samuel said...

Hello,

Thanks a lot for yet another very helpful article. I keep ending back up on your blog when I am looking for reliable help about interesting stuff such as nested loops (which I am trying to stay away from) or the Pentaho BI platform (which I am happily embracing).

Thanks a lot for all this.

Roland Bouman said...

Hi Samuel,

thanks for your kind words ;)

I'm glad it's useful for you.

Kind regards,

Roland

A. Pradhana said...

Thanks for the article, thats great, very useful and helpful for my cases,
Once again, thanks!

avanzaweb said...

Very very useful. i'm a newbie on cursors

'JSA said...

Great ARTICLE... it saved my life today. Me as a beginner to stored procs... :) Thank you a lot!

Alice said...

I have this cursor built into the following stored procedure. The loop keeps calling my second procedure eventhough it should exit when there are no rows. (I tried a few fixes to solve this which had the desired result, but the data doesn't update properly) Please give me a clue. It's no rush since I can live with it for now.

CREATE PROCEDURE MyProcedure2()

BEGIN

#Cursor related declarations
declare v_col varchar(30);
declare no_more_rows boolean default FALSE;
declare c1 cursor for

select distinct col1
from myTable
where col2 is null; -- this should exhaust with processing of the rows

declare continue handler for not found
set no_more_rows := TRUE;

open c1;

LOOP1: loop
fetch c1 into v_col1;
if no_more_rows then close c1;
leave LOOP1;
end if;
#We call the procedure the updates c2
#to a non null value
call proc2(v_col1);
end loop LOOP1;
END;

Roland Bouman said...

Hi!

I see something strange! You do

declare v_col varchar(30);

but your code references v_col1, not v_col.

Other than that, it looks good. Can you post some data and the code for proc2 so we can test?

Alice said...

Hi Roland:
Thanks for the prompt reply. Yes, that was a typo on my end, it should read declare v_col varchar(30).
I may not be able to post sample data but below is a description of the process:
proc1 loops through call numbers from a Call Log table and calls proc2 to determine the destination of the call. proc2 is getting the v_col as parameter, and it iteratively chops up digits from the end of the number till a match is found from a Country/Phone no table. A while loop takes care of that as in the sample below:
CREATE PROCEDURE proc2( in pPhone VARCHAR(20))

BEGIN

DECLARE phNoLen, cnt int;
DECLARE cntry varchar(100);
DECLARE SubPhone varchar(20);

SET phNoLen = length(pPhone);
SET cnt = 0;
SET subPhone = pPhone;

myloop:
WHILE length(subPhone) > 0 DO

SELECT description
INTO cntry
FROM CountryPhoneTbl
WHERE phone_no = subPhone;

IF length(cntry)> 0 THEN
update myTable
set Country=cntry
where phone_no like concat(subPhone,'%');
LEAVE myloop;
END IF;
SET cnt=cnt+1;
SET subPhone = SUBSTR(pPhone,1,(phNoLen - cnt));
END WHILE myloop;
END

I tested proc2 it and it works fine. I also tested separately the cursor of the selection from myTable it exits fine. As soon as I implement the call to proc2 it gets stuck(processes fine all the records) deactivating the mysql session.
We have MySQL 5.0.45 on RedHat Linux rel 5.2
Thanks again so very much as due to your article I have been able to solve this task.
Alice

Kevin Bedell said...

I Really liked your example here. It helped me when I was trying to put some similar code together.

Based on some of the things I learned -- combined with other ideas I picked up over time -- I created a base template for a stored procedure using a cursor and posted it on my blog as well

It's here:

example of a stored procedure with a cursor

Alice said...

Hi Kevin:
Thanks for providing the template. Will look through and test it for future use.
In the meantime, I got more background on my procedure. If the row set the cursor gets in proc1 is in the order of thousands, proc2 executes successfully. However, I see a huge number of warnings in the MySQL command line, indicating that no_data was found. I attach below a sample of the execution result:
mysql> call proc1;

Query OK, 0 rows affected, 11233 warnings (1 min 17.66 sec)

mysql> show warnings
-> ;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
...etc

May this be in connection with the update that takes place on some of the rows as we go along? And if so why are there so many?

Anonymous said...

Very Nice. Thanks a lot

Alice said...

Just an update on the progress on my thread above. I created a shell script with a log file in which I collect information about the execution, and it looks like the procedure with the cursor completes fine. However, it is taking a long time to execute which causes the connection to the server where the database is installed to error out and terminate. Will continue looking into it and will keep all abreast with any findings.

Jeffrey said...

Thanks a lot for this post, you really had answered what i was looking for 2 weeks ago. Thanks so much,....

Anonymous said...

Hi Everybody,
I try to write a procedure using a cursor for udpating some rows. It's possible?

Thanks a lot in advance for your help...

Roland Bouman said...

Hi Anonymous,

yes, it's possible. You have to write a regular UPDATE statement inside the cursor loop. So you *cannot* open a cursor FOR UPDATE and directly assgign the values.

That said, this technique is usually a sign of bad design, which can typically be refactored. See this post for a concreate example:

http://www.oreillynet.com/databases/blog/2006/09/refactoring_mysql_cursors.html

albert said...

Hi Roland,

Thanks for the example here, very useful. much better than using repeat.

Just one thing, i might be wrong but it seems your example on the "Using a separate block section" is not nested? Loop1 finishes what its doing before going to Block2.

Like i said, i might be wrong. im new to mysql procedures. just ignore me if i am

Roland Bouman said...

Hi Albert!

hey, good spot! You are absolutely right - I just fixed it...man, this article has been up for years, and all this time there was an error in it - one that defeats the entire point of that particular approach.

I'm much obliged. Thanks :)

SoeL said...

Hi Roland,
Thank you for this article. Nested loops example is very useful!
However, is there a way to create a cursor for select statement that uses string concatenation?
Here's example situation:
I have a routine(procedure) that takes comma separated IDs as a parameter and I want to create a cursor from rows taken from it:
create procedure Update_Whatever(
IN IDsString varchar(10000) /* EG: 1,2,3,4,5 */
IN anotherIDsString varchar(10000) /* EG: 7,8,9,10,22 */
)
declare no_more_rows boolean default false;
declare v_col1 int;
declare v_col2 int;
declare cursor1 cursor for
select col1
from MyTable where id IN (IDsString)
;
declare cursor2 cursor for
CONCAT('select col2
from MyOtherTable
where id IN (', anotherIDsString, ');');
declare continue handler for not found
set v_done := true;

open cursor1;
LOOP1: loop
fetch cursor1 into v_col1;
if v_done then
close cursor1;
leave LOOP1;
end if;
open cursor2;
LOOP2: loop
fetch cursor2 into v_col2;
if v_done then
set v_done := false;
close cursor2;
leave LOOP2;
end if;
/* do_here_whatever_is_needed */
end loop LOOP2;
end loop LOOP1;

When I declare cursor as in "cursor1" declaration - it takes only first integer value. But when I try to declare it as "cursor2" declaration it just doesn't compile at all. Could you please suggest what to do in such situation?

Roland Bouman said...

Hi Soel,

there is no straightforward way to do it. But it is possible, using a hack/workaround.

See, the problem is, is that your list of values is basically a string. But you don't want to use it in the string format, but as a proper list in an IN clause.

Here are 2 ways to deal with it (note that all of this has got nothing to do with nested cursors)

1) For simple queries on tables thatt do not have many rows, you can rewrite your query to use the FIND_IN_SET() function. Your cursor would become:

declare cursor2 cursor for
select col2 from myothertable
where find_in_set(id, anotherIDsString);

Information on FIND_IN_SET() is here: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

However, FIND_IN_SET() is not a real solution for this case. MySQL cannot use an index for this type of query and since your column is an id column performance is going to suck.

2) Parse your string of Ids into separate ids, and use it to generate a temporary table that contains the id values. Use dynamic SQL (prepare syntax, see http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html) to create the table. Then write your cursor as a JOIN against that temporary table.

Ugly, but it should work.

HTH,

Roland.

Nimitto said...

Hi,
I have read elsewhere an article by you where you state that using cursors slows down the process in general. However, you have also pointed out that in some cases, we have to use them.
I have a scenario in which my first query fetches a lot of rows from table 1 (around 30k). Then the second query should fetch some info from a single row in table 2 using some of the values obtained from each row in the initial query.
So say u have table EMPLOYEE with an ID col. Table EMPLOYEE has a 1000 rows. Now my first sql would be select ID from EMPLOYEE;
This gives me a 1000 IDs.
Now using these IDs, for each ID I need to fetch employee details from another table called say DETAILS with a query which looks like:

select d.col1, d.col2, d.col3.. d.coln from DETAILS d where d.colx =

this 2nd query should now be executed a 1000 times for a 1000 IDs of EMPLOYEE table.

Is it possible to avoid cursors in this regard and fasten up the entire process? what would be the best approach.. thanks in advance!

Roland Bouman said...

Hi Numitto,

yes - absolutely. At the very least, you don't need two cursors to do this - just use a JOIN:

SELECT e.id
, ...more columns...
FROM employee e
LEFT JOIN employee_details ed
ON e.id = ed.employee_id

What are you doing for each detail and for each employee? You might be able to put that in the SELECT statment as well, and do away with the first cursor two.

There are only very few scenarios where you can't avoid cursors. One is when you have to CALL a procedure with parameters coming out of a query, or when you need to repeatedly generate some SQL statement based on data coming from a query.

Nimitto said...

I think my requirements might belong to the second of the 2 scenarios you have talked above, but am hoping not, cause right now its taking too much of time to run (over an hour!!)

We basically are doing a check based on a number of sql queries. 2 of these queries return multiple rows.

so you have a chain like :

select col1 from table1; -- returns 1000 rows say
now loop through 1000 rows for each col1 value and do:
select col2 from table 2 where colx = above value of col1 -- returns 1 row
now use this 1 col2 value to fetch more from table3:
select col3 from table3 where colx = above value of col2

this goes on for a few more queries until we get a final value which we match with a stored value and arrive at our conclusion.

The total process involves around 7 queries of which 2 return multiple rows meaning whatever lies below them, has to be executed multiple times. So for those 2, I have used 2 cursors.

Can we do this without cursors and in a faster way? (please say yes)

Roland Bouman said...

Hi Nimitto,

It's impossible to say with the information you gave. You said: "...until we get a final value which we match with a stored value and arrive at our conclusion...". What is this exactly?

The part before that, that sounds like you should use JOINs, not cursors. See my article on refactoring cursors to get an idea for what I mean:

http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

Nimitto said...

Hi Roland,

I had gone through your refactoring article but am not sure it can resolve my case. Here's the procedure I have now:

-------------------------------------
CREATE PROCEDURE complex (IN param_fdn varchar(255), IN param_cellid int(11))

BLOCK1: BEGIN
DECLARE matched BOOLEAN DEFAULT FALSE;
DECLARE matched_counter INT DEFAULT 0;
DECLARE notmatched_counter INT DEFAULT 0;
DECLARE STR VARCHAR(255) DEFAULT NULL;
DECLARE STR2 VARCHAR(255) DEFAULT NULL;
DECLARE bidir VARCHAR(255);
DECLARE no_more_rows1 INT DEFAULT 0;

DECLARE cur_bidir CURSOR
FOR
SELECT UR1.utrancellref FROM Utranrelation UR1 WHERE UR1.utranCellFdn = param_fdn ;

DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET no_more_rows1 = 1;

OPEN cur_bidir;

LOOP1: LOOP
FETCH cur_bidir INTO bidir;
SET STR2 = (SELECT UC1.fdn FROM UtranCell UC1 WHERE UC1.cellid =
(SELECT EUC.cellId FROM ExternalUtranCell EUC WHERE EUC.fdn = bidir));

IF no_more_rows1 THEN
CLOSE cur_bidir;
LEAVE LOOP1;
END IF;

BLOCK2: BEGIN
DECLARE secdir VARCHAR(255);
DECLARE no_more_rows2 INT DEFAULT 0;
DECLARE cell_id INT DEFAULT 0;
DECLARE sec_bidir CURSOR
FOR
SELECT UR3.utrancellref FROM Utranrelation UR3 WHERE UR3.utranCellFdn = STR2;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET no_more_rows2 = 1;
OPEN sec_bidir;

LOOP2: LOOP
FETCH sec_bidir INTO secdir;
SELECT EUC1.cellId into cell_id FROM ExternalUtranCell EUC1 WHERE EUC1.fdn = secdir;

IF no_more_rows2 THEN
SET matched = FALSE;
CLOSE sec_bidir;
LEAVE LOOP2;
END IF;

IF cell_id = param_cellid THEN
SET matched = TRUE;
CLOSE sec_bidir;
LEAVE LOOP2;
END IF;
END LOOP LOOP2;
END BLOCK2;

IF matched THEN
SET matched_counter = matched_counter + 1;
SET STR = concat('BiDirectional exists for CELL with FDN = ', param_fdn);
SELECT STR;
ELSE
SET notmatched_counter = notmatched_counter + 1;
END IF;
END LOOP LOOP1;

SELECT notmatched_counter;
SELECT matched_counter;

END BLOCK1 //
-----------------------------------------

As you can see, I am just fetching values and in the end matching them against a set value and printing out the result.
Can this be refactored using JOINs instead of CURSORS and speed up the entire process?

Roland Bouman said...

Hi Nimitto,

I don't know your data model, so I can't be sure about the relationships and such, but it seems to me that most of the logic of your procedure can be expressed in a single SELECT statement:


SELECT COUNT(EUC1.cellId) AS MATCHED
, SUM(IF(EUC1.cellId IS NULL, 1, 0)) as NOT_MATCHED
FROM Utranrelation UR1
LEFT JOIN ExternalUtranCell EUC
ON UR1.utrancellref = EUC.fdn
LEFT JOIN UtranCell UC1
ON EUC.cellId = UC1.cellid
LEFT JOIN Utranrelation UR3
ON UC1.fdn = UR3.utranCellFdn
LEFT JOIN ExternalUtranCell EUC1
ON UR3.utrancellref = EUC1.fdn
AND param_cellid = EUC1.cellId
WHERE UR1.utranCellFdn = param_fdn ;

Please run this statment and try to see if the counts match your expectation.

To debug, remove the COUNT and SUM columns, and select those columns in the select list that you need to verify what is happening.

If the statement still happens to be slow,you most likely don't have proper indexes in place.

Good luck, and kind regards.

Nimitto said...

Hi Roland,

Apologies for the late reply. When I first saw your solution, I thought, you must be kidding. But after testing it a bit against my application, you might just have hit the nail on the head !!
There's just one thing thats causing a bit of a question mark here for me. If I remove the "AND param_cellid = EUC1.cellId" clause of the query, I still get the same result. I know this check is required, but I strangely get the results even without it.
Let me know if I have missed out on something. And thanks so much for this!

Roland Bouman said...

Hi Nimitto!

Well, this happens to a lot of people.

Especially if you're well at home in a procedural or object-oriented language, the instinct is to work with resultsets as if they are arrays, and you end up iterating them on a row-by-row basis. But RDBMS-es are generally not well suited for this type of operation, in fact they much rather have you using set based operations like JOINs, leaving the details of iteration behind the scenes. In a typical RDBMS a set-based approach will be the most efficient one - often much much more efficient than any procedural approach.

At first, this may feel awkward, and many people don't like this type of magic. But once you get the hang of it, it becomes a second nature, and you'll start to hate procedural languages because you have to do so much effort to get anything done.

Anyway.

I don't know your data model so I can't comment on what you should expect. But I am sure you'll figure it out if you look at each step.

good luck,

Roland

Nimitto said...

yes, I think I can/will

cheers mate! God Bless.

Anonymous said...

Thank you so much for the nested cursor info. It has solved a problem I have been having on a project. And it's working perfectly now! :)
Simon

anurag said...

Are cursor is really working on MYSQL

anurag said...

Hi Roland,

Thank you for this article, very useful.Really your given example is much better then examples given in MYSQL manual.Please keep continue sharing you knowledge.

Anonymous said...

This is great. You helped me a lot. This things you can't find on mysql site. There are only simple examples of evrything.

Mostafa said...

Thank you Roland Bouman.

Mostafa said...

The approach "Using a separate block" didn't
work in MySQL 5.1 properly, although the other
one "Resetting the loop control variable" worked
fine.

Thanks a lot mr.Bouman

Anonymous said...

Hola Roland,
Thank you for share your knowledge here.

I would appreciate if you or someone here could give a hand with the following code. I run that in MySQL 5.0 The SP is created and does not show any error buut it never does the update. the table has data. not sure what i have wrong. thx.Annie.

DROP PROCEDURE IF EXISTS gameanalytics_reports.A_MyProcedure2;

CREATE PROCEDURE gameanalytics_reports.A_MyProcedure2()

begin

declare v_done boolean default FALSE;

declare vMyUserId binary(16);

declare vSwitch int default 1;

declare vCalculateSessionNumber int DEFAULT 1;

declare vDate_Time1 datetime;

declare vDate_Time2 datetime;

declare cursor1 cursor for

select distinct UserId

from gameanalytics_reports.A_UserbySession order by UserId

;

declare cursor2 cursor for

select Date_Time

from gameanalytics_reports.A_UserbySession

where UserId= vMyUserId order by UserId,Date_Time ;

declare continue handler for not found

set v_done := true;





open cursor1; /* llegue borrando hasta aqui e incrediblemente no hace nada ni un single updade */

LOOP1: loop

fetch cursor1 into vMyUserId;

if v_done then

close cursor1;

leave LOOP1;

end if;

Set vSwitch = 1;

set vCalculateSessionNumber = 1;

open cursor2;

LOOP2: loop

fetch cursor2 into vDate_Time2;

if v_done then

set v_done := false;

close cursor2;

leave LOOP2;

end if;

if vSwitch = 1 then /* if vSwitch*/

set vDate_Time1 = vDate_Time2;

set vSwitch = 2;

else

/* if the time is less than 31 minutes the session is the same,otherwise the session increase 1 */

If vDate_Time2 - vDate_Time1 < 31 then

set vCalculateSessionNumber = vCalculateSessionNumber;

else /* the session increase 1 */

set vCalculateSessionNumber = vCalculateSessionNumber + 1;

end if;

update gameanalytics_reports.A_UserbySession

set Cal_SessionId = vCalculateSessionNumber

where UserId = vMyUserId

and Date_Time = vDate_Time2;

set vSwitch = 2;

set vDate_Time1 = vDate_Time2;

end if; /* end if vSwtich*/

end loop LOOP2;

end loop LOOP1;

end;

MySQL-Noobie said...

Thank you Roland Bouman, you helped me complete a stored procedure that was driving me nuts.

Anonymous said...

Mr Bouman, I have read your posts (and the refactoring one) with great interest. I am about to embark in a project that many have tackled before me: i.e. calculating technical indicators (moving averages, and the likes) for several stocks over a large period of time. I can easily do simple moving average with a single statement but there are situations where I don't see how I'll do it without a cursor. My question to you is: is there any good book out there about set-based programming which is fairly close in syntax to MySQL to help me go about this project a bit faster. Due to the sheer amount of data I need to process, speed is my main concern (but also worries me if I end up running out of memory). Thanks for your time and congratulations on your many fascinating and "relatively" easy to understand tutorials (or blogs if you prefer). Regards, E.

Roland Bouman said...

Hi E.,

thanks for the kind words - I'm glad my articles have been useful to you.

If you really need to calculate things like moving averages in pure SQL, you should certainly checkout "SQL for Smarties" by Joe Celko, "The Art of SQL" by Stephane Faroult, and the "MySQL Cookbook" by Paul DuBois.

Other things to consider for advanced analytical queries are using a ROLAP server like Mondrian (aka Pentaho Analysis Services) on top of MySQL. This typically does require you to preprocess your data (ETL) and schema (star schema) into a form that is more convenient for the task at hand.

Saurabh Goyal said...

Hi Roland,

I have try to run nested stored procedure Which syntax as follows :-

CREATE PROCEDURE `sggoyal_spInsertMsgKeyword`()
BEGIN

DECLARE l_intKeywordId INT;
DECLARE l_vchKeyword VARCHAR(30);
DECLARE l_bintMessageId BIGINT;

DECLARE cur1 CURSOR FOR
Select * from spd_keyword_master;

OPEN cur1;
Loop1: LOOP
FETCH cur1 INTO l_intKeywordId,l_vchKeyword;

DECLARE cur2 CURSOR FOR
Select bintMessageId from spd_message_master Where spd_message_master.vchMessage like "% l_vchKeyword %";

OPEN cur2;
loop2: LOOP
FETCH cur2 INTO l_bintMessageId;

insert into spd_message_keyword
values(l_bintMessageId,l_intKeywordId);
END LOOP loop2;
CLOSE cur2;

END LOOP loop1;
CLOSE cur1;

END;

But When I try to run It will show an Mysql error.Please help me out.

Regards
Saurabh Goyal

Roland Bouman said...

Saurabh Goyal,

Not sure how you expect anyone to be able to help if you don't include the error message.

Anyway - there are some very obvious errors in the code. It makes me doubt whether you actually read the blog article at all.

I also encourage to read this article:

http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

Anonymous said...

Thanks for this great article

Anth said...

Thanks for that. While not a nested cursor, I found that a select that was returning no rows was fudging up my cursor, so the tip of a separate block with it's own handler was right on the money.

Anonymous said...

Hi there, I need your help. i have these 2 SP'c

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_metadata`()
begin

declare a varchar(45);
declare b,c int;
declare done int default 0;

declare cur1 cursor for select tablename, start_position,end_position from test.table_metadata order by 1;

declare continue handler for not found set done=1;
open cur1;

read_loop1: LOOP
fetch cur1 into a,b,c;

if done then
close cur1;
leave read_loop1;
end if;
drop view if exists v1;
set @stmt_txt= concat("create view test.v1 (tablename,input_filename) as
select distinct '",a,"', substring(input_filename,", b,",",c,") from test.",a);
prepare stmt from @stmt_txt;
execute stmt;
call test.get_sequence();

end loop;

deallocate prepare stmt;
end



-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_sequence`()
begin
declare start_p int default 1;
declare done1 boolean default false;
declare file_seq int;
declare t1 varchar(20);

declare cur2 cursor for select tablename, input_filename from test.v1 order by 2;
declare continue handler for not found set done1:=true ;

open cur2;
READ_LOOP: LOOP

fetch cur2 into t1,file_seq;
if done1 then
set done1:=true;
close cur2;
leave read_loop;
end if;

if start_p=1 then
set start_p=file_seq;
elseif

file_seq<>start_p+1 then
insert into test.tbl_missing_seq (table_name, sequence_nbr, create_date) values (table_name,start_p+1, curdate());
set start_p=file_seq;

end if;
set start_p:=file_seq;
end loop;
end


The problem im having is that get_metadata calls get_sequence the first time and it executes correctly but when get_sequence gets called the second time it seems as if the cursor ( cur2 ) does not get initialized with the new values in V1,it still processes v1 with the data of the first read, even though V1 gets created with the new records. can you help?

Roland Bouman said...

Hi Anonymous,

TL;DR.

Anonymous said...

Hi There,

Sorry Im new to Mysql, what is TL;DR?

Thanks,

Roland Bouman said...

Anonymous, excuse my snarky reply from yesterday. TL;DR means: too long, didn't read.

You might have a better chance if you post these kinds of questions to an online forum or mailinglist, for example forums.mysql.com.

Anonymous said...

Hey Roland,

Please help with this post as above, i posted it and am getting no results. This problem is driving me crazy here, there's so many niggles when it comes to Mysql, should've stuck to PL/SQL ...lol.

Thanks man!

Roland Bouman said...

Anonymous,

can you post some sql so I can at least set up a schema to test it myself? to would also be helpful if you could explain what you want to achieve, I got a feeling there may be a simpler solution.

Charvi said...

HI Ronald..
I have a problem related to cursor. lets say..
there are tables A, B,C,D
for each id in A , there are 2 records in B and C and according to condition B i have to apply Action C on D.
plz help me how to do it by using cursor.

Roland Bouman said...

Charvi,

it's hard to go into detail based on this general question. You mention that you want to apply an Action - what action is that? If it's INSERT, DELETE or UPDATE you should probably not mess with a cursor, but simply write one big SQL statement per action - see my article on refactoring MySQL cursors to get some ideas on how that'd work out:

http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

If the action is a call to a stored routine, or some dynamic sql, then the most straightforward way would be to write a join of A,B and C, making sure you encode the condition logic on B into that statement to ensure you only select the appropriate C record. Then you can simply loop through that statement with a cursor in the usual way, and use a CASE...END CASE statement inside the loop to apply the action.

I hope this helps.

irina said...

hello roland,

I really did enjoy finding such a well explained, well exemplified explanation of cursors. I used to work with them back then, in Oracle (about 10 years ago), but I can't remember enough.
I came back to stored procedures in mysql last year, and now I got to the point where I can't refactor loops in joins: this one needs 7 tables, 2 of which are on a separate database (same server, luckily :-) 2 of which are "reference" and 2 levels of left joins where I need to consider the nulls: if they pop up, insert certain tables, if don't insert others.
I've used the call-to-a-different-procedure solution, this time, but I'd like to understand the exmaple of resetting the loop control variable: you leave loop 1, and close cursor 1 - and here's the questions:
a. can you then come back to loop 1 and reopen cursor 1?
b. when you reopen cursor1 (if the answer to the prebv question was yes), would it remember where it left?
c. if the answer to question b is no, is there a way I can set a counter so I could keep track of where I'm left?

and now the efficiency considerations:
1. would working one of loops line by line take more time, than calling a different procedure/function for the inner loop?
that is, I can write a simple select for the outter loop, then move this record to a different table (insert/delete statements), so the first record in the original table is always one I haven't processed yet. would this consume more time than calling a procedure?

2. is the 7 tables join likely to be faster than calling the other procedure? I could do it, if it saves me enough time. Mind you, I am working with hunders or records, and not likely to get even to 1000 any time soon.

3. is the restting of loop control, faster (in cpu time) than either calling a different procedure, or making the join?

thanks in advance for your time and consideration,
irina dalah

tomalex said...

It was just what i was looking for, i'm much thankfull to you. I'm new to store procedures. So i'm still in search for a good tutorial, can you please provided some use site which i can look in to.

Again Thank you very much for the valuable info

Roland Bouman said...

Irina, thanks for your kind words, and my apologies for a much belated reply. Sometimes things get a bit busy and I forget to reply to moderated comments, esp. on older posts.

Anyway, with regard to your questions:
a) yes, you can reopen a cursor. But it will execute the sql again. typically you'd have some local variable references inside the SQL, and the outer loop probably assigns to thos variables. The effect is very similar to a correlated subquery.
b) no: it would simply execute the SQL again, but it does not maintain state across multiple events of opening the cursor.
c) it depends on your schema - you should know best. In a typical example, you'd have an outer loop over a cursor that represents a "master", and a nested cursor loop for a set that represents a "detail" of the master in the outer loop. You can then bind the "detail" cursor using local variables. This is in fact precisely what I do in the first example beneath the "Resetting the loop control variable" heading: cursor2 is bound to v_col2, and the outer loop fetches the cursor data in to that v_col2 variable, thus driving the inner loop and correlating its results to the current row in the outer loop.

1) for efficientcy and performance questions, you should simply test your scenarios, but in general you should assume that for MySQL stored procedures, more code means more time. That said I am not really sure what your scenario is. What do you mean, the "first" record in the original table?

2) A 7 table join is not a problem if you have the right indexes. In will cerainly be much more efficient than cursor loops, esp. if you have nested curosr loops. You will notice the difference even with as few as a couple of hundred records.

3) I haven't tested it but I am quite sure that it is somewhat faster than calling a new procedure, but much slower than doing a join.

Wes Baker said...

Thanks Roland! I found this blog most helpful writing my first cursor stored procedure :)

Anonymous said...

Really fantastic, helpful information, saved me from much frustration. *thumbs up*

Anonymous said...

Great article. After much frustration was the first good documentation I had found.

Tim Buterbaugh said...

Thanks for the info. It was my first hit in my Google search and provided exactly the solution I needed.

Keep up the good work!

Roland Bouman said...

@Tim, glad it's useful for you. However, if you feel you need to nest cursors, you should probably consider if you really need it. I wrote about that here:

http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

kind regards,

Roland

Sriram.R said...

Hi,

This is my test code.

DELIMITER //
CREATE FUNCTION test ()
RETURNS INT
BEGIN
DECLARE id1 INT;
DECLARE string1 TEXT;
DECLARE id2 INT;
DECLARE string2 TEXT;
DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT idData,subject FROM yahoo_answers.data2;
DECLARE cur2 CURSOR FOR SELECT idData,subject FROM yahoo_answers.data3;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

OPEN cur1;

outer_loop: LOOP
FETCH cur1 INTO id1,string1;
IF no_more_rows THEN
SET @no_more_rows = FALSE;
LEAVE outer_loop;
END IF;
INSERT INTO my_table VALUES (id1, string1);
OPEN cur2;
inner_loop: LOOP
FETCH cur2 INTO id2,string2;
IF no_more_rows THEN
SET @no_more_rows = FALSE;
CLOSE cur2;
LEAVE inner_loop;
END IF;
INSERT INTO my_table VALUES (id2, string2);
END LOOP inner_loop;
END LOOP outer_loop;
RETURN 1;
END//

DELIMITER ;

I find that in the above code, once the inner loop terminates the outer loop doesn't proceed with the next iteration.

Can you please please please look into this?

by the way,
My ulterior aim is to find the pairs of best matching rows.

So I try to compare a row with every other row and find the most similar matching set of rows.

Is there any better way of doing that ?


-Sriram

Roland Bouman said...

Hi Sriram R,

"Can you please please please look into this?"

the problem is in the lines that read:

SET @no_more_rows = FALSE;

That should be:

SET no_more_rows = FALSE;

In your code, you're setting the user-defined variable @no_more_rows. But your loop condition reads from the local variable no_more_rows, and your handler also writes to that local variable. To make the outer loop continue, you must re-set that local variable.

"My ulterior aim is to find the pairs of best matching rows."

You should try to use a JOIN instead. But your code now does not compare rows at all, it simply loops through them.

Tinel Barb (Romania) said...

Thanks a lot, dude! Your code helped me a lot, after many hours trying to deal with conditional loops.
For instance, did you knew that in a select for a cursor you can't use LIMIT a,b? :)
I trying to jump to the desired row using
select a,b from tbl limit i,1
where i was incremented, but i've got an error. Mysql accept "LIMIT a", but it is equivalent with "LIMIT 0,a", which is not what I was needed. :)

Great article, as the one with the replacement of conditional loops with "LOOP".

Andrew said...

Nice article Roland, but I found that your second option just didn't work for my stored procedure on mySQL5.5. Setting v_done to false at the end of the inner loop didn't make any difference at all - instead it also caused the outer loop to terminate as it still saw v_done as true.

Roland Bouman said...

Andrew,

maybe you made a mistake somewhere? Chances that your computer is the disobedient type seem pretty slim.

Gangadhar said...

Hi Roland,

I have tried to write a procedure i have spent almost 1 day but no luck, can you please check my code where am doing wrong.


DROP PROCEDURE IF EXISTS Insert_CallPut_C0;
//

create procedure Insert_CallPut_C0()

begin
declare id int default 0;
declare idVal bigint;
declare EffectiveDate date;
declare CallPutFlag varchar(1) default 'C';
declare SecurityId bigint(20);
declare ModifiedBatchID bigint(20) default 0;
declare CreatedBatchID bigint(20) default 0;
declare DeletedStamp varchar(40);
declare CreatedStamp datetime;
declare CreatedLoginName varchar(80);
declare ModifiedStamp datetime;
declare Price double;
declare NextPrice double;
declare ModifiedLoginName varchar(80);
declare RecordType varchar(3) default 'C0';
declare NextDate date;
declare NoticePeriod varchar(5);
declare Timing varchar(2);

declare cur_sel CURSOR FOR select CASE WHEN calldet.CRDT is not null THEN calldet.CRDT WHEN calldet.CRDT is null THEN '1980-01-01' END, scx.SecurityId, scx.DeletedStamp, scx.CreatedStamp, scx.CreatedLoginName, scx.ModifiedStamp, calldet.CRPR, calldet.NXPR, scx.ModifiedLoginName, calldet.NXDT, calldet.CNOT, calldet.CTIM from sirs.SIRSSECCallPutDetail calldet, sirs.SIRSSECReference sirsref, sc_test.SCSECXref scx where calldet.ReferenceID=sirsref.ID and scx.SecurityId not in(select SecurityID from sc_test.SCSECCallPut where RecordType='C0') and scx.SecurityAlias=sirsref.USERNUM and scx.SecurityAliasType='CUSIP' limit 2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET id=1;
begin
select @max:= max(ID) from sc_test.SCSECCallPut;
SET idVal = @max;
end;

open cur_sel;
lbl: LOOP
IF id=1 THEN
LEAVE lbl;
END IF;
IF NOT id=1 THEN

SET idVal = idVal+1;
FETCH cur_sel INTO EffectiveDate,SecurityID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,NextDate,NoticePeriod,Timing;
insert into scp4.test_callput(ID,EffectiveDate,CallPutFlag,SecurityID,ModifiedBatchID,CreatedBatchID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,RecordType,NextDate,NoticePeriod,Timing) values(idVal,EffectiveDate,CallPutFlag,SecurityID,ModifiedBatchID,CreatedBatchID,DeletedStamp,CreatedStamp,CreatedLoginName,ModifiedStamp,Price,NextPrice,ModifiedLoginName,RecordType,NextDate,NoticePeriod,Timing);
END IF;
END LOOP;
CLOSE cur_sel;
END;
//


in my above procedure i have 1 cursor which select the columns from multiple tables,
then open the cursor, started the loop, in the loop am incrementing maxId value which is selected and stored in a variable idVal.
every thing is working fine like cursor fetching values and etc. but the thing is when i insert these values in to another table with above values from cursor and maxId value ie. idValue+1 then its always starting the idVal from 0 even though my maxId is returning 2000002.
can u please help in this.

Thanks,
Gangadhar

Roland Bouman said...

Gangadhar,

please take questions like this to a public forum.

Anonymous said...

A very useful article

Anonymous said...

Hey nice article.

Anonymous said...

nice article i found it very useful

Anonymous said...

Thank's a lot, your Blog would be very usefull. I'll use this post to teach my students as a very usefull and clear example.

Dave Tapson said...

Typo: no_more_rows should be v_done?

Roland Bouman said...

@Dave,

thanks you! You were absolutely right. I changed the occurrences of v_done to no_more_rows.

It's baffling no-one caught this earlier, and great that you did. Thanks again!

kind regards,

Roland

Anonymous said...

**** Great article *****

Gergő Borbléy said...

Hi Roland,

I have a nested cursor loop which doesn't work. This is in a SP. When I call it then the mysql server goes down. Could you help me what I am doing wrong? Thanks in advance.

Here is the SP:

DROP PROCEDURE IF EXISTS `get_scrap_list`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `get_scrap_list`()
BEGIN

DECLARE vintID, vintQTY INT DEFAULT 0;
DECLARE vchrGEN_PN2 VARCHAR(255);
DECLARE vendROW INT DEFAULT 0;

DECLARE vcurROW CURSOR FOR
SELECT
ID
FROM
gen_pns
WHERE
ON_HAND > RGD
ORDER BY
GEN_PN;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW = 1;

/* create temp table for results */
DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 (
PN VARCHAR(50) NOT NULL,
GEN_PN VARCHAR(50) NOT NULL,
ORG VARCHAR(50) NOT NULL,
LOCATION VARCHAR(50) NOT NULL
);


OPEN vcurROW;

LOOP1: LOOP

FETCH vcurROW INTO vintID;

IF vendROW THEN
CLOSE vcurROW;
LEAVE LOOP1;
END IF;

SELECT
(ON_HAND - RGD), GEN_PN INTO vintQTY, vchrGEN_PN2
FROM
gen_pns
WHERE
ID = vintID
LIMIT 1;

BLOCK2: BEGIN

DECLARE vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION VARCHAR(50);
DECLARE vendROW2 INT DEFAULT 0;

DECLARE vcurROW2 CURSOR FOR
SELECT
rp.PN,
rp.GEN_PN,
rp.ORG,
rp.LOCATION
FROM
rp,
gen_pns
WHERE
gen_pns.GEN_PN = rp.GEN_PN
AND gen_pns.GEN_PN = vchrGEN_PN2
ORDER BY
rp.PN
LIMIT vintQTY;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW2 = 1;

OPEN vcurROW2;

LOOP2: LOOP

FETCH vcurROW2 INTO vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION;

IF vendROW2 THEN
CLOSE vcurROW2;
LEAVE LOOP2;
END IF;

INSERT INTO temp1 (PN, GEN_PN, ORG, LOCATION) VALUES (vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION);

END LOOP LOOP2;

END BLOCK2;

END LOOP LOOP1;


SELECT * FROM temp1;
DROP TEMPORARY TABLE temp1;

END;

Regards,
Gergo

Gergő Borbléy said...

Hi Roland,

I have a nested cursor loop and I have no idea what is wrong. When I run it then the mysql server goes down. Please check my SP...
Thanks in advance.

DROP PROCEDURE IF EXISTS `get_scrap_list`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `get_scrap_list`()
BEGIN

DECLARE vintID, vintQTY INT DEFAULT 0;
DECLARE vchrGEN_PN2 VARCHAR(255);
DECLARE vendROW INT DEFAULT 0;

DECLARE vcurROW CURSOR FOR
SELECT
ID
FROM
gen_pns
WHERE
ON_HAND > RGD
ORDER BY
GEN_PN;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW = 1;

/* create temp table for results */
DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 (
PN VARCHAR(50) NOT NULL,
GEN_PN VARCHAR(50) NOT NULL,
ORG VARCHAR(50) NOT NULL,
LOCATION VARCHAR(50) NOT NULL
);


OPEN vcurROW;

LOOP1: LOOP

FETCH vcurROW INTO vintID;

IF vendROW THEN
CLOSE vcurROW;
LEAVE LOOP1;
END IF;

SELECT
(ON_HAND - RGD), GEN_PN INTO vintQTY, vchrGEN_PN2
FROM
gen_pns
WHERE
ID = vintID
LIMIT 1;

BLOCK2: BEGIN

DECLARE vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION VARCHAR(50);
DECLARE vendROW2 INT DEFAULT 0;

DECLARE vcurROW2 CURSOR FOR
SELECT
rp.PN,
rp.GEN_PN,
rp.ORG,
rp.LOCATION
FROM
rp,
gen_pns
WHERE
gen_pns.GEN_PN = rp.GEN_PN
AND gen_pns.GEN_PN = vchrGEN_PN2
ORDER BY
rp.PN
LIMIT vintQTY;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vendROW2 = 1;

OPEN vcurROW2;

LOOP2: LOOP

FETCH vcurROW2 INTO vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION;

IF vendROW2 THEN
CLOSE vcurROW2;
LEAVE LOOP2;
END IF;

INSERT INTO temp1 (PN, GEN_PN, ORG, LOCATION) VALUES (vchrPN, vchrGEN_PN, vchrORG, vchrLOCATION);

END LOOP LOOP2;

END BLOCK2;

END LOOP LOOP1;


SELECT * FROM temp1;
DROP TEMPORARY TABLE temp1;

END;

Regards,
Gergo

Roland Bouman said...

Grego,

no time. Go to stackoverflow or something like that.

Anonymous said...

Thank you so much! Helped a ton!