Ejercicios Resueltos de Consultas SQL y Programación PL/SQL en Oracle

1. Consultas SQL Avanzadas

1.1. Consulta con Múltiples Tablas y Filtros

(0,8 puntos)

SELECT D.PROVINCIA, D.POBLACION, D.NOMBRE, D.APELLIDOS, P.DESCRIPCION, P.FECHASALIDA, P.FECHASALIDA+2, P.MATRICULA, R.NOMBRE, R.APELLIDOS, M.MODALIDAD, M.PRECIOMOD, M.PRECIOMOD*1.21 AS TOTAL 
FROM PAQUETES P, DESTINATARIOS D, REMITENTES R, MODALIDADES M, REPARTIDOR REP 
WHERE P.DNID = D.DNID 
  AND P.DNIR = R.DNIR 
  AND P.IDMODALIDAD = M.IDMODALIDAD 
  AND REP.DNIREP = P.DNIREP 
  AND TO_CHAR(P.FECHASALIDA, 'YYYY') BETWEEN '2015' AND '2017' 
  AND UPPER(D.PROVINCIA) IN ('ALBACETE', 'MURCIA', 'CIUDAD REAL', 'TOLEDO') 
  AND D.POBLACION LIKE '%CALATRAVA%' 
  AND REP.NOMBRE = 'RAUL' 
  AND REP.APELLIDOS LIKE 'GONZÁLEZ%' 
ORDER BY D.PROVINCIA, D.POBLACION;

Versión con INNER JOIN (solo se muestran las partes distintas):

INNER JOIN DESTINATARIOS D ON P.DNID = D.DNID 
INNER JOIN REMITENTES R ON P.DNIR = R.DNIR 
INNER JOIN MODALIDADES M ON P.IDMODALIDAD = M.IDMODALIDAD 
INNER JOIN REPARTIDOR REP ON REP.DNIREP = P.DNIREP

1.2. Agregación y Filtrado con HAVING

(0,8 puntos)

SELECT R.NOMBRE, R.APELLIDOS, SUM(M.PRECIOMOD) 
FROM PAQUETES P, REPARTIDORES R, MODALIDADES M 
WHERE P.DNIREP = R.DNIREP 
  AND P.IDMODALIDAD = M.IDMODALIDAD 
  AND P.FECHASALIDA BETWEEN '01/02/2016' AND '01/02/2018' 
GROUP BY R.NOMBRE, R.APELLIDOS 
HAVING SUM(M.PRECIOMOD) > 2000;

1.3. Subconsultas y Cálculos de Fechas

(0,8 puntos)

SELECT P.DESCRIPCION, P.FECHASALIDA, P.FECHAENTREGA, D.NOMBRE, D.APELLIDOS 
FROM PAQUETES P, DESTINATARIOS D 
WHERE P.DNID = D.DNID 
  AND P.FECHAENTREGA - P.FECHASALIDA >= 4 
  AND P.DNID IN (
      SELECT DNID 
      FROM PAQUETES 
      WHERE ENTREGADO = 'S' 
      GROUP BY DNID 
      HAVING COUNT(*) > 3
  );

2. Programación en PL/SQL

2.1. Función: CALCULAR_INCREMENTO

• Código de la función:

  CREATE OR REPLACE FUNCTION CALCULAR_INCREMENTO (P_PESO IN NUMBER, P_PESOMOD IN NUMBER) 
  RETURN NUMBER IS 
  BEGIN 
      IF P_PESO - P_PESOMOD < 0 THEN 
          RETURN 0; 
      ELSIF P_PESO - P_PESOMOD BETWEEN 1 AND 3 THEN 
          RETURN 2; 
      ELSIF P_PESO - P_PESOMOD BETWEEN 4 AND 6 THEN 
          RETURN 4; 
      ELSE 
          RETURN 8; 
      END IF; 
  END;

2.2. Procedimiento: TOTAL_REPARTIDOR

Código del procedimiento con manejo de cursores y excepciones:

CREATE OR REPLACE PROCEDURE TOTAL_REPARTIDOR (P_DNIREP IN VARCHAR2, P_ANIO IN NUMBER, P_TOTAL OUT NUMBER) IS 
    CURSOR C1 IS 
        SELECT M.PRECIOMOD, PESO, PESOMOD 
        FROM PAQUETES P, MODALIDAD M 
        WHERE P.IDMODALIDAD = M.IDMODALIDAD 
          AND P.DNIREP = P_DNIREP 
          AND TO_CHAR(P.FECHASALIDA, 'YYYY') = P_ANIO 
          AND P.FECHAENTREGA - P.FECHASALIDA <= 5 
          AND M.DESCIPMOD LIKE '%RAPIDO%';

    VC1 C1%ROWTYPE; 
    N NUMBER;
    NOEXISTEREPARTIDOR EXCEPTION; 
    NOABIERTOCURSOR EXCEPTION; 
    NOHAYDATOS EXCEPTION; 
BEGIN 
    P_TOTAL := 0; 
    SELECT COUNT(*) INTO N FROM REPARTIDORES WHERE DNIREP = P_DNIREP; 
    
    IF N = 0 THEN 
        RAISE NOEXISTEREPARTIDOR; 
    ELSE 
        OPEN C1; 
        IF C1%ISOPEN THEN 
            FETCH C1 INTO VC1; 
            IF C1%NOTFOUND THEN 
                RAISE NOHAYDATOS; 
            ELSE 
                WHILE C1%FOUND LOOP 
                    P_TOTAL := P_TOTAL + VC1.PRECIOMOD + CALCULAR_INCREMENTO(VC1.PESO, VC1.PESOMOD); 
                    FETCH C1 INTO VC1;
                END LOOP; 
            END IF; 
            CLOSE C1; 
        ELSE 
            RAISE NOABIERTOCURSOR; 
        END IF; 
    END IF; 
EXCEPTION 
    WHEN NOEXISTEREPARTIDOR THEN P_TOTAL := -1; 
    WHEN NOABIERTOCURSOR THEN P_TOTAL := -2; 
    WHEN NOHAYDATOS THEN P_TOTAL := -3; 
    WHEN OTHERS THEN P_TOTAL := -4; 
END;

2.3. Bloque Anónimo de Prueba

• Ejecución y gestión de resultados:

  DECLARE 
      TOTAL_REP NUMBER; 
  BEGIN 
      TOTAL_REPARTIDOR('11111A', 2016, TOTAL_REP); 
      
      IF TOTAL_REP = -1 THEN 
          DBMS_OUTPUT.PUT_LINE('NO EXISTE ESE REPARTIDOR'); 
      ELSIF TOTAL_REP = -2 THEN 
          DBMS_OUTPUT.PUT_LINE('PROBLEMAS AL ABRIR EL CURSOR'); 
      ELSIF TOTAL_REP = -3 THEN 
          DBMS_OUTPUT.PUT_LINE('EL REPARTIDOR NO TIENE REPARTO'); 
      ELSIF TOTAL_REP = -4 THEN 
          DBMS_OUTPUT.PUT_LINE('SE HA PRODUCIDO OTRO ERROR'); 
      ELSE 
          IF TOTAL_REP >= 0 THEN 
              DBMS_OUTPUT.PUT_LINE('TOTAL: ' || TOTAL_REP); 
          END IF; 
      END IF; 
  END;