游标内的光标用于比较同一列的未执行记录

Question

我正在使用小PL/SQL程序，该程序将列记录与使用光标进行比较。当我执行代码时，花费很长的时间似乎陷入了无尽的循环。

Table Data :-
STD
----
 2
 1
 4
 3
 6
 5
 8
 7
 10
 9
 12
 11

以下是我的代码： -

declare 
s number;
s1 number;
c number := 0;
cursor c1 is (select std from data);
cursor c2 is (select std from data);
begin
open c1;
loop
    fetch c1 into s;
     open c2;
        loop
            fetch c2 into s1;
            if s < s1
            then    
                c := c + 1;
           end if;
        end loop;
     insert into con values(c);
     close c2;       
end loop;
close c1; 
end;
/

预期结果： -

C=10
C=10
C=8
C=8
C=6
C=6
C=4
C=4
C=2
C=2
C=0
C=null

Answer 1

您应该添加

EXIT WHEN c1%NOTFOUND;

EXIT WHEN c2%NOTFOUND;

相应的提取后。例如

declare 
s number;
s1 number;
c number := 0;
cursor c1 is (select std from data);
cursor c2 is (select std from data);
begin
open c1;
loop
    fetch c1 into s;
    EXIT WHEN c1%NOTFOUND;
     open c2;
        loop
            fetch c2 into s1;
            EXIT WHEN c2%NOTFOUND;
            if s < s1
            then    
                c := c + 1;
           end if;
        end loop;
     insert into con values(c);
     close c2;       
end loop;
close c1; 
end;
/

Answer 2

如果我了解很好，则假设您的数据以某种方式按照您的发布方式订购；鉴于此，您需要计算数据集中的每个值，值大于当前的值，并在当前行之后出现。

说我们可以使用附加列（ID）考虑您的数据样本以给出订单：

create table data(ID, std) as (
    select  1,  2 from dual union all
    select  2,  1 from dual union all
    select  3,  4 from dual union all
    select  4,  3 from dual union all
    select  5,  6 from dual union all
    select  6,  5 from dual union all
    select  7,  8 from dual union all
    select  8,  7 from dual union all
    select  9, 10 from dual union all
    select 10,  9 from dual union all
    select 11, 12 from dual union all
    select 12, 11 from dual
)

如果我非常了解您的需求，则可以避免使用PLSQL和光标，并通过一个查询获得结果：

select d1.std, count(d2.id)
from data d1
       left outer join data d2
         on ( d1.ID < d2.ID and d2.std > d1.std)
group by d1.std, d1.id   
order by d1.ID  

在示例数据上，这给出了：

   STD     COUNT(D2.ID)
---------- ------------
     2           10
     1           10
     4            8
     3            8
     6            6
     5            6
     8            4
     7            4
    10            2
     9            2
    12            0
    11            0