我有一个游标,它可能会为一个id返回2行。我想循环遍历光标,并在找到每个id的第一行时退出。
Cursor find_c is
Select t1.year,t1.sum(charges) charge
from table t1
join table t2 on t1.id=t2.id and t1.charge_id=t2.charge_id
where t1.id='3456'
group by t1.year
order by 2 desc;
数据:
Year Charge
2021 12
2020 56
程序
Begin
For lc in find_c loop
IF lc.charge > 0 then
lv_year = lc.year
END IF
END LOOP
End;
循环应该只返回第一行,即lv_year='2021'。在光标中添加rownum=1将获得最新的一行,但我正在for循环中寻找一些东西,以便在找到id 的第一年时退出
您没有指定使用的Oracle数据库版本。
如果是11g,那么fetch
子句将不起作用(因为它还不存在(:
SQL> select * from dept
2 fetch next 1 rows only;
fetch next 1 rows only
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
另一个答案建议根本不要使用游标或循环——这完全有道理,但您必须编写更多的代码来避免TOO_MANY_ROWS
(因为您无法将两行放入标量变量中(,并且可能还要避免NO_DATA_FOUND
。使用游标,您不必担心,因为Oracle会为您处理它。
因此,您可以执行所描述的操作——只运行一次循环迭代。但是,使用循环?只执行open - fetch - close
。这意味着您的代码可能是这样的(基于Scott的示例模式,因为我没有您的表(。
查询";模拟";你的工资是按部门计算的。我对最高的薪水感兴趣。
SQL> select d.dname, sum(e.sal) sumsal
2 from emp e join dept d on d.deptno = e.deptno
3 group by d.dname
4 order by sumsal desc;
DNAME SUMSAL
-------------- ----------
RESEARCH 10875 --> that's what I want
SALES 9400
ACCOUNTING 8750
程序:
SQL> declare
2 cursor find_c is
3 select d.dname, sum(e.sal) sumsal
4 from emp e join dept d on d.deptno = e.deptno
5 group by d.dname
6 order by sumsal desc;
7 cur_r find_c%rowtype;
8 lv_dname dept.dname%type;
9 begin
10 open find_c;
11 fetch find_c into cur_r;
12
13 if cur_r.sumsal > 0 then
14 lv_dname := cur_r.dname;
15 end if;
16
17 dbms_output.put_line('Picked DNAME = ' || lv_dname ||
18 '. Now, do something here, execute some more code');
19
20 -- some code goes here
21 dbms_output.put_line('First iteration is over; that''s the end');
22 close find_c;
23 end;
24 /
Picked DNAME = RESEARCH. Now, do something here, execute some more code --> good, RESEARCH is here
First iteration is over; that's the end
PL/SQL procedure successfully completed.
SQL>
只是为了表明如果没有光标,您可能会遇到一些问题:
TOO_MANY_ROWS:
SQL> declare
2 l_dname dept.dname%type;
3 l_sumsal number;
4 begin
5 select d.dname, sum(e.sal) sumsal
6 into l_dname, l_sumsal
7 from emp e join dept d on d.deptno = e.deptno
8 group by d.dname
9 order by sumsal desc;
10 dbms_output.put_line('Query executed');
11 end;
12 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
NO_DATA_FOUND:
SQL> declare
2 l_dname dept.dname%type;
3 l_sumsal number;
4 begin
5 select d.dname, sum(e.sal) sumsal
6 into l_dname, l_sumsal
7 from emp e join dept d on d.deptno = e.deptno
8 where 1 = 2 --> will cause NO_DATA_FOUND
9 group by d.dname
10 order by sumsal desc;
11 dbms_output.put_line('Query executed');
12 end;
13 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
SQL>
当然,可以修复这个问题(例如,使用异常处理程序部分(,但是-为什么要麻烦呢?
[EDIT,基于您的评论]
如果光标为每个ID返回多行,并且您希望跳过其余部分,则可以选择使用ROW_NUMBER
分析函数";排序";行,然后只为每个ID获取第一个ID。在我基于Scott表的示例中,它看起来像这样:
cursor find_c is
select dname, sumsal
from (select d.dname, sum(e.sal) sumsal,
row_number() over (partition by d.dname order by sum(e.sal) desc) rn
from emp e join dept d on d.deptno = e.deptno
group by d.dname
)
where rn = 1;
根据您的数据模型进行调整。
如果您的目标是一条记录,那么光标就是开销。我建议变量。正如另一个答案引发了对异常的担忧,这也是一个有效的场景。您可以处理未找到数据的异常,因为在我的查询中已经处理了多行,只使用fetch第一行。
Declare
Var1 <type> :=<initial value>;
Var2 <type> :=<initial value>;
Begin
Select x,y into var1,var2 from <table>
Order by x,y
Fetch first row only;
Exception when no_data_found
Var1 := <some value>;
Var2 := <some value>;
End;
使用光标循环一行没有多大意义,但这应该可以在中工作
Cursor find_c is
Select t1.year,t1.sum(charges) charge
from table t1
join table t2 on t1.id=t2.id and t1.charge_id=t2.charge_id
where t1.id='3456'
group by t1.year
order by 2 desc
FETCH NEXT 1 ROWS ONLY;