Return a table in a PL/SQL function

Question

I'm working on a SQL project. I want to create a sql*Plus function which return a table. I've make something like this, but it doesn't work and I don't know why:

CREATE OR REPLACE FUNCTION changeNbPersonnes(recette IN int, nbPersonne IN int)
RETURN table_res TABLE
(
  idIngredient int NOT NULL,
  nomIngredient varchar(255) NOT NULL,
  quantite int NOT NULL
)
AS
  CURSOR curseur_etape IS
  SELECT * FROM IngredientRecette ir
  JOIN recette r
  ON ir.idrecette=r.idrecette
  JOIN ingredient i
  ON ir.idingredient=i.idingredient
  WHERE r.idrecette=recette;
BEGIN
  FOR row_ingredient IS
    INSERT INTO res(idIngredient,nomIngredient,quantite)
    VALUES(
      row_ingredient.idingredient,
      row_ingredient.Nom,
      row_ingredient.quantite
    );
  END FOR;
  RETURN res;
END;
/

Can you help me ? Something wrong with the "RETURN table_res TABLE"

In fact I would like to return a list of result but the only way I found was to create a new table like this — Fedour Traktor, Oct 28 '17 at 21:20
Can you please provide the list (and type) of (relevant) columns of tables r and ir? Please explain what to do with input parameter nbPersonne? — Martin Berger, Oct 29 '17 at 07:30

XING · Accepted Answer · 2017-10-30T10:32:34.100

The syntax you use us certainly something which is not supported in Oracle PLSQL. In oracle PLSQL you need to do something like:

-- Create Object of your table
CREATE TYPE TABLE_RES_OBJ AS OBJECT (
     IDINGREDIENT                  INT ,
     NOMINGREDIENT                 VARCHAR (255) ,
     QUANTITE                      INT 
);

--Create a type of your object 
CREATE TYPE TABLE_RES AS TABLE OF TABLE_RES_OBJ;
/

--Function Use the type created as Return Type
CREATE OR REPLACE FUNCTION CHANGENBPERSONNES (
     RECETTE      IN   INT,
     NBPERSONNE   IN   INT)
     RETURN TABLE_RES
AS
     CURSOR CURSEUR_ETAPE
     IS
          SELECT  TABLE_RES_OBJ (IR.*)
            FROM INGREDIENTRECETTE IR 
            JOIN RECETTE R ON IR.IDRECETTE =R.IDRECETTE
                 JOIN INGREDIENT I ON IR.IDINGREDIENT = I.IDINGREDIENT
           WHERE R.IDRECETTE = RECETTE;

     VAR       TABLE_RES:= TABLE_RES();
BEGIN
     OPEN CURSEUR_ETAPE;

     LOOP
          FETCH CURSEUR_ETAPE
          BULK COLLECT INTO VAR LIMIT 100;

          EXIT WHEN CURSEUR_ETAPE%NOTFOUND;
     END LOOP;

     CLOSE CURSEUR_ETAPE;

     RETURN VAR;
END;
/

Or as per @a_horse_with_no_name, Using PipeLine Function, it could be as below:

CREATE OR REPLACE FUNCTION CHANGENBPERSONNES (RECETTE      IN INT,
                                              NBPERSONNE   IN INT)
   RETURN TABLE_RES
   PIPELINED
AS
   CURSOR CURSEUR_ETAPE
   IS
      SELECT *
        FROM INGREDIENTRECETTE IR
             JOIN RECETTE R ON IR.IDRECETTE = R.IDRECETTE
             JOIN INGREDIENT I ON IR.IDINGREDIENT = I.IDINGREDIENT
       WHERE R.IDRECETTE = RECETTE;
BEGIN
   FOR i IN CURSEUR_ETAPE
   LOOP
      PIPE ROW (TABLE_RES_OBJ (i.idingredient, i.Nom, i.quantite));
      EXIT WHEN CURSEUR_ETAPE%NOTFOUND;
   END LOOP;

   RETURN;
END;
/

Depending on the size of the result a pipelined function might be a better choice — , Oct 29 '17 at 10:31
@a_horse_with_no_name True. Even we can directly use `sys_refcursor`. There is absolute no need to return a table but donot know what's OP need. — XING, Oct 29 '17 at 11:47

Return a table in a PL/SQL function

1 Answers1

Linked