EMPLOYEES_PER_COUNTRY

/*
Gegevens van de werknemers per land en per locatie.
*/
CREATE OR REPLACE PROCEDURE EMPLOYEES_PER_COUNTRY
(
  P_REGION_ID IN REGIONS.REGION_ID%TYPE
) AS
  -- Cursor going through every country
  CURSOR country_cursor IS  SELECT * FROM HR.countries;
  country_row country_cursor%ROWTYPE;

  -- Cursor to loop through every country's locations
  CURSOR location_cursor(country country_cursor%ROWTYPE) IS
  SELECT * FROM HR.locations
  WHERE country_id = country.country_id;
  location_row location_cursor%ROWTYPE;

  -- Loop through every employee of a location
  CURSOR emp_cursor(loc location_cursor%ROWTYPE) IS
  SELECT e.first_name, e.last_name, d.department_name FROM HR.employees e
  INNER JOIN departments d ON d.department_id = e.department_id
  WHERE d.location_id = loc.location_id;
  emp_row emp_cursor%ROWTYPE;

  -- Counter to count our employees per department
  counter NUMBER;
BEGIN
--Outer loop : every country
FOR country_row IN country_cursor
LOOP
  --Print the country name and it's code
  DBMS_OUTPUT.put_line('Country= '||country_row.country_id || ' ' || country_row.country_name);
  -- Inner loop: loop thru every countries location
  FOR location_row IN location_cursor(country_row)
  LOOP
    --Print the location information (postal_code, city and state)
    DBMS_OUTPUT.put_line('  Location= '||location_row.postal_code||' '||location_row.city||' '||location_row.state_province);
    -- Final loop: go thru every employee of a location
    counter := 0;
    /* IF YOU WANT TO SAY THAT THERE AREN'T EMPLOYEES FOR A LOCATION DO THIS ...
    *   select count(*) INTO counter FROM HR.employees e
    *   INNER JOIN departments d ON d.department_id = e.department_id
    *   WHERE d.location_id = location_row.location_id;
    *   if counter > 0 then
        *       for emp_row in emp_cursor(location_row) loop
        *           dbms_output.put_line('   Employee= '||emp_row.first_name||' '||emp_row.last_name||'
    *               Department:'||emp_row.department_name);
        *       end loop;
        *       dbms_output.put_line('    --Total number of employees in '||location_row.city||' = '||counter);
    *   else
        *    dbms_output.put_line('    --No employees in our data');
    *   end if;
    *   end loop;
    */
    FOR emp_row IN emp_cursor(location_row) LOOP
      counter := counter +1;
     DBMS_OUTPUT.put_line('   Employee= '||emp_row.first_name||' '||emp_row.last_name||'  Department:'||emp_row.department_name);
    END LOOP;
    DBMS_OUTPUT.put_line('    --Total number of employees in '||location_row.city||' = '||counter);
  END LOOP;
END LOOP;
END EMPLOYEES_PER_COUNTRY;
/**** Test program ****/
BEGIN
employees_per_country(1);
END;