Explicit Cursor - Exercise

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

Explicit Cursor

Duration: 10 to 15 minutes.
  1. Modify the script you created using an implicit cursor for the last exercise so that employee name (first name and last name separated by a space), salary and hire date for all employees are retrieved from the employee table (sort by last name and first name).

Solution:

Use-of-SQL-in-PLSQL/Solutions/name_salary_date_cursor_solution.sql
DECLARE
  v_name   VARCHAR2(46);
  v_salary NUMBER;
  v_date   DATE;
  CURSOR c1 is  
      SELECT first_name || ' ' || last_name,
             salary, hire_date  
      FROM employees
      ORDER BY last_name, first_name;
BEGIN
  
  OPEN c1;
  LOOP
    FETCH c1 INTO v_name, v_salary, v_date;
    EXIT WHEN c1%NOTFOUND;
    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;
  close c1;
END;

Code Explanation

Next