Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

if -else condition in cursor

856037Jun 9 2011 — edited Jun 9 2011
Hi,

I want to write a if-else condition in cursor.i.e.
cursor c1 is
if par=1 then
select * from tab1;
else
select * from tab2;

can anyone help me on this?

Edited by: user12288167 on Jun 8, 2011 11:49 PM
This post has been answered by Mahir M. Quluzade on Jun 9 2011
Jump to Answer

Comments

Twinkle
hi,

Try coding. If error comes let us know.

Twinkle
LPS
Use parametrize cursor by passing parameter value in the var condition 1 = 1 and 2 = 2.
Mahir M. Quluzade
Answer
You can not use IF-else in cursor .
You can use

REF CURSOR, and in PL/SQL you open with for you candition .
like this 
 DECLARE 
  CUR REF CURSOR ;

  BEGIN 
   IF () THEN 
     OPEN CUR IS 'SELECT * FROM TAB1'; 
   ELSE 
     OPEN CUR IS 'SELECT * FORM TAB2';
 END IF ; 
  END;
Marked as Answer by 856037 · Sep 27 2020
856037
hi LPS..
can u elaborate on this?
and mahir,
let me try ur solution

Edited by: user12288167 on Jun 9, 2011 12:07 AM

Edited by: user12288167 on Jun 9, 2011 12:08 AM
LPS
declare

cursor c1(PV_IP number) is
select * from tab1 and 1 = PV_IP;

cursor c2(PV_IP number) is
select * from tab1 and 2 = PV_IP;

begin

open c1(1);

open c2(2)
{ code}
end;
1 person found this helpful
867688
Declare

TYPE cv_typ IS REF CURSOR;
cv cv_typ;

Begin
If(condition1 is TRUE) then
open cv FOR
'Select * from table_name1';
EXIT WHEN cv%NOTFOUND;
ELSE
open cv FOR
'Select * from table_name2';
EXIT WHEN cv%NOTFOUND;

End If;

CLOSE cv;
END;
1 person found this helpful
856037
thank u guys..
its done :)
BluShadow
Another alternative would be...
cursor c1 is
  select * from tab1 where par = 1
  union all
  select * from tab2 where par != 1;
This of course assumes that the columns from both tables are the same datatypes etc. so the union works.
If not, then the idea of writing a single procedure for two disperate queries is wrong and just poor design.
1 person found this helpful
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 7 2011
Added on Jun 9 2011
8 comments
21,277 views