Exit Loop when first record found

Question

I have a cursor that might return 2 rows for an id . I want to loop through the cursor and exit when the first row for each id is found .

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;

Data :

Year Charge
2021  12
2020  56 

Procedure

Begin 
For lc in find_c loop 
   IF lc.charge > 0 then 
      lv_year = lc.year
   END IF 
END LOOP
End;

The loop should return only the first row i.e) lv_year='2021'. Adding rownum=1 in the cursor will get me the latest row , but i am looking for something in the for loop to exit when the first year is found for an id

Answer 1

You didn't specify Oracle database version you use.

If it is 11g, then fetch clause won't work (as it didn't exist yet):

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

Another answer suggested not to use a cursor nor a loop at all - that perfectly makes sense, but then you have to write some more code to avoid TOO_MANY_ROWS (as you can't fit two rows into scalar variables) and, possibly, NO_DATA_FOUND. With a cursor, you don't have to worry about that as Oracle handles it for you.

Therefore, you could do what you described - run only one loop iteration. But, using a loop at all? Do just open - fetch - close. It means that your code might look like this (based on Scott's sample schema as I don't have your tables).

Query that "simulates" yours fetches sums of salaries per departments. I'm interested in the highest salary.

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

The procedure:

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>

---

Just to show that without cursor you might get some problems:

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>

Of course, you can fix that (e.g. using exception handler section), but - why bother?

---

[EDIT, based on your comment]

If cursor returns more than a single row for each ID and you want to skip the rest, one option to do that is to use ROW_NUMBER analytic function to "sort" rows and then fetch only the first one for each ID. In my example based on Scott's tables, that would looks like this:

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;

Adjust it to your data model.

Answer 2

If your aim is one record, then cursor is an overhead. I suggest variables. As the other answer raised a concern on the exceptions, which is a valid scenario too. You can handle the no data found exception as the multiple rows is already handled in my query with fetch first row only.

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;

Answer 3

Does not make much sense then to use a cursor for loop for one row, but this should work

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;