CODEWITHSUNDEEP
oracle-databaseloopscursor
ShootingWizard
ShootingWizard

Reputation: 13

Why is the output only the last value? Oracle loop cursor

I'm trying to output a list of the courses a professor teaches, by receiving the prof's id by parameter to my function, and showing all courses, each separated by a comma. For example, if a Professor teaches Humanities, Science and Math, I want the output to be: 'Humanities, Science, Math'. However, I'm getting just 'Math,'. It only shows the last field that it found that matched with the prof's id.

CREATE OR REPLACE FUNCTION listar_cursos(prof NUMBER) RETURN VARCHAR
IS
    CURSOR C1 IS
        SELECT subject.name AS name FROM subject
               INNER JOIN course_semester 
               ON subject.id = course_semester.id_subject
        WHERE  course_semester.id_profesor = prof
        ORDER BY subject.name;

    test VARCHAR(500);
BEGIN
    FOR item IN C1
    LOOP
        test:= item.name ||',';
    END LOOP;

    RETURN test;
END;
/

I am aware that listagg exists, however I do not wish to use it.

Upvotes: 0

Views: 261

Answers (1)

user5683823
user5683823

Reputation:

In your loop, you re-assign to the test variable, instead of appending to it. This is why, at the end of the loop, it will just hold the last value of item.name.

The assignment should instead be something like

test := test || ',' || item.name

Note also that this will leave a comma at the beginning of the string. Instead of returning test, you may want to return ltrim(test, ',').

Note that you don't need to declare a cursor explicitly. The code is easier to read (in my opinion) with an implicit cursor, as shown below. I create sample tables and data to test the function, then I show the function code and how it's used.

create table subject as
  select 1 id, 'Humanities' name from dual union all
  select 2   , 'Science'         from dual union all
  select 3   , 'Math'            from dual
;

create table course_semester as
  select 1 id_subject, 201801 semester, 1002 as id_profesor from dual union all
  select 2           , 201702         , 1002 as id_profesor from dual union all
  select 3           , 201801         , 1002 as id_profesor from dual
;

CREATE OR REPLACE FUNCTION listar_cursos(prof NUMBER)  RETURN VARCHAR IS
 test VARCHAR(500);
BEGIN
FOR item IN 
(
  SELECT subject.name AS name FROM subject
      INNER JOIN course_semester 
  ON subject.id = course_semester.id_subject
  WHERE course_semester.id_profesor = prof
  ORDER BY subject.name
)
LOOP
  test:= test || ',' || item.name;
END LOOP;
RETURN ltrim(test, ',');
END;
/

select listar_cursos(1002) from dual;

LISTAR_CURSOS(1002)
-----------------------
Humanities,Math,Science

Upvotes: 1

Related Questions