Forum Stats

  • 3,741,441 Users
  • 2,248,430 Discussions
  • 7,861,808 Comments

Discussions

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

2652078
2652078 Member Posts: 9
edited May 23, 2014 9:36AM in SQL & PL/SQL

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';
image

    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;

/

Tagged:

Best Answer

  • David Berger
    David Berger Member Posts: 345
    edited May 23, 2014 6:52AM Accepted Answer

    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

Answers

  • David Berger
    David Berger Member Posts: 345
    edited May 23, 2014 6:52AM Accepted Answer

    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
    Moazzam Member Posts: 1,356

    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

    Moazzam
  • 2652078
    2652078 Member Posts: 9

    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

This discussion has been closed.