Select Bulk Collect into Oracle Example
In the previous blog post I have given the example to use Oracle Bulk Collect Using Cursor Rowtype Type Object. Which first creates the cursor then type object of cursor type and then open the cursor, fetching into bulk collect and then finally process them using Forall. But sometimes you may be require to process some data for just one time with a temporary anonymous PLSQL block and you don’t want to write too much code to perform that task. For this kind of scenarios you can use Select Bulk Collect into option to perform any task quickly without wasting too much time to write code.
Below is the example of PLSQL block which will take the records from EMP table and update the BONUS table and also prints on the screen.
SET SEVEROUTPUT ON;<br />DECLARE<br /> TYPE t_emp IS TABLE OF emp%ROWTYPE;<br /><br /> e_rec t_emp;<br />BEGIN<br /> SELECT *<br /> BULK COLLECT INTO e_rec<br /> FROM emp;<br /><br />FORALL i IN e_rec.FIRST .. e_rec.LAST<br /> update bonus set amount = e_rec(i).sal * 15 / 100<br /> where empno = e_rec(i).empno;<br /><br />Commit;<br /> <br /> FOR i IN e_rec.FIRST .. e_rec.LAST<br /> LOOP<br /> DBMS_OUTPUT.<br /> put_line (<br /> 'Bonus Updated For Employee: '<br /> || e_rec (i).ename<br /> || ' '<br /> || 'Bonus: '<br /> || e_rec(i).sal * 15 / 100);<br /> END LOOP;<br />END;