Exemple for fetching cursor in Oracle

fetching_cursor.sql

declare
  cursor get_states_and_cities(pId_country number) is
  select c.nm_country,
         s.nm_state,
         ct.nm_city
    from countries c,
         states s,
         cities ct
   where ct.id_state = s.id_state
     and s.id_country = c.id_country
     and c.id_country = pId_country;
     
  type location_type is record(
    nm_country countries.nm_country%type,
    nm_state states.nm_state%type,
    nm_city cities.nm_city%type
  );
  
  location location_type;
begin
  open get_states_and_cities(1685);
  loop
    fetch get_states_and_cities into location;
    exit when get_states_and_cities%notfound;
    dbms_output.put_line(location.nm_country || ' - ' || location.nm_state || ' - ' || location.nm_city);
  end loop;
  close get_states_and_cities;
end;