Re: Display all the records from table using cursor

[kaji Pasa]

The below cursor i have made.
I could not find why the update code is not working which is in comment in the below code
************************************************************************************



drop procedure build_title_list2;

DELIMITER $$
CREATE PROCEDURE build_title_list2 ( )
BEGIN
declare no_more_rows boolean default false;
DECLARE v_title_sci varchar(1000) DEFAULT "";
DECLARE v_st varchar(1000) DEFAULT "";
Declare v_issn1 varchar(100);
declare v_issn2 varchar(100);
declare v_title varchar(1000);
declare v_issn varchar(100);
declare v_eissn varchar(100);
declare v_databas varchar(1000);
declare v_cc varchar(1000);



DECLARE v_b_title_sci varchar(1000) DEFAULT "";
DECLARE v_b_st varchar(1000) DEFAULT "";
Declare v_b_issn1 varchar(100);
declare v_b_issn2 varchar(100);
declare v_b_title varchar(1000);
declare v_b_issn varchar(100);
declare v_b_eissn varchar(100);
declare v_b_databas varchar(1000);
declare v_b_cc varchar(1000);



DEClARE master_cursor CURSOR FOR
SELECT StandardTitle,Title_sci,issn1,issn2,title,issn,eissn,databas,cc FROM master_data;

Declare base_cursor cursor for
-- select * from base_report_gt_zero
select standardtitle, title_sci,issn1,issn2,title,issn,eissn,databas,cc from base_report_gt_zero
where standardtitle = v_st and title_sci=v_title_sci and issn1=v_issn1 and issn2=v_issn2 and title=v_title
and issn=v_issn and eissn=v_eissn and databas=v_databas;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET no_more_rows := true;

OPEN master_cursor;
loop1: LOOP
FETCH master_cursor INTO v_st,v_title_sci,v_issn1,v_issn2,v_title,v_issn,v_eissn,v_databas,v_cc;
if no_more_rows then
close master_cursor;
LEAVE loop1;
END IF;
open base_cursor;
loop2:LOOP

FETCH base_cursor INTO v_b_st,v_b_title_sci,v_b_issn1,v_b_issn2,v_b_title,v_b_issn,v_b_eissn,v_b_databas,v_b_cc;

-- update base_report_gt_zero set cc=v_cc ;

if no_more_rows then
set no_more_rows := false;
close base_cursor;
leave Loop2;
end if ;
select v_cc ;
select v_b_st,v_b_title_sci,v_b_issn1,v_b_issn2,v_b_title,v_b_issn,v_b_eissn,v_b_databas,v_b_cc;

end loop loop2;
end loop loop1;

end$$

DELIMITER ;