oracle - 从 Oracle 表变量/数组中选择值？

Question

继我的上一个问题(Table Variables in Oracle PL/SQL?)之后...

一旦你在一个数组/表中有值，你如何让它们再次出现？最好使用 select 语句或类似的东西？

这是我到目前为止所得到的:

declare
    type array is table of number index by binary_integer;
    pidms array;
begin
    for i in    (
                select distinct sgbstdn_pidm
                from sgbstdn
                where sgbstdn_majr_code_1 = 'HS04'
                and sgbstdn_program_1 = 'HSCOMPH'
                )
    loop
        pidms(pidms.count+1) := i.sgbstdn_pidm;
    end loop;

    select *
    from pidms; --ORACLE DOESN'T LIKE THIS BIT!!!
end;

我知道我可以使用 dbms_output.putline() 输出它们，但我希望得到一个结果集，就像从任何其他表中选择一样。

提前致谢，
马特

Answer 1

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;

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

例如当您重新编译主体时，它会在之前已经使用过它的 session 中引发异常。 (因为变量值无效)

但是，您可以在包中声明类型(或在 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;