SHARE
TWEET

DAIS_COMPLET

HenX Feb 28th, 2012 166 Never
  1. ALTER TABLE "User" ADD (maxActiveReservationCount INT);
  2.  
  3.  
  4.  
  5. CREATE OR REPLACE
  6. FUNCTION getReservationCount (
  7.                   p_idUser "User".iduser%TYPE
  8.                   ) RETURN INT AS
  9.  
  10. reservationCounter INT;
  11. BEGIN
  12. SELECT COUNT(*) INTO reservationCounter FROM Reservation WHERE iduser = p_iduser;
  13. RETURN reservationCounter;
  14. END;
  15.  
  16. SET SERVEROUTPUT ON
  17. DECLARE
  18. output VARCHAR2(10);
  19. BEGIN
  20. output:= getReservationCount(5);
  21. DBMS_OUTPUT.PUT_LINE('NUMBER OF RESERVATIONS: '|| output);
  22. END;
  23.  
  24. CREATE OR REPLACE
  25. PROCEDURE insertReservation(
  26.                   p_idUser "User".iduser%TYPE
  27.                   ) AS
  28. maxReservation INT;
  29. BEGIN
  30. SELECT maxActiveReservationCount INTO maxReservation FROM "User" WHERE iduser = p_iduser;
  31. IF(getReservationCount(p_iduser) < maxReservation) THEN
  32.   INSERT INTO Reservation (iduser) VALUES (p_iduser);
  33.   DBMS_OUTPUT.PUT_LINE('INSERTION DONE');
  34.   COMMIT;
  35. ELSE
  36.   DBMS_OUTPUT.PUT_LINE('INSERTION ERROR');
  37.   ROLLBACK;
  38. END IF;
  39. END;
  40.  
  41. SET SERVEROUTPUT ON
  42. EXECUTE insertReservation(5);
RAW Paste Data
Top