Pages

Showing posts with label PLSQL Records. Show all posts
Showing posts with label PLSQL Records. Show all posts

Thursday, March 12, 2015

Enhancement of PLSQL Records Using as Procedural Parameter

paid surveys


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:
******************************************************************

paid surveys