Entry

How to handle the Continue Varaible --Problem Of Cursor inside a Cursor

Feb 18th, 2008 19:41
dman, Satyagopalakrishnan nemani, http://sturly.com

The handler varaible get the status of the first cursor .This creates 
you problem of comming out of the entire cursor Let have a look at the 
normal code (wrong way..)
DECLARE H1 int default 0;
DECLARE H2 int default 0;
DECLARE CUR_EX_OUTER CURSOR FOR
select .....;
DECLARE CUR_EX_INNER CURSOR FOR
select .....;
CONTINUE HANDLAER.....status...FOR H1=1
CONTINUE HANDLAER.....status...FOR H2=1
BEGIN
 OPEN CUR_EX_OUTER 
.....
IF NOT H1...
...
open CUR_EX_INNER 
  if NOT H2...
  end if;
CLOSE CUR_EX_INNER 
END IF;
CLOSE CUR_EX_OUTER ;
END
Here the H2 gets the exitt state of H1 which is wrong and it won't 
work properly...
Instead you do this in the following way(Using a nested Block begin end
there declare the H2 )
DECLARE H1 int default 0;
DECLARE CUR_EX_OUTER CURSOR FOR
select .....;
CONTINUE HANDLAER.....status...FOR H1=1
BEGIN
 OPEN CUR_EX_OUTER 
   .....
   IF NOT H1...
   BEGIN 
    DECLARE H2 int default 0;
    DECLARE CUR_EX_INNER CURSOR FOR
select .....;
    CONTINUE HANDLAER.....status...FOR H2=1
     OPEN  CUR_EX_INNER ;
      if not H2 then
       ........
       end if;
    END;
END IF;
CLOSE CUR_EX_OUTER ;
END