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

Trigger

Un trigger (o disparador) en una Base de datos , es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos.


Ejemplo

Un sencillo ejemplo (para SQL Server) sería crear un Trigger para insertar un pedido de algún producto cuando la cantidad de éste, en nuestro almacén, sea inferior a un valor dado.

BEFORE UPDATE ON tabla_almacen
FOR ALL records
IF :NEW.producto < 100 THEN INSERT INTO tabla_pedidos(producto) VALUES ('1000'); END IF; SELECT DBO.POLVE.TEST END
Fuente wikipedia

bienvenidos

este sera nuestro blog de la clase de programación a base de datos , el cual iremos desarrollando y actualizando nuestro blog con contenidos vistos en clases, ademas de algunas cosas importantes , que debemos recordar.

espero que todo les sirva de ayuda.