1. Mostrar 'Hola mundo' por pantalla.
begin
DBMS_OUTPUT.PUT_LINE('Hola mundo');
end;
/
2. Declarar una variable numerica y mostrar si es mayor de 10 o no.
declare
mi_numero number(8) := 12;
begin
if (mi_numero > 10) then
DBMS_OUTPUT.PUT_LINE('Mi numero es mayor de 10');
else
DBMS_OUTPUT.PUT_LINE('Mi numero es menor de 10');
end if;
end;
/
3. Declarar una variable numerica y pedir su valor y mostrarlo.
declare
mi_numero number(8) := №
begin
DBMS_OUTPUT.PUT_LINE('el valor introducido es ' || mi_numero);
end;
/
4. Mostrar los numeros del 1 al 100 con un while.
declare
i number(8) := 1;
begin
while (i<=10)
loop
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
end loop;
end;
/
5. Mostrar los numeros del 1 al 100 con un for.
begin
for i in 1..10
loop
DBMS_OUTPUT.PUT_LINE(i);
end loop;
end;
/
--POR SI LO PIDE AL REVES
-- De 10 a 1
begin
for i in reverse 1..10
loop
DBMS_OUTPUT.PUT_LINE(i);
end loop;
end;
/
6. Mostrar los numeros del 1 al 100 con un loop.
declare
i number(8) := 1;
begin
loop
DBMS_OUTPUT.PUT_LINE(i);
exit when i=10;
i := i+1;
end loop;
end;
/
7. Mostrar el nombre de un cliente dado su codigo.
declare
v_codigocliente clientes.codigocliente%type := &codigo;
v_nombrecliente clientes.nombrecliente%type;
begin
select nombrecliente into v_nombrecliente
from clientes
where codigocliente = v_codigocliente;
DBMS_OUTPUT.PUT_LINE('El nombre del cliente es ' || v_nombrecliente);
end;
/
8. Mostrar el precioVenta y la gama de un producto dado su codigo.
declare
v_codigoproducto productos.codigoproducto%type := &codigo;
v_nombreproducto productos.nombre%type;
v_gamaproducto productos.gama%type;
begin
select nombre, gama into v_nombreproducto, v_gamaproducto
from productos
where codigoproducto = v_codigoproducto;
DBMS_OUTPUT.PUT_LINE('El nombre del producto es ' || v_nombreproducto
|| ' y su gama es ' || v_gamaproducto);
end;
/
9. Mostrar toda la informacion de un pedido dado su codigo (fechaEsperada, fechaEntrega,
fechapedido, estado, comentarios)
declare
v_codigopedido pedidos.codigopedido%type := &codigo;
v_pedido pedidos%rowtype;
begin
select * into v_pedido
from pedidos
where codigopedido = v_codigopedido;
DBMS_OUTPUT.PUT_LINE('La fecha de pedido es ' || v_pedido.fechapedido
|| ', la fecha esperada es ' || v_pedido.fechaesperada
|| ', la fecha de entrega es ' || v_pedido.fechaentrega
|| ', el estado es ' || v_pedido.estado
|| ' y los comentarios son ' || v_pedido.comentarios
);
end;
/
10. Realizar una función que me devuelva la suma de pagos que ha realizado. Pasa el codigo
por parametro.
create or replace function Pagos_cliente(v_codigocliente clientes.codigocliente%type)
return Number
as
v_sumapagos pagos.cantidad%type := 0;
begin
select sum(cantidad) into v_sumapagos
from pagos
where codigocliente = v_codigocliente;
return v_sumapagos;
end;
/
declare
v_codigocliente clientes.codigocliente%type := &codigo;
v_suma pagos.cantidad%type;
begin
v_suma := Pagos_cliente(v_codigocliente);
DBMS_OUTPUT.PUT_LINE('La suma de pagos es ' || v_suma);
end;
/
11. Realizar un método o procedimiento que muestre el total en euros de un pedido, pasale el
codigo por parametro.
create or replace procedure total_pedido(v_codigopedido pedidos.codigopedido%type)
as
v_total number(8) := 0;
begin
select sum(dp.cantidad * dp.PRECIOUNIDAD) into v_total
from pedidos p, detallepedidos dp
where p.codigopedido = dp.codigopedido and p.codigopedido = v_codigopedido;
DBMS_OUTPUT.PUT_LINE('El pedido total es ' || v_total);
end;
/
declare
v_codigopedido pedidos.codigopedido%type := &codigo;
begin
total_pedido(v_codigopedido);
end;
/
12. Mostrar el nombre de un cliente dado su codigo. Controla en caso de que no se encuentre,
mostrando un mensaje por ejemplo.
declare
v_codigocliente clientes.codigocliente%type := &codigo;
v_nombrecliente clientes.nombrecliente%type;
begin
select nombrecliente into v_nombrecliente
from clientes
where codigocliente = v_codigocliente;
DBMS_OUTPUT.PUT_LINE('El nombre del cliente es ' || v_nombrecliente);
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('No existe el cliente');
end;
/
13. Realizar una función que me devuelva la suma de pagos que ha realizado. Pasa el codigo
por parametro. Controla en caso de que no se encuentre, en ese caso devuelve un -1.
create or replace function Pagos_cliente(v_codigocliente clientes.codigocliente%type)
return Number
as
v_sumapagos pagos.cantidad%type := 0;
begin
select sum(cantidad) into v_sumapagos
from pagos
where codigocliente = v_codigocliente;
if v_sumapagos is null then
raise no_data_found;
else
return v_sumapagos;
end if;
exception
when no_data_found then
return -1;
end;
/
declare
v_codigocliente clientes.codigocliente%type := &codigo;
v_suma pagos.cantidad%type;
begin
v_suma := Pagos_cliente(v_codigocliente);
if v_suma = -1 then
DBMS_OUTPUT.PUT_LINE('El cliente no existe');
else
DBMS_OUTPUT.PUT_LINE('La suma de pagos es ' || v_suma);
end if;
end;
/
14. Realizar un método o procedimiento que muestre el total en euros de un pedido, pasale el
codigo por parametro. Controla en caso de que no se encuentre, en ese caso devuelve un 0.
Pasale otro parametro, si supera ese limite, lanzaremos una excepcion propia y devolveremos
un 0.
create or replace function total_pedido_func(v_codigopedido pedidos.codigopedido%type,
v_limite number)
return number
as
v_total number(8) := 0;
limite_superado exception;
begin
select sum(dp.cantidad * dp.PRECIOUNIDAD) into v_total
from pedidos p, detallepedidos dp
where p.codigopedido = dp.codigopedido and p.codigopedido = v_codigopedido;
if v_total is null then
raise no_data_found;
else
if v_limite < v_total then
raise limite_superado;
else
return v_total;
end if;
end if;
exception
when no_data_found then
return -1;
when limite_superado then
DBMS_OUTPUT.PUT_LINE('Limite superado');
return 0;
end;
/
declare
v_codigopedido pedidos.codigopedido%type := &codigo;
v_total number(8);
v_limite number(8) := &limite;
begin
v_total := total_pedido_func(v_codigopedido , v_limite);
if v_total = -1 then
DBMS_OUTPUT.PUT_LINE('no existe el pedido');
else
DBMS_OUTPUT.PUT_LINE('El pedido total es ' || v_total);
end if;
end;
/
19. Crear un cursor para ver todos los clientes que no hayan hecho pagos. Hazlo con un loop.
declare
v_nombrecliente clientes.nombrecliente%type;
cursor clientes_sin_pagos_cursor is
select nombrecliente
from clientes c
where not exists(select codigocliente from pagos where codigocliente = c.codigocliente);
begin
open clientes_sin_pagos_cursor;
loop
fetch clientes_sin_pagos_cursor into v_nombrecliente;
exit when clientes_sin_pagos_cursor%notfound;
dbms_output.put_line(v_nombrecliente);
end loop;
close clientes_sin_pagos_cursor;
end;
/
20. Crear un cursor para ver todos los clientes que no hayan hecho pagos. Hazlo con un for.
declare
cursor clientes_sin_pagos_cursor is
select nombrecliente
from clientes c
where not exists(select codigocliente from pagos where codigocliente = c.codigocliente);
begin
for registro in clientes_sin_pagos_cursor loop
dbms_output.put_line(registro.nombrecliente);
end loop;
end;
/
20. Crear un cursor para ver todos los productos pedidos en un pedido. Muestra la cantidad
tambien.
create or replace procedure mostrarProductosPedido(p_codigopedido
pedidos.codigopedido%type)
as
cursor prod_pedido is
select p.nombre, dp.cantidad
from productos p, detallepedidos dp
where p.codigoproducto = dp.codigoproducto
and dp.CODIGOPEDIDO = p_codigopedido;
begin
for registro in prod_pedido loop
dbms_output.put_line('Se ha pedido del producto llamado ' || registro.nombre || ': ' ||
registro.cantidad || ' unidades');
end loop;
end;
/
declare
p_codigopedido pedidos.codigopedido%type := &codigo;
begin
mostrarProductosPedido(p_codigopedido);
end;
/
21. Crear un cursor para ver todos los empleados de un jefe.
dbms_output.put_line('El jefe llamado ' || v_nombre_jefe || ' tiene a cargo a los siguientes
empleados: ');
for registro in empleados_jefe loop
dbms_output.put_line(registro.nombre_empleado);
end loop;
exception
when no_data_found then
dbms_output.put_line('No existe ese jefe');
end;
/
declare
p_codigojefe empleados.codigojefe%type := &codigo;
begin
mostrarEmpleadosJefe(p_codigojefe);
end;
/
22. Trigger para actualizar el stock de productos despues de insertar en la tabla de
detallepedidos.
create or replace trigger productos_actualizar_stock
after insert on detallepedidos FOR EACH ROW
declare
begin
update productos
set cantidadenstock = cantidadenstock - :new.cantidad
where codigoproducto = :new.codigoproducto;
end;
/
23 .Crea un trigger que al actualizar la columna fechaentrega de pedidos la compare con la
fechaespera.
Si fechaentrega es menor que fechaespera añadir a los comentarios 'Pedido entregado antes
de lo esperado'.
Si fechaentrega es mayor que fechaespera añadir a los comentarios 'Pedido entregado con
retraso'.
update pedidos set fechaentrega = to_date('01/01/05') where codigopedido = 1;
create or replace trigger actualizar_comentarios_pedidos
before update of fechaentrega on pedidos FOR EACH ROW
declare
begin
if :new.fechaentrega is not null then
if :new.fechaentrega > :old.fechaesperada then
:new.comentarios := :old.comentarios || ' pedido entregado con retraso';
else
:new.comentarios := :old.comentarios || ' pedido entregado antes de los esperado';
end if;
end if;
end;
/
select codigoproducto, nombre, cantidadenstock from productos where codigoproducto = 'FR-4';
insert into detallepedidos values(1, 'FR-4', 10, 10, 6);
24. Modifica el anterior pero solo se ejecute si fechaentrega es mayor que fechaespera.
create or replace trigger actualizar_comentarios_pedidos
before update of fechaentrega on pedidos FOR EACH ROW
when (new.fechaentrega is not null and new.fechaentrega > old.fechaesperada)
declare
begin
:new.comentarios := :old.comentarios || ' pedido entregado con retraso';
end;
/
update pedidos set fechaentrega = to_date('01/01/15') where codigopedido = 1;
25. Modifica el trigger del video 11 si el stock que vamos a modificar se queda a cero o menor,
devolver una excepcion ORA.
create or replace trigger productos_actualizar_stock
before insert on detallepedidos FOR EACH ROW
declare
v_stock_actual productos.cantidadenstock%type;
begin
select cantidadenstock into v_stock_actual
from productos
where codigoproducto = :new.codigoproducto;
if v_stock_actual - :new.cantidad > 0 then
update productos
set cantidadenstock = cantidadenstock - :new.cantidad
where codigoproducto = :new.codigoproducto;
else
raise_application_error(-20001, 'No hay suficiente stock');
end if;
end;
/
select codigoproducto, nombre, cantidadenstock from productos where codigoproducto = 'FR-4';
insert into detallepedidos values(3, 'FR-4', 1, 10, 6);
PROCEDIMIENTOS
1. Crea un procedimiento que declare una variable y muestra por pantalla su valor.
delimiter $$
create procedure mostrarVariable()
begin
declare n int;
set n = 5;
select n;
end$$
delimiter ;
call mostrarVariable();
2. Declara una función que dandoles dos numeros devuelva la suma.
delimiter $$
create function sumaNumeros(num1 int, num2 int)
returns int
begin
declare suma int;
set suma = num1 + num2;
return suma;
end$$
delimiter ;
select sumaNumeros(1,2);
select sumaNumeros(1,1);
3. Haz lo mismo que el anterior ejercicio pero con un procedimiento.
delimiter $$
create procedure sumaNumeros(num1 int, num2 int)
begin
declare suma int;
set suma= num1 + num2;
select suma;
end$$
delimiter ;
call sumaNumeros(1,1);
PROCEDIMIENTOS JARDINERIA
4. Crea un procedimiento que muestre el nombre de un cliente a partir de su codigo de cliente.
delimiter $$
create procedure getNameClient(p_codigocliente int)
begin
declare nombre varchar(40);
select nombrecliente into nombre
from clientes
where codigocliente = p_codigocliente;
select nombre;
end$$
delimiter ;
call getNameClient(1);
5. Crea una funcion que devuelva el numero de clientes que tenemos.
delimiter $$
create function numClientes()
returns int
begin
declare numeroClientes int;
select count(*) into numeroClientes
from clientes;
return numeroClientes;
end$$
delimiter ;
select numClientes() as num_clientes;
6. Crea una función que devuelva el numero de clientes que sea de un pais. Este pais sera
pasado por parametro.
delimiter $$
create function numClientesNacionalidad(p_nacionalidad varchar(40))
returns int
begin
declare numeroClientes int;
select count(*) into numeroClientes
from clientes
where pais = p_nacionalidad;
return numeroClientes;
end$$
delimiter ;
select numClientesNacionalidad('USA') as num_clientes_nacionalidad;
PLSQL BASE DE DATOS CONCESIONARIO
15. crea una funcion a la que le pasaremos como parametros de entrada: MATRICULA,
NUEVO_PRECIO_COMPRA. lA FUNCION MODIFICARA LOS DATOS DEL COCHE QUE
TENGA LA MATRICULA INTRODUCIDA ACTUALIZANDO EL PRECIO_COMPRA DE LA
SIGUIENTE FORMA:
-Si precio_compra es nulo--> hacer un update en el campo precio_compra asignandole el valor
de nuevo_precio_compra
-Si no--> hacer un update en el campo precio_compra asignandole el valor de
precio_compra+(precio_compra-nuevo_precio_compra)
la funcion devolvera el numero de filas actualizadas
crea un bloque anonimo que ejecute la funcion anterior y muestre el resultado devuelto por la
funcion
create or replace function actualizaPrecioCoche (
v_matricula COCHE.MATRICULA%type,
v_nuevo_precio_compra COCHE.PRECIO_COMPRA%type)
return number
as
v_precio_compra COCHE.PRECIO_COMPRA%type;
begin
select precio_compra into v_precio_compra
from coche where matricula = v_matricula;
if v_precio_compra is null then
update coche
set precio_compra = v_nuevo_precio_compra
where matricula = v_matricula;
else
update coche
set precio_compra = precio_compra+(precio_compra-v_nuevo_precio_compra)
where matricula = v_matricula;
end if;
return SQL%ROWCOUNT;
end;
/
select * from coche
DECLARE
v_matricula COCHE.MATRICULA%type := &matricula;
v_nuevo_precio_compra COCHE.PRECIO_COMPRA%type := &nuevo_precio;
v_total_filas number(8);
BEGIN
v_total_filas := actualizaPrecioCoche(v_matricula, v_nuevo_precio_compra);
DBMS_OUTPUT.put_line('Se han modificado ' || v_total_filas || ' filas');
END;
/
16. Crea procedimiento que reciba como parametros de
entrada:P_ID_MARCA,P_NUMERO_COCHES. Utiliza un bucle para insertar N registros
nuevos en la tabla COCHE. El numero de registros a insertar viene indicado por el parametro
P_NUMEROS_COCHES(CONTADOR) y el bucle empezará en 1, los datos a insertar seran:
-matricula='A00'||CONTADOR
-DESCRIPCION=p_id_marca
-id_marca=p_id_marca
-precio_compra=nulo
Controlar excepcion para cuando exista algun coche en la bbdd y se viole la pk
create or replace procedure creaCoches(
p_id_marca coche.id_marca%type,
p_numero_coches number)
as
begin
for contador IN 1..p_numero_coches LOOP
insert into coche values('A00'||contador, p_id_marca, p_id_marca, null);
END LOOP;
exception
when dup_val_on_index then
DBMS_OUTPUT.put_line('Registro duplicado');
end;
/
declare
p_id_marca coche.id_marca%type := &id;
p_numero_coches number(8) := #
begin
creaCoches(p_id_marca, p_numero_coches);
end;
/
18.Crea un procedimiento al que le pasaremos el dni_cliente y la matricula. El procedimiento
debera controlar en las ventas de los coches(tabla vende) los siguientes supuestos:
A.SI NO EXISTE UN REGISTRO CON ESE DNI_CLIENTE Y ESA MATRICULA
SALTARA A LA ZONA DE EXCEPCIONES Y MOSTRARA UN MENSAJE
"NO EXISTE LA VENTA INTRODUCIDA"
B.SI EXISTE LA VENTA INTRODUCIDA:
I.MOSTRARA EL PRECIO ANTIGUO ||
II.ACTUALIZARA EL PRECIO SUBIENDO 1000 EUROS
III. DEVOLVERA EN UN PARAMETRO DE SALIDA DEL
PROCEDIMIENTO(PS_NUEVO_PRECIO) EL PRECIO NUEVO TRAS LA ACTUALIZACION
CREA UN BLOQUE ANONIMO QUE LLAME AL PROCEDIMIENTO ANTERIOR Y MUESTRE
EL PRECIO NUEVO DEVUELTO POR EL PROCEDIMIENTO.
create or replace procedure actualizaVenta(
p_dni_cliente vende.dni_cliente%type,
p_matricula vende.matricula%type,
ps_nuevo_precio out vende.precio%type
)
as
venta vende%rowtype;
begin
select * into venta
from vende
where dni_cliente = p_dni_cliente
and matricula = p_matricula;
DBMS_OUTPUT.PUT_line('el precio antiguo es ' || venta.precio);
ps_nuevo_precio := venta.precio + 1000;
update vende
set precio = ps_nuevo_precio
where dni_cliente = p_dni_cliente
and matricula = p_matricula;
EXCEPTION
WHEN no_data_found then
DBMS_OUTPUT.PUT_line('No existe la venta introducida');
end;
/
declare
v_dni_cliente vende.dni_cliente%type := &dni;
v_matricula vende.matricula%type := &matricula;
v_nuevo_precio vende.precio%type;
begin
actualizaVenta(v_dni_cliente, v_matricula, v_nuevo_precio);
if v_nuevo_precio is not null then
DBMS_OUTPUT.PUT_line('el nuevo precio es ' || v_nuevo_precio);
end if;
end;
/
