在PL/SQL函数中返回表 [英] Return a table in a PL/SQL function
本文介绍了在PL/SQL函数中返回表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在处理一个SQL项目.我想创建一个返回表的sql * Plus函数. 我已经做了类似的事情,但是它不起作用,我也不知道为什么:
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;
/
你能帮我吗? "RETURN table_res TABLE"出了点问题
Can you help me ? Something wrong with the "RETURN table_res TABLE"
推荐答案
您使用的语法肯定是Oracle PLSQL
不支持的语法.在oracle PLSQL
中,您需要执行以下操作:
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;
/
或者按照@a_horse_with_no_name,使用PipeLine函数,它可能如下所示:
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;
/
这篇关于在PL/SQL函数中返回表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文