Cursor with where condition

Question

I have a cursor which contains bills of all the persons grouped by their family name. Now, I want to check dynamically that which family name has outstanding bill against it. For eg. there is adams and perry family and many more. The cursor has values for all the members of the families with one column being the family name. Now I check for the outstanding amount, I find their is one in adams family. Now, I want my cursor to display all the members of adams family so I can distribute the bill to the family's other members.

CURSOR individual_cur is
select name,family_name, bill from table1;

CURSOR family_cur is
    select family_name from table1;

    for temp in family_cur loop
    select sum(bill) into extra_bill where family_name is temp.family_name;

    if extra_bill <>0 then

    -- Now here I want 
    for temp1 in individual_cur loop *where family_name is temp.family_name*

    -- How to do this.
    end loop;

Answer 1

Here's an example

declare
   -- add family name as parameter
   -- and use in where clause
   cursor individual_cur(p_family_name table1.family_name%type) is
      select name
            ,family_name
            ,bill
        from table1
       where family_name = p_family_name;

   --group by family 
   -- and filter on sum(bill)<>0
   cursor family_cur is
      select family_name
        from table1
       group by family_name
       having sum(bill)<>0;
begin

   for temp in family_cur
   loop
        -- select individuals from this family
         for temp1 in individual_cur(temp.family_name)
         loop

           -- do something
        end loop;
   end loop;
end;

Or in just one query

 select name
       ,family_name
       ,bill
   from table1
  where family_name in (select family_name
                        from table1
                        group by family_name
                        having sum(bill)<>0)