tag:blogger.com,1999:blog-15319370.post112811396394529527..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Why REPEAT and WHILE are usually not handy to handle MySQL CURSORsrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger70125tag:blogger.com,1999:blog-15319370.post-16015590002085661882017-10-05T15:28:46.806+02:002017-10-05T15:28:46.806+02:00Very nicely written article. Even years later it&#...Very nicely written article. Even years later it's useful as I try to track down why I'm getting a "call out of order" error message. Thanks!Vannoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-83742341557170196042017-04-17T19:38:10.525+02:002017-04-17T19:38:10.525+02:00Very nice article! Thank you a lot.Very nice article! Thank you a lot.Eduardo Cooperhttps://www.blogger.com/profile/03947997879244351097noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-36070739483211121722016-10-31T22:53:30.680+01:002016-10-31T22:53:30.680+01:00Nice article, though it has not persuaded me to ab...Nice article, though it has not persuaded me to abandon my decades-long practice of using the while-with-duplicate-fetch solution. :-) Your example of the unstructured-loop solution does show that all loop control is kept nicely in one place, but due to its small size it does not illustrate the true danger of unstructured loops: every single line of code in the loop must be examined to be sure there is no other exit, and the number of lines of code that can appear in a loop is in effect unbounded.<br /><br />The duplication of the fetch is a price I am willing to pay for the lack of a proper cursor control statement in MySQL (as you also observe). In contrast to the unstructured-loop approach, only two lines of code must be examined to check for correctness.<br /><br />One thing I particularly appreciate about your article is its thoughtful attention to style. So I feel I must address a stylistic issue in your recommended change from "done" to "hasMoreRows". It requires you to initially assert "DECLARE hasMoreRows BOOL DEFAULT TRUE;", which is not, in general, the case. A more accurate name would be "fetchHasNotFailed", but that seems a tad cumbersome compared to "NOT done".Weshttps://www.blogger.com/profile/03410813584826325914noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76789873082273286532016-01-06T23:00:15.347+01:002016-01-06T23:00:15.347+01:00That's ok. I still disagree though :)
END LO...That's ok. I still disagree though :) <br /><br />END LOOP is just the syntactical end of the loop. What actually ends at END LOOP is the loop body, not the loop itself. The loop or rather, the looping ends at LEAVE. So that's where I want to tear down and clean up.<br /><br />We can agree to disagree.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58327054260341330472016-01-06T22:52:45.863+01:002016-01-06T22:52:45.863+01:00That's kind of what I was trying to say in the...That's kind of what I was trying to say in the last sentence of my last post, as well.<br /><br />If code is so large that one has to scroll or fold to see that, then maybe it's time to break some of it out into a separate component.David Beroffhttps://www.blogger.com/profile/01214053968578074259noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-30368155909073236792016-01-06T19:42:18.288+01:002016-01-06T19:42:18.288+01:00KasH, thanks for the comment. Of course you may su...KasH, thanks for the comment. Of course you may suggest it :) <br /><br />You don't give an explicit argument as to why you think your suggestion is better but I will try and guess. <br /><br />I am assuming you feel the structural entry of the loop, formed by the LOOP keyword must coincide with the opening of the cursor, since the loop exists only to traverse the cursor. And of course, I agree. I'm guessing the next step in your thinking is that the structural end of the loop should coincide with closing the cursor. And again I would I agree.<br /><br />Where we probably differ in opinion is what exactly constitures the "end of the loop". You feel it should be the END LOOP keywords, because syntactically, LOOP...END LOOP forms one statement block. I on the other hand am not that interested in the syntactic structure. I am interested in the actual process of looping. So I feel that the actual, functional end of the loop is tied to the LEAVE statement. To me, END LOOP is only the demarcation of the loop body. It tells us nothing where the loop actually ends. <br /><br />So, for me it is more natural to close the cursor in the same block of code that determines that the cursor is exhausted. <br /><br />My style has what I feel is an extra advantage: all of the cursor control, and all of the loop control form one contiuous segment of code. So I have no trouble seeing both the OPEN and CLOSE of the cursor, and it is very easy to convince myself they match the same cursor variable. If you CLOSE the cursor after END LOOP, you might need to scroll the code, or use an editor that supports code folding to see this.<br /><br />I hope this helps.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-22453886391952950482016-01-06T18:06:45.542+01:002016-01-06T18:06:45.542+01:00This may or may not already have been mentioned..;...This may or may not already have been mentioned..;<br /><br />In your last and final example you open the cursor outside the loop, and close it inside. May i suggest closing the cursor at the same level you opened it?<br /><br />So..;<br /><br /><br />OPEN cur;<br /><br />blah: LOOP<br /> IF condition THEN<br /> LEAVE blah;<br /> END IF<br /> <br /> -- Do some work with cur...<br />END LOOP<br /><br />CLOSE cur;KasH.noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60766276971421265302015-08-18T10:56:02.926+02:002015-08-18T10:56:02.926+02:00@David Beroff,
thank you so much for pointing thi...@David Beroff,<br /><br />thank you so much for pointing this out! I didn't realize the manual was changed, but I'm happy they did. I modified the blog post text to inform the reader. Thanks again! rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-6868187065773770742015-08-18T07:02:35.359+02:002015-08-18T07:02:35.359+02:00Great post; thank you!
Take a look at the followi...Great post; thank you!<br /><br /><i>Take a look at the following snippet. I didn't make it up myself, I took it from the Cursors section in the MySQL Reference manual.</i><br /><br />At some point in the last ten years, the maintainers of said manual eventually came to agree with your conclusions! :-) (About the <i>only</i> substantial difference is they close the cursor(s) after the loop, rather than in the conditional, so as to balance the open and close at the same level of indentation, and I must admit that I prefer their approach.)David Beroffhttps://www.blogger.com/profile/01214053968578074259noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-48149263144120498652014-04-22T20:06:36.782+02:002014-04-22T20:06:36.782+02:00Beautiful tutorial.
Very detailed. Thank you very...Beautiful tutorial.<br /><br />Very detailed. Thank you very much.<br /><br />Even after 7 years, it solve my doubts.<br /><br />Thank you for share this knowledge.Anonymoushttps://www.blogger.com/profile/02165278255464720573noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-90141272303203640102013-11-22T16:22:54.098+01:002013-11-22T16:22:54.098+01:00Thanks for this... okay this is now over 7yrs old ...Thanks for this... okay this is now over 7yrs old but hey ho, glad it was here :)Unknownhttps://www.blogger.com/profile/13579821614251997752noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-37183487677623019162012-08-03T19:47:52.546+02:002012-08-03T19:47:52.546+02:00It was a bit long. Either way, I used the WHILE, b...It was a bit long. Either way, I used the WHILE, because it made better sense than the REPEAT for cursors. But I too was discontent with the two FETCH. I know why it was needed, but still, there had to be a better way.<br /><br />Thanks for the LOOP suggestion. Makes more sense.Walterhttps://www.blogger.com/profile/01410526645843872473noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-30416385831566917352012-08-03T19:46:48.112+02:002012-08-03T19:46:48.112+02:00It was a bit long. Either way, I used the WHILE, b...It was a bit long. Either way, I used the WHILE, because it made better sense than the REPEAT for cursors. But I too was discontent with the two FETCH. I know why it was needed, but still, there had to be a better way.<br /><br />Thanks for the LOOP suggestion. Makes more sense.Walterhttps://www.blogger.com/profile/01410526645843872473noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-81318667614001458162012-08-03T19:45:16.792+02:002012-08-03T19:45:16.792+02:00It was a bit long. Either way, I used the WHILE, b...It was a bit long. Either way, I used the WHILE, because it made better sense than the REPEAT for cursors. But I too was discontent with the two FETCH. I know why it was needed, but still, there had to be a better way.<br /><br />Thanks for the LOOP suggestion. Makes more sense.Walterhttps://www.blogger.com/profile/01410526645843872473noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-90388177647202071602012-07-16T09:39:25.743+02:002012-07-16T09:39:25.743+02:00@anonymous, what executes which warning?@anonymous, what executes which warning?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-8900708219172703922012-07-16T09:34:57.965+02:002012-07-16T09:34:57.965+02:00But It shows a warning when we execute the whole s...But It shows a warning when we execute the whole statement.How to deal with this problem?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-12225802968522769112012-04-25T11:57:26.895+02:002012-04-25T11:57:26.895+02:00Its like you read my mind! You seem to know so muc...Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but instead of that, this is great blog. A fantastic read. I'll certainly be back.Rumah Dijual Jakartahttp://www.propertykita.com/rumah.htmlnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-68049822392388335582012-02-12T14:11:29.991+01:002012-02-12T14:11:29.991+01:00@anonymous1: you can execute stored procedures on ...@anonymous1: you can execute stored procedures on WAMp just like you can on regular MySQL. Just open a command line and do:<br /><br />call spname();<br /><br />where spname is the name of the stored procedure in the current schema or<br /><br />call schemaname.spname()<br /><br />to run the procedure called spname in the database (schema) with the name schemaname.<br /><br />@anonymous2: why would you want to use a cursor for that? Also, what do you mean exaclty by "update for each product in different table."?<br /><br />suppose you have a table to store the likes as measured by a particular point in time, you could do:<br /><br />CREATE TABLE product_likes(<br /> productid int<br />, as_of date<br />, like_score decimal(3,2)<br />)<br /><br />insert <br />into product_likes <br />([product_id, as_of, like_score)<br />select product_id, current_date(), sum(if(likes='like',1,0))/count(likes)<br />from likes_table<br />group by productidrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-88882000093822633862012-02-11T11:12:17.130+01:002012-02-11T11:12:17.130+01:00Hi Roland,
Your Article really help me to underst...Hi Roland,<br /><br />Your Article really help me to understand SP concept. Thanks for posting this.<br /><br />I have to find product Likes score in one of my project. Formula is as below:<br /><br />Like Score = Like count / Total Votes<br /><br />Likes table has three fields:<br />id int(11), likes(enum: 'like','dislike'), productid int(11);<br /><br />Product table has two fields (for test):<br />productid int(11), pname varchar(25);<br /><br /><br />I want to write SP using cursor to execute above formula. And product Like score would be update for each product in different table.<br /><br />How can I write store procedure for above requirement. Please help me for same.<br /><br />Thanks in advanceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-33225729650851602942012-02-11T11:05:20.589+01:002012-02-11T11:05:20.589+01:00Hi Roland,
How can I run/execute stored procedure...Hi Roland,<br /><br />How can I run/execute stored procedure. I am using wamp Server. Your answer will help lot.<br /><br />Thanks in advanceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-696000298989468012012-01-07T22:52:24.332+01:002012-01-07T22:52:24.332+01:00Doug, it is what it is. I don't like standard ...Doug, it is what it is. I don't like standard SQL cursor handling syntax either. In Oracle you can write it like<br /><br />for rec in (<br /> select col1, col2, ...<br /> from ...<br />) loop<br /><br /> ..code goes here, use rec.field to fetch values...<br /><br />end; <br /><br />Which is a lot nicer. But alas, MySQL sticks to the standard which happens to be really ugly.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-88683342660462239102012-01-07T22:06:48.987+01:002012-01-07T22:06:48.987+01:00Hi Roland,
As a C/C++/other languages programmer,...Hi Roland,<br /><br />As a C/C++/other languages programmer, the more I use SQL stored procedures, the less I like them.<br /><br />Using exception handling is <i>ugly</i>, to put it mildly. Is there any way to place the updating of the <b>done</b> flag with the code where it belongs? Even for the simple code given here and in s. 12.6.6 of the manual, it took a while to see what was going on (on the surface, it looks like you have infinite loops.)<br /><br />Surely there should be some good, though less verbose, alternative to SQL. This reminds me of all the reasons I hate COBOL!Dougnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-198914271360191052011-08-03T21:21:19.089+02:002011-08-03T21:21:19.089+02:00Hi PrinceDhampir,
"I have read about some pr...Hi PrinceDhampir,<br /><br />"I have read about some problem with empty rows in the query that sets the done variable to true even if the query stilll have rows"<br /><br />never heard of that. link? <br /><br />It's possible that in you [operations] block, a no data found exception is raised, inadvertently setting the done variable to true. You can quickly check that by doing a <br /><br />SELECT done;<br /><br />right before the END LOOP; If that is the case you can start troubleshooting what statement causes that. To prevent it, please check out my "NESTING CURSOR LOOPS" post (http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html) to get a few ideas on how to remedy that. I would also recommend to read my "Refactoring cursors" post (http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html) if you really do have a nested cursor (or a select statement inside the cursor loop).<br /><br />HTH, <br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-29061586052828633032011-08-03T19:14:51.109+02:002011-08-03T19:14:51.109+02:00Hi Roland,
Interesting post i have a problem wit...Hi Roland, <br /><br />Interesting post i have a problem with a cursor loop, the loop ends after the first fetch everytime i call the sotre procedure, in example<br /><br />DECLARE done boolean default false;<br />DECLARE cursor1 CURSOR FOR <br />SELECT ... FROM [tables]<br />Where condition;<br /><br />DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = true;<br /><br />OPEN cursor1;<br /><br />cursor1_loop:LOOP<br /> fetch cursor1 into [variables]<br /> IF done THEN<br /> select done;<br /> LEAVE cursor1_loop;<br /> END IF;<br /> [operations]<br />END LOOP cursor1_loop;<br />Close cursor1;<br /><br />I have read about some problem with empty rows in the query that sets the done variable to true even if the query stilll have rows so what can i do to solve it?.<br /><br />Thanks in advance.<br /><br />GiovanniPrinceDhampirhttps://www.blogger.com/profile/13004376860429270676noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-87832963707372637822011-04-05T16:02:19.747+02:002011-04-05T16:02:19.747+02:00Sure! Here's a link to the example:
http://lm...Sure! Here's a link to the example:<br /><br /><a href="http://lmgtfy.com/?q=LOAD+DATA+INFILE&l=1" rel="nofollow">http://lmgtfy.com/?q=LOAD+DATA+INFILE&l=1</a>rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com