CREATE OR REPLACE PROCEDURE pr_comp_emp

  ( p_employee_id_1     employees.employee_id%TYPE
  , p_employee_id_2     employees.employee_id%TYPE) IS

–Variables to stores the FIRST record values
  v_employee_id_1             employees.employee_id%TYPE;
  v_first_name_1                employees.first_name%TYPE;
  v_last_name_1                 employees.last_name%TYPE;
  v_email_1                        employees.email%TYPE;
  v_phone_number_1         employees.phone_number%TYPE;
  v_hire_date_1                  employees.hire_date%TYPE;
  v_job_id_1                       employees.job_id%TYPE;
  v_salary_1                       employees.salary%TYPE;
  v_commission_pct_1      employees.commission_pct%TYPE;
  v_manager_id_1              employees.manager_id%TYPE;
  v_department_id_1          employees.department_id%TYPE;

–Variables to stores the SECOND record values

  v_employee_id_2             employees.employee_id%TYPE;
  v_first_name_2                employees.first_name%TYPE;
  v_last_name_2                employees.last_name%TYPE;
  v_email_2                        employees.email%TYPE;
  v_phone_number_2         employees.phone_number%TYPE;
  v_hire_date_2                  employees.hire_date%TYPE;
  v_job_id_2                       employees.job_id%TYPE;
  v_salary_2                        employees.salary%TYPE;
  v_commission_pct_2       employees.commission_pct%TYPE;
  v_manager_id_2              employees.manager_id%TYPE;
  v_department_id_2          employees.department_id%TYPE;

BEGIN

  –Fetch the first record
  SELECT *
      INTO v_employee_id_1
             , v_first_name_1
             , v_last_name_1
             , v_email_1
             , v_phone_number_1
             , v_hire_date_1
             , v_job_id_1
             , v_salary_1
             , v_commission_pct_1
             , v_manager_id_1
             , v_department_id_1
    FROM employees
   WHERE employee_id = p_employee_id_1;

  –Fetch the Second record
  SELECT *
      INTO v_employee_id_2
             , v_first_name_2
             , v_last_name_2
             , v_email_2
             , v_phone_number_2
             , v_hire_date_2
             , v_job_id_2
             , v_salary_2
             , v_commission_pct_2
             , v_manager_id_2
             , v_department_id_2
    FROM employees
   WHERE employee_id = p_employee_id_1;

–More statements will go here

EXCEPTION
  WHEN OTHERS THEN
      –Handle the exception
      DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);

END;

CREATE OR REPLACE PROCEDURE pr_comp_emp
  ( p_employee_id_1 employees.employee_id%TYPE
  , p_employee_id_2 employees.employee_id%TYPE) IS

–Instead of individual variables, lets declare a record type
v_emp_rec_1 employees%ROWTYPE;

v_emp_rec_2 employees%ROWTYPE;

BEGIN
  –Fetch the first record   SELECT * INTO v_emp_rec_1
    FROM employees

   WHERE employee_id = p_employee_id_1;

  –Fetch the second record 
  SELECT * INTO v_emp_rec_2

    FROM employees
   WHERE employee_id = p_employee_id_2;

–More statements will go here…

EXCEPTION
    WHEN OTHERS THEN
        –Handle the exception
        DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);

END;

CREATE OR REPLACE PROCEDURE pr_comp_emp
  ( p_employee_id_1     employees.employee_id%TYPE
  , p_employee_id_2     employees.employee_id%TYPE) IS

  –Variables to store FIRST record

  v_employee_id_1              employees.employee_id%TYPE;
  v_first_name_1                 employees.first_name%TYPE;
  v_last_name_1                  employees.last_name%TYPE;
  v_salary_1                         employees.salary%TYPE;
  v_department_id_1           departments.department_id%TYPE;
  v_department_name_1      departments.department_name%TYPE;

  –Variables to store SECOND record
  v_employee_id_2             employees.employee_id%TYPE;
  v_first_name_2                employees.first_name%TYPE;
  v_last_name_2                employees.last_name%TYPE;
  v_salary_2                       employees.salary%TYPE;
  v_department_id_2          departments.department_id%TYPE;
    v_department_name_2  departments.department_name%TYPE; 
BEGIN

   
SELECT employee_id
          , first_name
          , last_name
          , salary
          , d.department_id
          , d.department_name
   INTO v_employee_id_1
          , v_first_name_1
          , v_last_name_1
          , v_salary_1
          , v_department_id_1
          , v_department_name_1
  FROM employees e, departments d
WHERE e.department_id = d.department_id
   AND employee_id = p_employee_id_1;

   
SELECT employee_id
          , first_name
          , last_name
          , salary
          , d.department_id
          , d.department_name
   INTO v_employee_id_2
          , v_first_name_2
          , v_last_name_2
          , v_salary_2
          , v_department_id_2
          , v_department_name_2
  FROM employees e, departments d
WHERE e.department_id = d.department_id
   AND employee_id = p_employee_id_2;

  –More statements will go here…

  EXCEPTION
    WHEN OTHERS THEN
      –Handle the exception
      DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);
END;

CREATE OR REPLACE PROCEDURE pr_comp_emp
  ( p_employee_id_1     employees.employee_id%TYPE
  , p_employee_id_2     employees.employee_id%TYPE) IS

–Declare a cursor with the same select statement that is in use

  CURSOR emp_det_cur IS
    SELECT employee_id
             , first_name
             , last_name
             , salary
             , d.department_id
             , d.department_name
      FROM employees e, departments d
    WHERE e.department_id = d.department_id ;

  –Variables to store FIRST record
  v_emp_det_1   emp_det_cur%ROWTYPE;
  
  –Variables to store SECOND record
  v_emp_det_2   emp_det_cur%ROWTYPE;

BEGIN

  SELECT employee_id
            , first_name
            , last_name
            , salary
            , d.department_id
            , d.department_name
   INTO v_emp_det_1
    FROM employees e, departments d
  WHERE e.department_id = d.department_id
      AND employee_id = p_employee_id_1;

  SELECT employee_id
            , first_name
            , last_name
            , salary
            , d.department_id
            , d.department_name
   INTO v_emp_det_2
    FROM employees e, departments d
  WHERE e.department_id = d.department_id
      AND employee_id = p_employee_id_2;

  –More statements will go here…

  EXCEPTION
    WHEN OTHERS THEN
      –Handle the exception
      DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);
END;

CREATE OR REPLACE
PACKAGE pkg_common AS

  CURSOR emp_det_cur IS
      SELECT employee_id
               , first_name
               , last_name
               , salary
               , d.department_id
               , d.department_name
       FROM employees e, departments d
    WHERE e.department_id = d.department_id ;

END pkg_common;

CREATE OR REPLACE

PROCEDURE pr_comp_emp
  ( p_employee_id_1     employees.employee_id%TYPE
  , p_employee_id_2     employees.employee_id%TYPE) IS 

  –Variables to store FIRST record
   v_emp_det_1   pkg_common.emp_det_cur%ROWTYPE;
  
  –Variables to store SECOND record

   v_emp_det_2   pkg_common.emp_det_cur%ROWTYPE;

 
BEGIN
–Code will go here

NULL;

END;