Re: PL/SQL nested cursors and loops

From: Venkateswara R Polisetti <vrao_at_sctcorp.com>
Date: 1996/09/05
Message-ID: <1996Sep5.165005.8504_at_mcrcr6>#1/1

Kelvin Stingel wrote:

>Hi all,
 

>Can i remove this nested cursor problem
 

>Im feeding the result from c1 into c2.
>For every loop of c1, the cursor c2 opens fetches and closes
 

>Can I feed c1 into something then close c1 and open c2 to process the
>values.
 

>cursor c1 is select a from t1;
>cursor c2(a) is select a,b from t2 where col1 = (a)
 

>open c1
>loop
>fetch c1 into a;
>exit when notfound
> open c2(a)
> loop
> fetch c2 into x
> exit when notfound
> end loop
> close c2
>end loop c1
>close c1

>thanks
>Kelvin

Hi Kelvin,

        What I understood from your "c1" cursor is that you are selecting 'a' from all the rows from t1( there is no where clause). What you can do is rephrase your second cursor "c2" so that it will include a sub query to select the value "a" from table t1. Get rid of c1 cursor.  

It could be something like this:

DECLARE

	CURSOR  c2 IS select a,b from t2 where col1 in
			 ( select a 
			   from t1);
	/* declare variables for fetching from c2 */
BEGIN
	OPEN c2;
	LOOP
		FETCH c2 INTO ...;
  		/* do something with the fetched value */
		EXIT WHEN NO_DATA_FOUND;
	END LOOP;	
	CLOSE C2;

END; Venkateswara Rao

/-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\

| Venkateswara R.Polisetti                 Email: vrao_at_sctcorp.com  |
| SCT Utility Systems, Inc.             Phone(O): (803) 935-8160    |
| 9 Science Court                            (R): (803) 736-1609    |
| Columbia, SC  29203-9344                                          |

\-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-/ Received on Thu Sep 05 1996 - 00:00:00 CEST