Re: dependent cursors

[Roland Bouman]

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.