Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Having problems returning multiple rows with parameterised CURSOR and IF, ELSE DBMS_OUPUT

2652078May 23 2014 — edited May 23 2014

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;

/

This post has been answered by David Berger on May 23 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2014
Added on May 23 2014
3 comments
1,664 views