如何计算从表中所有Oracle varray中的元素数量？ [英] How to count the number of elements in all Oracle varrays from table?

问题描述

我有一个这样的表：

CREATE TABLE spatial_data (
  id NUMBER PRIMARY KEY,
  geometry SDO_GEOMETRY);

SDO_GEOMETRY有一个字段sdo_ordinates，具有以下类型：

SDO_GEOMETRY has a field sdo_ordinates with the following type:

TYPE SDO_ORDINATE_ARRAY AS VARRAY（1048576）OF NUMBER

TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER

我可以取得指定物件的点数：

I can get the number of points for specified object:

select count(*)
from table(
    select s.geometry.sdo_ordinates
    from spatial_data s
    where s.id = 12345
);

如何计算多个对象？不能使用

How can I get count for several objects? It's not possible to use

where s.id in (1, 2, 3, 4, 5)

我真的在乎性能。

推荐答案

我认为你可以用一个查询：

I think that you can do it with one query:

select s.id, count(*)
  from spatial_data s, table(s.geometry.sdo_ordinates)
 group by s.id

或者你可以写一个plsql简单函数，返回SDO_ORDINATE_ARRAY VARRAY ：

or you can write a plsql simple function that returns the count attribute of the SDO_ORDINATE_ARRAY VARRAY:

create or replace function get_count(ar in SDO_ORDINATE_ARRAY) return number is
begin
   return ar.count;
end get_count;

甚至更好将一个成员函数添加到返回count属性的SDO_GEOMETRY TYPE

or even nicer add a member function to SDO_GEOMETRY TYPE which return the count attribute

这篇关于如何计算从表中所有Oracle varray中的元素数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！