Conditioning with multiple Cursors

Question

I have 5 cursors and i'm trying to make a conditioning structure that opens and process the relevant cursor, based on user input. The function is supposed to collect the results in a single string. The code apparently has errors and i cannot for the life of me figure out where i've gone wrong. Thank you good sirs for your help!

create or replace function listnation1(region_name in varchar2)
return varchar2
IS
DECLARE
    CURSOR africaCursor IS 
    SELECT n_name
    FROM nation
    where n_regionkey = 0;

    CURSOR americaCursor IS 
    SELECT n_name
    FROM nation
    where n_regionkey = 1;

    CURSOR asiaCursor IS 
    SELECT n_name
    FROM nation
    where n_regionkey = 2;

    CURSOR europeCursor IS 
    SELECT n_name
    FROM nation
    where n_regionkey = 3;

    CURSOR midEastCursor IS 
    SELECT n_name
    FROM nation
    where n_regionkey = 4;

    aString varchar2 := "";

BEGIN
    IF region_name = "Africa" THEN
        aString := "0   ";
        FOR i in africaCursor LOOP
            CONCAT(aString, str(i.n_name));
            CONCAT(aString, ", ");
        END LOOP;

    ELSIF region_name = "America" THEN LOOP
        aString := "1   ";
        FOR i in americaCursor LOOP
            CONCAT(aString, str(i.n_name));
            CONCAT(aString, ", ");
        END LOOP;

    ELSIF region_name = "Asia" THEN LOOP
        aString := "2   ";
        FOR i in asiaCursor LOOP
            CONCAT(aString, str(i.n_name));
            CONCAT(aString, ", ");
        END LOOP;

    ELSIF region_name = "Europe" THEN LOOP
        aString := "3   ";
        FOR i in europeCursor LOOP
            CONCAT(aString, str(i.n_name));
            CONCAT(aString, ", ");
        END LOOP;

    ELSIF region_name = "Middle East" THEN LOOP
        aString := "4   ";
        FOR i in midEastCursor LOOP
            CONCAT(aString, str(i.n_name));
            CONCAT(aString, ", ");
        END LOOP;
    END IF;

    return aString;
END listnation1;
/

Answer 1

You are not using the return value of the concat function. It should be something like

aString := CONCAT(aString, ', ');

You can also write

aString := aString || i.n_name || ', ';

Note also that the string delimiter is a single quote. Double quotes are used to escape table or column names in Oracle.

In SQL you get the best performance with SELECT commands. Avoid loops and cursors if possible.

Answer 2

5 cursors solution is most probably the worst you could have written. Basically, you don't need cursors at all - LISTAGG can do it in a single query. Here's how:

create or replace function listnation1 (par_region_name in varchar2)
  return varchar2 
is
  retval varchar2(4000);
begin
  select listagg(n.n_name, ',') within group (order by n.n_name)
    into retval
    from nation n
    where n.n_regionkey = case when par_region_name = 'Africa'      then 0
                               when par_region_name = 'America'     then 1
                               when par_region_name = 'Asia'        then 2
                               when par_region_name = 'Europe'      then 3
                               when par_region_name = 'Middle East' then 4
                          end;
  return retval;
end;
/

---

If it must be cursor-related solution, why not cursor FOR loop?

create or replace function listnation1 (par_region_name in varchar2)
  return varchar2 
is
  retval varchar2(4000);
begin
  for cur_r in 
   (select n.n_name
    from nation n
    where n.n_regionkey = case when par_region_name = 'Africa'      then 0
                               when par_region_name = 'America'     then 1
                               when par_region_name = 'Asia'        then 2
                               when par_region_name = 'Europe'      then 3
                               when par_region_name = 'Middle East' then 4
                          end
   ) 
  loop
    retval := retval || cur_r.n_name ||',';
  end loop;    

  return rtrim(retval, ',');
end;
/