PL / SQL loop through cursor

Question

My problem is not too complicated, but I am new to PL / SQL.

I need to make a selection from the COMPANIES table based on certain conditions. Then I need to loop and convert some fields to a different format (I created a function for this) and finally use this converted version to join the lookup table to get the evaluation variable that I need. So basically:

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

make this choice

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

This is basically what I'm looking for. It's a little trickier, but I'm just looking for the basics and how to approach it to get me started!

Answer 1

Here is 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 do not need to use PL / SQL for 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 need to do this in the PL / SQL loop as specified, then I guarantee that you will do as little work as possible. However, I would recommend assembly instead of looping.

 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 any method, you need one index at ref.total_emps and preferably one at companies.turnover