Home » SQL & PL/SQL » SQL & PL/SQL » doubt in for cursor
doubt in for cursor [message #195028] Tue, 26 September 2006 12:04 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

All,
how to perform this..pls have a look at the commented statement

declare
  cursor c1 is select col1 from table_1;
 
begin
  for c2 in c1
  loop
	   select t2.col1 into v_1 from t2  -- first select statement
	   where t2.col2=c2.col1
	   if v_1 is  null
	   then
	     --i have to take the contrl to next value of cursor without executing the second select statement
	    
	   end if; 
	   select t3.col1 into v_2 from t3 -- second select statement 
	   where t3.col3 = c2.col1
	   if v_2 is null
	   then
	     -- .........
	   end if;

  end loop
end;
/


Naveen
Re: doubt in for cursor [message #195043 is a reply to message #195028] Tue, 26 September 2006 13:49 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

yep..i got the solution...can be performed using goto

Naveen
Re: doubt in for cursor [message #195057 is a reply to message #195043] Tue, 26 September 2006 15:24 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, use of the GOTO statement is a bad idea, especially in the loop. I'd rather not use it unless it is necessary. And, if I'm not wrong, that's not the situation here. Perhaps you could avoid GOTO using something like this:
for c2 in (select col1 from table_1) 
loop
   select t2.col1 into v_1 from table_2
   where t2.col2 = c2.col1;
   
   if v_1 is null 
   then
      null;
   else
      select t3.col1 into v_2 from table_3
      where t3.col3 = c2.col1;
      
      if v_2 is null then
         do_something;
      end if;
   end if;
end loop;
As you can see, it is about IF-THEN-ELSE-END IF.

Also, are you SURE that we are talking about "v_1 IS NULL"? Did you consider avoiding the NO-DATA-FOUND error in both SELECT statements? If not, perhaps you should include the exception handler into the code.
Re: doubt in for cursor [message #195112 is a reply to message #195057] Wed, 27 September 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, you could combine the first SELECT statment with the driving cursor, and remove the NDF problem Littlefoot highlighted, AND improve the performance and readablility of your code:

for c2 in (select t2.col_1
           from   table_1 t1
                 ,table_2 t2
           where  t2.col_2 = t1.col1) 
loop
   select t3.col1 into v_2 from table_3
   where t3.col3 = c2.col1;
      
   if v_2 is null then
      do_something;
   end if;
end loop;
Re: doubt in for cursor [message #195227 is a reply to message #195028] Wed, 27 September 2006 08:23 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Thanks Littlefoot, JRowbottom for your response.
I too agree with your statement
"Basically, use of the GOTO statement is a bad idea, especially in the loop"

But there is no other option for me...
Actually i have just given a basic sample.. but in actual code there are lot of conditions and loops so that at any case if i get any ORA error i have to terminate the execution for that record and move on to the next after logging the error into a file.. Also there are many calls to other procedure...

Once again thanks a lot for ur suggestions...

Naveen
Re: doubt in for cursor [message #195239 is a reply to message #195028] Wed, 27 September 2006 09:28 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I don't know if it's considered good practice but I've seen this idea used

declare

  -- Declare the exception
  next_rec exception;
  pragma exception_init(next_rec,-20000);

  cursor c1 is select col1 from table_1;
 
begin
  for c2 in c1
  loop

    begin -- Extra begin here

	   select t2.col1 into v_1 from t2
	   where t2.col2=c2.col1;

	   if v_1 is  null
	   then
             raise next_rec; -- raise exception
	   end if; 

           select t3.col1 into v_2 from t3
	   where t3.col3 = c2.col1;

	   if v_2 is null
	   then
	     raise next_rec; -- raise exception
	   end if;

    exception
       when next_rec then -- handle exception
          null;
    end;
	   
   -- code will then continue with next record

  end loop
end;


HTH
Re: doubt in for cursor [message #195240 is a reply to message #195239] Wed, 27 September 2006 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've seen that used too.
It's basically a GOTO which has had it's shoes polished and got a decent suit on.
Re: doubt in for cursor [message #195242 is a reply to message #195028] Wed, 27 September 2006 09:34 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Laughing

Does that mean it's as bad as a goto, or is it the acceptable face of a goto?
Re: doubt in for cursor [message #195246 is a reply to message #195242] Wed, 27 September 2006 09:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've seen them used by people who wouldn't use a GOTO, so I guess people feel that it's somehow more acceptable - after all, it doesn't have the word 'GOTO' in it, so it can't be all bad. Cool

What I have done in the past is to define an exception, and then Pragma it to a specific value, and use raise_application_error to attach an error message to the exception - we're still jumping through the code, but at least we can tell where we came from and why.
Previous Topic: Create shcema authorization [not my username]
Next Topic: grouping data
Goto Forum:
  


Current Time: Wed Dec 07 16:14:15 CST 2016

Total time taken to generate the page: 0.08416 seconds