ALTER TABLE "User" ADD (maxActiveReservationCount INT);
CREATE OR REPLACE
FUNCTION getReservationCount (
p_idUser "User".iduser%TYPE
) RETURN INT AS
reservationCounter INT;
BEGIN
SELECT COUNT(*) INTO reservationCounter FROM Reservation WHERE iduser = p_iduser;
RETURN reservationCounter;
END;
SET SERVEROUTPUT ON
DECLARE
output VARCHAR2(10);
BEGIN
output:= getReservationCount(5);
DBMS_OUTPUT.PUT_LINE('NUMBER OF RESERVATIONS: '|| output);
END;
CREATE OR REPLACE
PROCEDURE insertReservation(
p_idUser "User".iduser%TYPE
) AS
maxReservation INT;
BEGIN
SELECT maxActiveReservationCount INTO maxReservation FROM "User" WHERE iduser = p_iduser;
IF(getReservationCount(p_iduser) < maxReservation) THEN
INSERT INTO Reservation (iduser) VALUES (p_iduser);
DBMS_OUTPUT.PUT_LINE('INSERTION DONE');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('INSERTION ERROR');
ROLLBACK;
END IF;
END;
SET SERVEROUTPUT ON
EXECUTE insertReservation(5);