MySQL Stored Procedure : Variables in Cursor – Solution

MySQL stored procedures does not support variables in cursors. Here is a work around for handling the same.

It uses simple logic of creating a temporary cursor table, which will store required cursor information. Add additional column for storing the status… Read the content one by one from the cursor table.

Step #1: Create temporary cursorTable like normal table.Add a column to track, whether you have processed it or no

SET @myQuery = CONCAT(‘CREATE TABLE IF NOT EXISTS cursorTable(
ObjectType varchar(64) default  NULL,
ObjectTypeID bigint unsigned NOT NULL,
TableName varchar(64) default NULL,
STATUS char(1) DEFAULT “N”,
KEY STATUS(STATUS),
KEY ObjectType(ObjectType),
KEY ObjectTypeID(ObjectTypeID),
KEY TableName(TableName)
) — ENGINE=MEMORY ‘ );
CALL executeQuery(@myQuery);

SET  @myQuery = CONCAT(“INSERT INTO cursorTable
SELECT ‘”,ObjectType,”‘, T.”,TypeTableTypeField,”,UPPER(T.TABLENAME),’N’
FROM “,@myDBName,”.”,TypeTable,” T
where T.TableName IS NOT NULL
GROUP BY T.TableName
ORDER BY T.TableName
“);
CALL executeQuery(@myQuery);

Ex:

create table temptables.CursorTable select distinct myID, ‘N’ Processed from mastertable where <some condition>;

As this is a standard statement, you can use prepared statement.

Step #2: declare cursorCount as 0<zero>. Note : It is a global variable. So no need to declare it earlier. It is visible for all procedures executed through the same connection

Ex :

set @cursorCount=0;

Step #3: Now set @cursorCount as actual counts need to be handled. Use following prepared statement. Trick is by using global variable ie ‘@’…

SET  @myQuery = CONCAT(“select count(*) INTO @cursorCount
from cursorTable
where STATUS = ‘N’
AND ObjectType = ‘”,ObjectType,”‘
“);
CALL executeQuery(@myQuery);

Step #4: Now you handle it with in a while loop. With in while loop also, you read data from your cursorTable through select into global variable.

WHILE (@cursorCount> 0) DO
SET  @myQuery = CONCAT(“select TableName INTO @EXT_TblNm
from cursorTable
where STATUS = ‘N’
AND ObjectType = ‘”,ObjectType,”‘
LIMIT 1″);
CALL executeQuery(@myQuery);

SET  @myQuery = CONCAT(“update cursorTable
SET STATUS = ‘Y’
where TableName = ‘”,@EXT_TblNm,”‘
AND ObjectType = ‘”,ObjectType,”‘
AND STATUS = ‘N'”);
CALL executeQuery(@myQuery);

…………………….. Process you cursor variables here

SET  @myQuery = CONCAT(“select count(*) INTO @cursorCount
from cursorTable
where STATUS = ‘N’
AND ObjectType = ‘”,ObjectType,”‘”);
CALL executeQuery(@myQuery);
END WHILE;

If require, drop your cursorTable here…

Hope it will be useful for some one.

Note : executeQuery(@myQuery);

It is a simple procedure, which logs all query along with execution time in my development environment. In production environment, it just executes the query. As I am logging all queries, it is easy for us to know actual query which failed.

DECLARE myLogID BIGINT UNSIGNED DEFAULT 0;
SET @executeQuery = CONCAT(“insert into log(LOG) values(‘”,REPLACE(userQuery,’\”,’\\\”),”‘)”);
PREPARE executeQueryStmt FROM @executeQuery;
EXECUTE executeQueryStmt;
DEALLOCATE PREPARE executeQueryStmt;

SELECT LAST_INSERT_ID() INTO myLogID;
SET @executeQuery = userQuery;
PREPARE executeQueryStmt FROM @executeQuery;
EXECUTE executeQueryStmt;
DEALLOCATE PREPARE executeQueryStmt;
SET @executeQuery = CONCAT(“UPDATE log
SET STATUS = ‘S’,
StartTimeStamp = StartTimeStamp,
EndTimeStamp = NULL
where ID = “,myLogID);
PREPARE executeQueryStmt FROM @executeQuery;
EXECUTE executeQueryStmt;
DEALLOCATE PREPARE executeQueryStmt;

S.Sekar

Tags: cursor, MySQL, procedure, variable mysql stored procedure cursor variable