从存储过程、选择语句中返回多行 - PL/SQL

Question

这是一个匿名块的过程部分，它需要参数pID，即包裹ID。我的问题是select语句旨在查找并显示所有与查询包裹接触的包裹，在正常的SQL查询中，当我输入时，它可以完美地工作。

select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
    from Parking target, Parking query
    where query.parcel_id = 68
    and  target.district_id = 1
    and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

这个例子返回了与68号包裹接触的5个不同包裹的5行。然而，当我在过程中执行时，要么会出现“太多行错误”，要么如果我尝试添加“And Rownum <2”，它可以正常工作，但不显示所有关系，即它只运行并显示每个包裹的一个关系。有什么办法可以改善这种情况吗？我一直在阅读网站上的其他帖子，并提到引用游标，但我不知道它们如何应用于此处。非常感谢。

procedure Payx (pID number )is 

  varDistrict Parking.District_id%type;
  vID Parking.parcel_id%type;
  vQED Parking.parcel_id%type;
  varRel varchar2(20);
  begin

select target.district_id, target.parcel_id,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
      into varDistrict
           vID,
           vQED,
           varRel
      from Parking target, Parking query
      where query.parcel_id = pID
      and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE'
      and rownum <2;
    ---  dbms_output.put_line('')

  end Payx;

- Delta1x

---

你需要使用游标。 - OldProgrammer

可能是重复的问题：创建一个返回表的Oracle函数。 - Muhammad Muazzam

Answer 1

正如@Muhammad Muazzam所提到的，由于查询返回了多行，因此您需要使用集合一次性保存记录，或者您可以循环遍历选择并将记录保存在您声明的变量中。我将向您展示如何使用RECORD来完成这项任务。

create or replace procedure payx (pid   number) 
is
   --Created a record by bundling all your single defined variables  
     type xx is RECORD
     (
         vardistrict                   parking.district_id%type,
         vid                           parking.parcel_id%type,
         vqed                          parking.parcel_id%type,
         varrel                        varchar2 (20);

     );

     type var is table of xx index by pls_integer;

     var1 var;       

begin
     select target.district_id,
            target.parcel_id,
            query.parcel_id,
            sdo_geom.relate (target.geom,
                             'determine',
                             query.geom,
                             0.05
                            ) relationship
       bulk collect into var1
       from parking target,
            parking query
      where query.parcel_id = pid
        and sdo_relate (target.geom,
                        query.geom,
                        'mask=TOUCH'
                       ) = 'TRUE' ;

      for i in 1..var1.count
      loop                       
       dbms_output.put_line (   var1 (i).vardistrict
                            || var1 (i).vid
                            || var1 (i).vqed
                            || var1 (i).varrel);

      end loop;
end payx;

- XING

---

谢谢，我尝试了这种方法，似乎解决了问题。很抱歉回复晚了，我花了一些时间弄清楚为什么它有效，所以我将来会知道这个问题。 - Delta1x

Answer 2

问题实际上是：你想用这些结果做什么？通常应用程序中使用的所有典型SELECT语句都会返回多个结果，可能有很多。然后应用程序需要准备好逐个处理这些结果。这是根据您的应用程序编写的语言而定的。例如，对于Java查询返回一个ResultSet对象，您可以使用其next()方法进行迭代。Python使用类似的技术。在PL/SQL中，只需使用for循环即可，无需使用显式游标：

for t in (
  select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
  from Parking target, Parking query
  where query.parcel_id = 68
  and  target.district_id = 1
  and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE'
)
loop
   -- process the results here
end loop;

在循环内，通过在每个结果前缀循环变量（这里是 t）来引用返回的每一列。例如：

  dbms_output.put_line ('district_id='||t.district_id);

显然，我想你的应用程序的目的不是在sqlplus中打印结果。您可以对结果进行计算，将其写入另一个表中...

现在，如果您的目的是将结果保存在某个表中以供以后处理，则只需执行以下操作：

create table query_results as 
select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
from Parking target, Parking query
where query.parcel_id = 68
and  target.district_id = 1
and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

or

insert into query results
select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
from Parking target, Parking query
where query.parcel_id = 68
and  target.district_id = 1
and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

您使用哪种应用程序语言？您会如何处理查询结果？ 编辑： 您可以通过编写以下语法来简化代码：

and  sdo_touch(target.geom, query.geom) = 'TRUE'

而返回 sdo_geom.relate(target.geom, 'determine', query.geom, 0.05) 是没有意义的，因为你检查了TOUCH关系：结果总是TOUCH。它只会增加运行查询的CPU成本。

- Albert Godfrind

---

谢谢您的回复，最终我采用了这个答案和上面的一个答案的组合。由于某种原因（我认为是我的数据集），当我尝试进一步处理时，循环选项在某些行中不断抛出“未找到数据”的错误（我正在使用一个函数来计算大小并显示结果）。但是，如果没有一些问题行，这个解决方案本来是更好的选择，而且数据库太大了，无法找到它们所有的问题行。另一方面，我已经学会了如何在没有游标的情况下进行循环，所以非常感谢您。 - Delta1x

Answer 3

由于您尝试输出多个值，因此需要使用游标或集合来帮助实现。希望这可以帮到您。

procedure Payx (pID number,
                p_ref_out OUT sys_refcursor )is 

  varDistrict Parking.District_id%type;
  vID Parking.parcel_id%type;
  vQED Parking.parcel_id%type;
  varRel varchar2(20);
  begin

OPEN p_ref_out FOR
select target.district_id, target.parcel_id,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
      into varDistrict
           vID,
           vQED,
           varRel
      from Parking target, Parking query
      where query.parcel_id = pID
      and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';
    ---  and rownum <2;
    ---  dbms_output.put_line('')

  end Payx;

- Avrajit Roy

---

当您尝试将多行放入声明为某种数据类型的单个维度变量中时，会出现“TOO Many Rows Error”错误。如果您通过过程/函数返回它，它不会出现。此外，OP的意图不是将结果集作为“OUT”参数返回。 - XING

我的意思是说，要么使用游标，要么使用集合来处理这种情况。 - Avrajit Roy

在我的回答中，OUT的意思是：仅将多个值放入标量变量中。希望你明白了。 - Avrajit Roy