PL/SQL Video Store Management System: Procedures and Functions

PL/SQL Video Store Management System

Sequence Creation

Create a sequence to automatically generate unique primary keys for members:

CREATE SEQUENCE SOCIO_PK_SEQ
INCREMENT BY 1
START WITH 01
MAXVALUE 1000
NOCYCLE;

Member Management Procedures

Create a procedure to register new members and execute test inserts:

CREATE OR REPLACE PROCEDURE NUEVO_SOCIO(
    P_APE1 SOCIOS.APE1%TYPE, 
    P_APE2 SOCIOS.APE2%TYPE, 
    P_DIREC SOCIOS.DIRECCION%TYPE, 
    P_CIUDAD SOCIOS.CIUDAD%TYPE, 
    P_TELEF SOCIOS.TELEFONO%TYPE
) IS
BEGIN
    INSERT INTO SOCIOS
    VALUES (SOCIO_PK_SEQ.NEXTVAL, P_APE1, P_APE2, P_DIREC, P_CIUDAD, P_TELEF, SYSDATE);
    COMMIT;
END NUEVO_SOCIO;
/

BEGIN 
    NUEVO_SOCIO('ARMENTEROS', 'NAVO', 'POZOA S/N', 'VITORIA', 945112233);
END;
/

BEGIN 
    NUEVO_SOCIO('PEREZ', 'ALONSO', 'GORBEA 33', 'VITORIA', 945998877);
END;
/

BEGIN 
    NUEVO_SOCIO('PEREZ', 'BEITIA', 'DATO S/N', 'VITORIA', 945445566);
END;
/

Movie and Copy Update Procedures

Create a procedure to update movie copy statuses and handle non-existent records:

CREATE OR REPLACE PROCEDURE ACTUALIZA_PELICULA(
    P_IDPELI COPIA_PELICULA.ID_PELICULA%TYPE, 
    P_IDCOPIA COPIA_PELICULA.ID_COPIA%TYPE, 
    P_ESTADO COPIA_PELICULA.ESTADO%TYPE
) IS
BEGIN
    UPDATE COPIA_PELICULA
    SET ESTADO = UPPER(P_ESTADO)
    WHERE ID_PELICULA = P_IDPELI AND ID_COPIA = P_IDCOPIA;
    
    COMMIT;
    
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('LA PELICULA O COPIA A ACTUALIZAR NO EXISTE');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('PELICULA ACTUALIZADA');
    END IF;
    /* SE PONE EL SQL NOTFOUND PORQUE EL UPDATE NO GENERA UN ERROR EN EXCEPTION */
END ACTUALIZA_PELICULA;
/

Movie Reservation Procedure

Create a procedure to handle movie reservations when copies are unavailable:

CREATE OR REPLACE PROCEDURE RESERVA_PELICULA(
    P_IDSOCIO RESERVAS.ID_SOCIO%TYPE, 
    P_IDPELI RESERVAS.ID_PELICULA%TYPE
) IS 
BEGIN 
    INSERT INTO RESERVAS VALUES (P_IDSOCIO, P_IDPELI, SYSDATE);
    COMMIT;
END RESERVA_PELICULA;
/

Rental Management Functions

First Rental Function

Function to process a rental using the member ID and check for available copies:

CREATE OR REPLACE FUNCTION NUEVO_ALQUILER1(
    P_IDPELI PELICULAS.ID_PELICULA%TYPE, 
    P_IDSOCIO SOCIOS.ID_SOCIO%TYPE
) 
RETURN DATE 
IS 
    CURSOR C_ALQUILER1 IS 
        SELECT * 
        FROM COPIA_PELICULA 
        WHERE ID_PELICULA = P_IDPELI;
        
    VREG C_ALQUILER1%ROWTYPE;
    V_FLAG BOOLEAN := FALSE;
BEGIN
    OPEN C_ALQUILER1;
    FETCH C_ALQUILER1 INTO VREG;
    
    WHILE NOT V_FLAG AND C_ALQUILER1%FOUND LOOP
        IF VREG.ESTADO = 'DISPONIBLE' THEN
            ACTUALIZA_PELICULA(VREG.ID_PELICULA, VREG.ID_COPIA, 'ALQUILADA');
            INSERT INTO ALQUILERES 
            VALUES (VREG.ID_PELICULA, VREG.ID_COPIA, P_IDSOCIO, SYSDATE, SYSDATE + 3);
            V_FLAG := TRUE;
        END IF;
        FETCH C_ALQUILER1 INTO VREG;
    END LOOP;
    
    COMMIT;
    CLOSE C_ALQUILER1;
    
    IF V_FLAG THEN
        RETURN (SYSDATE + 3);
    ELSE 
        RESERVA_PELICULA(P_IDSOCIO, P_IDPELI);
        RETURN NULL;
    END IF;
END NUEVO_ALQUILER1;
/

DECLARE
    vresul DATE;
BEGIN
    vresul := nuevo_alquiler1(90, 1);
    dbms_output.put_line('Fecha de devolución: ' || vresul);
END;
/

Second Rental Function with Surname Lookup

Function to process a rental using the member's surname, including exception handling for duplicate surnames:

CREATE OR REPLACE FUNCTION NUEVO_ALQUILER2(
    P_IDPELI PELICULAS.ID_PELICULA%TYPE, 
    P_APESOCIO SOCIOS.APE1%TYPE
) 
RETURN DATE 
IS 
    CURSOR C_ALQUILER1 IS 
        SELECT * 
        FROM COPIA_PELICULA 
        WHERE ID_PELICULA = P_IDPELI;
        
    VREG C_ALQUILER1%ROWTYPE;
    V_FLAG BOOLEAN := FALSE;
    
    CURSOR C_ALQUILER2 IS 
        SELECT * 
        FROM SOCIOS 
        WHERE APE1 = P_APESOCIO;
        
    V_APE1 SOCIOS.APE1%TYPE;
BEGIN
    SELECT APE1 INTO V_APE1 
    FROM SOCIOS 
    WHERE APE1 = P_APESOCIO;
    
    OPEN C_ALQUILER1;
    FETCH C_ALQUILER1 INTO VREG;
    
    WHILE NOT V_FLAG AND C_ALQUILER1%FOUND LOOP
        IF VREG.ESTADO = 'DISPONIBLE' THEN
            ACTUALIZA_PELICULA(VREG.ID_PELICULA, VREG.ID_COPIA, 'ALQUILADA');
            -- Note: Ensure R_ALQUILER2 reference aligns with proper cursor loop context
            INSERT INTO ALQUILERES 
            VALUES (VREG.ID_PELICULA, VREG.ID_COPIA, 0, SYSDATE, SYSDATE + 3);
            V_FLAG := TRUE;
        END IF;
        FETCH C_ALQUILER1 INTO VREG;
    END LOOP;
    
    COMMIT;
    CLOSE C_ALQUILER1;
    
    IF V_FLAG THEN
        RETURN (SYSDATE + 3);
    ELSE 
        RESERVA_PELICULA(0, P_IDPELI);
        RETURN NULL;
    END IF;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        FOR R_ALQUILER2 IN C_ALQUILER2 LOOP
            DBMS_OUTPUT.PUT_LINE('CUIDADO! HAY MAS DE UN SOCIO CON EL MISMO APELLIDO');
            DBMS_OUTPUT.PUT_LINE(R_ALQUILER2.ID_SOCIO || '-' || R_ALQUILER2.APE1 || ', ' || R_ALQUILER2.APE2);
        END LOOP;
        RETURN NULL;
END NUEVO_ALQUILER2;
/