LuCiiernaga2

FEBRERO 09 A partir del modelo E-R se extraen las siguientes tablas: o La tabla TCines almacena el código del cine (CodCine) y la localidad donde se encuentra (Localidad). o La tabla TSalas almacena el código de la sala (CodSala) y el aforo máximo permitido para la misma (Aforo). El código de la sala no se repetirá entre los distintos cines, es decir, la sala 1 solo existirá en el cine Z. o La tabla TPeliculas almacena el código de la película (CodPelicula), su titulo (Titulo), su duración en minutos (Duración), y el tipo de la misma (Tipo). El tipo de las películas podrá ser: ficción, aventuras o terror. o La tabla TEntradas almacena el identificador de la entrada (CodEntrada) y el precio de la misma (Precio). No se podrán vender más entradas para la proyección de una película, en una sesión y fecha determinada, que el aforo permitido para la sala en la que se proyecta. o La tabla TProyectan indicará las películas que son proyectadas en cada sala. Almacenará también la sesión en la que se proyecta (Sesion), la fecha en la que se hace (Fecha), y el número de entradas vendidas para dicha proyección (EntradasVendidas). o La sesión será alguno de los siguientes valores: 5, 7 o 10. o No se podrá proyectar una película en una sesión si la duración de la película impide comenzar la siguiente sesión. Ej. Si la película comienza a las 5 pero dura 180 minutos no se podrá permitir esa proyección. o EntradasVeVendidas es un atributo que debe ser mantenido automáticamente por el sistema. . En PLSQL (se valorará la utilización de SQL): a) Una función que devuelva el tipo de la película más vista en la última sesión. Esta función se debe invocar y mostrar su resultado desde el programa Java que se realice. b) Una función o procedimiento que muestre por pantalla (DBMS_OUTPUT) para los cines de una determinada localidad, que recibe como parámetro, la recaudación total obtenida en cada cine así como la obtenida por cada una de las películas en él proyectadas. Cine 1 - Recaudación_total Codpelícula1 – Titulo1 - Recaudación_total_pelicula_1_en_cine1 Codpelícula 2 – Titulo2 – Recaudación _total_pelicula_2_en_cine1 Cine 2 - Recaudación_total Codpelícula1 – Titulo1 - Recaudación_total_película_ 1_en_cine2 Codpelícula 2 – Titulo2 – Recaudación_total_película _ 2_en_cine2 Codpelícula 3 - Titulo3 – Recaudación_total_película_3_en_cine2 ------------------------------------------------------ -- Export file for user PRUEBA -- -- Created by Administrador on 11/02/2009, 12:48:40 -- ------------------------------------------------------ spool ExamenFebrero09.log prompt prompt Creating procedure EJERCICIO2A prompt ============================== prompt CREATE OR REPLACE PROCEDURE PRUEBA.EJERCICIO2A AS ttipo varchar2(40); BEGIN ttipo:=' '; select tipo into ttipo from tpeliculas where codpelicula in (Select codpelicula from ( Select sum(entradasvendidas),tpeliculas.codpelicula from tproyectan,tpeliculas where tproyectan.codpelicula = tpeliculas.codpelicula and sesion=10 group by tpeliculas.codpelicula order by sum(entradasVendidas) desc) where rownum<2) DBMS_OUTPUT.put_line('TIPO: '||ttipo); END EJERCICIO2A; / prompt prompt Creating procedure EJERCICIO2B prompt ============================== prompt CREATE OR REPLACE PROCEDURE PRUEBA.EJERCICIO2B ( param1 IN VARCHAR2 ) AS cursor c1 is select codcine From tcines where localidad=param1; cursor c2 (mcine tcines.codcine%TYPE) is Select tpeliculas.codpelicula, tpeliculas.titulo, sum(precio) recaudacion From tentradas,tsalas,tpeliculas where tsalas.codcine=mcine and tsalas.codsala=tentradas.codsala and tpeliculas.codpelicula=tentradas.codpelicula group by tpeliculas.codpelicula,tpeliculas.titulo; recaudaciontotal int; BEGIN for i in c1 loop recaudaciontotal:=0; Select sum(precio) into recaudaciontotal From tsalas,tentradas where tsalas.codcine=i.codcine and tentradas.codsala = tsalas.codsala; DBMS_OUTPUT.put_line('Cine: '||i.codcine||' Recaudacion : '||recaudaciontotal); for j in c2(i.codcine) loop DBMS_OUTPUT.put_line('--->Pelicula: '||j.codpelicula||' Titulo: '||j.Titulo||' Recaudación: '||j.recaudacion); end loop; end loop; END EJERCICIO2B; prompt prompt Creating trigger TRIGGER1 prompt ========================= prompt CREATE OR REPLACE TRIGGER PRUEBA.TRIGGER1 BEFORE INSERT OR UPDATE ON TPROYECTAN FOR EACH ROW declare tduracion int; DuracionExcedida exception; BEGIN tduracion:=0; select duracion into tduracion from tpeliculas where codpelicula=:NEW.codpelicula; if (:NEW.sesion=5 and tduracion>120) then raise DuracionExcedida; elsif (:NEW.sesion=7 and tduracion>180) then raise DuracionExcedida; end if; EXCEPTION WHEN duracionExcedida THEN RAISE_APPLICATION_ERROR (-20222,'Duración demasiado larga'); END; / prompt prompt Creating trigger TRIGGER2 prompt ========================= prompt create or replace trigger PRUEBA.Trigger2 after insert or delete or update on tentradas for each row declare -- local variables here begin if inserting or updating then update tproyectan set entradasvendidas=entradasvendidas+1 where codsala=:NEW.codsala and codpelicula=:new.codpelicula and sesion=:NEW.sesion and fecha=:NEW.fecha; end if; if deleting or updating then update tproyectan set entradasvendidas=entradasvendidas-1 where codsala=:OLD.codsala and codpelicula=:old.codpelicula and sesion=:old.sesion and fecha=:old.fecha; end if; end Trigger2; / prompt prompt Creating trigger TRIGGER3 prompt ========================= prompt create or replace trigger PRUEBA.trigger3 before insert or update on tentradas for each row declare -- local variables here numentradas int; aforop int; salallena EXCEPTION; begin numentradas:=0; aforop:=0; Select entradasvendidas into numentradas from tproyectan where sesion=:NEW.sesion and fecha=:NEW.fecha AND codsala=:NEW.codsala and codpelicula=:NEW.codpelicula; Select aforo into aforop from tsalas where codsala=:NEW.codsala; if aforop<numentradas+1 then raise salallena; end if; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20222,'Proyección no existente'||sqlerrm); WHEN salaLlena THEN raise_application_error(-20223,'la sala está llena'); end trigger3; / spool off ------------------------------ --------------------------------- ENERO 2012 A partir del modelo E-R se extraen las siguientes tablas: o La tabla TEstablecimientos almacena el identificador del establecimiento (id), el código postal (codPostal), la dirección (dirección), el nombre (nombre), el país (país), la población (población) y la provincia del mismo (provincia). o La tabla THabitaciones almacena la identificación de la habitación (id), el número de habitación (cod_habitacion), el piso de la habitación (piso) y el precio de la habitación (precio). El piso debe ser un número comprendido entre 1 y 7 (ambos incluidos). El número de habitación deberá comenzar por el mismo número que el piso. o La tabla TClientes almacena el documento oficial de identidad (doi), los apellidos (apellido) y el nombre del mismo (nombre). o La tabla TServicios almacena la identificación del servicio (id), el precio del servicio (precio) y el tipo del servicio (tipo). o La tabla TReservas almacena la reserva para un determinado cliente. No hay reservas sin cliente. Una reserva consta del localizador de la reserva (localizador), los días de estancia (días_duracion), la fecha de entrada (fecha) y el precio total de la reserva (precio_final). Los días de duración de la reserva serán mayores o iguales a uno. El precio final de la reserva se calculará automáticamente por el sistema en función del precio de la habitación y de los servicios aplicados. o La tabla THabitaciones_TReservas almacena las habitaciones asociadas a la reserva. o La tabla TExtras almacena el número de unidades de un determinado servicio (cantidad) que se ha consumido en una reserva y la fecha (fecha). La cantidad no puede ser negativa o cero. La fecha en la que se aplica el servicio no puede ser anterior ni posterior al periodo de reserva. Se pide que el alumno: 1. Completa las tablas para implementar el modelo de dominio anterior lo más completo posible con claves primarias, únicas y ajenas. Es condición necesaria para corregir el examen que las CLAVES PRIMARIAS y las AJENAS estén bien establecidas. 2. (2 puntos) Añade los checks y triggers necesarios para mantener las restricciones enunciadas. 3. En PL/SQL: a) (2 puntos) Una función que devuelve el nombre y los apellidos del cliente que más gastó, contando todas sus reservas, para un establecimiento que se pasa como parámetro. b) (3 puntos) Una función o procedimiento que muestre por pantalla (DBMS_OUTPUT) la información referente a los establecimientos: NombreEstablecimiento Población Provincia NumHabitaciones  Cod_habitacion Piso Precio TotalReservasHabitacion  LocalizadorReserva FcehaEntrada FechaSalida PrecioHabitacion PrecioExtra  LocalizadorReserva FcehaEntrada FechaSalida PrecioHabitacion PrecioExtra … NumHabitaciones es el número total de habitaciones que tiene el establecimiento. TotalReservasHabitacion es el total de reservas que se han realizado para una determinada habitación en un establecimiento determinado. FechaSalida se calcula la fecha de salida en base a los días de estancia. PrecioExtras representa el precio de los servicios aplicados. ________________________________________________________________ TRIGGER1 _______________________________________________________________ create or replace TRIGGER "EX2012ENERO_TEXTRAS" BEFORE INSERT OR UPDATE OF FECHA ON TEXTRAS FOR EACH ROW DECLARE f_ini treservas.fecha%TYPE; f_fin treservas.fecha%TYPE; dias treservas.dias_duracion%TYPE; fecha_incorrecta EXCEPTION; BEGIN if inserting then select fecha,dias_duracion into f_ini,dias from treservas where localizador=:NEW.RESERVA_LOCALIZADOR; f_fin:=f_ini+dias; IF :NEW.fecha<f_ini OR :NEW.fecha>f_fin THEN RAISE fecha_incorrecta; END IF; elsif updating then select fecha,dias_duracion into f_ini,dias from treservas where localizador=:OLD.RESERVA_LOCALIZADOR; f_fin:=f_ini+dias; IF :NEW.fecha<f_ini OR :NEW.fecha>f_fin THEN RAISE fecha_incorrecta; END IF; end if; EXCEPTION WHEN fecha_incorrecta THEN RAISE_APPLICATION_ERROR (-20001,'Fecha de cargo incorrecta'); END; ________________________________________________________________ TRIGGER2 _______________________________________________________________ create or replace TRIGGER "EX2012ENERO_TEXTRAS2" AFTER INSERT OR DELETE OR UPDATE OF CANTIDAD ON TEXTRAS FOR EACH ROW DECLARE VPRECIO TSERVICIOS.PRECIO%TYPE; BEGIN IF INSERTING THEN SELECT PRECIO INTO VPRECIO FROM TSERVICIOS WHERE TSERVICIOS.ID=:NEW.SERVICIO_ID; UPDATE TRESERVAS SET PRECIO_FINAL=PRECIO_FINAL+VPRECIO*:NEW.CANTIDAD WHERE LOCALIZADOR=:NEW.RESERVA_LOCALIZADOR; ELSIF DELETING THEN SELECT PRECIO INTO VPRECIO FROM TSERVICIOS WHERE TSERVICIOS.ID=:OLD.SERVICIO_ID; UPDATE TRESERVAS SET PRECIO_FINAL=PRECIO_FINAL-VPRECIO*:OLD.CANTIDAD WHERE LOCALIZADOR=:OLD.RESERVA_LOCALIZADOR; ELSIF UPDATING THEN SELECT PRECIO INTO VPRECIO FROM TSERVICIOS WHERE TSERVICIOS.ID=:OLD.SERVICIO_ID; UPDATE TRESERVAS SET PRECIO_FINAL=PRECIO_FINAL-(VPRECIO*:OLD.CANTIDAD)+(VPRECIO*:NEW.CANTIDAD) WHERE LOCALIZADOR=:OLD.RESERVA_LOCALIZADOR; END IF; END; ________________________________________________________________ TRIGGER3 _______________________________________________________________ create or replace TRIGGER "EX2012ENERO_TEXTRAS" BEFORE INSERT OR UPDATE OF FECHA ON TEXTRAS FOR EACH ROW DECLARE f_ini treservas.fecha%TYPE; f_fin treservas.fecha%TYPE; dias treservas.dias_duracion%TYPE; fecha_incorrecta EXCEPTION; BEGIN if inserting then select fecha,dias_duracion into f_ini,dias from treservas where localizador=:NEW.RESERVA_LOCALIZADOR; f_fin:=f_ini+dias; IF :NEW.fecha<f_ini OR :NEW.fecha>f_fin THEN RAISE fecha_incorrecta; END IF; elsif updating then select fecha,dias_duracion into f_ini,dias from treservas where localizador=:OLD.RESERVA_LOCALIZADOR; f_fin:=f_ini+dias; IF :NEW.fecha<f_ini OR :NEW.fecha>f_fin THEN RAISE fecha_incorrecta; END IF; end if; EXCEPTION WHEN fecha_incorrecta THEN RAISE_APPLICATION_ERROR (-20001,'Fecha de cargo incorrecta'); END; ________________________________________________________________ FUNCION _______________________________________________________________ create or replace FUNCTION FEX2012ENERO ( IDESTA IN NUMBER ) RETURN VARCHAR2 AS CURSOR C1 IS select sum(treservas.precio_final) as p, treservas.cliente_doi from testablecimientos,thabitaciones,thabitaciones_treservas,treservas where testablecimientos.id=thabitaciones.establecimiento_id and testablecimientos.id=idesta and thabitaciones.id=thabitaciones_treservas.habitaciones_id and treservas.localizador=thabitaciones_treservas.reservas_localizador group by treservas.cliente_doi; vdoi tclientes.doi%TYPE; vapellido tclientes.apellidos%TYPE; vnombre tclientes.nombre%TYPE; maximo treservas.precio_final%TYPE; BEGIN maximo:=0; for i in C1 loop if i.p>=maximo then maximo:=i.p; vdoi:=i.cliente_doi; end if; end loop; select nombre, apellidos into vnombre,vapellido from tclientes where doi=vdoi; RETURN vnombre||' '||vapellido; END FEX2012ENERO; ________________________________________________________________ PROCEDIMIENTO ______________________________________________________________ create or replace PROCEDURE EX2012ENERO AS CURSOR C1 IS SELECT ID,NOMBRE,POBLACION,PROVINCIA FROM TESTABLECIMIENTOS; CURSOR C2(COD TESTABLECIMIENTOS.ID%TYPE) IS SELECT ID,COD_HABITACION, PISO, PRECIO FROM THABITACIONES WHERE ESTABLECIMIENTO_ID=COD; CURSOR C3(NUM THABITACIONES.ID%TYPE) IS SELECT TRESERVAS.LOCALIZADOR,TRESERVAS.FECHA,TRESERVAS.DIAS_DURACION,TRESERVAS.PRECIO_FINAL FROM TRESERVAS,THABITACIONES_TRESERVAS WHERE HABITACIONES_ID=NUM AND TRESERVAS.LOCALIZADOR=THABITACIONES_TRESERVAS.RESERVAS_LOCALIZADOR; NUMHABITACIONES NUMBER; TOTALRESERVASHABITACION NUMBER; FECHA_FIN TRESERVAS.FECHA%TYPE; PRECIOEXTRAS NUMBER; VDIAS NUMBER; BEGIN FOR I IN C1 LOOP SELECT COUNT(COD_HABITACION) INTO NUMHABITACIONES FROM THABITACIONES WHERE ESTABLECIMIENTO_ID=I.ID; DBMS_OUTPUT.PUT_LINE(I.NOMBRE||' '||I.POBLACION||' '||I.PROVINCIA||' '||NUMHABITACIONES); FOR J IN C2(I.ID) LOOP SELECT COUNT(RESERVAS_LOCALIZADOR)INTO TOTALRESERVASHABITACION FROM THABITACIONES_TRESERVAS WHERE HABITACIONES_ID=J.ID; DBMS_OUTPUT.PUT_LINE(' -->'||J.COD_HABITACION||' '||J.PISO||' '||J.PRECIO||' '||TOTALRESERVASHABITACION); FOR K IN C3(J.ID) LOOP FECHA_FIN:=K.FECHA+K.DIAS_DURACION; PRECIOEXTRAS:=K.PRECIO_FINAL-J.PRECIO; DBMS_OUTPUT.PUT_LINE(' -->'||K.LOCALIZADOR||' '||K.FECHA||' '||FECHA_FIN||' '||J.PRECIO*K.DIAS_DURACION||' '||PRECIOEXTRAS); END LOOP; END LOOP; END LOOP; END EX2012ENERO; ----------------------------- PRÁCTICA 1 SOLUCIÓN 1)select au_nombre,au_apellido from bd.autores 2)select titulo, precio*ventas_previstas from bd.titulos 3)select titulo, ventas_previstas from bd.titulos where ventas_previstas>=200 and ventas_previstas<=5000 4)select au_nombre,au_apellido from bd.autores where au_telefono is NULL 5) select titulo_id, titulo, ventas_previstas,precio from bd.titulos where tipo='BD' OR tipo='PROG' order by precio DESC 6)select au_nombre from bd.autores where au_telefono like '456%' 7)select avg(precio) from bd.titulos o select SUM(precio)/COUNT(precio) from bd.titulos select avg(precio) from bd.titulos where tipo='BD' o select SUM(precio)/COUNT(precio) from bd.titulos where tipo='BD' 8) select e.ed_id, count(t.titulo_id) from bd.editoriales e, bd.titulos t where t.ed_id=e.ed_id group by e.ed_id 9) select tipo, count(tipo) from bd.titulos group by tipo 10)select tipo, avg(precio) from bd.titulos where f_publicacion>'30/12/2000' group by tipo 11) select tipo, count(*) from bd.titulos group by tipo 12) select tipo, avg(precio) from bd.titulos where precio>35 group by tipo 13) select e.ed_id, avg(t.precio) as media from bd.editoriales e, bd.titulos t where e.ed_id>2 and t.ed_id=e.ed_id group by e.ed_id having avg(t.precio)>60 order by e.ed_id 14) select titulo_id, editor_nombre, editor_apellido, orden_editores from bd.editores e, bd.tituloseditores te where te.titulo_id=1 and e.editor_id=te.editor_id 15) select ed.ed_nombre, edit.editor_nombre, ed.ed_ciudad from bd.editoriales ed, bd.editores edit where ed.ed_ciudad=edit.editor_ciudad 16) select t.titulo, au.au_nombre, au.au_apellido, ta.orden_autores from bd.titulos t, bd.autores au, bd.titulosautores ta where ta.titulo_id=t.titulo_id and ta.au_id=au.au_id and t.tipo='BD' 17) select b1.editor_nombre n , b1.editor_apellido, b2.editor_nombre from bd.editores b1, bd.editores b2 where b1.editor_jefe=b2.editor_id 18) select a1.au_id, a1.au_nombre, a1.au_apellido, a2.au_id, a2.au_nombre, a2.au_apellido from bd.autores a1, bd.autores a2 where a1.au_apellido=a2.au_apellido and a1.au_id <>a2.au_id 19) select ed_nombre from bd.editoriales ed, bd.titulos ti where ed.ed_id=ti.ed_id and ti.tipo='PROG' 20)select titulo, precio from bd.titulos where precio=(select MIN(precio) from bd.titulos) ---------------------------------- ---------------------------------- PRACTICA 4 SOLUCION 1) select nombre_cliente, ciudad_cliente from bd.cliente 2) select cl.nombre_cliente, cl.ciudad_cliente from bd.cliente cl, bd.prestamo p, bd.prestatario pr where pr.nombre_cliente=cl.nombre_cliente and pr.num_prestamo=p.num_prestamo and p.nombre_sucursal='Perryridge' 3) select num_cuenta from bd.cuenta where saldo between 700 and 900 4) select nombre_cliente from bd.cliente where calle_cliente like '%Hill' 5a) select cl.nombre_cliente from bd.cliente cl, bd.prestatario p, bd.prestamo pr, bd.depositante d, bd.cuenta cu where cl.nombre_cliente=p.nombre_cliente and p.num_prestamo=pr.num_prestamo and pr.nombre_sucursal='Perryridge' and cl.nombre_cliente=d.nombre_cliente and d.num_cuenta=cu.num_cuenta and cu.nombre_sucursal='Perryridge' 5b) select cl.nombre_cliente from bd.cliente cl where exists (select * from bd.prestatario p, bd.prestamo pr where cl.nombre_cliente=p.nombre_cliente and p.num_prestamo=pr.num_prestamo and pr.nombre_sucursal='Perryridge') and exists (select *from bd.depositante d, bd.cuenta cu where cl.nombre_cliente=d.nombre_cliente and d.num_cuenta=cu.num_cuenta and cu.nombre_sucursal='Perryridge') 6a) SELECT cl.nombre_cliente FROM bd.cliente cl, BD.depositante d, BD.cuenta cc WHERE cl.nombre_cliente = d.nombre_cliente AND d.num_cuenta = cc.num_cuenta AND cc.nombre_sucursal = 'Perryridge' MINUS SELECT cl.nombre_cliente FROM bd.cliente cl, BD.prestatario p, BD.prestamo po WHERE cl.nombre_cliente = p.nombre_cliente AND p.num_prestamo = po.num_prestamo AND po.nombre_sucursal = 'Perryridge' 6b) select cl.nombre_cliente from bd.cliente cl where not exists (select * from bd.prestatario p, bd.prestamo pr where cl.nombre_cliente=p.nombre_cliente and p.num_prestamo=pr.num_prestamo and pr.nombre_sucursal='Perryridge') and exists (select *from bd.depositante d, bd.cuenta cu where cl.nombre_cliente=d.nombre_cliente and d.num_cuenta=cu.num_cuenta and cu.nombre_sucursal='Perryridge') 7) select distinct cl.nombre_cliente, cl.ciudad_cliente from bd.cliente cl, bd.prestatario p where cl.nombre_cliente=p.nombre_cliente 8) Mal. la subconsulta devuelve 2 registros select cl.nombre_cliente from bd.cliente cl, bd.depositante d, bd.cuenta cu where cl.nombre_cliente=d.nombre_cliente and d.num_cuenta=cu.num_cuenta and cu.nombre_sucursal= (select distinct s.nombre_sucursal from bd.cuenta cu, bd.depositante d, bd.sucursal s, bd.cuenta cu2, bd.depositante d2 where s.nombre_sucursal=cu.nombre_sucursal and cu.num_cuenta=d.num_cuenta and d.nombre_cliente='Hayes') 8 biennnn SELECT distinct cl.nombre_cliente FROM bd.cliente cl, BD.depositante d, BD.cuenta cc WHERE cl.nombre_cliente = d.nombre_cliente AND d.num_cuenta = cc.num_cuenta AND cc.nombre_sucursal IN ( SELECT distinct cc2.nombre_sucursal FROM BD.depositante d2, BD.cuenta cc2 WHERE d2.nombre_cliente = 'Hayes' and d2.num_cuenta = cc2.num_cuenta) 9) select s.nombre_sucursal from bd.sucursal s where s.activo> any (select activo from bd.sucursal s) 10) select s.nombre_sucursal from bd.sucursal s where s.activo>= (select max(activo) from bd.sucursal s) 11)- 12) select * from bd.prestamo order by cantidad DESC 12) select * from bd.prestamo order by num_prestamo ASC 13) select s.nombre_sucursal, avg(cu.saldo) from bd.sucursal s, bd.cuenta cu where cu.nombre_sucursal=s.nombre_sucursal group by s.nombre_sucursal 14) select s.nombre_sucursal, count(d.nombre_cliente) from bd.sucursal s, bd.cuenta cu, bd.cliente cl, bd.depositante d where cu.nombre_sucursal=s.nombre_sucursal and cu.num_cuenta=d.num_cuenta and d.nombre_cliente=cl.nombre_cliente group by s.nombre_sucursal 15) select avg(saldo) from bd.cuenta 16) select s.nombre_sucursal, avg(cu.saldo) from bd.sucursal s, bd.cuenta cu where cu.nombre_sucursal=s.nombre_sucursal having avg(cu.saldo)>700 group by s.nombre_sucursal 17) select s.nombre_sucursal, avg(cu.saldo) from bd.sucursal s, bd.cuenta cu where cu.nombre_sucursal=s.nombre_sucursal group by s.nombre_sucursal order by avg(cu.saldo) DESC 18) select count(*) from bd.cliente 19) select avg(cu.saldo) from bd.cuenta cu, bd.depositante d, bd.cliente cl, bd.depositante d2 where cu.num_cuenta=d.num_cuenta and d.nombre_cliente=cl.nombre_cliente and cl.ciudad_cliente='Harrison' and cl.nombre_cliente=d2.nombre_cliente and d2.num_cuenta!=cu.num_cuenta -------------------------- -------------------------------- PLSQL Lógica fuera +Libertad para cambiar la BD +Lenguaje potente para la lógica negocio -Subir datos: trasiego de red Lógica dentro +Modificaciones más rápidas +Centralización de la lógica -Lenguaje no estándar: no puedo migrar PL/SQL Procedimientos: bloques código (actualizar(3)) No devuelve nada Funciones: r:=actualizar(3); devuelve algo Se pueden invocar desde otro proc o func, o desde el cliente. En SQL execute ASIGNACIÓN := COMPARACIÓN = CONCATENACIÓN || SUBBLOQUES-> bloques anónimos EXCEPCIONES -Lanzar: RAISE .-Recoger: EXCEPTION WHEN_THEN_ ----------ejemplo--------------- CREATE OR REPLACE PROCEDURE HOLA AS --c char; c char:='V'; BEGIN --fecha *12/12/2009* > sysdate dbms_output.put_line('Hola mundo'); declare i number; begin i:= 125; dbms_output.put_line('i vale' || i); --raise NO_DATA_FOUND; dbms_output.put_line('Esto no sale'); exception when NO_DATA_FOUND then dbms_output.put_line('Me ha saltado la excepción'); end; -------------------------------------------- PARÁMETROS -De entrada: si no declaro nada, todos. EJ: name in varchar -De salida: out -Ambos: in out Ejemplín: (al compilar) declare v varchar2; BEGIN HOLA(125,v); dbms_output.put_line(v); END; -- Param con valor x defecto: CREATE OR REPLACE PROCEDURE HOLA(code NUMBER:= 1, name in out varchar2) AS ... ----------------------------------------------------------------------------------------- FUNCIONES TIpos de variables: i number; j char; k varchar2; today date; b boolean; r record; --fila, en C un struct t table; --indexada por clave, parecida a hashmap v array; Tipo (no me acuerdo) cifc concesionarios.cifc%type; (practica coches) TIpo igual q una fila de una tabla reg concesionarios%rowtype; --La tabla no está en mi esquema: micifc BD.concesionarios.cifc%type ÁMBITOS: m number:=20; i number:=500*m; BEGIN declare i number:=25; begin i:=m*i; --La i es la de dentro, la m la de fuera end; RETURN 100; END FUNCTION1; --- declare Y number:=25; begin Y:=m*i; end; Y:= --no se puede ------------------------------------EJ: completo-------------- CREATE OR REPLACE FUNCTION FUNCTION2(micifc BD.concesionarios.cifc%type) RETURN NUMBER AS RETURN BD.concesionarios.cifc%type AS cifc concesionarios.cifc%type; reg concesionarios%rowtype; m number:=20; i number:=500*m; BEGIN declare Y number:=25; begin Y:=m*i; end; RETURN 100; END FUNCTION1; ----------------------------------------------------------------------- Operadores: primero los AND, luego los OR--> si no, paréntesis IF ____ THEN ____ ELSE____ END IF; IFS encadenados: elsif --------------------------------EJ_----------- create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number:=500*m; BEGIN if i>100 then return 25; end if; if m<100 then return 25; elsif m>200 then return 25; else return 12; end if; RETURN 100; END FUNCTION1; ------------------------------------------------------------------------ BUCLE create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number; BEGIN i:=0; loop i:=i+1; dbms_output.put_line(i); exit when i>10; end loop; RETURN 100; END FUNCTION1; tb loop exit when___ --------------------------------- otro bucle: i:=0; while i<11 loop dbms_output.put_line(i); end loop; ---- for: for i in 0..10 loop dbms_output.put_line(i); end loop; --------------- CON BASES DE DATOS create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number; BEGIN select count(*) into i from bd.ventas; dbms_output.put_line('Hay '||i||' ventas en la bd de coches'); return i; END FUNCTION1; -- OJO: no poner select * from... into... --> devolvería filas ERR BEGIN select count(*) into i from bd.ventas where color='amarillento azulado'; dbms_output.put_line('Hay '||i||' ventas en la bd de coches'); return i; END FUNCTION1; devuelve cero ventas ---- Por el medio puedo poner sentencias SQL: insert into ___ (__) values ___, update, delete... Poner después commit; rollback; para la transacción Para procesar x ej. ventas: select * into i from bd.ventas; for r in r.cifc r.dni end loop; Hay que declarar un cursor: cursor c1 is select * from bd.ventas; create or replace FUNCTION FUNCTION1 RETURN NUMBER AS cursor c1 is select * from bd.ventas; BEGIN for r in c1 loop dbms_output.put_line('cifc '||r.cifc); dbms_output.put_line('dni '||r.dni); end loop; return 1; END FUNCTION1; -------------------------------------- Con el for es simple, si no hay que procesar el cursor: abrirlo,... fetch c1 into___(moverlo), cerrarlo create or replace FUNCTION FUNCTION1 RETURN NUMBER AS cursor c1 is select cifc,codcoche from bd.ventas; v_cifc bd.ventas.cifc%type; v_codcoche bd.ventas.codcoche%type; BEGIN for r in c1 loop dbms_output.put_line('cifc '||r.cifc); end loop; open c1; loop fetch c1 into v_cifc, v_codcoche; exit when c1%NOTFOUND; dbms_output.put_line('cifc '||v_cifc); dbms_output.put_line('dni '||v_codcoche); end loop; close c1; return 1; END FUNCTION1; ------------------------------------------------- PRACTICA 7 EJ3 create or replace FUNCTION FUNCTION1 RETURN NUMBER AS -- cursor c1 is select cifc,codcoche from bd.ventas; --v_cifc bd.ventas.cifc%type; -- v_codcoche bd.ventas.codcoche%type; cursor c2 is select cantidad from BD.distribucion; i number; BEGIN i:=0; for r in c2 loop if(r.cantidad>i) then i:=r.cantidad; end if; --dbms_output.put_line('cifc '||r.cifc); end loop; dbms_output.put_line('max '||i); return i; END FUNCTION1; ----------- EJ4 CREATE OR REPLACE PROCEDURE EJ4P7 AS cursor c is select d.cifc, sum(d.cantidad) from p7_distribucion d group by d.cifc; v_cifc p7_distribucion.cifc%type; v_cantidad p7_distribucion.cantidad%type; BEGIN open c; fetch c into v_cifc, v_cantidad; while c%FOUND loop dbms_output.put_line(v_cifc||'tiene'||v_cantidad); fetch c into v_cifc, v_cantidad; end loop; close c; END EJ4P7; o bien CREATE OR REPLACE PROCEDURE EJ42 AS cursor c is select d.cifc, sum(d.cantidad) as total from p7_distribucion d group by d.cifc; v_cifc p7_distribucion.cifc%type; v_cantidad p7_distribucion.cantidad%type; BEGIN for r in c loop dbms_output.put_line(r.cifc||' tiene '||r.total); end loop; END EJ42; --------------------------------- ----------------------- PROCEDIMIENTOS PROCEDIMIENTOS ej4) create or replace PROCEDURE EJ4P7 AS cursor c is select d.cifc, sum(d.cantidad) from p7_distribucion d group by d.cifc; v_cifc p7_distribucion.cifc%type; v_cantidad p7_distribucion.cantidad%type; BEGIN open c; fetch c into v_cifc, v_cantidad; while c%FOUND loop dbms_output.put_line(v_cifc||'tiene'||v_cantidad); fetch c into v_cifc, v_cantidad; end loop; close c; END EJ4P7; p5) create or replace PROCEDURE EJ5P7 AS vventas number; coches number; concesionarios number; marcas number; clientes number; BEGIN select count(*) into vventas from p7_ventas; select count(*) into coches from p7_coches; select count(*) into marcas from p7_marcas; select count(*) into concesionarios from p7_concesionarios; select count(*) into clientes from p7_clientes; delete from p7ej5; insert into p7ej5(totalventas,totalcoches,totalmarcas,totalconcesionarios,totalclientes) values(vventas,coches,marcas,concesionarios,clientes); END EJ5P7; p6) create or replace PROCEDURE EJ6P7 AS cursor c1 is select * from p7_clientes; BEGIN delete from historico; for row in c1 loop begin insert into historico(dni,nombre,apellido, ciudad) values(row.dni,row.nombre,row.apellido,row.ciudad); dbms_output.put_line('Insertado'|| row.dni); exception when DUP_VAL_ON_INDEX then update historico set nombre=row.nombre, apellido=row.apellido, ciudad=row.ciudad where dni=row.dni; dbms_output.put_line('eXcp'); end; end loop; END EJ6P p7) create or replace PROCEDURE EJERCICIO7P7 AS cursor c1 is select cl.dni, cl.nombre, cl.apellido, count(*) as cantidad from p7_ventas v, p7_clientes cl where v.dni=cl.dni group by cl.dni, cl.nombre,cl.apellido; BEGIN delete from compras; for r in c1 loop begin insert into compras(dni, nombre, apellido, cochescomprados) values(r.dni, r.nombre, r.apellido, r.cantidad); exception when DUP_VAL_ON_INDEX then update compras set nombre=r.nombre, apellido=r.apellido, cochescomprados=r.cantidad where dni= r.dni; end; end loop; NULL; END EJERCICIO7P7; p8) create or replace PROCEDURE EJER8(param in p7_ventas.cifc%type, res out number) AS BEGIN select count(*) into res from p7_ventas where cifc=param group by cifc; END EJER8; --------------------------------------- --------------------------------------- VISTAS VISTAS EJERCICIO 1 La consulta sería: select co.cifc, co.ciudadc, co.nombrec,count(d.cantidad) from p7_concesionarios co, p7_distribucion d where d.cifc=co.cifc group by co.cifc, co.nombrec, co.ciudadc Para crear la vista: poner nombre al count EJERCICIO 2-ROWNUM select * from (select * from view1 order by tptal desc) where rownum < 4 EJERCICIO 3 Consulta para los clientes y Consulta para cada cliente, obtener coches CREATE OR REPLACE PROCEDURE P8EJ3 AS cursor c1 is select dni, nombre, apellido from p7_clientes; cursor c2 (param p7_clientes.dni%type) is select co.codcoche, co.nombrech, co.modelo, v.color from p7_ventas v , p7_coches co where v.codcoche=co.codcoche and v.dni=param; BEGIN for cliente in c1 loop dbms_output.put_line('Cliente '||cliente.nombre||' '||cliente.apellido||' '); for coche in c2 (cliente.dni) loop dbms_output.put_line(' Coches: '||coche.codcoche||' '||coche.nombrech||' '||coche.modelo||' '||coche.color||' '); end loop; end loop; END P8EJ3; Para que los q no compran no salgan: en cursor1 cambiar la consulta: join con ventas CREATE OR REPLACE PROCEDURE P8EJ3 AS cursor c1 is select distintc cl.dni, cl.nombre, cl.apellido from p7_clientes cl, p7_ventas v where v.dni=cl.dni; cursor c2 (param p7_clientes.dni%type) is select co.codcoche, co.nombrech, co.modelo, v.color from p7_ventas v , p7_coches co where v.codcoche=co.codcoche and v.dni=param; BEGIN for cliente in c1 loop dbms_output.put_line('Cliente '||cliente.nombre||' '||cliente.apellido||' '); for coche in c2 (cliente.dni) loop dbms_output.put_line(' Coches: '||coche.codcoche||' '||coche.nombrech||' '||coche.modelo||' '||coche.color||' '); end loop; end loop; END P8EJ3; EJERCICIIO 4 CREATE OR REPLACE PROCEDURE P8EJ4(param in p7_clientes.dni%type) AS cursor c1(param in p7_clientes.dni%type) is select co.codcoche, co.nombrech, co.modelo, v.color from p7_ventas v , p7_coches co where v.codcoche=co.codcoche and v.dni=param; v_nombre varchar(40); v_apellido varchar(40); BEGIN select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=param; dbms_output.put_line('Cliente '|| v_nombre||' '||v_apellido); for coche in c1(param) loop dbms_output.put_line(' Coches: '||coche.codcoche||' '||coche.nombrech||' '||coche.modelo||' '||coche.color||' '); end loop; END P8EJ4; Para que no casque si metemos un dni q no existe: excepciones, para poner msj en nuestra propia excepcion--> aplication-error CREATE OR REPLACE PROCEDURE P8EJ4(param in p7_clientes.dni%type) AS cursor c1(param in p7_clientes.dni%type) is select co.codcoche, co.nombrech, co.modelo, v.color from p7_ventas v , p7_coches co where v.codcoche=co.codcoche and v.dni=param; v_nombre varchar(40); v_apellido varchar(40); MAL_DNI exception; ERROR_GENERICO exception; BEGIN select nombre, apellido into v_nombre, v_apellido from p7_clientes where dni=param; dbms_output.put_line('Cliente '|| v_nombre||' '||v_apellido); for coche in c1(param) loop dbms_output.put_line(' Coches: '||coche.codcoche||' '||coche.nombrech||' '||coche.modelo||' '||coche.color||' '); end loop; exception when NO_DATA_FOUND then raise APPLICATION_ERROR(-20001, 'El DNI no existe'); when OTHERS then raise ERROR_GENERICO; END P8EJ4; Está sin acabar! ---------------------------- ----------------------- PLSQL ii Lógica fuera +Libertad para cambiar la BD +Lenguaje potente para la lógica negocio -Subir datos: trasiego de red Lógica dentro +Modificaciones más rápidas +Centralización de la lógica -Lenguaje no estándar: no puedo migrar PL/SQL Procedimientos: bloques código (actualizar(3)) No devuelve nada Funciones: r:=actualizar(3); devuelve algo Se pueden invocar desde otro proc o func, o desde el cliente. En SQL execute ASIGNACIÓN := COMPARACIÓN = CONCATENACIÓN || SUBBLOQUES-> bloques anónimos EXCEPCIONES -Lanzar: RAISE .-Recoger: EXCEPTION WHEN_THEN_ ----------ejemplo--------------- CREATE OR REPLACE PROCEDURE HOLA AS --c char; c char:='V'; BEGIN --fecha *12/12/2009* > sysdate dbms_output.put_line('Hola mundo'); declare i number; begin i:= 125; dbms_output.put_line('i vale' || i); --raise NO_DATA_FOUND; dbms_output.put_line('Esto no sale'); exception when NO_DATA_FOUND then dbms_output.put_line('Me ha saltado la excepción'); end; -------------------------------------------- PARÁMETROS -De entrada: si no declaro nada, todos. EJ: name in varchar -De salida: out -Ambos: in out Ejemplín: (al compilar) declare v varchar2; BEGIN HOLA(125,v); dbms_output.put_line(v); END; -- Param con valor x defecto: CREATE OR REPLACE PROCEDURE HOLA(code NUMBER:= 1, name in out varchar2) AS ... ----------------------------------------------------------------------------------------- FUNCIONES TIpos de variables: i number; j char; k varchar2; today date; b boolean; r record; --fila, en C un struct t table; --indexada por clave, parecida a hashmap v array; Tipo (no me acuerdo) cifc concesionarios.cifc%type; (practica coches) TIpo igual q una fila de una tabla reg concesionarios%rowtype; --La tabla no está en mi esquema: micifc BD.concesionarios.cifc%type ÁMBITOS: m number:=20; i number:=500*m; BEGIN declare i number:=25; begin i:=m*i; --La i es la de dentro, la m la de fuera end; RETURN 100; END FUNCTION1; --- declare Y number:=25; begin Y:=m*i; end; Y:= --no se puede ------------------------------------EJ: completo-------------- CREATE OR REPLACE FUNCTION FUNCTION2(micifc BD.concesionarios.cifc%type) RETURN NUMBER AS RETURN BD.concesionarios.cifc%type AS cifc concesionarios.cifc%type; reg concesionarios%rowtype; m number:=20; i number:=500*m; BEGIN declare Y number:=25; begin Y:=m*i; end; RETURN 100; END FUNCTION1; ----------------------------------------------------------------------- Operadores: primero los AND, luego los OR--> si no, paréntesis IF ____ THEN ____ ELSE____ END IF; IFS encadenados: elsif --------------------------------EJ_----------- create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number:=500*m; BEGIN if i>100 then return 25; end if; if m<100 then return 25; elsif m>200 then return 25; else return 12; end if; RETURN 100; END FUNCTION1; ------------------------------------------------------------------------ BUCLE create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number; BEGIN i:=0; loop i:=i+1; dbms_output.put_line(i); exit when i>10; end loop; RETURN 100; END FUNCTION1; tb loop exit when___ --------------------------------- otro bucle: i:=0; while i<11 loop dbms_output.put_line(i); end loop; ---- for: for i in 0..10 loop dbms_output.put_line(i); end loop; --------------- CON BASES DE DATOS create or replace FUNCTION FUNCTION1 RETURN NUMBER AS m number:=20; i number; BEGIN select count(*) into i from bd.ventas; dbms_output.put_line('Hay '||i||' ventas en la bd de coches'); return i; END FUNCTION1; -- OJO: no poner select * from... into... --> devolvería filas ERR BEGIN select count(*) into i from bd.ventas where color='amarillento azulado'; dbms_output.put_line('Hay '||i||' ventas en la bd de coches'); return i; END FUNCTION1; devuelve cero ventas ---- Por el medio puedo poner sentencias SQL: insert into ___ (__) values ___, update, delete... Poner después commit; rollback; para la transacción Para procesar x ej. ventas: select * into i from bd.ventas; for r in r.cifc r.dni end loop; Hay que declarar un cursor: cursor c1 is select * from bd.ventas; create or replace FUNCTION FUNCTION1 RETURN NUMBER AS cursor c1 is select * from bd.ventas; BEGIN for r in c1 loop dbms_output.put_line('cifc '||r.cifc); dbms_output.put_line('dni '||r.dni); end loop; return 1; END FUNCTION1; -------------------------------------- Con el for es simple, si no hay que procesar el cursor: abrirlo,... fetch c1 into___(moverlo), cerrarlo create or replace FUNCTION FUNCTION1 RETURN NUMBER AS cursor c1 is select cifc,codcoche from bd.ventas; v_cifc bd.ventas.cifc%type; v_codcoche bd.ventas.codcoche%type; BEGIN for r in c1 loop dbms_output.put_line('cifc '||r.cifc); end loop; open c1; loop fetch c1 into v_cifc, v_codcoche; exit when c1%NOTFOUND; dbms_output.put_line('cifc '||v_cifc); dbms_output.put_line('dni '||v_codcoche); end loop; close c1; return 1; END FUNCTION1; ------------------------------------------------- PRACTICA 7 EJ3 create or replace FUNCTION FUNCTION1 RETURN NUMBER AS -- cursor c1 is select cifc,codcoche from bd.ventas; --v_cifc bd.ventas.cifc%type; -- v_codcoche bd.ventas.codcoche%type; cursor c2 is select cantidad from BD.distribucion; i number; BEGIN i:=0; for r in c2 loop if(r.cantidad>i) then i:=r.cantidad; end if; --dbms_output.put_line('cifc '||r.cifc); end loop; dbms_output.put_line('max '||i); return i; END FUNCTION1; ----------- EJ4 CREATE OR REPLACE PROCEDURE EJ4P7 AS cursor c is select d.cifc, sum(d.cantidad) from p7_distribucion d group by d.cifc; v_cifc p7_distribucion.cifc%type; v_cantidad p7_distribucion.cantidad%type; BEGIN open c; fetch c into v_cifc, v_cantidad; while c%FOUND loop dbms_output.put_line(v_cifc||'tiene'||v_cantidad); fetch c into v_cifc, v_cantidad; end loop; close c; END EJ4P7; o bien CREATE OR REPLACE PROCEDURE EJ42 AS cursor c is select d.cifc, sum(d.cantidad) as total from p7_distribucion d group by d.cifc; v_cifc p7_distribucion.cifc%type; v_cantidad p7_distribucion.cantidad%type; BEGIN for r in c loop dbms_output.put_line(r.cifc||' tiene '||r.total); end loop; END EJ42; ****Ej, proc saque color del coche más vendido CREATE OR REPLACE PROCEDURE EJINVP7 AS v_color p7_ventas.colot%type; BEGIN select distinct color into v_color from p7_ventas v where( select count(*) from p7_ventas where color=v_color ) =(select max(count(*)) from p7_ventas group by color ) dbms_output.put_line('El color de coche más vendido es'||v_color||' '); NULL; END EJINVP7; ****Nº COCHES DE CADA CLIENTE CREATE OR REPLACE PROCEDURE EJ4BP7 AS cursor c1 is select dni, count(*) as cont from p7_ventas v group by dni; BEGIN for r in c1 loop dbms_output.put_line('Cliente ' ||r.dni|| ' compró '|| r.cont); end loop; NULL; END EJ4BP7; -> Con cursor moviendo CREATE OR REPLACE PROCEDURE EJ4BP7 AS cursor c1 is select dni, count(*) as cont from p7_ventas v group by dni; v_dni p7_ventas.dni%type; v_cont number; --v_row p7_ventas%rowtype; BEGIN open c1; loop fetch c1 into v_dni, v_cont; exit when c1%NOTFOUND; dbms_output.put_line('Cliente ' ||v_dni|| ' compró '|| v_cont); end loop; close c1; NULL; END EJ4BP7; Cosas de CURSORES FOUND, NOTFOUND, ROWCOUNT,ISOPEN El cursor automáticamente se llama SQL : SQL%FOUND, etc EJERCICIO 5 Para rellna CREATE OR REPLACE PROCEDURE EJ5P7 AS vventas number; coches number; concesionarios number; marcas number; clientes number; BEGIN select count(*) into vventas from p7_ventas; select count(*) into coches from p7_coches; select count(*) into marcas from p7_marcas; select count(*) into concesionarios from p7_concesionarios; select count(*) into clientes from p7_clientes; insert into p7ej5(totalventas,totalcoches,totalmarcas,totalconcesionarios,totalclientes) values(vventas,coches,marcas,concesionarios,clientes); END EJ5P7; --Bien, pero rellena una fila de cada vez, y mete la misma fila --> cerrar la transacción (?) Metemos detrás de los select count: delete from p7ej5; EJERCICIO 6 create table historicoclientes as select * from p7_clientes; --delete from historico; El procedimiento: CREATE OR REPLACE PROCEDURE EJ6P7 AS cursor c1 is select * from p7_clientes; BEGIN for row in c1 loop insert into historico(dni,nombre,apellido, ciudad) values(row.dni,row.nombre,row.apellido,row.ciudad); end loop; END EJ6P7; Para que no duplique : delete antes del FOR ----------EXCEPCIONES En el declare como si fuera una variable: jkasdjfljsakl exception; Para lanzarlas raise Para recogerlas: exception when then Msj predefinido= Exc de usuario lanzada, y un numero fijo Bloques anidados igual que java, se propaga hasta un bloq que la trate *pág. 63 de los apuntes EJERCICIO 7 CREATE OR REPLACE PROCEDURE EJERCICIO7P7 AS cursor c1 is select cl.dni, cl.nombre, cl.apellido, count(*) as cantidad from p7_ventas v, p7_clientes cl where v.dni=cl.dni group by cl.dni, cl.nombre,cl.apellido; BEGIN delete from compras; for r in c1 loop begin insert into compras(dni, nombre, apellido, cochescomprados) values(r.dni, r.nombre, r.apellido, r.cantidad); exception when DUP_VAL_ON_INDEX then update compras set nombre=r.nombre, apellido=r.apellido, cochescomprados=r.cantidad where dni= r.dni; end; end loop; NULL; END EJERCICIO7P7; EJERCICIO 8 CREATE OR REPLACE PROCEDURE EJER8(param in p7_ventas.cifc%type, res out number) AS BEGIN select count(*) into res from p7_ventas where cifc=param group by cifc; END EJER8; EJERCICIO 8 POR MI CREATE OR REPLACE PROCEDURE EJ8P7 AS cursor c1 is select co.cifc, count(*) as cantidad from p7_ventas v, p7_concesionarios co where v.cifc=co.cifc group by co.cifc; BEGIN for r in c1 loop dbms_output.put_line('Concesionario'||r.cifc||' vendio '||r.cantidad||' '); end loop; NULL; END EJ8P7; EJERCICIO 8 funcion CREATE OR REPLACE FUNCTION EJ8(param in p7_ventas.cifc&type) RETURN NUMBER AS BEGIN select count(*) into res from p7_ventas where cifc=param group by cifc; RETURN res; END EJ8; EJERCICIO 9- PAAQUETE Como un jar con muchas cosas, funciones, procedimientos...Tiene una parte pública y una privada, se parece a una clase en C++ La parte púb lica: defino lo que pueden ver los users del paquete: defino las cabeceras, variables, cursores, excepciones, tipos de datos... La implementación va en el cuerpo del paquete, aparte. Dif; no puedo instanciar objs de paquete, se crea una inst automáticamente para el usuario al iniciar la sesión Dura lo que dura la sesión del user. Para ampliarlo es <nombre_paquete>.<nombre_funcion> Si tiene variables privadas, mantiene estados para ellas en cada sesión.

--------------------------------------------- ---------------------------------------------- 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; ----------------------------------------------------------- ----------------------------------------------------------