CODEWITHSUNDEEP
plsql
user6689989
user6689989

Reputation: 11

pl/sql block going in infinite loop

create or replace
function f_amt(date_in in varchar2) return number 
as 
BEGIN 
DECLARE 
   v_at            ES.AMT%TYPE; 
   i                number := 0;
   BEGIN 

      v_at := 0;
      WHILE v_at = 0 
      LOOP
        BEGIN
        select nvl(AMT,0) 
        into v_at 
        from es 
        where date1 = to_date(date_in,'MM/DD/YYYY') - i; 
        i := i + 1;    
        EXCEPTION when NO_DATA_FOUND 
        then
        v_at:=0; 
      END;
      END LOOP;
      RETURN v_at; 
   END; 
EXCEPTION 
  WHEN OTHERS THEN 
     RETURN 0;
END;

ES table has date and amount, and I want to amount as o/p for latest date available in ES for date given.

Eg: If date_in='20160223' and amount in ES is available for '20160220', then this amt should be returned in v_at and above while loop should exit. But it is going infinitely. Please suggest the correction in code required.

Upvotes: 0

Views: 1154

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

In case there is no date1 <= date_in you will search on forever. In order to find the last amt for date1 <= date_in you should use Oracle SQL's keep dense_rank last. 

create or replace function f_amt(date_in in varchar2) return number as 
  v_amt es.amt%type; 
begin 
  select max(amt) keep (dense_rank last order by date1)
  into v_amt
  from es
  where date1 <= to_date(date_in,'mm/dd/yyyy')
  and amt <> 0; 

  return v_amt; 
exception when others then 
  return 0;
end;

As you see, the PL/SQL function is only needed now to react on invalid date strings. Otherwise pure SQL would suffice. You may want to consider validating the date string somewhere else in PL/SQL (and give a proper error message in case it is invalid) and then use the mere SQL query with the date got instead of a PL/SQL function. (See also Mottor's comment on WHEN OTHERS and my answer to that.)

Upvotes: 0

Mottor
Mottor

Reputation: 1948

change:

EXCEPTION when NO_DATA_FOUND 
        then
        v_at:=0;

with:

EXCEPTION when NO_DATA_FOUND 
        then
        exit;

Infinite loop happens, because at some i there is always no_data_found exception and v_at is always 0. You can write it 

CREATE OR REPLACE FUNCTION f_amt (date_in IN VARCHAR2)
   RETURN NUMBER
AS
BEGIN
   FOR c1 IN (  SELECT amt
                  FROM es
                 WHERE date1 <= TO_DATE (date_in, 'MM/DD/YYYY')
                   AND nvl(amt,0) <> 0
              ORDER BY date1 DESC)
   LOOP
      RETURN c1.amt;
   END LOOP;

   RETURN 0;
END;

Try not to use EXCEPTION WHEN OTHERS. When OTHERS happens, you want to see it. And if a function is often called, try to avoid exceptions. They have to be actually exception. They are expensive. When you expect NO_DATA_FOUND, instead of select into open cursor and use %NOTFOUND or use for loop.

Upvotes: 1

P. Kouvarakis
P. Kouvarakis

Reputation: 1943

What happens if there is no prior value?

Wouldn't it be simpler, faster (and safer) to do:

select AMT
into v_at
from es
where date1 = (
   select max(date1)
   from es
   where date1 <= to_date(date_in,'MM/DD/YYYY')
         and AMT is not NULL
         and AMT <> 0)

There is no loop, only two index seeks (provided there is an index on date1). Also you don't mention if date1 is unique (but your code would also fail if not).

Upvotes: 2

Related Questions