One of the my Anonymous friend ask the question on my blog and the question is
"What if my cursor select query itself is dynamic? Does MySql support this functionality?"
First of all thanks to Anonymous friend to ask a question.
Yes it can support, its a little bit tricky.
First of all the problem is that we have to open a cursor for a query which is dynamic, variable will be pass at runtime.
We will open the cursor for view which is not exist at the time. In later step we will create view.
Then we will open cursor and later we can delete the view.
A prepare statement can be used to create a view with the dynamic SQL and the cursor can select from this statically-named view. There is almost no performance impact.
we have an example of using a view to pass the column name and table name into a cursor.
DELIMITER //
-- drop procedure if exists
DROP PROCEDURE IF EXISTS dynamic_Cursor//
CREATE PROCEDURE dynamic_Cursor(IN columnname varchar(255), tablename varchar(50))
BEGIN
DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
DECLARE v_column_val VARCHAR(50);
DECLARE done INT DEFAULT 0;
-- declare the cursor for view which is not exist at that time
DECLARE cur_table CURSOR FOR SELECT * FROM emp_vw;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;
-- dynamic query will built the view
SET @query = CONCAT('CREATE VIEW emp_vw as select ', columnname, ' from ', tablename);
select @query;
PREPARE stmt from @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Open cursor
OPEN cur_table;
FETCH cur_table INTO v_column_val;
WHILE done = 0 DO
SELECT v_column_val;
FETCH cur_table INTO v_column_val;
END WHILE;
CLOSE cur_table;
DROP VIEW emp_vw;
END;
//
DELIMITER ;
call dynamic_Cursor('ename','emp');
continue part-2 of post
Create procedure, Cursor example , execute immediate
"What if my cursor select query itself is dynamic? Does MySql support this functionality?"
First of all thanks to Anonymous friend to ask a question.
Yes it can support, its a little bit tricky.
First of all the problem is that we have to open a cursor for a query which is dynamic, variable will be pass at runtime.
We will open the cursor for view which is not exist at the time. In later step we will create view.
Then we will open cursor and later we can delete the view.
A prepare statement can be used to create a view with the dynamic SQL and the cursor can select from this statically-named view. There is almost no performance impact.
we have an example of using a view to pass the column name and table name into a cursor.
DELIMITER //
-- drop procedure if exists
DROP PROCEDURE IF EXISTS dynamic_Cursor//
CREATE PROCEDURE dynamic_Cursor(IN columnname varchar(255), tablename varchar(50))
BEGIN
DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
DECLARE v_column_val VARCHAR(50);
DECLARE done INT DEFAULT 0;
-- declare the cursor for view which is not exist at that time
DECLARE cur_table CURSOR FOR SELECT * FROM emp_vw;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;
-- dynamic query will built the view
SET @query = CONCAT('CREATE VIEW emp_vw as select ', columnname, ' from ', tablename);
select @query;
PREPARE stmt from @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Open cursor
OPEN cur_table;
FETCH cur_table INTO v_column_val;
WHILE done = 0 DO
SELECT v_column_val;
FETCH cur_table INTO v_column_val;
END WHILE;
CLOSE cur_table;
DROP VIEW emp_vw;
END;
//
DELIMITER ;
call dynamic_Cursor('ename','emp');
continue part-2 of post
Create procedure, Cursor example , execute immediate
