PROCEDURES.txt

CREATE OR REPLACE FUNCTION GET_MOVIE_RATING (MID IN NUMBER, PID IN VARCHAR2, EML IN VARCHAR2)
RETURN NUMBER IS
	R NUMBER DEFAULT -1;
BEGIN
	SELECT RATING INTO R
	FROM MOVIE_WATCH
	WHERE MOVIE_ID = MID AND PROFILE_ID = PID AND EMAIL = EML;
	
	RETURN R;
END;
/


CREATE OR REPLACE PROCEDURE GET_MOVIE_TIMESTAMP (MID IN NUMBER, PID IN VARCHAR2, EML IN VARCHAR2, WATCHED_UPTO OUT NUMBER) IS
BEGIN
	SELECT MW.WATCHED_UPTO INTO WATCHED_UPTO
	FROM MOVIE_WATCH MW
	WHERE MW.MOVIE_ID = MID AND MW.PROFILE_ID = PID AND MW.EMAIL = EML;
	
	IF WATCHED_UPTO = NULL THEN
		WATCHED_UPTO := 0;
	END IF;
	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		WATCHED_UPTO := 0;
	
END;
/

CREATE OR REPLACE PROCEDURE GET_EPISODE_TIMESTAMP (SID IN NUMBER, SEASON IN NUMBER,  
EPISODE IN NUMBER, PID IN VARCHAR2, EML IN VARCHAR2, WATCHED_UPTO OUT NUMBER) IS
BEGIN
	SELECT EW.WATCHED_UPTO INTO WATCHED_UPTO
	FROM EPISODE_WATCH EW
	WHERE EW.SHOW_ID = SID AND EW.PROFILE_ID = PID AND EW.EMAIL = EML
	AND SEASON_NO = SEASON AND EPISODE_NO = EPISODE;
	
	IF WATCHED_UPTO = NULL THEN
		WATCHED_UPTO := 0;
	END IF;
	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		WATCHED_UPTO := 0;
	
END;
/
CREATE 
	OR REPLACE PROCEDURE SET_MOVIE_TIMESTAMP ( MID IN NUMBER, PID IN VARCHAR2, EML IN VARCHAR2, WU IN NUMBER ) IS BEGIN
		INSERT INTO MOVIE_WATCH ( MOVIE_ID, PROFILE_ID, EMAIL, WATCHED_UPTO )
	VALUES
		( MID, PID, EML, WU );
	EXCEPTION 
		WHEN DUP_VAL_ON_INDEX THEN
		UPDATE MOVIE_WATCH 
		SET WATCHED_UPTO = WU,
		TIME = SYSDATE 
	WHERE
		PROFILE_ID = PID 
		AND MOVIE_ID = MID 
		AND EMAIL = EML;
	
END;
/

CREATE 
	OR REPLACE PROCEDURE SET_EPISODE_TIMESTAMP ( SID IN NUMBER, SNO IN NUMBER, ENO IN NUMBER, PID IN VARCHAR2, EML IN VARCHAR2, WU IN NUMBER ) IS BEGIN
		INSERT INTO EPISODE_WATCH ( SHOW_ID, SEASON_NO, EPISODE_NO, PROFILE_ID, EMAIL, WATCHED_UPTO )
	VALUES
		( SID, SNO, ENO, PID, EML, WU );
	EXCEPTION 
		WHEN DUP_VAL_ON_INDEX THEN
		UPDATE EPISODE_WATCH 
		SET WATCHED_UPTO = WU,
		TIME = SYSDATE 
	WHERE
		PROFILE_ID = PID 
		AND SHOW_ID = SID 
		AND EMAIL = EML
		AND SEASON_NO = SNO
		AND EPISODE_NO = ENO;
	
END;
/

CREATE OR REPLACE PROCEDURE SET_MOVIE_RATING (MID IN NUMBER, EML IN VARCHAR2, PID IN VARCHAR2, MRATING IN NUMBER) IS
BEGIN
	INSERT INTO MOVIE_WATCH (MOVIE_ID, EMAIL, PROFILE_ID, RATING) 
	VALUES (MID, EML, PID, MRATING);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
			UPDATE MOVIE_WATCH
      SET RATING = MRATING
      WHERE MOVIE_ID = MID AND PROFILE_ID = PID AND EMAIL = EML;
END;
/

CREATE OR REPLACE PROCEDURE SET_SHOW_RATING (SID IN NUMBER, EML IN VARCHAR2, PID IN VARCHAR2, SRATING IN NUMBER) IS
BEGIN
	INSERT INTO SHOW_WATCH (SHOW_ID, EMAIL, PROFILE_ID, RATING) 
	VALUES (SID, EML, PID, SRATING);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
			UPDATE SHOW_WATCH
      SET RATING = SRATING
      WHERE SHOW_ID = SID AND PROFILE_ID = PID AND EMAIL = EML;
END;
/

CREATE OR REPLACE PROCEDURE CHECK_VALIDATION(EML IN VARCHAR2) IS
BEGIN
	UPDATE SUBSCRIPTION 
  SET RUNNING=0 , TOTAL_BILL = ROUND( ( MONTHS_BETWEEN(SYSDATE,START_DATE)) *BILL  ,2)
  WHERE EMAIL = EML AND END_DATE < SYSDATE;
EXCEPTION	
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS');
	WHEN DUP_VAL_ON_INDEX THEN
		DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE ON INDEX');
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR');
END;
/