Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)

DAIS_COMPLET

By: HenX on Feb 28th, 2012  |  syntax: PL/SQL  |  size: 1.02 KB  |  hits: 159  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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);