Cursor for For Loop

Question

How do I combine these 2 example cursors below so that they retrieve data for all 4 conditions: all, candy, cake, popcorn; in a for loop?

I have 3 session/global variables that can be empty string


    s_Candy   Varchar2(5);
    s_Cake    Varchar2(5);
    s_Popcorn Varchar2(5);



    Cursor My_All_Cur Is 
        ... Gets all values
        Select * From My_Table t Where t.State = 'A';



    Cursor My_Other_Cur Is
         ... Gets data by condition
         From My_Table t
         Where t.State = 'A'
           And (t.Candy_Lovers = s_Candy
           Or t.Cake_Lovers = s_Cake
           Or t.Popcorn_Lovers = s_Popcorn);



    For i In My_Unified_Cur Loop
      ... do stuff
    End Loop;

I posted my answer below. Don't know why I got down-voted. People need to be a bit more relaxed. — , Aug 10 '16 at 08:52
I certainly not downvoted you. I have no intentions to demotivate any techie here. But since someone did so am upvoting you so that you get some reputations. — XING, Aug 10 '16 at 09:06

score 1 · Answer 1 · 2016-08-10T08:54:50.003

The solution is to union all 2 cursors and condition them via

s_All_Id

which is also a global variable.


    s_Candy   Varchar2(5);
    s_Cake    Varchar2(5);
    s_Popcorn Varchar2(5);
    s_All_Id  Varchar2(1) := 'N';


    If s_Candy Is Null And s_Cake Is Null And s_Popcorn Is Null Then
      s_All_Id := 'Y';
    End If;


    Cursor My_All_Cur Is 
        Select * 
          From My_Table t 
          Where s_All_Id = 'Y'
            And t.State = 'A'
        Union All
        Select *
          From My_Table t
            Where s_All_Id = 'N'
              And t.State = 'A'
              And (t.Candy_Lovers = s_Candy
              Or t.Cake_Lovers = s_Cake
              Or t.Popcorn_Lovers = s_Popcorn);

Cursor for For Loop

1 Answers1