从Oracle表变量/数组中选择值? [英] Selecting Values from Oracle Table Variable / Array?

问题描述

接下来是我的最后一个问题( Oracle PL/SQL中的表变量?)...

Following on from my last question (Table Variables in Oracle PL/SQL?)...

一旦在数组/表中有值，如何再次取回它们?最好使用select语句之类的东西?

Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like?

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

Here's what I've got so far:

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()来输出它们，但是我希望得到一个像从其他任何表中进行选择一样的结果集.

I know I can output them using dbms_output.putline(), but I'm hoping to get a result set like I would from selecting from any other table.

预先感谢， 马特

推荐答案

您可能需要一个GLOBAL TEMPORARY TABLE.

You might need a GLOBAL TEMPORARY TABLE.

在Oracle中，这些操作仅创建一次，然后在调用时，数据对您的会话是私有的.

In Oracle these are created once and then when invoked the data is private to your session.

Oracle文档链接

尝试这样的事情...

Try something like this...

CREATE GLOBAL TEMPORARY TABLE temp_number
   ( number_column   NUMBER( 10, 0 )
   )
   ON COMMIT DELETE ROWS;

BEGIN 
   INSERT INTO temp_number
      ( number_column )
      ( select distinct sgbstdn_pidm 
          from sgbstdn 
         where sgbstdn_majr_code_1 = 'HS04' 
           and sgbstdn_program_1 = 'HSCOMPH' 
      ); 

    FOR pidms_rec IN ( SELECT number_column FROM temp_number )
    LOOP 
        -- Do something here
        NULL; 
    END LOOP; 
END; 
/

这篇关于从Oracle表变量/数组中选择值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！