![Page 1: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/1.jpg)
Cursores, Triggers, Indices, Transacciones, Vistas
Gestión y Modelación de Datos
![Page 2: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/2.jpg)
CursoresCREATE OR REPLACE FUNCTION incSalario (INTEGER) RETURNS TEXT AS ' DECLARE curEmp CURSOR FOR SELECT nombres, apellidos, salario FROM empleado ORDER BY apellidos, nombres FOR UPDATE; nombres TEXT; apellidos TEXT; sal INTEGER; BEGIN OPEN curEmp; <<ciclo>> LOOP FETCH curEmp INTO nombres, apellidos, sal; IF NOT FOUND THEN EXIT ciclo; END IF; UPDATE empleado SET salario = salario + $1 WHERE CURRENT OF curEmp; $1 = $1 + 5000; END LOOP; CLOSE curEmp; RETURN '' '';END; ' LANGUAGE 'plpgsql';
![Page 3: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/3.jpg)
Cursores
● Declaración name REFCURSOR; name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query [FOR UPDATE];
Ejemplos: DECLARE curs1 REFCURSOR; curs2 CURSOR FOR SELECT * FROM empleado; curs3 CURSOR (sal integer) IS SELECT * FROM empleado WHERE salario >= sal;
![Page 4: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/4.jpg)
Cursores
● Abrir OPEN name [ ( argument_values ) ];; OPEN name [ [ NO ] SCROLL ] FOR query [FOR UPDATE]; OPEN name [ [ NO ] SCROLL ] FOR EXECUTE query_string [ USING expression [, ... ] ];
Ejemplos: OPEN curs1 FOR SELECT * FROM empleado; OPEN curs3 (1000000); OPEN curs4 FOR EXECUTE ’SELECT * FROM ’ || quote_ident(tabname) || ’ WHERE col1 = $1’ USING keyvalue;
![Page 5: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/5.jpg)
Cursores
● Usar FETCH [ direction { FROM | IN } ] cursor INTO target;
direction: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD
Verificar resultado con FOUND
Ejemplos: FETCH curs1; FETCH curs3 RELATIVE -2;
![Page 6: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/6.jpg)
Cursores
● Reposicionar: MOVE [ direction { FROM | IN } ] cursor;
Verificar resultado con FOUND
● Actualizar/Eliminar UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;
● Cerrar: CLOSE cursor;
![Page 7: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/7.jpg)
Ejercicio
● Agregar a la tabla empleados un campo “SucesorDpto” de tipo entero
● Escribir una función que asigne el SucesorDpto, este valor se calcula así:
cod_dpto * 100 + consecutivo
● El consecutivo se asigna a cada empleado del departamento en orden alfabético, iniciando en 1.
![Page 8: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/8.jpg)
Cursores
● Recorrido
[ <<label>> ]
FOR recordvar IN bound_cursorvar
[ ( argument_values ) ] LOOP
statements
END LOOP [ label ];
![Page 9: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/9.jpg)
CREATE OR REPLACE FUNCTION incSalario2 (INTEGER) RETURNS TEXT AS ' DECLARE curEmp CURSOR FOR SELECT nombres, apellidos, salario FROM empleado ORDER BY apellidos, nombres FOR UPDATE; BEGIN <<ciclo>> FOR emp IN curEmp LOOP UPDATE empleado SET salario = salario + $1 WHERE CURRENT OF curEmp; RAISE INFO '' % % '', emp.apellidos, emp.salario; $1 = $1 + 5000; END LOOP; RETURN ''OK'';END; ' LANGUAGE 'plpgsql';
![Page 10: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/10.jpg)
Triggers● Trigger: operaciones que se realizan cuando
un evento específico ocurre en la BD
● Trigger Function: función invocada por un trigger. Debe retornar un valor de tipo de dato opaque
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR event ... ] } ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE function ( arguments )
● Eventos: INSERT, UPDATE, DELETE
![Page 11: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/11.jpg)
Triggers - EjemploCREATE TRIGGER ValidaSueldo BEFORE UPDATE ON empleado FOR EACH ROW EXECUTE PROCEDURE ValidaSueldo ()
CREATE OR REPLACE FUNCTION ValidaSueldo () RETURNS opaque AS ' DECLARE sueldoJefe int; BEGIN SELECT INTO sueldoJefe jefe.salario FROM empleado as jefe INNER JOIN empleado ON (jefe.cedula = empleado.ced_jefe) WHERE empleado.cedula = NEW.cedula; IF NOT FOUND THEN RAISE EXCEPTION ''No hay jefe''; END IF; IF sueldoJefe <= NEW.salario THEN RAISE EXCEPTION ''Salario del Jefe es Menor''; END IF; RETURN NEW;END; ' LANGUAGE 'plpgsql';
![Page 12: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/12.jpg)
Trigger Function
● Variables:
● NEW: en INSERT y UPDATE● OLD: en UPDATE Y DELETE● TG_WHEN: 'BEFORE', 'AFTER'● TG_OP: 'INSERT', 'UPDATE','DELETE'● TG_RELNAME: nombre de la relación● TG_NARGS: número de argumentos● TG_ARGV[]: argumentos
![Page 13: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/13.jpg)
Ejercicio
● Crear un trigger para la actualización y creación del empleado, que valide que el jefe existe en la base de datos antes de hacer la operación.
![Page 14: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/14.jpg)
Ejemplo de uso de tablas temporales
CREATE OR REPLACE FUNCTION ejemploTmp () RETURNS char AS ' DECLARE curEmp REFCURSOR; emp empleado%ROWTYPE; BEGIN DROP TABLE IF EXISTS empTmp; CREATE TEMP TABLE empTmp AS SELECT * FROM empleado; OPEN curEmp FOR SELECT * FROM empTmp WHERE salario > 1000000; <<ciclo>> LOOP FETCH curEmp INTO emp; IF NOT FOUND THEN EXIT ciclo; END IF; PERFORM actualizaSal (emp.cedula, emp.salario, emp.ced_jefe); END LOOP; CLOSE curEmp; RETURN ''''; END; ' LANGUAGE 'plpgsql';
![Page 15: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/15.jpg)
Ejemplo de uso de tablas temporales
CREATE OR REPLACE FUNCTION actualizaSal (integer, integer, integer) RETURNS char AS ' DECLARE ced ALIAS FOR $1; sal ALIAS FOR $2; cedJefe ALIAS FOR $3; newSal integer;
BEGIN IF sal > (SELECT salario FROM empleado WHERE cedula = cedJefe)/2 THEN newSal = TRUNC(sal * 1.05); UPDATE empTmp SET salario = newSal WHERE cedula = ced; END IF; RETURN ''''; END; ' LANGUAGE 'plpgsql';
![Page 16: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/16.jpg)
Ejemplo de uso de tablas temporales
Las tablas temporales existen mientras no se borren o hasta que se cierre la sesión. En este ejemplo, después de ejecutar la función ud. Puede consultar datos de empTmp, mientras permanezca en la misma sesión.
![Page 17: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/17.jpg)
Indices
● Una forma de mejorar el tiempo de respuesta de las consultas a la base de datos, pero también crean una sobrecarga para el sistema en las actualizaciones.
CREATE INDEX name ON table (column [, ...]);
CREATE UNIQUE INDEX name ON table (column [, ...]);
Ejemplo:
CREATE INDEX nombres ON empleado(Apellidos, Nombres)
![Page 18: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/18.jpg)
Vistas
● Darle un nombre a una consulta de la base de datos, para referirse a ella como a una tabla.
● Útiles para encapsular la estructura de las tablas, para que las interfaces permanezcan consistentes a pesar de la evolución de la aplicación.
CREATE VIEW name AS query;
● Ejemplo:
CREATE VIEW nombres AS (SELECT cedula, nombres || ' ' || apellidos as NombreCompleto FROM empleado);
![Page 19: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/19.jpg)
Transacciones
● Une varios pasos en una sola operación que se ejecuta “Todo-o-nada”
BEGIN; Statements...; COMMIT;
● Propiedades ACID
● ROLLBACK;
● SAVEPOINT savepointname;
● ROLLBACK TO savepointname:
![Page 20: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/20.jpg)
Transacciones
Ejemplo: en una base de datos al registrar las ventas, se actualiza también el valor de la comisión del vendedor que la realizó, y la cantidad en inventario del producto.
![Page 21: Cursores, Triggers, Indices, Transacciones, Vistascic.javerianacali.edu.co/wiki/lib/exe/fetch.php?media=... · 2011. 10. 24. · de los jefes de los empleados de esta regional. Cree](https://reader035.vdocumento.com/reader035/viewer/2022071605/6141231783382e045471e491/html5/thumbnails/21.jpg)
Ejercicios● Cree un indice sobre la tabla departamento.
● Cree una vista “EmpleadosEspaña”, que seleccione los datos de los empleados del departamento “Regional España”.
● Usando la vista “EmpleadosEspaña” seleccione la cédula de los jefes de los empleados de esta regional.
● Cree una función que cambie el jefe de un departamento. Esta función actualiza la ced_jefe de departamento, y actualiza ced_jefe de empleado, para todos los empleados de ese departamento. Use una transacción para asegurar la consistencia de la base de datos.