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?

"If any one of the columns is NULL" - define "any columns". Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 1, 2015 at 15:34
Avoid loop at the first instance. Row by row, aka, slow by slow. — Lalit Kumar B
– Lalit Kumar B, Commented Mar 1, 2015 at 15:50

user272735 · Accepted Answer · 2015-03-01 19:59:50Z

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 ;)

edited Mar 1, 2015 at 19:59

user272735

10.6k9 gold badges72 silver badges100 bronze badges

answered Mar 1, 2015 at 16:14

Matze

3251 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matze Over a year ago

Test your input for null and handle it there. I do not know what the desired behavior would be if there is a null, but by moving the checks inside the procedure you won't have to break the loop. So you can iterate over all your elements.

Jacob Over a year ago

Thanks for insight, appreciated.

Alessandro Rossi · Accepted Answer · 2015-03-01 18:04:44Z

1

product_cats_nos can't be null (it can contain nulls or be empty, but that's a different concept: the collection is not null by the time it is initialized as the first step of collect aggregation function) and you can only have the last one(you're grouping by that field) occurrence of pm.product_holder (because nulls are last as default when you're using order by) to be null. Given this you can simply modify the query adding a where clause on product_holder is not null.

answered Mar 1, 2015 at 18:04

Alessandro Rossi

2,44617 silver badges24 bronze badges

Comments

halfer · Accepted Answer · 2024-01-03 20:07:23Z

It appears that PRODUCT_NO is a column on PRODUCT_MASTER, while PRODUCT_CAT_NOS is an object produced by the query. Given this, it seems that to test the NULL status of these two columns/objects you'd want to test the first in the WHERE clause, and the second in the HAVING clause:

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
       WHERE pm.PRODUCT_HOLDER IS NOT NULL     -- Added
       group by pm.product_holder
       HAVING PRODUCT_CAT_NOS IS NOT NULL  -- Added
       order by pm.product_holder
   ) loop
      test_proc(         
        holder.product_holder,
        holder.product_cats_nos
      );
   end loop;

On the whole, however, I think it's clearer to use an IF statement inside the loop - but it's your choice as to which method you prefer.

Collectives™ on Stack Overflow

Conditional execution of loop

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related