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