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
"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.