Cursores

---------------------------------------------------------------------------------------------------------

CURSOR 1.0

set serveroutput on

declare

cursor miPrimerCursor is 

  select EMPLOYEE_ID, FIRST_NAME from employees;

vnombre employees.first_name%Type;

vid employees.employee_id%Type;

begin

open miPrimerCursor;

loop

  fetch miPrimerCursor into vid,vnombre;

  exit when miPrimerCursor%NOTFOUND;

  dbms_output.put_line('ID: '||vid||' - ' ||'Nombre: '||vnombre);

end loop;

close miPrimerCursor;

end;

---------------------------------------------------------------------------------------------------------

CURSOR 2.0

set serveroutput on

declare

cursor miPrimerCursor is 

  select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date desc;

vnombre employees.first_name%Type;

vid employees.employee_id%Type;

vFechaContrato employees.HIRE_DATE%Type;

antiguedad integer;

begin

open miPrimerCursor;

loop

  fetch miPrimerCursor into vid,vnombre,vFechaContrato;

  exit when miPrimerCursor%NOTFOUND;

  antiguedad := round((sysdate - vFechaContrato)/365);

  if(antiguedad = 12) then

  dbms_output.put_line(antiguedad||' * ' || ' ID: '||vid||' - ' ||'Nombre: '||vnombre);

  end if;

end loop;

close miPrimerCursor;

end;

---------------------------------------------------------------------------------------------------------

DOS CURSORES

--select TABLE_NAME from all_tables where owner= 'HR'; --obtener tablas

--select OWNER,TABLE_NAME,column_name from all_tab_columns where TABLE_NAME = 'JOBS' AND owner = 'HR'; -- obtener columnas

set serveroutput on -- para que se vean los dbms :D 

declare

nomTabla all_tables.table_name%Type;

nomColumna all_tab_columns.column_name%Type;

cursor CursorTabla is select TABLE_NAME from all_tables where owner ='HR';

cursor CursorColumna is select column_name from all_tab_columns where owner ='HR' ;

BEGIN

open CursorTabla;

loop

fetch CursorTabla into nomTabla;

  exit when CursorTabla%NOTFOUND;

  open CursorColumna;

  loop

  fetch CursorColumna into nomColumna;

    exit when CursorColumna%NOTFOUND;

    dbms_output.put_line(nomTabla || ' - ' || nomColumna);

  end loop;

  close CursorColumna;

end loop;

close CursorTabla;

end;