保存并返回函数pl/sql oracle中的多行

Question

declare
type t_trayIds is table of number(38,0) index by binary_integer;
v_trayIdsTable t_trayIds;
create or replace function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE)   
return t_trayIds
as 
v_trayIdsTable t_trayIds := null;
begin
select t.trayid into v_trayIds from tray t 
where t.diameterincm = v_diameterincm;
return v_trayIdsTable;
end;

所以我想要的是，要求具有特定直径的所有托盘id并将它们存储在数组或表中。在Java中，我使用了ArrayList。我想在最后返回Table以将结果传递给另一个函数。上面的代码似乎不起作用。SQL开发人员在单词create处给了我一个语法错误。有人能帮忙吗?

Answer 1

你的代码失败了，因为你混合了声明section，后面必须跟一个begin节，带有"创建或替换"函数。这是一个创建对象的独立语句;

如果你想声明一个PL/SQL表类型并将其设为public，你必须把它放在包规范中，这样它就可以被任何函数看到(我在这里还声明了函数F_getTrayIdByDiameter，以使它可见):

CREATE OR REPLACE package utils is
type t_trayIds is table of number(38,0) index by binary_integer;
function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE) return t_trayIds;
end utils;
/

此外，您不能使用SELECT INTO语法，因为

select col into var

只能用于单行，不能用于列表;在PL/SQL中，如果你想管理多行，你必须使用游标;因此，如果你想创建你的PL/SQL表，你可以获取你的游标并建立你的列表(PL/SQL表);所以，你的包体可以是

CREATE OR REPLACE package body utils is
function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE) return t_trayIds is 
v_trayIdsTable t_trayIds;
i number := 0;
cursor c is 
select t.trayid from tray t 
where t.diameterincm = v_diameterincm;
begin
for my_rec in c loop
v_trayIdsTable(i) := my_rec.trayid;
i := i + 1;
end loop;

return v_trayIdsTable;
end;
end utils;
/

然后，您可以在另一个函数或匿名块中使用列表，例如:

declare
my_result utils.t_trayIds;
begin
my_result := utils.F_GETTRAYIDBYDIAMETER(20);
dbms_output.put_line(my_result(0));
end;

Answer 2

从declare开始，您正在创建一个没有返回值的匿名块。他们只是干了一件事就不干了。听起来你想创建一个函数。

首先，要返回trayids的集合，您需要创建一个要返回的类型。这必须在模式级别完成;它本身就是一个物体。在Oracle中有三种类型的集合:嵌套表，关联数组(' ' index by ' ');表)和数组。我几乎从不使用varray，我不认为你可以像这样使用关联数组(但我忘记了，这可能在Oracle的最新版本中发生了变化)。所以创建你的字体:

create or replace type t_trayids as table of number;

现在创建函数。这里的关键是必须使用bulk collect来填充数组。这比创建结果集并遍历它要快得多。

create or replace function F_getTrayIdByDiameter(v_diameterInCm tray.diameterincm%TYPE)   
return t_trayIds
as 
v_trayIdsTable t_trayIds;
begin
select t.trayid bulk collect into v_trayIdsTable from tray t 
where t.diameterincm = v_diameterincm;
return v_trayIdsTable;
end;

正如Nicola指出的，您还可以创建一个包并在包规范中声明类型。您可以这样使用关联数组。选择哪一种方法取决于你想要做什么。