MySQL Forums
Forum List  »  Stored Procedures

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

Yes if b.cc<>0 then set b.cc=m.cc, that is the requirement.

I have this cursor.But this seems to be in some way not correct.
Takes long time to complete execution and also update does not work.

Any thing wrong here?




drop procedure build_title_listp;

DELIMITER $$
CREATE PROCEDURE build_title_listp ( )
BEGIN
declare no_more_rows boolean default false;
DECLARE v_title_sci varchar(1000); -- Declaring variables for master table
DECLARE v_st varchar(1000) ;
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) ; -- Declaring variables for base_report table
DECLARE v_b_st varchar(1000) ;
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 v_b_id varchar(100);



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 standardtitle, title_sci,issn1,issn2,title,issn,eissn,databas,cc,base_report_id from base_report_gt_zero;


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,v_b_id;

if (v_st=v_b_st and v_title_sci=v_b_title_sci and v_title=v_b_title and v_issn1 = v_b_issn1 and v_issn2 =v_b_issn2 and
v_issn =v_b_issn and v_eissn =v_b_eissn and v_databas=v_b_databas) then
update base_report_gt_zero set cc=v_cc where base_report_id=v_b_id ;
end if;

if no_more_rows then
set no_more_rows := false;
close base_cursor;
leave Loop2;
end if ;
select v_cc ; -- master cc value
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; -- base values.

end loop loop2;
end loop loop1;

end$$

DELIMITER ;



******************************************************************************************************************************************
this is a different way of defining the cursor statement.I defined the cursor with where clause here but not in the above procedure(b*m rows are processed in the above procedure).In the above procedure the where clause is inside the base cursor loop.
.The below one gets run pretty quickly. as it will have only few records fetched by base cursor.Just different way of calling the cursor.

So which is the best way to create a cursor.

Though the update is not working in this too.



drop procedure build_title_listp1;

DELIMITER $$
CREATE PROCEDURE build_title_listp1 ( )
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 v_id varchar(10);



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,base_report_id 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,v_id;

update base_report_gt_zero set cc=v_cc where base_report_id = v_id;
if no_more_rows then
set no_more_rows := false;
close base_cursor;
leave Loop2;
end if ;
select v_cc ; -- master cc value
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; -- base values.

end loop loop2;
end loop loop1;

end$$

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Display all the records from table using cursor
645
September 26, 2017 01: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.