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

Wednesday, September 28, 2005

Want to write a CURSOR LOOP with MySQL?

Like all procedural database languages I know, the MySQL stored procedure language supports explicit cursors. I just wrote "explicit cursor" but for I'll refer to those by just saying "cursor".

A cursor allows a programmer to traverse a set of records retrieved by a relational database query in a sequential, one-by-one fashion. As such, a cursor forms an interface between the relationally oriented, declarative SQL language and the procedurally oriented calling context. A database cursor is like a pointer positioned somewhere in a resultset which can be moved programmatically to browse through all the records in the resultset. So, that's quite analogous to the wordprocessor cursor you use to browse through the characters and words on your computer screen.

(For those that are wondering what an implicit cursor is - well, that's just a piece of memory used by the database server to work with resulsets internally. Implicit cursors are not accessible via an externally exposed API, whereas explicit cursors are.)

Lots of php programmers are probably familiar with the cursor concept:


<?php
//connect to the database server
$connection = mysql_connect(
$_SESSION['DB_SERVER']
, $_SESSION['DB_USER']
, $_SESSION['DB_PASSWORD']
);
//get the 'cursor'
$cursor = $mysql_query(
'SELECT * FROM table1'
, $connection
);
//browse through the 'cursor'
while(
$row = mysql_fetch_row(
$cursor
)
){
//process records, one by one.
echo('<div>');
foreach(
$row
as $field
) {
echo('<span>');
echo($field);
echo('</span>');
}
echo('</div>');
}
?>


The cursor interface is really convenient when you need to do complex resultset processing. Processing a resultset is quite common in a client program, especially when the resultset needs to be displayed or printed, but somwtimes, it can be quite useful to do resultset processing on the database server side.

In most cases, purely data-oriented problems can usually be solved without the explicit resulset processing provided by cursors. It is often much better to use implicit resulset processing using 'ordinary' SQL statements. In fact, ordinary SQL statements are very powerful, and should be used whenever it's possible.
However, in some cases, the cursor interface is easier to write and maintain, and in some cases, there's no ordinary SQL substitute for the cursor interface.

Let's think of an example that is screaming for explicit resultset processing.

Suppose we have a table of employees, emp and a stored procedure p_calculate_salary. Now, assume that this stored procedure contains all the logic and business rules we need to calculate an employee's salary for a given month, including benefits, bonuses, and witheld fines for taking part in health or educational plans. What's more, the procedure does not only calculate all that, but also makes the necessary changes elsewhere in the system, trigger autonomous processes such as sending each employee it's monthly and (when applicable) annual salary reports.

For those that are still not convinced that all this logic should be embedded in a procedure, think of the following scenario. Suppose that the user that needs to calculate the salaries is not authorized to directly access the data that is needed to perform the business logic implemented by the procedure, and that the procedure is created using SQL SECURITY DEFINER. In that case, there really is no way to solve this using ordinary SQL. Resultset processing using a cursor is really the only way in this case.

To conveniently do the salary run for a particular month, we could use a cursor to traverse the list of employees, getting the relevant employee data and plugging that into the procedure. This type of use case is typical for a cursor: We do not need any direct output, so an ordinary SELECT is not necessary, spurious even.

Also, we cannot call a procedure in an ordinary SELECT, and it is not always possible to wrap all the business rules into a function (which could be used inside a SELECT).

This salary run is all back-end stuff. We do not want some external script doing this. We want to keep all those business rules and logic as closely tied to the database as we can, especially when the internals of the procedure need to refer to the database. So, that's why the database procedure is really the solution we want to use to calculate the salaries. Once we admit to that, we really cannot avoid cursor-based resultset processing anymore. So let's dig into some resultset processing...



The pseudocode for this operation would look like this:


for all records in the employee table do:
get the current employee record
calculate salary for the current employee record
until all employee records are processed.


In Oracle, you could write it like this:

declare
v_col1 ....; --declare 1..N variables to access the record
v_col2 ....; --I left out the datatypes intentionally
...
v_colN ....;
cursor csr_emp --define the cursor
as
select *
from emp
;
begin
open csr_emp; --open the cursor
loop --traverse the cursor
fetch csr_emp into --fetch current record
v_col1
, v_col2
..
, v_colN
;
exit when csr_emp%notfound; --stop traversal when no more records

p_calculate_salary( --process a record
v_col1
, v_col2
...
, v_colN
);
end loop;
close csr_emp; --release resources
exception when others then --capture all errors
if csr_emp%isopen then --release resources
close csr_emp;
end if;
end;


This snippet illustrates the general pattern for working with a cursor:

  1. You define or declare the cursor.

  2. You open the cursor

  3. You fetch a record from the cursor, storing field values into variables

  4. You check if there are more records, and if so, repeat step 3. If not, proceed

  5. You close the cursor when you've fetched the last record.



Now, compare this to the equivalent MySQL syntax:


begin
declare v_col1 ....; -- define variables to store record fields
declare v_col2 ....; -- datatypes intentionally ommitted
declare v_colN ....;
declare v_notfound -- define the loop control variable
BOOL default FALSE;
declare csr_emp -- define the cursor
cusor for
select *
from emp;
declare continue handler -- handle cursor exhaustion
for not found
set v_notfound := TRUE; -- mark our loop control variable
declare exit handler -- handle other errors
for sqlexception
close csr_emp; -- free resources before exit
open csr_emp; -- open cursor
cursor_loop: loop
fetch csr_emp into -- fetch record values
v_col1
, v_col2
...
, v_colN
;
if v_not_found then -- exit the loop when the cursor is exhausted
leave cursor_loop;
end if;
call p_calculate_salary( -- process a record
v_col1
, v_col2
...
, v_colN
);
end loop;
close csr_emp; -- free resources
end;


The ingredients are about the same for both examples. Both Oracle and MySQL open the cursor, fetch from it, and close it when it runs out of records.

The difference between MySQL and Oracle boils down to the difference in the way the loop control is handled.

In Oracle you could use a so-called cursor attribute. Cursor attributes are essentially builtin, readonly variables associated with the cursor. That's actually quite like member variables encountered in object oriented languages. To refer to a particular cursor attribute, you use this syntax: cursor_name%attribute_name. Oracle defines the following cursor attributes: FOUND, NOTFOUND, ROWCOUNT and ISOPEN.

In our snippet, we used the NOTFOUND attribute to check wheter the cursor was exhausted in order to exit the loop. We also used the ISOPEN attribute in the general catch-all WHEN OTHERS exception handler wich allows us to explicitly close the cursor when it was left open when an exception occurred in the inner block.

Although MS SQL Server's Transact SQL dialect does not define cursor attributes, there's a device there used to write cursor loops that in practice resembles the Oracle FOUND and NOTFOUND cursor attributes: the @@FETCH_STATUS (session wide) global variable. Theres a similar global resembling the Oracle ROWCOUNT attribute: @@CURSOR_ROWS.

The difference with Oracle cursor attributes is that in MS SQL, there's only one instance of the globals, reflecting the status of the last cursor statement. In practice, this is of no consequence because you usually only reference the cursor attributes in the immediate vincinity of the statements that could change the cursor status. T-SQL has one other device that provides information about the status of an arbitrary cursor (not just the current one), the CURSOR_STATUS(), but usually, the globals are sufficient for most purposes.

MySQL does not define any cursor attributes, not any globals that provide cursor status information. In fact, cursor functionality is limited to opening a cursor, fetching values from it's current record, and closing it. Like in other rdbms products, fetching from an exhausted cursor will raise an error situation in MySQL. Because there's no general way to know on beforehand if the cursor is exhausted, there's nothing left to do but fetching from the cursor and handling the error situation. Always.

So, in MySQL, we must declare a HANDLER for the builtin NOT FOUND condition. Usually, we want to continue our procedure when the cursor is exhausted, so this will usually be a CONTINUE handler. Inside the handler statement, we must place the statements that will update our loop control variable. Like in the Oracle case, we still have to check our loop control variable to actually exit the loop to stop the cursor traversal process.

When writing these loops for MySQL it might seems a bit awkward at first to use these contructs, especially when you are used to cursor attributes or cursor status globals. In my opinion, it's actually quite daft to have to rely on general error handling mechanisms for handling cursor exhaustion situations. After all, there's nothing exceptional or erroneous about the cursor becoming exhausted. But for now, it's no use sulking. This is just how MySQL does these things right now.

38 comments:

Anonymous said...

sir
i am yohi.i working as PHP& MYSQL developer.
could you tell me if there is any statement to forword cursor position.
thanking you

Roland Bouman said...

Hi there!

No, there is no such statement. Thw only way to manipulate the cursor position it throught the fetch statement, wich retrieves the values for the current position and automatically increases the position.

Also, MySQL cursors can be traversed in a forward manner only: you cannot fetch in the reverse direction.

Do you know that the mysql forum list includes a cursor forum? It's really helpful, lots of people ask their questions there, and...they are answered too! I hang around a lot there, so, post your ques

Anonymous said...

Small type maybe there?

if v_not_found then -- exit the loop when the cursor is exhausted leave cursor_loop; end if;

should v_not_found be v_notfound

Roland Bouman said...

Yep, you are right. Thank you very much for pointing it out.

The

if v_not_found then

should indeed read:

if v_notfound then

As a sidenote:
I have noticed that this an my other MySQL cursor articles still remain quite popular. Stay tuned: I will be posting a article that explains exactly why and how cursors can be avoided in the majority of cases.

thanks again for you interest!

Gordon Royle said...

An interesting wrinkle in MySQL 5.0.22 (and possibly other versions) is that you can use FOUND_ROWS immediately after opening a cursor ...

DECLARE regionOnly CURSOR (omitted)

OPEN regionOnly;

SELECT FOUND_ROWS() INTO numRows;

Then iteration through the cursor can be done with a loop rather than stumbling off the end and catching the resulting error.

Not documented, and probably not intended.

Roland Bouman said...

Hi Gordon,

thanks for your comment, and for this interesting observation!

I asked around: the explanation is that the OPEN statement materializes the entire set associated with the cursor. The global variable that back FOUND_ROWS() is updated as a side-effect.

There are plans to alter the materialization behaviour of the OPEN statement. This means that FOUND_ROWS() is likely to change behaviour too when this get's implemented.

So, you are right in assuming that it is not recommended to rely on FOUND_ROWS() in this situation.

I also checked the documentation (http://dev.mysql.com/doc/refman/5.1/en/information-functions.html), this behaviour does not actually violate that:

"The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:"

So, you should *never* rely on FOUND_ROWS() unless it's the first thing you call after executing a SELECT SQL_CALC_FOUND_ROWS

Anyway, thanks for this interesting observation.

Cheers,

Roland

Deepthi said...

hi, this is Deepthi. I am having a query egd. pl/sql.

I want to update the salaies of all the employees.
1st emp in the table shud b updated as sal = sal+1
2nd emp sal = sal+2
3d emp sal = sal+3
etc... till the last record.
Could u plz suggest this using the cursors?

I have written a proc. but its not working...

declare
cursor c is select rownum a, e.* from emp e;
begin
for i in c loop
update emp set sal = sal + i.a;
dbms_output.put_line(i.a);
end loop;
end;

Roland Bouman said...

Hi Deepthi,

sure, you are almost there. Your UPDATE statement is missing a WHERE clause that identifies the current record fetched from the cursor though. So now, you are just updating all records in the table for each iteration of the loop.

So it should be something like this:

DECLARE
CURSOR c
AS
SELECT rownum a
, e.*
FROM emp e
;
BEGIN
FOR i IN c LOOP
UPDATE emp
SET sal = sal + i.a
WHERE id = i.id
;
END LOOP;
END;

Of course, assuming that id is the primary key of emp.

In Oracle there is a nice short cut you can use too:

UPDATE emp
SET sal = sal + i.a
WHERE CURRENT OF c
;

That said, I wonder what the purpose is of this update process. Do you realise that the order of the records is not explicitly determined? In other words, each time you run this code, another record might happen to be first, second etc. Also, you can do this all without a cursor. A single UPDATE statement is enough to accomplish this:

UPDATE emp e1
SET sal = sal + (
SELECT count(*)
FROM emp e2
WHERE e2.id <= e1.id
)

Mark Roeling said...

Is it possible to program a loop inside a loop? I'm converting oracle code to Mysql, but I can't get it working...

Roland Bouman said...

Hi Mark!

Sure, it's possible. See:

rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html

However, I recommend to avoid it. It is almost always possible to rewrite it to a JOIN.

Post your code and I'll have a look ;)

shymaa said...

i have this error can any body tell me why

the error message is
"1328 incorrect number of fetch variables "

the code is

DELIMITER ;;
CREATE TRIGGER `t_kosten_update` AFTER UPDATE ON `t_data_kosten` FOR EACH ROW begin

DECLARE kid int;
DECLARE OldID int;
DECLARE done INT DEFAULT 0 ;

DECLARE id_cur CURSOR FOR SELECT NEW.KostenID FROM t_data_kosten;
DECLARE CONTINUE HANDLER FOR not found SET done = 1;

OPEN id_cur;

repeat
FETCH id_cur INTO kid;
if not done then
BEGIN

SET OldID = (SELECT MAX(History_KostenID) FROM t_history_kosten WHERE BelongsTo=kid);

INSERT INTO t_history_kosten
(OldID, BelongsTo, FirmaID, KostenstelleID, AuftragID, KontogruppeID, BetragqualifizierungID, KalenderID, LieferantID, Kosten, updated, updatedBy, created,creatednew)
SELECT OldID,
new.KostenID, new.FirmaID, new.KostenstelleID, new.AuftragID, new.KontogruppeID, new.BetragqualifizierungID, new.KalenderID, new.LieferantID, new.Kosten, new.updated, new.updatedBy, new.created,now() FROM t_data_kosten WHERE new.KostenID=kid;


END;
end if;
until done end repeat;

CLOSE id_cur;

end;;

i know that this statement cause this error
DECLARE id_cur CURSOR FOR SELECT NEW.KostenID FROM t_data_kosten;

but i dont know why i just fetch one column in the variable

Erik said...

Hi
Please help me...

How if i want to create procedure in mysql procedure using text parameter.

For examples:
Here's the exists procedure

DELIMITER $$
CREATE PROCEDURE `create_procedure`(
ptxtStatement text
)
begin
if length(ptxtStatement) > 0 then
set @str = concat(ptxtStatement);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end if;
end $$
DELIMITER ;

The Question:
Can i call the procedure like this from my application??

call create_procedure('delimiter$$ create procedure xample(pinId int) begin select * from table1; end$$ delimiter;')

I want to use the procedure "create_procedure" to create another procedure

Is it another solution?
Thanks a lot

Roland Bouman said...

Hi Erik,

"Can i call the procedure like this from my application??

call create_procedure('delimiter$$ create procedure xample(pinId int) begin select * from table1; end$$ delimiter;')"

Alas, you cannot. The PREPARE syntax can currently not be used to execute CREATE TABLE statements.

There is a clever hack though, see:

http://datacharmer.blogspot.com/2006/04/talk-at-mysql-uc-higher-order-mysql.html

kind regards,

Roland

Anonymous said...

Your has give me great reference that's solve my need

Thank you very much

Erik said...

Hi, i need some help in mysql procedure

I want to migrate syntax from sql server procedure to mysql. But there seems some function that is not i found in mysql.

This example procedure in sql server:
/*==============================*/
CREATE PROCEDURE dbo.procedure1
/*This for parameter input*/
@pinTableId tinyint = null,
@pvcTableCode varchar(10) = null,
@pvcTableName varchar(50) = null

as
set nocount on

/*This for declaration variable*/
declare @inReturn int
declare @inErrNo int
declare @vcErrMsg varchar(255)
declare @iDoc int

/*
I want to insert data from parameter to table1 using START TRANSACTION and COMMIT or ROLLBACT function (in mysql)
*/

/*First initialize START TRANSACTION*/
BEGIN TRAN

/*checking @pvcTableCode parameter is already exists in field "code" in table1*/
IF @pvcTableCode IN (SELECT code FROM table1)
BEGIN
/*if exists set error message*/
SET @vcErrMsg = 'Code already exists'
GOTO ErrHandler
END

/*if none do the insert*/
INSERT table1(code, name)
values (@pvcTableCode,@pvcTableName)

/*if error during insert set error message*/
IF @@error != 0
BEGIN
SET @vcErrMsg = 'Insert data failed'
GOTO ErrHandler
END

END

/*this the syntax for set error message will appear within sql server error*/
ErrHandler:
IF @vcErrMsg IS NOT NULL
BEGIN
ROLLBACK TRAN

SET @inErrNo = 200000
/*function for set error message from my own*/
RAISERROR @inErrNo @vcErrMsg
RETURN 1
END
ELSE
COMMIT TRAN
RETURN 0
/*==============================*/


So if i execute those procedure will appear error message "Code already exists", and that message can i catch up in my application.
So i want to validate the application by the stored procedure routine.

is there any code for MySQL for syntax like RAISERROR??

MySQL only have

I'm sory i post to long
I don't know how to explain

Thx for help

Roland Bouman said...

Hi Erik,

(please consider using the MySQL forums for this type of question next time. I don't mind requests at all but your's seems to be off topic with regard to the article.)

To cut a long story short, indeed, raising custom errors is not possible in MySQL stored procedures. Support has been added in I believe 6.1 (alpha/ development tree)

However, you can work around it in a number of ways, see:

http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

Erik said...

Oh i'm sorry i write off the topic
Your blog have a lot of topic thats make me hard how to find appropriate topic

Mysql not yet have command to raise my on error exept version 6

Thanks for your article. I will try for it

Anonymous said...

Hi,

I am trying to write a cursor for loading data into a table after fetching it from other table.My table into which i am loading the data cant allow duplicates, hence I need to write an handler which will skip such insertion and move forward with the next row in the source table. All I can do in the below stored procedure is to exit the loop without throwing error.


delimiter //
CREATE PROCEDURE load_feed()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,b,c VARCHAR(45);
DECLARE cur1 CURSOR FOR SELECT serv_name,km_name,method_name FROM reporting.raw_data_table ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET done=0;
OPEN cur1;
lb1: LOOP
IF done=1 THEN
LEAVE lb1;
END IF;
IF NOT done=1 THEN
FETCH cur1 INTO a, b,c ;
INSERT INTO reporting.serv_attributes (serv_name,function_type,attribute) VALUES (a,b,c);
END IF;
END LOOP ;
CLOSE cur1 ;
END ;
//
delimiter ;

Roland Bouman said...

Hey Anonymous,

please look up the ON DUPLICATE KEY UPDATE and the REPLACE syntax.

You can find it here:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

and

http://dev.mysql.com/doc/refman/5.1/en/replace.html

Good luck!

Anonymous said...

Hello!

Can you please help point out to me how I can modify this code, so I wont get this error anymore? ERROR 1064 (42000) at line 26: When Creating a Stored Procedure

With lots of thanks!

delimiter //
DROP PROCEDURE IF EXISTS sp_loe_calc //
CREATE PROCEDURE sp_loe_calc ()
BEGIN
DECLARE exit_flag INT DEFAULT 0;
DECLARE num_accts INT;
DECLARE LOE1st TIME;
DECLARE LOEsub TIME;
DECLARE LOE_count TIME;
DECLARE LOE_total TIME DEFAULT 0;
DECLARE project_flag INT;
DECLARE row_count INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT count(crt_sr_details.account_num) as num_anis_per_accts,
crt_LOE.LOE_1st_req,
crt_LOE.LOE_subsequent_req
FROM crt_servicerequest
Inner Join crt_sr_details ON crt_servicerequest.servicerequest_id = crt_sr_details.servicerequest_id
Inner Join crt_LOE ON crt_servicerequest.request_type_id = crt_LOE.request_type_id
AND crt_servicerequest.request_detail_id = crt_LOE.request_detail_id
AND crt_servicerequest.request_action_id = crt_LOE.request_action_id
WHERE crt_servicerequest.servicerequest_id = 78
Group by account_num;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;
OPEN c;
fetch_loop: LOOP
FETCH c INTO num_accts, LOE1st, LOEsub;
IF num_accts = 1 THEN LOE_count = LOE1st;
ELSE LOE_count = (LOE1st + ((num_accts - 1) * LOEsub));
END IF;
IF exit_flag THEN LEAVE fetch_loop; END IF;
SET row_count = row_count + 1;
SET LOE_total = LOE_total + LOE_count;
END LOOP;
CLOSE c;
SELECT 'number of rows fetched =' , row_count;
SELECT 'Total LOE =' , LOE_total;
END //
delimiter ;

anshu said...

I,ve a cursor for a sql query,say q1, and a continue handler for the same.

But my problem is that I am having another query with SELECT INTO clause,say q2.

Incase my q2 returns empty resultset, the continue handler for q1 gets executed.

How can I avoid this ??

anshu said...

I,ve a cursor for a sql query,say q1, and a continue handler for the same.

But my problem is that I am having another query with SELECT INTO clause (IMPLICIT CURSOR),say q2.

Incase my q2 returns empty resultset, the continue handler for q1 gets executed.

How can I avoid this ??

Roland Bouman said...

Hi anshu,

please take a look at these articles:

http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html
http://rpbouman.blogspot.com/2006/09/refactoring-mysql-cursors.html

I am sure you can find something in there that you can use to improve your procedures.

For a quick hack, you can:

- enclose the SELECT...INTO into it's own BEGIN...END block to prevent the exception from bubbling up.

my_loop: LOOP
FETCH csr INTO ...
IF v_done THEN
CLOSE csr;
LEAVE my_loop;
END;
...
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN /* noop */ END;
SELECT ...
INTO ...
;
END;
END;


- set a marker variable right before the SELECT...INTO, and have your continue handler check for that. So:

DECLARE v_done BOOL DEFAULT FALSE;
DECLARE v_select_into BOOL DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_done := NOT v_select_into;
...
my_loop: LOOP
FETCH csr INTO ...
IF v_done THEN
CLOSE csr;
LEAVE my_loop;
END IF;

SET v_select_into := TRUE;
SELECT ...
INTO ...
;
SET v_select_into := FALSE:
END LOOP;

Roland Bouman said...

ansu:

just put the SELECT INTO in its own block and trap there NOT FOUND condition there to prevent it from bubbling up.
Alternatively, set a flag before runnin the SELECT INTO and have the handler check if the flag was set. Of course you need to unset the flag again right after the SELECT INTO.

Abhu said...

Hey there,
Is there anyways i can return more then one value from a stored function....????

Picas said...

Hi Rolland, Is there any syntax to get number of row of the cursor in mysql..?

Roland Bouman said...

@Abhu no you can't.

@picas: It's "Roland"

Yes, there is a very dirty trick that actually works. However, it is not guaranteed that this will work in future versions of MySQL because it relies on a rather unfortunate detail in the way cursors are implemented.

See, under the covers, MySQL cursors work by evaluating the query, and storing the result in a temporary table. This happens when opening the cursor. As a side-effect, the FOUND_ROWS() function actually works exaclty like it would for executing a regular query. Here's an example that proves it:

delimiter //

create procedure p_cursor()
begin
declare csr cursor for
select SQL_CALC_FOUND_ROWS *
from information_schema.tables;

select found_rows();
open csr;
select found_rows();
end;
//

delimiter ;

call p_cursor();

If you execute it, you get:

mysql> call p_cursor();
+--------------+
| found_rows() |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)

+--------------+
| found_rows() |
+--------------+
| 1946 |
+--------------+
1 row in set (2 min 12.66 sec)

However, I should point out that in my opinion, the materialization of the resultset when opening the cursor is a very big mistake. It makes MySQL cursors extremely expensive: a lot of memory may be required to store the resultset, and it makes them slow.

So I actually hope this will be cleaned up ASAP after which this hack will stop working.

Anonymous said...

Hi, I need help with dual cursors.

one cursor for a list mysql tables
2nd cursor for a column data "with the table name" as first row.

I can fiqure out the column data part, but the first one, which selects the tables, which change, is hard for me.


any help?

Roland Bouman said...

Hi Anonymous,

what do you want to do with those lists? Are you sure you need a cursor?

Anonymous said...

I asked for help b4 couldnt understand the code to modify. If I cant figure out what the code does it, not helpful. Your idea of two cursors came to mind, again


USE `TEST2`;

DROP TABLE IF EXISTS teams, bats,cats, rats, dogs, output;

CREATE TABLE teams(id INT ,tname CHAR(32));
CREATE TABLE bats(DATE DATETIME, temp INT);
CREATE TABLE cats(DATE DATETIME, temp INT);
CREATE TABLE rats(DATE DATETIME, temp INT);
CREATE TABLE dogs(DATE DATETIME, temp INT);


INSERT INTO teams VALUES(1,'bats'),(2,'cats'),(3,'rats'),(4,'dogs');

INSERT INTO bats VALUES
('2008-1-1',112),('2008-1-2 ',402),
('2008-1-3 ',121),('2008-1-4 ',421);

INSERT INTO cats VALUES
('2008-1-1 20:00:00',1121),('2008-1-2 20:00:00',1402),
('2008-1-3 20:00:00',1121),('2008-1-4 20:00:00',1421);

INSERT INTO rats VALUES
('2008-1-1 20:00:00',2112),('2008-1-2 20:00:00',2402),
('2008-1-3 20:00:00',2121),('2008-1-4 20:00:00',2421);

INSERT INTO dogs VALUES
('2008-1-1 20:00:00',3112),('2008-1-2 20:00:00',3402),
('2008-1-3 20:00:00',3121),('2008-1-4 20:00:00',3421);

I need this......output
+----------+---------+-----------+------+
| team |'2008-1-1' | '2008-1-2' | '2008-1-3' |'2008-1-4'|
+----------+---------+-----------+------+
| bats | 112 | 402 | 121 | 421 |
| cats | 1112 | 1402 | 1121 | 1421 |
| rats | 2112 | 2402 | 2121 | 2421 |
| dogs | 3112 | 3402 | 3121 | 3421 |
+----------+---------+-----------+------+

Do you see one cursor for teams, cycle through each team using a second cursor for team data.

Insert data in to output table using a loop.
I looked at your dual cursors blog.... in over my head. But the logic is there.

TIA

Anonymous said...

Great blog, helped an OCP to program a procedure in MySql.

Moiz

Prasad said...

Hi Roland,
Could you please let us know how to come over the problem of memory issues in case of large number of rows (> 1 million) while using cursors?

Learned that even using limit clause with higher offset would also cause memory issues.

Roland Bouman said...

Hi Parad,

what is your question exactly? What memory issues - are you experiencing a specific error? then please explain what error message you're getting.

Prasad said...

Hi Roland,
Thanks a lot for your quick response.
I am getting the following error during the execution of a stored procedure wherein a cursor is selecting all of the rows of a large table. Basically it iterates over the large table rows, extract some column values and updates those values into another table.

ERROR 3 (HY000): Error writing file '/tmp/MYmbeDU6' (Errcode: 28)

I would like to know is there anything equivalent to Oracle's BULK COLLECT with the LIMIT clause and iterates over the 3 million rows.

The only option that I have explored is using of dynamic SQL query in a loop with the LIMIT clause. Even in this case like "Select * from LargeTable LIMIT 100000, 5000" - here, the offset is too high and this would cause performance issues. Not sure how to tackle this issue efficiently. Please enlighten me on this subject.

Thanks.

Roland Bouman said...

Hi Prasad,

there is nothing like BULK COLLECT in MySQL.

That said, to me it sounds like that what you want to achieve - "extract some column values and updates those values into another table" - should not be done with a cursor. You should probably write a simple UPDATE statement to do that.

To do the update using values from another table, either use a subquery, or use the multi-table update syntax (see http://dev.mysql.com/doc/refman/5.1/en/update.html)

rock said...

5 years after posting this was still relevant to me! Thanks for the brush up on cursors!

Manoj Kansal said...

Hi,

I have been trying to run a stored procedure in mysql, that is somewhat like this:

declare c1 cursor for
select cust_id, rate, updated_date
from customers_all
order by rate desc, updated_date;

And when i am looping through this cursor, the cursor does not ever take the order by clause into consideration. It always runs as the order by clause was not there, make what so ever changes to the order by claue.

Any Idea what might be the problem.

Thanks,
Manoj

Roland Bouman said...

Hi Manjo,

please file a bug at bugs.mysql.com. I can't help here.