---------------------------------------------
----------------------------------------------
TRIGGERS
DISPARADORES
Como los eventos. Cödigo q escribo para manejar el evento.
A nivel de sentencia.
DML: insert, update, delete. modificación
DDL: normalmente para intercaptar DMLs. definición de datos
Por ejemplo,
-Before: (Update) tengo variables extra NEW(datos nuevos que voy a colocar en la fila..) y OLD (datos metidos en la tabla).
Ambos tienen tantos campos cmo tiene la fila.
Si es una insert no tengo datos en old(todo nulos)
Si es delete no tengo new
********
SAVEPOINT para transacciones(metes x el medio x si casca, q no empiece de cero): puntos de retorno
No se puede llamar desde un disp
EJERCICIO 1
create or replace
TRIGGER EJ1P9
BEFORE INSERT ON COMPRAS
FOR EACH ROW
BEGIN
:new.datosmayus := upper(:new.apellido)||','||upper(:new.nombre);
dbms_output.put_line('actualizado campo datosmayous');
END;
Para p robarlo insertar una fila en la tabla compras o bien ejectuar el proc 7 de la practicA 7
EJERCICIO 2
-Si el cliente ya compro hay q actualizar. Si no, hay q insertar en ventas tb.
Podemos hacerlo con una excepcion si intentamos insertar una venta d un cliente q ya compro y q entonces actualice,
o al revés, con una select
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
BEGIN
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
EXCEPTION
when DUP_VAL_ON_INDEX then
update compras set cochescomprados= cochescomprados + 1 where dni=:new.dni;
END;
De la otra manera:
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
v_cantidad numbre;
BEGIN
select count(*) into v_cantidad from compras
where dni=:new.dni;
if(v_cantidad=0) then
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
else
update compras set cochescomprados= cochescomprados + 1 where dni=:new.dni;
end if;
END;
EJERCICIO 3
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE OR DELETE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
v_cantidad number;
BEGIN
IF INSERTING THEN
select count(*) into v_cantidad from compras
where dni=:new.dni;
if(v_cantidad=0) then
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
else
update compras set cochescomprados= cochescomprados + 1 where dni=compras.dni;
end if;
-- EXCEPTION
-- when DUP_VAL_ON_INDEX then
-- update compras set cochescomprados= cochescomprados + 1 where dni=compras.dni;
ELSIF DELETING THEN
update compras set cochescomprados= cochescomprados - 1 where dni=:OLD.dni;
END IF;
END;
EJERCICIO 4
3 ESTAMENTOS con inserting, updating, deleting
CREATE OR REPLACE TRIGGER EJ4P
AFTER INSERT OR DELETE OR UPDATE ON P7_CLIENTES
FOR EACH ROW
BEGIN
if INSERTING then
insert into auditoria_clientes(dniact,nombreact, apellidoact, ciudadact, fechahora)
values (:new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
ELSIF UPDATING then
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, dniact, nombreact, apellidoact, ciudadact, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, :new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
elsif DELETING then
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, sysdate);
end if;
END;
EJERCICIO 5
create or replace
TRIGGER EJ5P9
AFTER INSERT ON P7_VENTAS
FOR EACH ROW
BEGIN
update p7_distribucion set cantidad=cantidad-1
where cifc=:new.cifc and codcoche=:new.codcoche;
END;
sIN ACABAR. vigilar q el coche exista y q queden unidades
O CON UNO SOLO, en realidad da igual q estemos borrando, insertando o act:
-si insertamos: los old no tienen nada, los new si
-si actualizms: tenemos los dos
-si borramos: solo los old
CREATE OR REPLACE TRIGGER EJ4P
AFTER INSERT OR DELETE OR UPDATE ON P7_CLIENTES
FOR EACH ROW
BEGIN
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, dniact, nombreact, apellidoact, ciudadact, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, :new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
END;
EJERCICIO 5-- BIENN!!!!!!
create or replace
TRIGGER EJ5P9
BEFORE INSERT ON P7_VENTAS
FOR EACH ROW
DECLARE
nCoches number;
cochesInsuficientes exception;
cocheErroneo exception;
BEGIN
select cantidad INTO nCoches from P7_DISTRIBUCION
where cifc=:new.cifc and codcoche=:new.codcoche;
if(nCoches<1) THEN RAISE cochesInsuficientes;
else
update p7_distribucion set cantidad=cantidad-1
where cifc=:new.cifc and codcoche=:new.codcoche;
END IF;
EXCEPTION
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20001, 'el coche no existe');
when cochesInsuficientes then
RAISE_APPLICATION_ERROR(-20002, 'nO quedan coches');
END;
EJERCICIO 6
CREATE OR REPLACE TRIGGER TRIGGER2
BEFORE DELETE ON COMPRAS
FOR EACH ROW
DECLARE
usuarioNoValido exception;
horaNoValida exception;
BEGIN
IF USER <> 'uo205169' THEN RAISE usuarioNoValido;
elsif(to_char(sysdate,'hh24') <11 or to_char(sysdate, 'hh24') >13) then RAISE horaNoValida;
end if;
EXCEPTION
when usuarioNoValido then
RAISE_APPLICATION_ERROR(-20001, 'El usuario no es el propietario del esquema');
WHEN horaNoValida then
RAISE_APPLICATION_ERROR(-20002, 'La hora no es válida');
END;
EJERCICIO 7
CREATE OR REPLACE TRIGGER EJ7P9
BEFORE INSERT OR DELETE OR UPDATE ON compras
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('inserción', sysdate, user);
ELSIF DELETING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('borrado', sysdate, user);
ELSIF UPDATING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('actualización', sysdate, user);
END IF;
END;
-------------------------------
-------------------------------
ENERO 2012 X ALBERTO
EXAMEN ENERO 2012
ESTABLECIMIENTO: id clave primaria
HABITACIONES: id(clave) y estable_id (clave ajena)
Restricciones: num del piso (check o trigger¿) check solo sobre un campo de la tabla
(si hay q mirar otras tablas tiene que ser un trigger)
CHECK: Rest de control
piso>=1 and <=7
-Substring o numeros trunc(cod_habitacion/100)=piso o cod_h like concat...
RESERVA: localizador(clave) y cliente_doi(clave ajena)
Restric: no hay reserva sin cliente: cliente_doi no puede ser null
dias duracion>=1
precio final calculo automatico (trigger)
HABITACIONES_RESERVAS: habitaciones_id y reservas_localizador (clave primaria los dos juntos, y cada uno por separado clave ajena)
EXTRAS: reserva_localizador y servicio_id (clave primaria los dos, clave ajena separados)
Restricion: cantidad no puede ser neg ni 0 (check)
fecha no anterior del periodo de reserva (trigger)
SERVICIOS: ID clave primaria
CLIENTES: DOI clave primaria
**EN el de febrero Proyectos funciona como relación(la clave primaria es la concat de las 2 entidades q relaciona)
y como entidad. En entradas metere un campo para meter la clave primaria de la relacion-entidad.(codsala, codpelicula , sesion y fecha)
2) Checks y triggers. Checks listos. 2 triggers: fecha y precio
fECHA: sobre la tabla extras. Insertar y actualizar. Antes de y nivl de fila. Column seleccionada: fecha
recuperar la fecha y dias de la tabla reserva (sumando tng la final). Comparar con la fecha nueva (:new.fecha)
TRIGGER PARA LA FECHA DE EXTRA
CREATE OR REPLACE TRIGGER EXAMENFECHA
BEFORE INSERT OR UPDATE OF FECHA ON TEXTRAS
FOR EACH ROW
declare
inicio TReservas.fecha%type; --date
fin TReservas.fecha%type;
BEGIN
select fecha, fecha + dias_duracion
into inicio, fin
from TReservas
where localizador = :new.reserva_localizador;
if(not :new.fecha between inicio and fin) then
raise_application_error(-20111, 'la fecha del extra no es válida');
end if;
END;
----------------
TRIGGER PARA EL CALCULO DEL PRECIO
Mirar cuantas habitaciones, precio de las habitaciones, extras, precio del serviciom, modificaciones precio hab...
-Cambian dias d duracion
-Insertan/quitan habs
-Insertan/quitan/cambian extras
TRIGGER 1 Sobre reservas. Actuallizar dias_duracion despues de, nivel filal.
*Al insertar no, pq al insertr reserva meter hab
-Importe habitaciones e importe extras
Si luego quiero manipular :new.Precio=88789789.. tiene q ser before update
TRIGGER 1
CREATE OR REPLACE TRIGGER EXAMENPRECIO1
before UPDATE OF DIAS_DURACION ON TRESERVAS
FOR EACH ROW
declare
por_habitaciones TReservas.precio_final%type; --number?
por_extras TReservas.precio_final%type;
BEGIN
select sum(h.precio)
into por_habitaciones
from THabitaciones_treservas hr, THabitaciones h
where hr.habitaciones_id=h.id
and hr.reservas_localizador = :new.localizador;
select sum(s.precio * e.cantidad)
into por_extras
from TServicios s, TExtras e
where s.id= e.servicio_id
and e.reserva_localizador=:new.localizador;
:new.precio_final :=por_habitaciones* :new.dias_duracion + por_extras;
END;
--------------------------- también en fubncion
create or replace
FUNCTION EXAMEN1PRECIO(PARAM in TReservas.localizador%type)
return TReservas.precio_final%type AS
por_habitaciones Treservas.precio_final%type;
por_extras Treservas.precio_final%type;
total TReservas.precio_final%type;
BEGIN
select sum(h.precio * r.dias_duracion)
into por_habitaciones
from THabitaciones_treservas hr, THabitaciones h,Treservas r
where hr.habitaciones_id=h.id
and r.localizador=hr.reservas_localizador
and hr.reservas_localizador = param;
select sum(s.precio * e.cantidad)
into por_extras
from TServicios s, TExtras e
where s.id= e.servicio_id
and e.reserva_localizador=param;
--total=ph+pe
return por_habitaciones + por_extras;
END EXAMEN1PRECIO;
y modificamos el trigger
total
BEGIN
total:=...funcion()
update
TReservas....
--------------------------
----------------------------
TRIGGER 2
CREATE OR REPLACE TRIGGER EXAMEN_PRECIO3
AFTER INSERT OR DELETE ON THABITACIONES_TRESERVAS
FOR EACH ROW
DECLARE
v_localizador TReservas.localizador%type;
total TReservas.precio_final%type;
BEGIN
if inserting then
v_localizador:= :new.reservas_localizador;
end if;
if deleting then
v_localizador:= :old.reservas_localizador;
end if;
total:= EXAMEN1PRECIO(v_localizador);--funcion
update TReservas set precio_final =total where localizador=v_localizador;
END;
--------------
TRIGGER 3 En extras, insert,supr y actu. Despues de. Cantidad. Casi igual q el otro pero con updating
CREATE OR REPLACE TRIGGER EXAMENPRECIO4
AFTER INSERT OR DELETE OR UPDATE OF CANTIDAD ON TEXTRAS
FOR EACH ROW
DECLARE
v_localizador TReservas.localizador%type;
total TReservas.precio_final%type;
BEGIN
if inserting or updating then
v_localizador:= :new.reserva_localizador;
end if;
if deleting then
v_localizador:= :old.reserva_localizador;
end if;
total:= EXAMEN1PRECIO(v_localizador);--funcion
update TReservas set precio_final =total where localizador=v_localizador;
END
-------------------------
-----------------------------
-------------------------------
3) Funcion cliente q mas gastó
Maximo reserva, agrupamos x cliente y ordenamos x la suma d las reservas
rownum<2 para q salga el primero NO pq el agrupamiento y ord van despues y sacaria el primero, pero no el q mas gasta
CREATE OR REPLACE FUNCTION EXAMENEJ3(PARAM IN Testablecimientos.id%type) RETURN VARCHAR2 AS
v_doi TClientes.doi%type;
v_apellidos TClientes.apellidos%type;
v_nombre TCLientes.nombre%type;
cursor c1 is select r.cliente_doi as doi
from treservas r, thabitaciones_treservas hr, Thabitaciones h, TClientes cl
where r.localizador=hr.reservas_localizador
and hr.habitaciones_id=h.id
and h.establecimiento_id=param
group by r.cliente_doi
order by sum(r.precio_final) desc;
BEGIN
open c1;
fetch c1 into v_doi;
close c1;
------------
-----------
3b) No va con joins normales, hay q sacar habs tengan o no tngan en habsreservas. ->INNERJOIN no dimos
from thabitaciones h left join thabitaciones_treservas hr
on h.id=hr.habitaciones_id
OTRA ALTERNATIVA
select h.cod_habitacion, h.piso, h.precio, (select count(*)from thabitaciones_treservas where habitacione_id=h.id)
from thabitaciones h
where h.establecimiento_id=param (del cursor del medio)
**podemos poner subselects en cualquier parte
el tercer cursor
Tres bucles anidados y a imprimir dbms
--------------------------------------------------
-------------------------------------------------------
FEBRERO 2010
Hay que tener en cuenta que una temporada comienza el 01 de septiembre y finaliza el 31 de julio y que
en este modelo se almacenarán únicamente los datos correspondientes a una temporada.
A partir del modelo E-R se extraen las siguientes tablas:
o La tabla TCategoria almacena el código de la categoría (cod_categoria) y el nombre de la
misma (nom_categoria). El nombre de la categoría puede ser primera, segunda o tercera.
o La tabla TEquipo almacena el código del equipo (cod_equipo) y el nombre del mismo
(nom_equipo).
o La tabla TEntrenador almacena el código del entrenador (cod_entrenador) y el nombre del
mismo (nom_entrenador).
o La tabla TEntrena indica los entrenadores que entrenan a cada equipo, almacenando la
fecha en la que comienza a entrenarlo (fecha_ini) y la fecha en la que finaliza (fecha_fin). Un
entrenador puede entrenar un equipo más de una vez en la misma temporada.
o La tabla TJugador almacena el código del jugador (cod_jugador), su nombre (nom_jugador) y el
número de equipos en los que ha jugado (num_equipos) en esta temporada. Este último
campo (num_equipos) ha de ser mantenido automáticamente por el sistema.
o La tabla TJuega indica los equipos en los que juega cada jugador, almacenando la fecha en
la que comienza a jugar en dicho equipo (fecha_ini) y la fecha en la que finaliza (fecha_fin).
Bases de Datos
E.U. DE INGENIERÍA TÉCNICA INFORMÁTICA DE OVIEDO
Examen Práctico (Enero 2011)
Hay que tener en cuenta que un jugador puede jugar en un equipo más de una vez en una
temporada. Además, un jugador puede jugar como máximo en dos equipos distintos en la
misma temporada.
o La tabla TCompeticion almacena el código de la competición (cod_competicion) y el nombre
de la misma (nom_competicion). El nombre de la competición puede ser liga, copa o
champions.
o La tabla TParticipa indica las competiciones en las que participa cada equipo y jugador.
o Se pide que el alumno:
1. Implemente el modelo anterior lo más completo posible (claves primarias, únicas, ajenas, checks,
triggers, …). Se recuerda que es condición necesaria para corregir el examen que las
CLAVES PRIMARIAS y las AJENAS estén bien establecidas.
Los triggers serán valorados con un máximo de 2 puntos.
2. En PLSQL (se valorará la utilización de SQL):
a) (2 puntos) Una función que dado el nombre de una categoría devuelva el nombre del
jugador que más entrenadores ha tenido en dicha categoría. Esta función se debe
invocar y mostrar su resultado desde el programa Java que se realice.
b) (3 puntos) Una función o procedimiento que muestre por pantalla (DBMS_OUTPUT)
para aquellos jugadores que hayan jugado en algún equipo durante esta
temporada la siguiente información
Cod_jugador1/Num_de_equipos/Equipo_mas_tiempo-jugó /Num_competiciones_distintas_participó
Nom_equipo1 / fecha_ini / fecha_fin
Nom_competicion1
Nom_competicion2
Nom_equipo2 / fecha_ini / fecha_fin
Nom_competicion3
Nom_competicion4
Cod_jugador2/Num_de_equipos/Equipo_mas_tiempo_estuvo /Num_competiciones_distintas_participó
SOLUCIÓN
create or replace
FUNCTION JUGADOR_MAS_ENTRENADORES
(
PARAM1 IN VARCHAR2
) RETURN VARCHAR2 AS
v_nombrejugador tjugador.nom_jugador%type;
v_codcategoria tcategoria.cod_categoria%type;
BEGIN
v_codcategoria :='';
v_nombrejugador :='';
select cod_categoria into v_codcategoria
from tcategoria
where nom_categoria = PARAM1;
select nom_jugador into v_nombrejugador
from(select nom_jugador, count(tentrena.cod_entrenador) nument
from tjugador, tjuega, tequipo, tentrena, tentrenador
where tequipo.cod_categoria = v_codcategoria
and tequipo.cod_equipo = tentrena.cod_equipo
and tentrenador.cod_entrenador = tentrena.cod_entrenador
and tequipo.cod_equipo = tjuega.cod_equipo
and tjuega.cod_jugador = tjugador.cod_jugador
group by nom_jugador
order by nument desc)
where rownum<2;
RETURN v_nombrejugador;
END JUGADOR_MAS_ENTRENADORES;
/*el procedimiento:*/
create or replace
PROCEDURE INFO_JUGADORES AS
cursor c1 is
select tjugador.cod_jugador, tjugador.num_equipos
from tjugador
where tjugador.num_equipos>0
;
cursor c2(c1_codjugador tjugador.cod_jugador%type) is
select nom_equipo, fecha_ini, fecha_fin, tequipo.cod_equipo
from tjuega, tequipo
where tjuega.cod_jugador = c1_codjugador
and tequipo.cod_equipo = tjuega.cod_equipo;
cursor c3(c1_codjugador tjugador.cod_jugador%type, c2_codequipo tequipo.cod_equipo%type) is
select distinct tcompeticion.nom_competicion
from tcompeticion, tparticipa
where tparticipa.cod_jugador = c1_codjugador
and tparticipa.cod_equipo = c2_codequipo
and tcompeticion.cod_competicion = tparticipa.cod_competicion
;
BEGIN
for i in c1 loop
dbms_output.put_line(i.cod_jugador||' - '||i.num_equipos);
for j in c2(i.cod_jugador) loop
dbms_output.put_line(' '||j.nom_equipo||' - '|| j.fecha_ini||' - '|| j.fecha_fin);
for k in c3(i.cod_jugador, j.cod_equipo) loop
dbms_output.put_line(' '||k.nom_competicion);
end loop;
end loop;
end loop;
END INFO_JUGADORES;
-----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------
TRIGGERS
DISPARADORES
Como los eventos. Cödigo q escribo para manejar el evento.
A nivel de sentencia.
DML: insert, update, delete. modificación
DDL: normalmente para intercaptar DMLs. definición de datos
Por ejemplo,
-Before: (Update) tengo variables extra NEW(datos nuevos que voy a colocar en la fila..) y OLD (datos metidos en la tabla).
Ambos tienen tantos campos cmo tiene la fila.
Si es una insert no tengo datos en old(todo nulos)
Si es delete no tengo new
********
SAVEPOINT para transacciones(metes x el medio x si casca, q no empiece de cero): puntos de retorno
No se puede llamar desde un disp
EJERCICIO 1
create or replace
TRIGGER EJ1P9
BEFORE INSERT ON COMPRAS
FOR EACH ROW
BEGIN
:new.datosmayus := upper(:new.apellido)||','||upper(:new.nombre);
dbms_output.put_line('actualizado campo datosmayous');
END;
Para p robarlo insertar una fila en la tabla compras o bien ejectuar el proc 7 de la practicA 7
EJERCICIO 2
-Si el cliente ya compro hay q actualizar. Si no, hay q insertar en ventas tb.
Podemos hacerlo con una excepcion si intentamos insertar una venta d un cliente q ya compro y q entonces actualice,
o al revés, con una select
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
BEGIN
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
EXCEPTION
when DUP_VAL_ON_INDEX then
update compras set cochescomprados= cochescomprados + 1 where dni=:new.dni;
END;
De la otra manera:
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
v_cantidad numbre;
BEGIN
select count(*) into v_cantidad from compras
where dni=:new.dni;
if(v_cantidad=0) then
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
else
update compras set cochescomprados= cochescomprados + 1 where dni=:new.dni;
end if;
END;
EJERCICIO 3
create or replace
TRIGGER "EJ2P9" AFTER INSERT OR UPDATE OR DELETE ON P7_VENTAS
FOR EACH ROW
DECLARE
v_nombre varchar(40);
v_apellido varchar(40);
v_cantidad number;
BEGIN
IF INSERTING THEN
select count(*) into v_cantidad from compras
where dni=:new.dni;
if(v_cantidad=0) then
select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=:new.dni;
insert into compras (dni,nombre,apellido,cochescomprados) values (:new.dni, v_nombre, v_apellido, 1);
-- v_nombre y v_apellido variables q vienen de selects into
else
update compras set cochescomprados= cochescomprados + 1 where dni=compras.dni;
end if;
-- EXCEPTION
-- when DUP_VAL_ON_INDEX then
-- update compras set cochescomprados= cochescomprados + 1 where dni=compras.dni;
ELSIF DELETING THEN
update compras set cochescomprados= cochescomprados - 1 where dni=:OLD.dni;
END IF;
END;
EJERCICIO 4
3 ESTAMENTOS con inserting, updating, deleting
CREATE OR REPLACE TRIGGER EJ4P
AFTER INSERT OR DELETE OR UPDATE ON P7_CLIENTES
FOR EACH ROW
BEGIN
if INSERTING then
insert into auditoria_clientes(dniact,nombreact, apellidoact, ciudadact, fechahora)
values (:new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
ELSIF UPDATING then
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, dniact, nombreact, apellidoact, ciudadact, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, :new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
elsif DELETING then
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, sysdate);
end if;
END;
EJERCICIO 5
create or replace
TRIGGER EJ5P9
AFTER INSERT ON P7_VENTAS
FOR EACH ROW
BEGIN
update p7_distribucion set cantidad=cantidad-1
where cifc=:new.cifc and codcoche=:new.codcoche;
END;
sIN ACABAR. vigilar q el coche exista y q queden unidades
O CON UNO SOLO, en realidad da igual q estemos borrando, insertando o act:
-si insertamos: los old no tienen nada, los new si
-si actualizms: tenemos los dos
-si borramos: solo los old
CREATE OR REPLACE TRIGGER EJ4P
AFTER INSERT OR DELETE OR UPDATE ON P7_CLIENTES
FOR EACH ROW
BEGIN
insert into auditoria_clientes(dniant, nombreant, apellidoant, ciudadant, dniact, nombreact, apellidoact, ciudadact, fechahora)
values(:old.dni, :old.nombre, :old.apellido, :old.ciudad, :new.dni, :new.nombre, :new.apellido, :new.ciudad, sysdate);
END;
EJERCICIO 5-- BIENN!!!!!!
create or replace
TRIGGER EJ5P9
BEFORE INSERT ON P7_VENTAS
FOR EACH ROW
DECLARE
nCoches number;
cochesInsuficientes exception;
cocheErroneo exception;
BEGIN
select cantidad INTO nCoches from P7_DISTRIBUCION
where cifc=:new.cifc and codcoche=:new.codcoche;
if(nCoches<1) THEN RAISE cochesInsuficientes;
else
update p7_distribucion set cantidad=cantidad-1
where cifc=:new.cifc and codcoche=:new.codcoche;
END IF;
EXCEPTION
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20001, 'el coche no existe');
when cochesInsuficientes then
RAISE_APPLICATION_ERROR(-20002, 'nO quedan coches');
END;
EJERCICIO 6
CREATE OR REPLACE TRIGGER TRIGGER2
BEFORE DELETE ON COMPRAS
FOR EACH ROW
DECLARE
usuarioNoValido exception;
horaNoValida exception;
BEGIN
IF USER <> 'uo205169' THEN RAISE usuarioNoValido;
elsif(to_char(sysdate,'hh24') <11 or to_char(sysdate, 'hh24') >13) then RAISE horaNoValida;
end if;
EXCEPTION
when usuarioNoValido then
RAISE_APPLICATION_ERROR(-20001, 'El usuario no es el propietario del esquema');
WHEN horaNoValida then
RAISE_APPLICATION_ERROR(-20002, 'La hora no es válida');
END;
EJERCICIO 7
CREATE OR REPLACE TRIGGER EJ7P9
BEFORE INSERT OR DELETE OR UPDATE ON compras
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('inserción', sysdate, user);
ELSIF DELETING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('borrado', sysdate, user);
ELSIF UPDATING THEN
INSERT INTO AUDITACOMPRA(operación, fecha, usuario) values ('actualización', sysdate, user);
END IF;
END;
-------------------------------
-------------------------------
ENERO 2012 X ALBERTO
EXAMEN ENERO 2012
ESTABLECIMIENTO: id clave primaria
HABITACIONES: id(clave) y estable_id (clave ajena)
Restricciones: num del piso (check o trigger¿) check solo sobre un campo de la tabla
(si hay q mirar otras tablas tiene que ser un trigger)
CHECK: Rest de control
piso>=1 and <=7
-Substring o numeros trunc(cod_habitacion/100)=piso o cod_h like concat...
RESERVA: localizador(clave) y cliente_doi(clave ajena)
Restric: no hay reserva sin cliente: cliente_doi no puede ser null
dias duracion>=1
precio final calculo automatico (trigger)
HABITACIONES_RESERVAS: habitaciones_id y reservas_localizador (clave primaria los dos juntos, y cada uno por separado clave ajena)
EXTRAS: reserva_localizador y servicio_id (clave primaria los dos, clave ajena separados)
Restricion: cantidad no puede ser neg ni 0 (check)
fecha no anterior del periodo de reserva (trigger)
SERVICIOS: ID clave primaria
CLIENTES: DOI clave primaria
**EN el de febrero Proyectos funciona como relación(la clave primaria es la concat de las 2 entidades q relaciona)
y como entidad. En entradas metere un campo para meter la clave primaria de la relacion-entidad.(codsala, codpelicula , sesion y fecha)
2) Checks y triggers. Checks listos. 2 triggers: fecha y precio
fECHA: sobre la tabla extras. Insertar y actualizar. Antes de y nivl de fila. Column seleccionada: fecha
recuperar la fecha y dias de la tabla reserva (sumando tng la final). Comparar con la fecha nueva (:new.fecha)
TRIGGER PARA LA FECHA DE EXTRA
CREATE OR REPLACE TRIGGER EXAMENFECHA
BEFORE INSERT OR UPDATE OF FECHA ON TEXTRAS
FOR EACH ROW
declare
inicio TReservas.fecha%type; --date
fin TReservas.fecha%type;
BEGIN
select fecha, fecha + dias_duracion
into inicio, fin
from TReservas
where localizador = :new.reserva_localizador;
if(not :new.fecha between inicio and fin) then
raise_application_error(-20111, 'la fecha del extra no es válida');
end if;
END;
----------------
TRIGGER PARA EL CALCULO DEL PRECIO
Mirar cuantas habitaciones, precio de las habitaciones, extras, precio del serviciom, modificaciones precio hab...
-Cambian dias d duracion
-Insertan/quitan habs
-Insertan/quitan/cambian extras
TRIGGER 1 Sobre reservas. Actuallizar dias_duracion despues de, nivel filal.
*Al insertar no, pq al insertr reserva meter hab
-Importe habitaciones e importe extras
Si luego quiero manipular :new.Precio=88789789.. tiene q ser before update
TRIGGER 1
CREATE OR REPLACE TRIGGER EXAMENPRECIO1
before UPDATE OF DIAS_DURACION ON TRESERVAS
FOR EACH ROW
declare
por_habitaciones TReservas.precio_final%type; --number?
por_extras TReservas.precio_final%type;
BEGIN
select sum(h.precio)
into por_habitaciones
from THabitaciones_treservas hr, THabitaciones h
where hr.habitaciones_id=h.id
and hr.reservas_localizador = :new.localizador;
select sum(s.precio * e.cantidad)
into por_extras
from TServicios s, TExtras e
where s.id= e.servicio_id
and e.reserva_localizador=:new.localizador;
:new.precio_final :=por_habitaciones* :new.dias_duracion + por_extras;
END;
--------------------------- también en fubncion
create or replace
FUNCTION EXAMEN1PRECIO(PARAM in TReservas.localizador%type)
return TReservas.precio_final%type AS
por_habitaciones Treservas.precio_final%type;
por_extras Treservas.precio_final%type;
total TReservas.precio_final%type;
BEGIN
select sum(h.precio * r.dias_duracion)
into por_habitaciones
from THabitaciones_treservas hr, THabitaciones h,Treservas r
where hr.habitaciones_id=h.id
and r.localizador=hr.reservas_localizador
and hr.reservas_localizador = param;
select sum(s.precio * e.cantidad)
into por_extras
from TServicios s, TExtras e
where s.id= e.servicio_id
and e.reserva_localizador=param;
--total=ph+pe
return por_habitaciones + por_extras;
END EXAMEN1PRECIO;
y modificamos el trigger
total
BEGIN
total:=...funcion()
update
TReservas....
--------------------------
----------------------------
TRIGGER 2
CREATE OR REPLACE TRIGGER EXAMEN_PRECIO3
AFTER INSERT OR DELETE ON THABITACIONES_TRESERVAS
FOR EACH ROW
DECLARE
v_localizador TReservas.localizador%type;
total TReservas.precio_final%type;
BEGIN
if inserting then
v_localizador:= :new.reservas_localizador;
end if;
if deleting then
v_localizador:= :old.reservas_localizador;
end if;
total:= EXAMEN1PRECIO(v_localizador);--funcion
update TReservas set precio_final =total where localizador=v_localizador;
END;
--------------
TRIGGER 3 En extras, insert,supr y actu. Despues de. Cantidad. Casi igual q el otro pero con updating
CREATE OR REPLACE TRIGGER EXAMENPRECIO4
AFTER INSERT OR DELETE OR UPDATE OF CANTIDAD ON TEXTRAS
FOR EACH ROW
DECLARE
v_localizador TReservas.localizador%type;
total TReservas.precio_final%type;
BEGIN
if inserting or updating then
v_localizador:= :new.reserva_localizador;
end if;
if deleting then
v_localizador:= :old.reserva_localizador;
end if;
total:= EXAMEN1PRECIO(v_localizador);--funcion
update TReservas set precio_final =total where localizador=v_localizador;
END
-------------------------
-----------------------------
-------------------------------
3) Funcion cliente q mas gastó
Maximo reserva, agrupamos x cliente y ordenamos x la suma d las reservas
rownum<2 para q salga el primero NO pq el agrupamiento y ord van despues y sacaria el primero, pero no el q mas gasta
CREATE OR REPLACE FUNCTION EXAMENEJ3(PARAM IN Testablecimientos.id%type) RETURN VARCHAR2 AS
v_doi TClientes.doi%type;
v_apellidos TClientes.apellidos%type;
v_nombre TCLientes.nombre%type;
cursor c1 is select r.cliente_doi as doi
from treservas r, thabitaciones_treservas hr, Thabitaciones h, TClientes cl
where r.localizador=hr.reservas_localizador
and hr.habitaciones_id=h.id
and h.establecimiento_id=param
group by r.cliente_doi
order by sum(r.precio_final) desc;
BEGIN
open c1;
fetch c1 into v_doi;
close c1;
------------
-----------
3b) No va con joins normales, hay q sacar habs tengan o no tngan en habsreservas. ->INNERJOIN no dimos
from thabitaciones h left join thabitaciones_treservas hr
on h.id=hr.habitaciones_id
OTRA ALTERNATIVA
select h.cod_habitacion, h.piso, h.precio, (select count(*)from thabitaciones_treservas where habitacione_id=h.id)
from thabitaciones h
where h.establecimiento_id=param (del cursor del medio)
**podemos poner subselects en cualquier parte
el tercer cursor
Tres bucles anidados y a imprimir dbms
--------------------------------------------------
-------------------------------------------------------
FEBRERO 2010
Hay que tener en cuenta que una temporada comienza el 01 de septiembre y finaliza el 31 de julio y que
en este modelo se almacenarán únicamente los datos correspondientes a una temporada.
A partir del modelo E-R se extraen las siguientes tablas:
o La tabla TCategoria almacena el código de la categoría (cod_categoria) y el nombre de la
misma (nom_categoria). El nombre de la categoría puede ser primera, segunda o tercera.
o La tabla TEquipo almacena el código del equipo (cod_equipo) y el nombre del mismo
(nom_equipo).
o La tabla TEntrenador almacena el código del entrenador (cod_entrenador) y el nombre del
mismo (nom_entrenador).
o La tabla TEntrena indica los entrenadores que entrenan a cada equipo, almacenando la
fecha en la que comienza a entrenarlo (fecha_ini) y la fecha en la que finaliza (fecha_fin). Un
entrenador puede entrenar un equipo más de una vez en la misma temporada.
o La tabla TJugador almacena el código del jugador (cod_jugador), su nombre (nom_jugador) y el
número de equipos en los que ha jugado (num_equipos) en esta temporada. Este último
campo (num_equipos) ha de ser mantenido automáticamente por el sistema.
o La tabla TJuega indica los equipos en los que juega cada jugador, almacenando la fecha en
la que comienza a jugar en dicho equipo (fecha_ini) y la fecha en la que finaliza (fecha_fin).
Bases de Datos
E.U. DE INGENIERÍA TÉCNICA INFORMÁTICA DE OVIEDO
Examen Práctico (Enero 2011)
Hay que tener en cuenta que un jugador puede jugar en un equipo más de una vez en una
temporada. Además, un jugador puede jugar como máximo en dos equipos distintos en la
misma temporada.
o La tabla TCompeticion almacena el código de la competición (cod_competicion) y el nombre
de la misma (nom_competicion). El nombre de la competición puede ser liga, copa o
champions.
o La tabla TParticipa indica las competiciones en las que participa cada equipo y jugador.
o Se pide que el alumno:
1. Implemente el modelo anterior lo más completo posible (claves primarias, únicas, ajenas, checks,
triggers, …). Se recuerda que es condición necesaria para corregir el examen que las
CLAVES PRIMARIAS y las AJENAS estén bien establecidas.
Los triggers serán valorados con un máximo de 2 puntos.
2. En PLSQL (se valorará la utilización de SQL):
a) (2 puntos) Una función que dado el nombre de una categoría devuelva el nombre del
jugador que más entrenadores ha tenido en dicha categoría. Esta función se debe
invocar y mostrar su resultado desde el programa Java que se realice.
b) (3 puntos) Una función o procedimiento que muestre por pantalla (DBMS_OUTPUT)
para aquellos jugadores que hayan jugado en algún equipo durante esta
temporada la siguiente información
Cod_jugador1/Num_de_equipos/Equipo_mas_tiempo-jugó /Num_competiciones_distintas_participó
Nom_equipo1 / fecha_ini / fecha_fin
Nom_competicion1
Nom_competicion2
Nom_equipo2 / fecha_ini / fecha_fin
Nom_competicion3
Nom_competicion4
Cod_jugador2/Num_de_equipos/Equipo_mas_tiempo_estuvo /Num_competiciones_distintas_participó
SOLUCIÓN
create or replace
FUNCTION JUGADOR_MAS_ENTRENADORES
(
PARAM1 IN VARCHAR2
) RETURN VARCHAR2 AS
v_nombrejugador tjugador.nom_jugador%type;
v_codcategoria tcategoria.cod_categoria%type;
BEGIN
v_codcategoria :='';
v_nombrejugador :='';
select cod_categoria into v_codcategoria
from tcategoria
where nom_categoria = PARAM1;
select nom_jugador into v_nombrejugador
from(select nom_jugador, count(tentrena.cod_entrenador) nument
from tjugador, tjuega, tequipo, tentrena, tentrenador
where tequipo.cod_categoria = v_codcategoria
and tequipo.cod_equipo = tentrena.cod_equipo
and tentrenador.cod_entrenador = tentrena.cod_entrenador
and tequipo.cod_equipo = tjuega.cod_equipo
and tjuega.cod_jugador = tjugador.cod_jugador
group by nom_jugador
order by nument desc)
where rownum<2;
RETURN v_nombrejugador;
END JUGADOR_MAS_ENTRENADORES;
/*el procedimiento:*/
create or replace
PROCEDURE INFO_JUGADORES AS
cursor c1 is
select tjugador.cod_jugador, tjugador.num_equipos
from tjugador
where tjugador.num_equipos>0
;
cursor c2(c1_codjugador tjugador.cod_jugador%type) is
select nom_equipo, fecha_ini, fecha_fin, tequipo.cod_equipo
from tjuega, tequipo
where tjuega.cod_jugador = c1_codjugador
and tequipo.cod_equipo = tjuega.cod_equipo;
cursor c3(c1_codjugador tjugador.cod_jugador%type, c2_codequipo tequipo.cod_equipo%type) is
select distinct tcompeticion.nom_competicion
from tcompeticion, tparticipa
where tparticipa.cod_jugador = c1_codjugador
and tparticipa.cod_equipo = c2_codequipo
and tcompeticion.cod_competicion = tparticipa.cod_competicion
;
BEGIN
for i in c1 loop
dbms_output.put_line(i.cod_jugador||' - '||i.num_equipos);
for j in c2(i.cod_jugador) loop
dbms_output.put_line(' '||j.nom_equipo||' - '|| j.fecha_ini||' - '|| j.fecha_fin);
for k in c3(i.cod_jugador, j.cod_equipo) loop
dbms_output.put_line(' '||k.nom_competicion);
end loop;
end loop;
end loop;
END INFO_JUGADORES;
-----------------------------------------------------------
----------------------------------------------------------