jeudi 9 avril 2020

Using an If/Else in an Explicit Cursor PL/SQL

I am trying to execute an If/Else statement within my Explicit cursor on PL/SQL. When I run my script, I run into an error: enter image description here

The goal of the script is to create a loop that returns 5 movies with the most votes in the selected year and sort them based on votes from high to low. For each movie, display the title, votes, score and the name of the lead actor. If a user enters a year <1990 or >2013, return the message 'Invalid Year'.

Here is my script that I have written:

DECLARE
    v_year NUMBER(4):=&user_input; 
    CURSOR c_year IS
        SELECT movie.title AS title, movie.votes AS votes, movie.score AS score, actor.name AS lead
        FROM movie, casting, actor
        WHERE movie.id = casting.movie_id
        AND actor.id = casting.actor_id
        AND casting.ord = 1
        AND movie.yr >= 1990 AND movie.yr <= 2013
        ORDER BY votes DESC;
    v_year c_year%ROWTYPE;
BEGIN
    OPEN c_year;
    LOOP
    FETCH c_year INTO v_year;
    IF c_year > 2013 THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Year.');
    ELSIF c_year < 1990 THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Year.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Rank'||c_year%ROWCOUNT||':'||title|| 'Votes:'||votes|| 'Score:'||score|| 'Lead actor:'||lead);
    EXIT WHEN c_year%ROWCOUNT > 5;
    END IF;
    END LOOP;
    CLOSE c_year;
END;

Aucun commentaire:

Enregistrer un commentaire