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

David Berger

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

Marked as Answer by 2652078 · Sep 27 2020

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?

CREATE OR REPLACE TYPE user_data_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15));
/

CREATE OR REPLACE FUNCTION GET_EMPLOYEE_AND_USER(in_login IN fnd_user.user_name%TYPE)
  RETURN user_data_type AS

  out_var user_data_type;

  CURSOR buffer_cur IS
   SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;

BEGIN 
  OPEN buffer_cur;
  FETCH buffer_cur INTO out_var.val1, out_var.val2;

  CLOSE buffer_cur;
  RETURN out_var;
END GET_EMPLOYEE_AND_USER;

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

1 person found this helpful

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

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 20 2014

Added on May 23 2014

#cursor, #pl, #sql

3 comments

1,663 views

SQL & PL/SQL

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

Comments

Post Details