Conditional execution of loop

Question

I have the following plsql block

for holder in (
     select pm.product_holder
           , cast(
                collect(
                   product_table(pm.product_no,pm.product_catalogue)
                   order by pm.product_catalogue
                          , pm.product_no
                ) as t_prod_cat_no_table
             ) product_cats_nos 
        from product_master pm
       group by pm.product_holder
       order by pm.product_holder
   ) loop
      test_proc(         
        holder.product_holder,
        holder.product_cats_nos
      );
   end loop;

In the above sql, if any one of the column is null, I wouldn't like to execute loop, it should terminate execution.

Columns are product_holder,product_cats_nos

How could I achieve this?

Answer 1

You should be able to do this like that:

loop
  if holder.product_holder   is null or
     holder.product_cats_nos is null 
  then
    exit;
  end if; 

  test_proc(         
    holder.product_holder,
    holder.product_cats_nos
  );
end loop;

Exit will break the loop. For more information look up the docs: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/exit_statement.htm

Another approach would be to make your test_proc procedure null proof, but then again I don't really know what you want to do ;)