Loop query with a variable in Oracle

Svperstar Published at Dev

Svperstar

I have a query based on a date with get me the data I need for a given day (lets say sysdate-1):

SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
  TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
  || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
FROM UI.UIS_T_DIFFUSION
WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
AND PSTATE = 'OK'
AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR( sysdate-1, 'DD-MM-YYYY')
AND ROWNUM <= 22
GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');

Gives me this (as expected):

╔════════════╦══════════╗
║    DAY     ║ DURATION ║
╠════════════╬══════════╣
║ 2016-02-28 ║       303║
╚════════════╩══════════╝

Now I'm trying to add a loop to get the results for each day since 10-10-2015. Somehting like this:

╔═══════════╦══════════╗
║    DAY    ║ DURATION ║
╠═══════════╬══════════╣
║ 2016-02-28║       303║
╠═══════════╬══════════╣
║ 2016-02-27║       294║
╠═══════════╬══════════╣
║        ...║       ...║
╠═══════════╬══════════╣
║ 2015-10-10║        99║
╚═══════════╩══════════╝

I've tried to put the query inside a loop:

DECLARE
  i NUMBER := 0;
BEGIN
  WHILE i <= 142
  LOOP
    i := i+1;
    SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR(sysdate - i, 'DD-MM-YYYY')
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');
  END LOOP;
END;

but I'm getting this error:

Error report -
ORA-06550: line 7, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Can anyone tell me how to accomplish this?

Michael Broughton

While bastihermann gave you the query to get all of those values in a single result set, if you want to understand the issue with your pl/sql block, the following should simplify it for you. The error relates to the fact that, in pl/sql you need to select INTO local variables to contain the data for reference within the code.

To correct (and simplify with a FOR LOOP) your block:

DECLARE
  l_day      varchar2(12);
  l_duration varchar2(30);;
BEGIN
  -- don't need to declare a variable for an integer counter in a for loop
  For i in 1..142
  LOOP
    SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD'),
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') 
    INTO l_Day, l_duration
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR(sysdate - i, 'DD-MM-YYYY')
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');
    -- and here you would do something with those returned values, or there isn't much point to this loop. 
  END LOOP;
END;

Assuming you needed to do something with those values and want even more efficient, you could simplify even further with a cursor loop;

BEGIN
  -- don't need to declare a variable for an integer counter in a for loop
  For i_record IN
    (SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') the_Day,
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') the_duration
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') <= TO_CHAR( sysdate, 'DD-MM-YYYY')
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') >= TO_CHAR( sysdate-142, 'DD-MM-YYYY')  
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD')
    ORDER BY to_char(start_date,'dd-mm-yyyy')
    )
  LOOP
    -- and here you would do something with those returned values, but reference them by record_name.field_value. 
    -- For now I will put in the NULL; command to let this compile as a loop must have at least one command inside.
    NULL;
  END LOOP;
END;

Hope that helps

Collected from the Internet

Please contact [email protected] to delete if infringement.