Laboratorio#4- Registros(con y sin variables)-Cursores Avanzados

· Registros sin variables:

SET SERVEROUTPUT ON;

SET VERIFY OFF;

DECLARE

CURSOR cursorProvincias IS

SELECT d.department_name, d.department_id, d.manager_id, d.location_id, l.city, l.street_address, l.state_province

FROM departments d, locations l where l.location_id=d.location_id;

rProvincias cursorProvincias%ROWTYPE;

BEGIN

OPEN cursorProvincias;

LOOP

FETCH cursorProvincias INTO rProvincias;

EXIT WHEN cursorProvincias%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(rProvincias.department_name || ‘ , ’ || rProvincias.department_id || ‘ , ’ || rProvincias.manager_id|| ‘ , ’ || rProvincias.location_id|| rProvincias.city|| rProvincias.street_address||' , '||rProvincias.state_province);

END LOOP;

CLOSE cursorProvincias;

END;

· Registros con variables:

SET SERVEROUTPUT ON;

SET VERIFY OFF;

DECLARE

CURSOR cursEjemplo IS

SELECT d.department_name, d.department_id, d.manager_id, d.location_id, l.city, l.street_address, l.state_province

FROM departments d, locations l where l.location_id=d.location_id;

v_nombre departments.department_name%TYPE;

v_o departments.department_id%TYPE;

v_m departments.manager_id%TYPE;

v_b departments.location_id%TYPE;

v_poblacion locations.city%TYPE;

v_p locations.street_address%TYPE;

v_l locations.state_province%TYPE;

BEGIN

OPEN cursEjemplo;

LOOP

FETCH cursEjemplo INTO v_nombre, v_o, v_m, v_b, v_poblacion, v_p, v_l;

EXIT WHEN cursEjemplo %NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_nombre || ',' || v_o|| ',' || v_m || ',' ||v_b|| ',' ||v_poblacion|| ',' ||v_p|| ',' || v_l );

END LOOP;

CLOSE cursEjemplo;

END;

· Cursores Avanzados

DECLARE

cursor dept_cursor is

select department_id, department_name

from departments

order by department_id;

cursor emp_cursor (v_deptno number) is

select first_name, job_id, hire_date, salary

from employees where department_id=v_deptno;

v_curr_deptno departments.department_id%type;

v_curr_dname departments.department_name%type;

v_ename employees.first_name%type;

v_job employees.job_id%type;

v_hiredate employees.hire_date%type;

v_sal employees.salary%type;

v_line varchar2(100);

begin

v_line:=' ';

open dept_cursor;

loop

fetch dept_cursor into v_curr_deptno, v_curr_dname;

exit when dept_cursor%notfound;

dbms_output.put_line('numero depar: '||v_curr_deptno|| ' nombre departamento'||' '||v_curr_dname );

dbms_output.put_line(v_line);

if emp_cursor%isopen then

close emp_cursor;

end if;

open emp_cursor(v_curr_deptno);

loop

fetch emp_cursor into v_ename, v_job, v_hiredate, v_sal;

exit when emp_cursor%notfound;

dbms_output.put_line(v_ename||' '||v_job||' '||v_hiredate||' '||v_sal);

end loop;

if emp_cursor%isopen then

close emp_cursor;

end if;

dbms_output.put_line(v_line);

end loop;

close dept_cursor;

end;