Más ejemplos de Cursores

Clase 23 de agosto de 2011

SELECT DEPARTMENT_NAME as "Departamento",count(*) as "Total empleados"

FROM employees E, departments D



WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

desc departments

desc employees





SELECT DEPARTMENT_NAME, count(EMPLOYEE_ID)

FROM employees E, departments D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

ORDER BY DEPARTMENT_NAME



SELECT DEPARTMENT_NAME, count(EMPLOYEE_ID)

FROM employees E, departments D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

HAVING count(EMPLOYEE_ID)> 3

GROUP BY DEPARTMENT_NAME

ORDER BY DEPARTMENT_NAME





SELECT DEPARTMENT_ID as "Departamento", count(*) as "Total empleados"

FROM employees

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID





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





DECLARE



CURSOR myCursor is



SELECT DEPARTMENT_ID, count(*)

FROM employees

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||' '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END



DECLARE

CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||' '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END





DECLARE

CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

DBMS_output.put_line('Departamento	Total empleados');

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||'              '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END



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





declare

vNumDepto integer := 10;

vNomDepto varchar2(30);

BEGIN

SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vNumDepto;

DBMS_output.put_line('El codigo del departamento es : ' || vNumDepto);

DBMS_output.put_line('El codigo del departamento es : ' || vNomDepto);  END





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





DECLARE CURSOR myCursor is SELECT DEPARTMENT_ID, count(*) FROM employees WHERE DEPARTMENT_ID is not NULL GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID; vDepartamento employees.employee_id%type; vTotalEmpleados integer; vNomDepto char(30); BEGIN OPEN myCursor; DBMS_output.put_line('Departamento	  Total empleados'); LOOP FETCH myCursor INTO vDepartamento, vTotalEmpleados; EXIT WHEN myCursor%NOTFOUND;                  SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vDepartamento;

DBMS_output.put_line(vNomDepto||''|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END





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





DECLARE



CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

vNomDepto char(20);

vMensaje varchar2(20);

BEGIN

OPEN myCursor;

DBMS_output.put_line('Departamento	  Total empleados');

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;               

SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vDepartamento;  IF vTotalEmpleados > 5 THEN

vMensaje  := 'Departamento Grande';

ELSE

vMensaje  := 'Departamento Chico';

END IF;  DBMS_output.put_line(vNomDepto||''||               

vTotalEmpleados||'      '||vMensaje);

END LOOP;

CLOSE myCursor;

END