Oracle PL SQL : Learn Bulk Collect and FOR ALL | Bulk Collect and LIMIT With Example

 1. Looping concept            -- .15seconds...
2. Bulk Collect               -- 0seconds..
3. Bulk Collect using LIMIT   --0seconds..
4. Direct Insert              --01seconds..

https://www.youtube.com/watch?v=PM6fLDgSl7I
alter session set current_schema=hr;
SET SERVEROUTPUT ON
select count(*) from EMPLOYEES ;
create table EMPLOYEES2 as select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES where 1=2;
select count(*) from employees2;
--(1)
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  l_err_count number;
  l_ename varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 loop 
  insert into EMPLOYEES2(EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID) values(i.EMPLOYEE_ID,i.LAST_NAME, i.DEPARTMENT_ID);
  end loop;
  commit;
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error:'||sqlerrm);
END;

--(2)
--Bulk Collect
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 
  loop 
  l_tp.extend;
  l_tp(l_tp.last).EMPLOYEE_ID   :=i.EMPLOYEE_ID;
  l_tp(l_tp.last).LAST_NAME     :=i.LAST_NAME;
  l_tp(l_tp.last).DEPARTMENT_ID :=i.DEPARTMENT_ID;
  end loop; 
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
  insert into EMPLOYEES2 values l_tp(i);
  commit;
  --
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;





--(3)
--Bulk Collect using LIMIT 
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype index by binary_integer;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  open c1;
  loop 
      fetch c1 bulk collect into l_tp limit 50000;
  exit when l_tp.COUNT=0;
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
      insert into EMPLOYEES2 values l_tp(i);
      commit;
  --
  end loop;
  close c1;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;




--(4)
--Direct Insert
DECLARE 
  l_start number default dbms_utility.get_time;
  BEGIN
      insert into EMPLOYEES2 (EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID)
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
      commit;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error :' ||sqlerrm);
END;