我正在开发一个 SQL 项目。我想创建一个返回表的 sql*Plus 函数。
我做了这样的事情,但它不起作用,我不知道为什么:
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;
/
你能帮助我吗 ? “RETURN table_res TABLE”有问题
您使用的语法肯定是 Oracle 不支持的PLSQL
。在甲骨文中PLSQL
你需要做类似的事情:
-- 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;
/
或者根据 @a_horse_with_no_name,使用 PipeLine 函数,它可能如下所示:
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;
/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)