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

2652078

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;

/

David Berger

Hello d670...

I would try this code:

DECLARE
  p_studentid   NUMBER;
  v_result      VARCHAR2(20);  
BEGIN  
  p_studentid := 1;

  FOR rec_result IN (SELECT students.firstname
                          , test_history.score
                          , test_id.test_name
                          , test_id.passing_grade  
                       FROM students  
                       JOIN test_history  
                         ON students.STUDENT_ID = test_history.student_id  
                       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   -->  This row has to be deleted!
                    )
  LOOP
     IF rec_result.score < rec_result.passing_grade 
      THEN v_result := 'Fail';
      ELSE v_result := 'Pass';
     END IF; 

     DBMS_OUTPUT.PUT_LINE (rec_result.firstname || ' ' || TO_CHAR(rec_result.score) || ' ' || rec_result.test_name || ' ' || TO_CHAR(rec_result.passing_grade) || ' ' || 'Result = ' || v_result);    
  END LOOP;  

END;  
/

I hope it helps.

Regards, David

David Berger

Hello d670...

I would try this code:

DECLARE
  p_studentid   NUMBER;
  v_result      VARCHAR2(20);  
BEGIN  
  p_studentid := 1;

  FOR rec_result IN (SELECT students.firstname
                          , test_history.score
                          , test_id.test_name
                          , test_id.passing_grade  
                       FROM students  
                       JOIN test_history  
                         ON students.STUDENT_ID = test_history.student_id  
                       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   -->  This row has to be deleted!
                    )
  LOOP
     IF rec_result.score < rec_result.passing_grade 
      THEN v_result := 'Fail';
      ELSE v_result := 'Pass';
     END IF; 

     DBMS_OUTPUT.PUT_LINE (rec_result.firstname || ' ' || TO_CHAR(rec_result.score) || ' ' || rec_result.test_name || ' ' || TO_CHAR(rec_result.passing_grade) || ' ' || 'Result = ' || v_result);    
  END LOOP;  

END;  
/

I hope it helps.

Regards, David

Moazzam

However, if the student has passed it just returns 'Result = Pass'  and fails to return their name, tests, grade and passing grade.

If you find yourself wanting a function that returns multiple values, you would generally want to either
- create a procedure that has multiple OUT parameters
- create a function that returns an object type that encapsulates the values you want to return

can functions return multiple values?

<span class="kwd" style="color: #00008b;">CREATE</span><span class="pln"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln"> REPLACE TYPE user_data_type </span><span class="kwd" style="color: #00008b;">AS</span><span class="pln"> OBJECT</span><span class="pun">(</span><span class="pln">val1 NUMBER</span><span class="pun">(</span><span class="lit" style="color: #800000;">15</span><span class="pun">),</span><span class="pln"> val2 NUMBER</span><span class="pun">(</span><span class="lit" style="color: #800000;">15</span><span class="pun">));</span><span class="pln"><br/></span><span class="pun">/</span><span class="pln"><br/><br/></span><span class="kwd" style="color: #00008b;">CREATE</span><span class="pln"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln"> REPLACE </span><span class="kwd" style="color: #00008b;">FUNCTION</span><span class="pln"> GET_EMPLOYEE_AND_USER</span><span class="pun">(</span><span class="pln">in_login </span><span class="kwd" style="color: #00008b;">IN</span><span class="pln"> fnd_user</span><span class="pun">.</span><span class="pln">user_name</span><span class="pun">%</span><span class="pln">TYPE</span><span class="pun">)</span><span class="pln"><br/>  </span><span class="kwd" style="color: #00008b;">RETURN</span><span class="pln"> user_data_type </span><span class="kwd" style="color: #00008b;">AS</span><span class="pln"><br/><br/>  out_var user_data_type</span><span class="pun">;</span><span class="pln"><br/><br/>  </span><span class="kwd" style="color: #00008b;">CURSOR</span><span class="pln"> buffer_cur </span><span class="kwd" style="color: #00008b;">IS</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">SELECT</span><span class="pln"> f</span><span class="pun">.</span><span class="pln">user_id</span><span class="pun">,</span><span class="pln"> f</span><span class="pun">.</span><span class="pln">employee_id </span><span class="kwd" style="color: #00008b;">FROM</span><span class="pln"> Fnd_User f </span><span class="kwd" style="color: #00008b;">WHERE</span><span class="pln"> f</span><span class="pun">.</span><span class="pln">user_name </span><span class="pun">=</span><span class="pln"> in_login</span><span class="pun">;</span><span class="pln"><br/><br/></span><span class="kwd" style="color: #00008b;">BEGIN</span><span class="pln"> <br/>  </span><span class="kwd" style="color: #00008b;">OPEN</span><span class="pln"> buffer_cur</span><span class="pun">;</span><span class="pln"><br/>  </span><span class="kwd" style="color: #00008b;">FETCH</span><span class="pln"> buffer_cur </span><span class="kwd" style="color: #00008b;">INTO</span><span class="pln"> out_var</span><span class="pun">.</span><span class="pln">val1</span><span class="pun">,</span><span class="pln"> out_var</span><span class="pun">.</span><span class="pln">val2</span><span class="pun">;</span><span class="pln"><br/><br/>  </span><span class="kwd" style="color: #00008b;">CLOSE</span><span class="pln"> buffer_cur</span><span class="pun">;</span><span class="pln"><br/>  </span><span class="kwd" style="color: #00008b;">RETURN</span><span class="pln"> out_var</span><span class="pun">;</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">END</span><span class="pln"> GET_EMPLOYEE_AND_USER</span><span class="pun">;</span>

plsql - Returning multiple values from PL/SQL function - Stack Overflow

2652078

Hi Folks

Thank you very much for your help. I should have mentioned that i needed to do this in a parameterised cursor. Despite your answers working perfectly, i realised i could just move the DBMS_OUTPUT.PUT_LINE inside the loop and i ended up solving my own problem.

Your code looks much better though!!!

Thanks again

Ben