Cursores Explicitos

--CICLO LOOP

set serveroutput on;

declare

Cursor cursorPaises(pregion_id number) is

Select COUNTRY_ID, COUNTRY_NAME, REGION_ID from countries

where REGION_ID = pregion_id;

registros cursorPaises%rowtype;

begin

open cursorPaises(to_number('&REGION'));

loop

  fetch cursorPaises into registros;

  exit when cursorPaises%NOTFOUND;

  if registros.REGION_ID = 1 then

    dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                        ||registros.COUNTRY_ID||' esta de la region de Europa');

  elsif registros.REGION_ID = 2 then

    dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                        ||registros.COUNTRY_ID||' esta de la region de America');

  elsif registros.REGION_ID = 3 then

  dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                      ||registros.COUNTRY_ID||' esta de la region de Asia');

  else

  dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                      ||registros.COUNTRY_ID||' esta de la region de Africa');

end if;

end loop;

close cursorPaises;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

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

--CICLO WHILE

set serveroutput on;

declare

Cursor Cursor_Departamentos(ploc_id number) is

select DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID

from departments

where LOCATION_ID = ploc_id;

registros Cursor_departamentos%rowtype;

begin

open Cursor_departamentos('&IngreseLocalizacionDeDepartamento');

fetch Cursor_departamentos into registros;

while Cursor_departamentos%FOUND

loop

dbms_output.put_line('ID '||registros.DEPARTMENT_ID||' Nombre '

                    ||registros.DEPARTMENT_NAME||' MANAGER_ID '

                    ||registros.MANAGER_ID||' LOCATION_ID '

                    ||registros.LOCATION_ID);

fetch Cursor_departamentos into registros;

end loop;

close Cursor_departamentos;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

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

--CICLO FOR

set serveroutput on;

declare

Cursor CursorEmpleados(pman_id number) is

select FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID

from employees

where MANAGER_ID = pman_id;

begin

for registro in CursorEmpleados('&IngreseManagerID') loop

dbms_output.put_line('Nombre '||registro.FIRST_NAME||' Apellido '

                    ||registro.LAST_NAME||' Salario '

                    ||registro.SALARY);

end loop;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

--CICLO LOOP

set serveroutput on;

declare

Cursor cursorPaises(pregion_id number) is

Select COUNTRY_ID, COUNTRY_NAME, REGION_ID from countries

where REGION_ID = pregion_id;

registros cursorPaises%rowtype;

begin

open cursorPaises(to_number('&REGION'));

loop

  fetch cursorPaises into registros;

  exit when cursorPaises%NOTFOUND;

  if registros.REGION_ID = 1 then

    dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                        ||registros.COUNTRY_ID||' esta de la region de Europa');

  elsif registros.REGION_ID = 2 then

    dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                        ||registros.COUNTRY_ID||' esta de la region de America');

  elsif registros.REGION_ID = 3 then

  dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                      ||registros.COUNTRY_ID||' esta de la region de Asia');

  else

  dbms_output.put_line('El pais '||registros.COUNTRY_NAME||' ID '

                      ||registros.COUNTRY_ID||' esta de la region de Africa');

end if;

end loop;

close cursorPaises;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

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

--CICLO WHILE

set serveroutput on;

declare

Cursor Cursor_Departamentos(ploc_id number) is

select DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID

from departments

where LOCATION_ID = ploc_id;

registros Cursor_departamentos%rowtype;

begin

open Cursor_departamentos('&IngreseLocalizacionDeDepartamento');

fetch Cursor_departamentos into registros;

while Cursor_departamentos%FOUND

loop

dbms_output.put_line('ID '||registros.DEPARTMENT_ID||' Nombre '

                    ||registros.DEPARTMENT_NAME||' MANAGER_ID '

                    ||registros.MANAGER_ID||' LOCATION_ID '

                    ||registros.LOCATION_ID);

fetch Cursor_departamentos into registros;

end loop;

close Cursor_departamentos;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

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

--CICLO FOR

set serveroutput on;

declare

Cursor CursorEmpleados(pman_id number) is

select FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID

from employees

where MANAGER_ID = pman_id;

begin

for registro in CursorEmpleados('&IngreseManagerID') loop

dbms_output.put_line('Nombre '||registro.FIRST_NAME||' Apellido '

                    ||registro.LAST_NAME||' Salario '

                    ||registro.SALARY);

end loop;

exception

  when VALUE_ERROR then

    dbms_output.put_line('El valor ingresado no es valido');

end;

Trabajaron en esta tarea Miguel Sepulveda Obreque
                                       Gerald Mauricio Caris Caris
                                        Enrique Antonio Mimó Lueiza