Oracle：当游标内部光标（使用游标表达式）时，选择distinct不起作用 [英] Oracle : Select distinct doesn't work when cursor inside a cursor (using cursor expression)

问题描述

我的需要是做一份报告列出一些人和他们的发票;由于我生成一些XML（XML发布者），一种方法是使用游标表达式。
SQL请求包含提供联系人列表的第一个游标，以及第一个列表中的第二个游标，每个联系人的发票。
问题是联系人SQL请求返回几次同一联系人（事实上他有发票）
所以我需要使用distinct，它不工作！

my need is to make a reports with a list of some people and their invoice; Since I am generating some XML (XML publisher) a way is to use cursor expression. The SQL request consist of a first cursor that give a list of contact(s), and a second cursor inside the first one list the invoice(s) for each contact. The problem is that the contact SQL request returns several times the same contact (as many as he has invoices in fact) so I need to use distinct, and it doesn't work !

为了有一个工作在任何地方的重现性目的的请求，我将使用基于oracle系统表的all_table和dual的sql请求;

To have a request that works everywhere for reproducibility purpose i will use sql request based on oracle system table all_table and dual ;

SQL请求重现联系人列表：

SQL request reproducing a list of contacts :

select 
    'ALBERT EINSTEIN' CONTACT_NAME 
  from 
  ALL_VIEWS
  where view_name IN ( 'ALL_INDEXES', 'ALL_TABLES') ;

=>
ALBERT EINSTEIN
ALBERT EINSTEIN

=> ALBERT EINSTEIN ALBERT EINSTEIN

重复连接到联系人的（1）发票的列表的SQL请求：

SQL request reproducing a list of (1) invoices connected to the contact :

SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, 'EUR' INVOICE_CURRENCY FROM DUAL  ;

=>

123456 10000 EUR

123456 10000 EUR

因此，使用游标表达式的报表的SQL是：

So the SQL for the report using cursor expression is :

select distinct
cursor (
  select 
    distinct 
    'ALBERT EINSTEIN' CONTACT_NAME 
    ,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, 'EUR' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( 'ALL_INDEXES', 'ALL_TABLES')
  ) AS CONTACTS
  from dual ;

以生成XML：

select dbms_xmlgen.getxml('
select distinct
cursor (
  select 
    distinct 
    ''ALBERT EINSTEIN'' CONTACT_NAME 
    ,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, ''EUR'' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( ''ALL_INDEXES'', ''ALL_TABLES'')
  ) AS CONTACTS
  from dual 
 ') from dual
;

即：

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <CONTACTS>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
    <INVOICES>
     <INVOICES_ROW>
      <INVOICE_NUMBER>123456</INVOICE_NUMBER>
      <INVOICE_AMOUNT>10000</INVOICE_AMOUNT>
      <INVOICE_CURRENCY>EUR</INVOICE_CURRENCY>
     </INVOICES_ROW>
    </INVOICES>
   </CONTACTS_ROW>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
    <INVOICES>
     <INVOICES_ROW>
      <INVOICE_NUMBER>123456</INVOICE_NUMBER>
      <INVOICE_AMOUNT>10000</INVOICE_AMOUNT>
      <INVOICE_CURRENCY>EUR</INVOICE_CURRENCY>
     </INVOICES_ROW>
    </INVOICES>
   </CONTACTS_ROW>
  </CONTACTS>
 </ROW>
</ROWSET>

=>显示两次相同的联系人使用相同的发票，是无用的，

=> show two times the same contact with the same invoice that is useless, and not expected since distinct is used.

通过注释第二个光标所有不同的工作：

By commenting the second cursor all the distinct works :

select dbms_xmlgen.getxml('
select distinct
cursor (
  select 
    distinct 
    ''ALBERT EINSTEIN'' CONTACT_NAME 
    --,CURSOR (SELECT 123456 INVOICE_NUMBER, 10000 INVOICE_AMOUNT, ''EUR'' INVOICE_CURRENCY FROM DUAL ) AS INVOICES
  from 
  ALL_VIEWS
  where view_name IN ( ''ALL_INDEXES'', ''ALL_TABLES'')
  ) AS CONTACTS
  from dual 
 ') from dual
;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <CONTACTS>
   <CONTACTS_ROW>
    <CONTACT_NAME>ALBERT EINSTEIN</CONTACT_NAME>
   </CONTACTS_ROW>
  </CONTACTS>
 </ROW>
</ROWSET>

因此，当游标只在游标内时会出现问题。

So the problem arise when there is a cursor inside a cursor only.

有人对此有解决方法吗？

Does some people have a workaround about that ?

推荐答案

有一段时间我意识到，如果我使用的 ORDER BY 条款在子光标那么它是不是给在 DISTINCT 值。
使用您的 子光标 不添加 ORDER BY 子句。它肯定会给 DISTINCT 值

I too get the same situation, after some time I realized that if I am using ORDER BYclause at sub cursor then it is not giving the DISTINCT values. Use your sub cursor without adding the ORDER BY clause. It will definitely give DISTINCT values

这篇关于Oracle：当游标内部光标（使用游标表达式）时，选择distinct不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！