PL/SQL loop through cursor

Question

My problem isn't overly complicated, but I am a newbie to PL/SQL.

I need to make a selection from a COMPANIES table based on certain conditions. I then need to loop through these and convert some of the fields into a different format (I have created functions for this), and finally use this converted version to join to a reference table to get the score variable I need. So basically:

select id, total_empts, bank from COMPANIES where turnover > 100000 

loop through this selection

insert into MY_TABLE (select score from REF where conversion_func(MY_CURSOR.total_emps) =  REF.total_emps)

This is basically what I am looking to do. It's slightly more complicated but I'm just looking for the basics and how to approach it to get me started!

Answer 1

Here's the basic syntax for cursor loops in PL/SQL:

BEGIN

    FOR r_company IN (
        SELECT
            ID,
            total_emps,
            bank
        FROM
            companies
        WHERE
            turnover > 100000
    ) LOOP

        INSERT INTO 
            my_table
        SELECT
            score
        FROM
            ref_table
        WHERE
            ref.total_emps = conversion_func( r_company.total_emps )
        ;

    END LOOP;

END;
/

Answer 2

You don't need to use PL/SQL to do this:

insert into my_table
select score
  from ref r
  join companies c
    on r.total_emps on conversion_func(c.total_emps)
 where c.turnover > 100000

If you have to do this in a PL/SQL loop as asked, then I'd ensure that you do as little work as possible. I would, however, recommend bulk collect instead of the loop.

begin

   for xx in ( select conversion_func(total_emps) as tot_emp
                 from companies
                where turnover > 100000 ) loop

      insert into my_table
      select score
        from ref
       where total_emps = xx.tot_emp
             ;

   end loop;

end;
/

For either method you need one index on ref.total_emps and preferably one on companies.turnover