Ejercicios en PL/SQL
1. Mostrar para cada uno de los instructores su identificación, nombre completo, y sueldo a pagar en un mes X de un año Z si se sabe que corresponde al producto de la cantidad de lecciones dictadas en el mes por 0.05% del valor total de de los pagos realizados en el mes.
DECLARE
v_mes VARCHAR(2);
v_año VARCHAR(4);
CUROR c_pago IS
SELECT identificacion,nombre,apellido,COUNT(fecha) lecciones
FROM INSTRUCTORES,LECCIONES
WHERE TO_CHAR(fecha,'mm')=v_mes
AND identificacion=id_instructor
AND TO_CHAR(fecha,'yyyy')=v_año;
BEGIN
v_mes:='&mes';
v_año:='&año';
SELECT (SUM(valor) *0.05) parcial
FROM PAGOS
WHERE TO_CHAR(fecha,'mm')=v_mes
AND TO_CHAR(fecha,'yyyy')=v_año;
DBMS_OUTPUT.PUT_LINE('Pagos a instructores');
FOR r_pago IN c_pago LOOP
DBMS_OUTPUT.PUT_LINE('Identificacion: '||r_pago.identificacion||'Nombre:'||r_ipago.nombre||'
'||r_pago.apellido||' El sueldo es: '||parcial*r_pago.lecciones);
END LOOP;
END;
/
-----------------------------------------------------------------------------------------------------------------------------------
2. Capturar la identificación de un cliente y mostrar un informe que conste de las siguientes partes: una primera parte sus datos básicos (nombre completo, dirección de residencia y valor total de los pagos realizados en el año 2009), una segunda parte el detalle de sus pagos realizados en el año 2009 (fecha y valor).
DECLARE
v_id_cliente CLIENTES.identificacion%TYPE;
CURSOR c_cliente IS
SELECT nombre,apellido,direccion_res,SUM(valor) valor
FROM CLIENTES,PAGOS
WHERE identificacion=id_cliente
AND identificacion=v_id_cliente
AND TO_CHAR(fecha,'yyyy')='2009';
CUROSR c_detalle IS
SELECT fecha,valor
FROM PAGOS
WHERE id_cliente=v_id_cliente
AND TO_CHAR(fecha,'yyyy')='2009';
BEGIN
v_id_cliente:='&ident';
FOR r_cliente IN c_cliente LOOP
DBMS_OUTPUT.PUT_LINE('Nombre: '||r_cliente.nombre||' '||r_cliente.apellido||
'direccion: '||r_cliente.direccion_res||'total'||r_cliente.valor);
FOR r_pago IN c_detalle LOOP
DBMS_OUTPUT.PUT_LINE('Fecha: 'r_pago.fecha||' valor'||r_pago.valor);
END LOOP;
END LOOP;
END;
/
----------------------------------------------------------------------------------------------------------------------------------
3. Escribir una función que reciba la identificación de un cliente y un año X y devuelva el valor total de los pagos realizados en el año X.
CREATE OR REPLACE FUNCTION f_pago (ide_clien CLIENTES.identificacion%TYPE,
v_año VARCHAR2) RETURN NUMBER IS
v_valor PAGOS.valor%TYPE;
BEGIN
SELECT SUM(valor) valor
INTO v_valor
FROM PAGOS
WHERE id_cliente=ide_clien
AND TO_CHAR(fecha,'YYYY')=v_año;
RETURN v_valor;
END f_cita;
/
----------------------------------------------------------------------------------------------------------------------------------
4. Escribir una función que reciba la identificación de un cliente y devuelve la información completa del cliente.
CREATE OR REPLACE FUNCTION f_cliente (ide_clien CLIENTES.identificacion%TYPE)
RETURN CLIENTES%ROWTYPE IS
v_info CLIENTES%ROWTYPE;
BEGIN
SELECT *
INTO v_info
FROM CLIENTES
WHERE identificacion=ide_clien
RETURN v_info;
END f_cliente;
/
----------------------------------------------------------------------------------------------------------------------------------
5. Escribir un procedimiento almacenado que reciba la identificación de un cliente y el valor de un año X, y muestre un informe que conste de las siguientes elementos: sus datos básicos (invocar la función del numeral 2), valor total de los pagos realizados en el año X. (invocar la función del numeral 1).
CREATE OR REPLACE PROCEDURE p_informe (ide_clien CLIENTES.identificacion%TYPE, v_año VARCHAR2) AS
v_cliente CLIENTES%ROWTYPE;
v_pago PAGOS.valor%TYPE;
BEGIN
v_cliente:=f_cliente(ide_cliente);
v_pago:=f_pago(ide_cliente,v_año);
IF v_pago > 100 THEN
DBMS_OUTPUT.PUT_LINE('nombre completo: '||v_cliente.nombre||' '||v_cliente.apellido||'direccion residencia: '||
v_cliente.direccion_res||' telefono res: '||v_cliente.telefono_res||' direcion oficina: '|| v_cliente.direccion_of||' telefono of: '||v_cliente.telefono_of||' celular: '||
v_cliente.celular);
DBMS_OUTPUT.PUT_LINE('el valor total de los pagos es: '||v_pago);
ELSE
DBMS_OUTPUT.PUT_LINE('El valor no es el adecuado');
END IF;
END;
/
----------------------------------------------------------------------------------------------------------------------------------
