Optimization of Oracle function

I wrote a function in the oracle database that calculates saldo and finds the date of the operation. Here’s how it works:

1. He will receive
2. Start subtracting the first cell of TURNOVER_DEBIT column from the vSumm and save it.
3. Then, from the saved number, subtract the next cell again
4. And so on until vSumm becomes less than or equal to 0.
5. When vSumm <= 0 it grabes and return OPER_DAY record.

It is taking over 20 minutes. Because the average saldo records for per customer is 70-80 and It is looping for 140 000 clients.

How can I optimize my query? Any help would be much appreciated! Thank you.

create function get_date_overdue
(iAccount varchar2, iSaldo number, iDate date := get_operday())
return date
is
    version CONSTANT char(14) := '->>26112020<<-';
    vDate date;
    vSumm number(22) := iSaldo;
    vSign integer := 0;
begin
    for r in (
        select --+ index_desc(s UK_SALDO_ACCOUNT_DAY)
          *
        from ibs.Saldo@iabs s
        where s.ACCOUNT_CODE = iAccount
          and  s.OPER_DAY between date '2015-01-01' and iDate
    ) loop
        vSumm := vSumm - r.TURNOVER_DEBIT;
        if vSign = 0 and vSumm <= 0 then
            vDate := r.OPER_DAY;
            vSign := 1;
        end if;
        EXIT WHEN vSign = 1;
    end loop;

    return vDate;

exception
    when NO_DATA_FOUND then return null;
end;

Answer

You can use the single query to fetch the OPER_DAY with the mentioned requirement and then return it from the function as follows:

SELECT OPER_DAY INTO vDate 
  FROM (SELECT SUM(S.TURNOVER_DEBIT) 
                 OVER(ORDER BY OPER_DAY DESC NULLS LAST) AS SUM_TURNOVER_DEBIT,
               OPER_DAY
          FROM IBS.SALDO@IABS S
         WHERE S.ACCOUNT_CODE = IACCOUNT
           AND S.OPER_DAY BETWEEN DATE '2015-01-01' AND IDATE)
  WHERE SUM_TURNOVER_DEBIT >= ISALDO
ORDER BY OPER_DAY DESC 
FETCH FIRST ROW ONLY;

Here, I have considered that you want to scan from highest OPER_DAY to lowest OPER_DAY to sum the TURNOVER_DEBIT and once the sum of the TURNOVER_DEBIT becomes equal or more than ISALDO, the scan should stop and you must return that OPER_DAY.

A single query can give results way faster than looping through each and every record of the table, do some arithmetic, and take decisions based on that arithmetic.