Re: Want to Avoid Cursor

[Roland Bouman]

You could do that, providing you put the declare inside it's own BEGIN..END block. Unfortunately, you'd have to put the loop in scope, meaning you'd have to duplicate the loop as well:

main_block: begin
declare v_done bit
default false
;
declare continue handler
for not found
set v_done := true
;
case school
when 'bla' then
begin
declare csr cursor for
SELECT ...
FROM ..
etc.
;
open csr;
csr_loop: loop
fetch csr into
...
, ...
, ...
;
if v_done then
close csr;
leave csr_loop;
end if;
..
.. interesting stuff goes here
..
end loop;
end;
when 'blabla' then
begin
declare csr cursor for
SELECT ...
FROM ..
etc.
;
open csr;
csr_loop: loop
fetch csr into
...
, ...
, ...
;
if v_done then
close csr;
leave csr_loop;
end if;
..
.. interesting stuff goes here
..
end loop;
end;
else
end;
end main_block;

Alternatively (and this is what I'd probably do) you declare as many cursors as you need, but open only one conditionally:

main_block: begin
declare v_done bit
default false
;
declare continue handler
for not found
set v_done := true
;

declare cursor csr_bla for
SELECT ...
FROM bla.<tablename>
...
;
declare cursor csr_blabla for
SELECT ...
FROM bla.<tablename>
...
;
case school
when 'bla' then
open csr_bla;
csr_loop: loop
...
...etc.
...
end loop;
when 'blabla' then
open csr_blabla;
csr_loop: loop
...
...etc.
...
end loop;
end;
end main_block;