如何在PL/SQL函数中将多行存储到变量中？

Question

我正在编写一个pl/sql函数，需要从select语句中选择多行：

    SELECT pel.ceid
    FROM pa_exception_list pel
    WHERE trunc(pel.creation_date) >= trunc(SYSDATE-7)

如果我使用：

SELECT pel.ceid
INTO v_ceid

它只存储一个值，但我需要存储此选择返回的所有值。由于这是一个函数，我不能简单地使用简单的select，因为我会得到错误消息“期望INTO”。

- h0neybaLL

Answer 1

您可以使用记录类型来实现。下面的示例应该适用于您。

DECLARE
    TYPE v_array_type IS VARRAY (10) OF NUMBER;
        var v_array_type;
BEGIN
    SELECT x
    BULK COLLECT INTO
        var 
    FROM (
            SELECT 1 x
            FROM dual
            UNION
            SELECT 2 x
            FROM dual
            UNION
            SELECT 3 x
            FROM dual
        );
        FOR I IN 1..3 LOOP
              dbms_output.put_line(var(I));
            END LOOP;

END;

所以在您的情况下，它可能类似于：

select pel.ceid 
  BULK COLLECT INTO <variable which you create>
  from pa_exception_list
 where trunc(pel.creation_Date) >= trunc(sysdate-7);

- Sudipta Mondal

Answer 2

如果你真的需要存储多行数据，请查看BULK COLLECT INTO语句和示例。但也许FOR cursor LOOP和逐行处理会是更好的决策。

- Sanders the Softwarer

Answer 3

您可以将所有内容存储在一个rowtype参数中，并显示您想要显示的任何列（假设ceid是主键列，col1和2是表中的其他列）：

SQL> set serveroutput on;
SQL> declare
  l_exp pa_exception_list%rowtype;
begin
 for c in  (   select *
                 from pa_exception_list pel
                where trunc(pel.creation_date) >= trunc(SYSDATE-7) 
           ) -- to select multiple rows
 loop     
      select *
        into l_exp            
        from pa_exception_list
       where ceid = c.ceid; -- to render only one row( ceid is primary key )

      dbms_output.put_line(l_exp.ceid||' - '||l_exp.col1||' - '||l_exp.col2); -- to show the results     
 end loop;
end;
/   

- Barbaros Özhan

Answer 4

SET SERVEROUTPUT ON

BEGIN
    FOR rec IN (
        --an implicit cursor is created here
        SELECT pel.ceid AS ceid
        FROM pa_exception_list pel
        WHERE trunc(pel.creation_date) >= trunc(SYSDATE-7)
    )
    LOOP
        dbms_output.put_line(rec.ceid);
    END LOOP;

END;
/

来自这里的笔记：

在这种情况下，游标 FOR 循环声明、打开、从中提取并关闭一个隐式游标。但是，隐式游标是内部的；因此，您无法引用它。

请注意，Oracle 数据库会自动将游标 FOR 循环优化为类似于 BULK COLLECT 查询的工作方式。虽然您的代码看起来好像一次只提取一行，但 Oracle 数据库会一次提取多行，并允许您逐个处理每一行。

- jumping_monkey