MySQL Forums
Forum List  »  InnoDB

Re: dependent cursors
Posted by: Roland Bouman
Date: June 29, 2005 06:14AM

Yes, it's possible, but instead of starting a new declaration at line 8, you must open an new block and include the necessary declarations right there:

create procedure dependant_cursors()
begin
declare v_table_schema varchar(128);
declare v_table_name varchar(128);
declare csr1_no_more_rows boolean default FALSE;
declare csr1 cursor for
SELECT table_schema,table_name
FROM information_schema.TABLES
WHERE table_schema = 'information_schema'
AND table_name = 'TABLES'
;
declare continue handler for sqlstate '02000'
set csr1_no_more_rows = TRUE
;
open csr1;
repeat
fetch
from csr1
into v_table_schema
, v_table_name
;
select 'csr1'
, v_table_schema
, v_table_name
;
--
--new block, and associated declarations
--
begin
declare v_column_name varchar(128);
declare csr2_no_more_rows boolean default FALSE;
declare csr2 cursor for
SELECT COLUMN_NAME
FROM information_schema.COLUMNS c
WHERE table_schema = v_table_schema
AND table_name = v_table_name
;
declare continue handler for sqlstate '02000'
set csr2_no_more_rows = TRUE
;
open csr2;
repeat
fetch
from csr2
into v_column_name
;
select 'csr2'
, v_column_name
;
until csr2_no_more_rows
end repeat;
close csr2;
end;
until csr1_no_more_rows
end repeat;
close csr1;
end;


Another word about this.
I believe that setting up a repeat loop for a cursor is not a good idea unless you know for sure it will yield at least one row. It would be better to use a LOOP..END LOOP, because this gives us the opportunity to FETCH right after starting the loop, en test wheter the resultset is exhausted so when can LEAVE if it is before continuing all the hard work we plan on doing inside the loop.

I tried this from within a similar begin..end block, but i can't compile it.

l: loop
fetch csr into var;
if csr_no_more_rows then
leave l:
end if;
end loop l;

In fact, anything I try with a LOOP..END LOOP, with or without cursors goes fine until i insert the loop labels or write the LEAVE statement.

Anyone?



Edited 1 time(s). Last edit at 06/29/2005 06:16AM by Roland Bouman.

Options: ReplyQuote


Subject
Views
Written By
Posted
3715
May 20, 2005 02:25AM
Re: dependent cursors
2776
June 29, 2005 06:14AM
2814
June 29, 2005 10:27PM
2417
July 01, 2005 05:26AM


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.