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
3896
September 22, 2017 06:21PM
787
September 22, 2017 07:46PM
810
September 22, 2017 10:11PM
724
September 25, 2017 09:07AM
722
September 25, 2017 11:17AM
661
September 25, 2017 11:15AM
772
September 25, 2017 12:49PM
631
September 25, 2017 01:18PM
581
September 25, 2017 01:36PM
582
September 25, 2017 02:00PM
574
September 25, 2017 03:51PM
658
September 25, 2017 06:24PM
594
September 26, 2017 01:34PM
607
September 26, 2017 01:56PM
532
September 26, 2017 02:22PM
646
September 26, 2017 03:35PM
609
September 27, 2017 10:28AM
680
September 27, 2017 11:39AM
642
September 26, 2017 12:43PM
616
September 26, 2017 01:27PM
586
September 26, 2017 01:44PM
662
September 25, 2017 01:46PM
713
September 25, 2017 12:56PM