Oracle cursor example

tools.sql

-- Remember to `set serveroutput on` to see the output.

create or replace package hr.tools is
  type salary_cursor_record_type is record (salary number(8,2), department_name varchar2(30));
  type salary_cursor_type is ref cursor return salary_cursor_record_type;

  procedure show_augmented_salary;
  procedure show_augmented_salary_helper (salary_cursor in out salary_cursor_type);
end tools;
/

create or replace package body hr.tools is

  procedure show_augmented_salary is
    salary number(8,2);
    department_name varchar2(30);
    salary_cursor salary_cursor_type;
  begin
    show_augmented_salary_helper(salary_cursor);
    loop
      fetch salary_cursor into salary, department_name;
      exit when salary_cursor%notfound;
      department_name := nvl(department_name, 'NULL');
      dbms_output.put_line(rpad(department_name || ' ', 34, '.') || ' ' || to_char(salary));
    end loop;
    close salary_cursor;
  end;

  procedure show_augmented_salary_helper (salary_cursor in out salary_cursor_type) is
  begin
    open salary_cursor for select
      e.salary * 1.10 as salary,
      d.department_name
    from hr.employees e left join hr.departments d on e.department_id = d.department_id;
  end;

end tools;
/