How to get an hourly output for 24 hours oracle-CodePudding

Hi everyone im very very new to messing with oracle but I have to figure out how to somehow loop a query to get an hourly output on a 24 hour interval. I dont know how to declare that the time should start whenever SYSDATE is and then to add hour hour each to that. For example something like:

Declare the system time here Add an hour for the start time until 24 hours is complete. Do i use loop while for that so it automatically generates without having to put in new query everyday?

CodePudding user response：

Not sure what are you expecting, but here is sql that will give you 24 hours (records) starting with the hour of Sysdate. You can use it as a cursor to loop through or something else....

Select
    To_Char(SYSDATE, 'hh24') "HOUR_START",
    CASE 
        WHEN To_Number(To_Char(SYSDATE, 'hh24'))   LEVEL-1 > '23' THEN
            LPAD(To_Number(To_Char(SYSDATE, 'hh24'))   LEVEL-1 - 24, 2, '0')
    ELSE 
        LPAD(To_Number(To_Char(SYSDATE, 'hh24'))   LEVEL-1, 2, '0')
    END "HOURS_24"
From
    Dual
CONNECT BY LEVEL <= 24

CodePudding user response：

No need to use a loop or a cursor within a PL/SQL block, but using a clean SELECT statement containing XML techniques such as

SELECT TO_NUMBER(TO_CHAR(sysdate, 'hh24')) AS hour_start,
       XMLCast(column_value AS INT) AS hours         
  FROM XMLTable('1 to 24')
 ORDER BY CASE WHEN SIGN(hours-hour_start)>=0 THEN hours ELSE hours 24 END

Demo