从 Oracle 表变量/数组中选择值?

2021-12-24 00:00:00 oracle plsql

继我的上一个问题之后(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.

提前致谢，马特

推荐答案

您可能需要一个全局临时表.

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 文档链接

尝试这样的事情...

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; 
/

相关文章