Enhancement of PLSQL Records Using as Procedural Parameter

Enhancement of PLSQL Records Using as Procedural Parameter. In this example, I am using INSERT, UPDATE & DELETE command with variable from PLSQL records.

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);                                

  PROCEDURE Modify_emp_data (emp_rec       IN    Emp_tab_type); 

  PROCEDURE Delete_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;

  PROCEDURE Modify_emp_data (emp_rec IN Emp_tab_type) IS

  Begin        
     execute immediate 'Alter trigger update_job_history disable';        
     execute immediate 'Alter table JOB_HISTORY disable constraint JHIST_EMP_FK';

     FOR i IN emp_rec.FIRST .. emp_rec.LAST

     LOOP

          Update employees

          set last_name=emp_rec(i).last_name,

              hire_date=emp_rec(i).hire_date,

              job_id   =emp_rec(i).job_id

          where employee_id=emp_rec(i).employee_id;                   

     END LOOP;         
     execute immediate 'Alter trigger update_job_history enable';
     execute immediate 'Alter table JOB_HISTORY enable constraint JHIST_EMP_FK';  

  Exception

    when others then null;     

  End;

 

  PROCEDURE Delete_emp_data (emp_rec IN Emp_tab_type) IS

  Begin

    --Disable your trigger & constraint if required.

     execute immediate 'Alter trigger update_job_history disable';        

     execute immediate 'Alter table JOB_HISTORY disable constraint JHIST_EMP_FK';

     FOR i IN emp_rec.FIRST .. emp_rec.LAST

     LOOP

         delete from employees

         where employee_id= emp_rec(i).employee_id;

     END LOOP;        

     execute immediate 'Alter trigger update_job_history enable';

     execute immediate 'Alter table JOB_HISTORY enable constraint JHIST_EMP_FK';

     --Enabling disable trigger & constraint.

  Exception

    when others then null;     

  End; 

END Emp_access_data;

*************************************************************

Insert multiple records using PLSQL Records or Collections:

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 SQL Output:

********************************************************************

--Update using PLSQL Records or Collections

declare

  l_rec    Emp_access_data.Emp_rec_type;

  l_array  Emp_access_data.Emp_tab_type;

begin

  l_rec.Employee_id:=999;

  l_rec.Last_Name:='Rumon Ahmad';

  l_rec.Hire_Date:='01-Dec-2005';

  l_rec.Job_Id:='SA_REP';

  l_array(1):=l_rec;   

  Emp_access_data.Modify_emp_data(emp_rec => l_array);  

commit;

exception

  when others then dbms_output.put_line(sqlerrm);

end; 

--See SQL Output:

********************************************************************

--Delete using PLSQL Records or Collections

declare

  l_rec    Emp_access_data.Emp_rec_type;

  l_array  Emp_access_data.Emp_tab_type;

begin

  l_rec.Employee_id:=999;

  l_rec.Last_Name:=null;

  l_rec.Hire_Date:=null;

  l_rec.Job_Id:=null;

  l_array(1):=l_rec;     

  Emp_access_data.Delete_emp_data(emp_rec => l_array);  

commit;

exception

  when others then dbms_output.put_line(sqlerrm);

end; 

--See SQL Output:

******************************************************************