pl/SQL for loop-CodePudding

DECLARE 
   a number(2);
  cnt number;
BEGIN 
    
   FOR a in 01 .. 12 LOOP 
     
     SELECT count(*) 
     INTO cnt
     FROM ECMS_ONLINE_TRANSACTION eoa
     WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                              AND TO_DATE('2021-01-01 01:59:59','YYYY-MM-DD HH24:MI:SS'); 
 
     dbms_output.put_line(' month: ' || a || ' time stamp 01:00:00- 01:59:59  number of transaction :' || cnt ); 
   END LOOP; 
END;

i having an issue on changing the month according to the loop in where the clause i have used between function the month of todate function have to be changed according to the loop if a becomes 1 then the month in where clause has to be changed according to 1 if a becomes 2 the month have be changed as 2

foe example

if a =1

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-01-01 01:59:59','YYYY-MM-DD HH24:MI:SS');

if a =2

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-02-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-02-01 01:59:59','YYYY-MM-DD HH24:MI:SS');

i have used timestamp variable

CodePudding user response：

Use this condition:

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-' || lpad(a, 2, '0') || '-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-' || lpad(a, 2, '0') || '-01 01:59:59','YYYY-MM-DD HH24:MI:SS');

It might be simpler to write this (but it won't use an index on CREATETIME if you have one):

WHERE to_char(eoa.CREATETIME, 'MM') = a
  AND to_char(eoa.CREATETIME, 'HH24' = '01'