Employee Report by State/Province - Exercise

Contact Us or call 1-877-932-8228
Employee Report by State/Province - Exercise

Employee Report by State/Province

Duration: 15 to 20 minutes.
  1. Create a stored procedure named employee_report_by_state_prov.
  2. Modify the procedure so that it takes an input parameter named v_state of type varchar2.
  3. Create a cursor that takes a cursor variable called state of type varchar2. The query should select all columns from the EMP_DETAILS_VIEW and return rows that have a STATE_PROVINCE column that matches the state parameter. Sort the results by last name followed by first name.
  4. In the body of the procedure, open the cursor using the parameter passed in through v_state.
  5. Loop through the results and output the first name, last name and job id.
  6. Test the procedure using Texas, Washington and Bavaria.


create or replace procedure employee_report_by_state_prov
( v_state in varchar2) 
  CURSOR crs (state varchar2) IS
     select * 
     from emp_details_view 
     where state_province = state
     order by last_name, first_name;
  rec emp_details_view%rowtype;   
 open crs (v_state);
   fetch crs into rec;
   exit when crs%notfound;
   dbms_output.put_line(rec.first_name || ' ' || rec.last_name
                        || ' ' || rec.job_id);
  end loop;
end employee_report_by_state_prov;

-- To test execute the following:

Code Explanation