How to use PLSQL records as a procedural parameter & insert bulk records into database

Procedure parameter used as a PLSQL records or PLSQL collections or PLSQL table to insert bulk records into database table at one shot. I have inserted here two sample data as initialize variable. You can insert bulk records by using LOOP, CURSOR or SQL command etc.

Please connect HR schema (default schema of ORACLE) then compile below Emp_access_data package specification & package body accordingly.

Package Specification:

CREATE OR REPLACE PACKAGE Emp_access_data AS

  TYPE Emp_rec_type IS RECORD(

    Employee_id Employees.Employee_Id%type,

    Last_name   Employees.Last_Name%type,

    Hire_Date   Employees.Hire_Date%type,

    Job_id      Employees.Job_Id%type);

  TYPE Emp_tab_type IS TABLE OF Emp_rec_type INDEX BY BINARY_INTEGER;

  PROCEDURE Insert_emp_data(emp_rec IN Emp_tab_type);

END Emp_access_data;

 Package Body:

CREATE OR REPLACE PACKAGE BODY Emp_access_data AS

  PROCEDURE Insert_emp_data(emp_rec IN Emp_tab_type) IS

  Begin

    FOR i IN emp_rec.FIRST .. emp_rec.LAST LOOP

      insert into employees(employee_id, last_name, hire_date, job_id)

      values(emp_rec(i).employee_id,emp_rec(i).last_name,emp_rec(i).hire_date,emp_rec(i).job_id);

    END LOOP;

  Exception

    when others then null;

  End;

END Emp_access_data;

Afterwards, execute below anonymous PLSQL block.

Here,

l_rec     :: is employees record type which is already declared in package specification

l_array  :: is array of INDEX BY BINARY_INTEGER table & associated with employee record(l_rec).

declare

  l_rec   Emp_access_data.Emp_rec_type;

  l_array Emp_access_data.Emp_tab_type;

begin

  l_rec.Employee_id := 888;

  l_rec.Last_Name   := 'Tamzid';

  l_rec.Hire_Date   := '01-Jan-2001';

  l_rec.Job_Id      := 'SA_REP';

  l_array(1)        := l_rec;

  l_rec.Employee_id := 999;

  l_rec.Last_Name   := 'Rumon';

  l_rec.Hire_Date   := '01-Jun-2001';

  l_rec.Job_Id      := 'SA_REP';

  l_array(2)        := l_rec;

  Emp_access_data.Insert_emp_data(emp_rec => l_array);

  commit;

exception

  when others then

    dbms_output.put_line(sqlerrm);

end;

See the below your expected output after execution of the above code

select * from employees where employee_id in (999,888);

 

SQL Output: