Zain Qurashi Zain Qurashi - 8 months ago 69
SQL Question

Can I use an if statement around a cursor or a while loop with fetch

I had an issue with solving this problem I was given.


If n < 0, display an error message “Invalid number!”.

If n = 0, display all rows (Sex, Rank, Given_Name, and
Number_of_Occurrences) in the tbl_baby_name table. Sort your output
in ascending order by sex, and then rank.

If n > 0, display all rows (Sex, Rank, Given_Name, and
Number_of_Occurrences) having rank ≤ n in the tbl_baby_name table.
Sort your output in ascending order by sex, and then rank.


How can I display only the rank of the babies that are less than the number the user inputted. I have the output of all the babies but I only wanted the ones with rank less then what the user at entered.

Whenever I try and put an if statement around the cursor it gives me an error. It also won't let me add a while loop around the fetch statement.

Can someone please help!

ACCEPT p_1 PROMPT 'Enter a number :'
DECLARE
v_n NUMBER := &p_1;
v_baby_rank TBL_BABY_NAME.RANK%TYPE;
v_baby_sex VARCHAR(80);
v_baby_name TBL_BABY_NAME.GIVEN_NAME%TYPE;
v_baby_numoc TBL_BABY_NAME.NUMBER_OF_OCCURRENCES%TYPE;

CURSOR c_baby IS
SELECT rank, CASE sex WHEN 'M' THEN 'Male' ELSE 'Female' END, GIVEN_NAME, NUMBER_OF_OCCURRENCES
FROM TBL_BABY_NAME
ORDER BY sex,rank;
BEGIN
if v_n < 0 Then
DBMS_OUTPUT.PUT_LINE('Invalid Number');
Else
OPEN c_baby;
FETCH c_baby
INTO v_baby_rank, v_baby_sex, v_baby_name, v_baby_numoc;
DBMS_OUTPUT.PUT_LINE( RPAD('Sex',10) || RPAD('Rank',10) || RPAD('Given Name',15) || ('Number of Occurrences') );
DBMS_OUTPUT.PUT_LINE('===========================================================');
while c_baby%FOUND loop
DBMS_OUTPUT.PUT_LINE(RPAD(v_baby_sex, 10) || RPAD(v_baby_rank,10) || RPAD(v_baby_name,30) || v_baby_numoc);
FETCH c_baby
INTO v_baby_rank, v_baby_sex, v_baby_name, v_baby_numoc;
END LOOP;
CLOSE c_baby;
END IF;
END;


My output

APC APC
Answer Source

"It just won't let me put an if statment around the cursor. Won't execute"

The simplest way of meeting the requirement would be this:

    while c_baby%FOUND loop
        if  v_n = 0 
        or  c_baby.rank < v_n 
        then
            DBMS_OUTPUT.PUT_LINE(RPAD(v_baby_sex, 10) || RPAD(v_baby_rank,10) || RPAD(v_baby_name,30) || v_baby_numoc);
        end if;
        FETCH c_baby 

It selects all the records but only displays the ones which matches teh input criterion.

If performance is a concern - not for your homework but it will matter in real life - then you might want to select a filtered result set instead of selecting everything and discarding the unwanted rows. In this scenario you would use a cursor variable instead:

.....
    c_baby sys_refcursor;
BEGIN
    if v_n < 0 Then 
        DBMS_OUTPUT.PUT_LINE('Invalid Number');
    Elsif v_n > 0 Then 
        OPEN c_baby for
            SELECT         rank, CASE sex WHEN 'M' THEN 'Male' ELSE 'Female' END, GIVEN_NAME, NUMBER_OF_OCCURRENCES
            FROM           TBL_BABY_NAME
            where rank < v_n
            ORDER BY       sex,rank;
    Else 
        OPEN c_baby for
            SELECT         rank, CASE sex WHEN 'M' THEN 'Male' ELSE 'Female' END, GIVEN_NAME, NUMBER_OF_OCCURRENCES
            FROM           TBL_BABY_NAME
            ORDER BY       sex,rank;
    end if;
    FETCH c_baby 
    INTO .... -- the rest of your posted code

Using a cursor variable allows us to inject a different result set into a standard set of processing. It is possible to avoid the duplicated code in the SELECT by using dynamic SQL, but that probably exceeds the brief.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download