oracle数组内存消耗,oracle - 从Oracle表变量/数组中选择值？ - 堆栈内存溢出

sql数组类型不是必需的。 如果元素类型是原始元素，则不是。 (Varchar，数字，日期......)

非常基本的样本：

declare

type TPidmList is table of sgbstdn.sgbstdn_pidm%type;

pidms TPidmList;

begin

select distinct sgbstdn_pidm

bulk collect into pidms

from sgbstdn

where sgbstdn_majr_code_1 = 'HS04'

and sgbstdn_program_1 = 'HSCOMPH';

-- do something with pidms

open :someCursor for

select value(t) pidm

from table(pidms) t;

end;

当你想重用它时，知道它会是什么样子可能会很有趣。 如果发出多个命令，则可以将这些命令分组到一个包中。 上面的私有包变量技巧有其缺点。 当你向包添加变量时，你给它一个状态，现在它不作为无状态的一堆函数，而是作为一些奇怪的单例对象实例。

例如，当您重新编译正文时，它将在之前已经使用它的会话中引发异常。 (因为变量值被无效)

但是，您可以在包中声明类型(或在sql中全局声明)，并将其用作应该使用它的方法中的参数。

create package Abc as

type TPidmList is table of sgbstdn.sgbstdn_pidm%type;

function CreateList(majorCode in Varchar,

program in Varchar) return TPidmList;

function Test1(list in TPidmList) return PLS_Integer;

-- "in" to make it immutable so that PL/SQL can pass a pointer instead of a copy

procedure Test2(list in TPidmList);

end;

create package body Abc as

function CreateList(majorCode in Varchar,

program in Varchar) return TPidmList is

result TPidmList;

begin

select distinct sgbstdn_pidm

bulk collect into result

from sgbstdn

where sgbstdn_majr_code_1 = majorCode

and sgbstdn_program_1 = program;

return result;

end;

function Test1(list in TPidmList) return PLS_Integer is

result PLS_Integer := 0;

begin

if list is null or list.Count = 0 then

return result;

end if;

for i in list.First .. list.Last loop

if ... then

result := result + list(i);

end if;

end loop;

end;

procedure Test2(list in TPidmList) as

begin

...

end;

return result;

end;

怎么称呼它：

declare

pidms constant Abc.TPidmList := Abc.CreateList('HS04', 'HSCOMPH');

xyz PLS_Integer;

begin

Abc.Test2(pidms);

xyz := Abc.Test1(pidms);

...

open :someCursor for

select value(t) as Pidm,

xyz as SomeValue

from table(pidms) t;

end;