Implicit Cursor - Exercise

Contact Us or call 1-877-932-8228
Implicit Cursor - Exercise

Implicit Cursor

Duration: 5 to 10 minutes.
  1. Modify the exercise script below so that the name (first name and last name separated by a space), salary, and hire date are retrieved from the employee table (use employee_id = 100 for the initial test in step 3).
  2. Output the results in a sentence like the following: <first name last name> was hired <hire date> and will be paid <salary amount>.
  3. Execute your code.
  4. On a new line after the BEGIN statement, add the code to retrieve the lowest and highest employee ids.
  5. Modify your code so that you loop through each person in the employees table, retrieving and displaying each employee's information. Use the values you just retrieved in step 4 as your start and end values for the loop.
  6. Add an exception handler that will display any unexpected error codes.
  7. Execute your code.

Code Sample:

Use-of-SQL-in-PLSQL/Exercises/name_salary_date_query.sql
DECLARE
  v_name        VARCHAR2(46);
  v_salary      employees.SALARY%type;
  v_date        employees.HIRE_DATE%type;
  
  -- Add code here for steps 3-5.
BEGIN
  
  -- Add code here for steps 1-2 and then change it for steps 3-5.
  
  dbms_output.put_line(v_name || ' was hired ' || 
                       to_char(v_date, 'mm/dd/yyyy') || 
                       ' and will be paid ' ||
                       to_char(v_salary, '$999,999.00') || '.');
                       
  -- Add code here for step 6.
END;

Code Explanation

Solution:

Use-of-SQL-in-PLSQL/Solutions/name_salary_date_query_solution.sql
DECLARE
  v_name        VARCHAR2(46);
  v_salary      employees.salary%type;
  v_date        employees.hire_date%type;
  v_min_empid   employees.employee_id%type;
  v_max_empid   employees.employee_id%type;
BEGIN

  SELECT min(employee_id), max(employee_id)
  INTO v_min_empid, v_max_empid
  FROM employees;
  
  -- The only problem with this solution is
  -- if the employee_id values in the range
  -- are missing.
  -- We'll see how to control that issue in
  -- the next chapter.
  -- Or we can use a completely different
  -- solution which we'll see in the next
  -- section of this chapter.
  FOR i IN v_min_empid..v_max_empid LOOP
  
    SELECT first_name || ' ' || last_name,
            salary, hire_date  
    INTO v_name, v_salary, v_date
    FROM employees 
    WHERE EMPLOYEE_ID = i;
  
    dbms_output.put_line(v_name || ' was hired ' ||
                         to_char(v_date, 'mm/dd/yyyy') ||
                         ' and will be paid ' ||
                         trim(to_char(v_salary, '$999,999.00')) ||
                         '.');
  END LOOP;
  
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Unexpected error # ' ||
                         sqlcode);
END;

Code Explanation

Next