CODEWITHSUNDEEP
sqloracle-databaseplsql
Jacob
Jacob

Reputation: 14731

Conditional execution of loop

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?

Upvotes: 0

Views: 67

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 1

Matze
Matze

Reputation: 325

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

Upvotes: 1

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

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.

Upvotes: 1

Related Questions