plsql-Oracle选择集合 [英] plsql - Oracle select with collections

问题描述

我有两个数字集合，一个包含n个id，一个包含n个值.我也有一个包含n条记录的select语句.现在，我想加入他们的行列. 凭直觉，我试图做这样的事情:

I have two number collections, one containing n ids and one containing n values. I also have a select statement containing n records. Now, I want to join them together. Intuitively I tried to do something like this:

select * from
(
  select t.stat                       StatNr
       , t.statNr                     StatValue
       , t.statName                   statusName
    from myTable t
   where t.nr = 1234
   order by ...
) status,
(
  select statId.v   StatNr
       , statVal.v  StatValue
  from
  (
  select column_value v
       , rownum       r  
    from table( numberCollection( 98, 45, 66, 153, 103, 67, 27 ) ) 
  ) statId,
  (
  select column_value v
       , rownum       r  
    from table( numberCollection(  1,  2,  1,   1,   3,  2,  2 ) )
  ) statVal
  where statId.r = statVal.r
) statusNew
where status.StatNr = statusNew.StatNr
  and status.StatValue <> statusNew.StatValue

因此，不带数字收集的select语句返回如下内容:

So, the select statement without number collection returns something like this:

ID  VALUE   NAME          
98  1       some varchar
153 1       some varchar
66  1       some varchar
27  3       some varchar
67  4       some varchar
45  1       some varchar
103 1       some varchar

我想从集合中添加相应的ID和值，以查看表中的值行与集合中的值行是否不同，然后返回名称.

And I want to add the corresponding id's and values from my collections to see if the value row from my table is different to the value row from my collection, and then return the name.

所以，结果看起来像这样:

So, the result looks like this:

ID  VALUE   NAME            ID  VALUE
98  1       some varchar    98  1
153 1       some varchar    153 1
66  1       some varchar    66  1
27  3       some varchar    27  2
67  4       some varchar    67  2
45  1       some varchar    45  2
103 1       some varchar    103 3

前一个ID和值来自表，后一个ID和值列来自集合.

Where the former id and value are from the table and the latter id and value columns are from the collections.

最后是where子句:

So the where clause at the end:

and status.StatValue <> statusNew.StatValue

将为我提供值已更改的记录，并返回第4-7行.

would give me the records with changed values, returning row 4 - 7.

现在，从我的角度来看，这看起来有点丑陋.尤其是我在哪里检查rownum的where子句.无论如何，我对Oracle还是比较陌生的，这有不利之处还是实现这一目标的更好方法?

Now, this looks a bit ugly from my point of view. Especially the where clause where I check for the rownum. Anyway I'm relatively new to Oracle, is there a downside to this or a better way to accomplish this?

推荐答案

由于您的匹配信息和比较数据位于两个独立的numberCollection中，因此同步这两个列表的唯一方法是按位置进行同步，这就是ROWNUM.

Because you have your matching-information and your compare-data in two independent numberCollections, the only way to synchronize these two lists is by position, which here is the ROWNUM.

但是您可以使用WITH子句使您的SQL更具可读性:

But you can make your SQL a bit more readable by using the WITH-clause:

WITH comparedata AS (
 SELECT statid.v StatNr, statval.v StatValue
   FROM (
       select column_value v
            , rownum       r  
         from table( numberCollection( 98, 45, 66, 153, 103, 67, 27 ) ) 
       ) statId,
       (
       select column_value v
            , rownum       r  
         from table( numberCollection(  1,  2,  1,   1,   3,  2,  2 ) )
       ) statVal
       where statId.r = statVal.r
  )
select t.statNr   
     , t.statValue
     , t.statName 
  from myTable t
  JOIN comparedata d ON d.StatNr ON t.StatNr
 where t.nr = 1234
  and status.StatValue <> statusNew.StatValue
order by ...

这里，您将定义一个名为"comparedata"的子查询，并带有映射的numberCollections的结果. 之后，您可以将比较数据"用作真实表中JOIN子句中的任何视图或表.

Here you define a named subquery called "comparedata" with the result of the mapped numberCollections. After this, you use "comparedata" as any view or table in the JOIN-clause with your real table.

实现目标的其他方法最终将需要更多的代码(如例程，该例程将"id = value; [id = value; ...]"等格式的字符串拆分为具有两列ID和价值. 但是，如果您必须更频繁地执行此类工作，则可以考虑是否值得花时间创建这种功能.

Other ways to achieve your goal will end up in more code (like a routine, that splits strings of a format like "id=value;[id=value;...]" into a collection with two columns ID and VALUE. But if you have to do such jobs more often, you may consider, if it´s worth to spend the time in creating such a function.

这篇关于plsql-Oracle选择集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！