Display all the records from table using cursor
Posted by:
kaji Pasa
Date: September 22, 2017 06:21PM
Hello, I am new in cursor,
I am trying to fetch all the records form a table using cursor. Here is my code , i am able do display the first row only.
how can i display all the rows
DELIMITER $$
CREATE PROCEDURE build_title_list (INOUT title_list varchar(4000),INOUT st varchar(4000))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_title varchar(100) DEFAULT "";
DECLARE v_st varchar(100) DEFAULT "";
DEClARE title_cursor CURSOR FOR
SELECT StandardTitle,Title_sci FROM master_data;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN title_cursor;
get_title: LOOP
FETCH title_cursor INTO v_title,v_st;
IF v_finished = 1 THEN
LEAVE get_title;
END IF;
SET title_list = v_title;
set st= v_st;
END LOOP get_title;
CLOSE title_cursor;
END$$
DELIMITER ;
call build_title_list(@title_list,@st);
select @title_list,@st
Below is the output.
output
---------------------------------------------------------------
AMERICANJOURNALOFEDUCATION American Journal of Education
___________________________________________________________________
Subject
Views
Written By
Posted
Display all the records from table using cursor
4157
September 22, 2017 06:21PM
932
September 22, 2017 07:46PM
965
September 22, 2017 10:11PM
841
September 25, 2017 09:07AM
821
September 25, 2017 11:17AM
776
September 25, 2017 11:15AM
888
September 25, 2017 12:49PM
804
September 25, 2017 01:18PM
708
September 25, 2017 01:36PM
697
September 25, 2017 02:00PM
702
September 25, 2017 03:51PM
819
September 25, 2017 06:24PM
721
September 26, 2017 01:34PM
775
September 26, 2017 01:56PM
682
September 26, 2017 02:22PM
788
September 26, 2017 03:35PM
723
September 27, 2017 10:28AM
839
September 27, 2017 11:39AM
796
September 26, 2017 12:43PM
759
September 26, 2017 01:27PM
720
September 26, 2017 01:44PM
834
September 25, 2017 01:46PM
832
September 25, 2017 12:56PM