MySQL Forums
Forum List  »  Stored Procedures

Re: Display all the records from table using cursor
Posted by: kaji Pasa
Date: September 25, 2017 12:56PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Display all the records from table using cursor
749
September 25, 2017 12:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.