SQL PI/SQL Function-CodePudding

 DECLARE 
   a number(2);
  a1 int;
BEGIN 
   FOR a in 01 .. 12 LOOP 
    SET a1 =(SELECT count(*) 
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('value of a: ' || a); 
  END LOOP; 
END;

**I have an error on this code I need the value of month which means a and the respective count and also in where condition the month should change according to a please someone help on this issue i am using oracle db **

CodePudding user response：

This is how I understood the question.

Sample data (only createtime column (displayed in YYYY-MM-DD format), as query doesn't use any other):

SQL> select * from ecms_online_transaction order by createtime;

CREATETIME
----------
2021-01-01
2021-02-24
2021-02-25
2021-04-13
2021-08-22
2021-08-29

6 rows selected.

SQL> set serveroutput on

As it seems you want to calculate number of rows per month (days, hours, years, ... - they don't matter), you could write such a query in the cursor and just display what it fetched):

SQL> BEGIN
  2     FOR cur_r IN (  SELECT TO_CHAR (createtime, 'mm') month, COUNT (*) cnt
  3                       FROM ecms_online_transaction
  4                   GROUP BY TO_CHAR (createtime, 'mm')
  5                   ORDER BY TO_CHAR (createtime, 'mm'))
  6     LOOP
  7        DBMS_OUTPUT.put_line (
  8           'Month = ' || cur_r.month || ', count = ' || cur_r.cnt);
  9     END LOOP;
 10  END;
 11  /
Month = 01, count = 1
Month = 02, count = 2
Month = 04, count = 1
Month = 08, count = 2

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

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-' || 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'); 

 dbms_output.put_line(' month: ' || a || ' time stamp 01:00:00- 01:59:59  number of transaction :' || cnt );

END LOOP; END;