Oracle create function using cursors-CodePudding

I have a requirement to create a function in which I have to pass Query result as input to the output query concatenate by space . The below code is roughly written. Need help in modifying the function.

CREATE or replace FUNCTION GETPGM(Year IN Number, ID IN Number)
RETURN VARCHAR2 IS
result  VARCHAR2(200);

cursor getterm is
      select term_code from table_term 
      where proc_yr = Year;
      
BEGIN
loop
fetch cur into TERM;
exit when cur%NOTFOUND;
select f_getp (ID,:TERM1,Year)||' ' f_getp (ID,:TERM2,Year) from dual -- output Result set

end loop;
RETURN result;
END;

Let me know if any doubts.

CodePudding user response：

It is unclear what TERM1 and TERM2 are (parameters? If so, you should pass them to the function), nor what is the result supposed to be.

Anyway, see if something like this helps:

CREATE OR REPLACE FUNCTION getpgm (par_year   IN NUMBER,
                                   par_id     IN NUMBER,
                                   par_term1  IN NUMBER,
                                   par_term2  IN NUMBER)
   RETURN VARCHAR2
IS
   result  VARCHAR2 (200);
BEGIN
   FOR cur_r IN (SELECT term_code
                   FROM table_term
                  WHERE proc_yr = par_year)
   LOOP
      result :=
            result
         || ' '
         || f_getp (par_id, par_term1, par_year)
         || ' '
         || f_getp (par_id, par_term2, par_year);
   END LOOP;

   RETURN result;
END;

result should be concatenated with its "previous" value (otherwise, you'd get the last cursor's value as the result, not everything)
use cursor FOR loop as Oracle does all the dirty job for you (you don't have to declare cursor variable, open the cursor, fetch from it, worry about exiting the loop, close the cursor - note that a lot of those things your code doesn't have, while it should)
pay attention to return value's datatype; will a string whose length is 200 characters enough? The result will be a space-separated list of some values. Wouldn't you rather return a ref cursor or a collection?

CodePudding user response：

If you want to apply the f_getp function to every row of the query result and concatenate the results into a space delimited string then you do need to use a cursor and can use LISTAGG:

CREATE FUNCTION GETPGM(
  i_year IN table_term.proc_yr%type,
  i_id   IN Number
) RETURN VARCHAR2
IS
  result  VARCHAR2(200);
BEGIN
  SELECT  LISTAGG(f_getp(i_id, term_code, i_year), ' ') WITHIN GROUP (ORDER BY term_code)
  INTO    result
  FROM    table_term 
  WHERE   proc_yr = i_year;

  RETURN result;
END;
/