Creacion Cursor

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



-------------------------CURSOR FINAL------------------------------------

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