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:

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:

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:

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.

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;

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:

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.

Hi RolandI 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 !

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).

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.

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))

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.2Thanks again so very much as due to your article I have been able to solve this task.Alice

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;

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.

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.

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?

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!

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.

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?

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!

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.

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.

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.

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.

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?

HI Ronald..I have a problem related to cursor. lets say..there are tables A, B,C,Dfor 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.

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:

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 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?

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.

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.

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,1where 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".

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.

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.

Search This Blog

About Me

I'm Roland Bouman (@rolandbouman on twitter). I'm a software (web) application developer and I work on both the front end as well as the back end. I do data modeling, database design, ETL, Analytics, and Business Intelligence.