Hi folks
I'm using 11g Express Edition 11.2.0.2.0.
I'm trying to return the name of a student, all of their tests taken, grade, passing grade and whether this is a pass or fail.
In the code i've 'cobbled' together, if the student has failed it works. However, if the student has passed it just returns 'Result = Pass' and fails to return their name, tests, grade and passing grade.
A second issue (and please let me know if i need to post this in a separate thread) is if a student has carried out multiple tests, it only returns one.
Thanks for any advice you can give me.
SET SERVEROUTPUT ON
DECLARE
CURSOR c_pass_fail_cursor
(p_studentid number) IS
SELECT students.firstname,
test_history.score,
test_id.test_name,
test_id.passing_grade
FROM students
INNER JOIN test_history
ON students.STUDENT_ID = test_history.student_id
INNER JOIN test_id
ON test_id.test_id = test_history.test_id
WHERE students.student_id = p_studentid
AND test_history.SCORE < test_id.passing_grade;
v_name students.firstname%type;
v_score test_history.SCORE%type;
v_test test_id.test_name%type;
v_passing test_id.passing_grade%type;
v_result varchar2(4);
BEGIN
OPEN c_pass_fail_cursor (1);
LOOP
FETCH c_pass_fail_cursor INTO v_name, v_score, v_test, v_passing;
EXIT WHEN c_pass_fail_cursor%notfound;
END LOOP;
CLOSE c_pass_fail_cursor;
IF v_score < v_passing then
v_result := 'Fail';
DBMS_OUTPUT.PUT_LINE (v_name || ' ' || v_score || ' ' || v_test || ' ' || v_passing || ' ' || 'Result = ' || v_result);
ELSE
v_result := 'Pass';
DBMS_OUTPUT.PUT_LINE (v_name || ' ' || v_score || ' ' || v_test || ' ' || v_passing || ' ' || 'Result = ' || v_result);
END IF;
END;
/