Storing variables in Oracle SQL-CodePudding

I have a query that returns dates greater than 16/02/2022 and the number of this dates (count) where a column is more than 1(CALCULUS).

I want the dates that returns me this query in a VARIABLE. ¿Can I save the exact dates and the count in a VARIABLE?

SELECT COUNT(*) N, DATE
FROM  Table1
WHERE DATE >='16/02/2022'
AND CALCULUS > 1
GROUP BY DATE

CodePudding user response：

Here's one option; you'll need PL/SQL for that.

select (line #8) stores the result into a locally declared collection
the result is then displayed in the for loop (line #15)

SQL> set serveroutput on
SQL>
SQL> declare
  2    type    t_rec is record (cnt number,
  3                             datum date
  4                            );
  5    type    t_tab is table of t_rec index by binary_integer;
  6    l_tab   t_tab;
  7  begin
  8    select count(*), hiredate
  9      bulk collect into l_tab
 10      from emp
 11      where hiredate > date '1980-01-01'
 12        and empno >= 7900
 13      group by hiredate;
 14
 15    for i in l_tab.first .. l_tab.last loop
 16      dbms_output.put_line(l_tab(i).datum ||': '|| l_tab(i).cnt);
 17    end loop;
 18  end;
 19  /
03.12.1981: 2
23.01.1982: 1

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

and here is another way to work with the query result:

declare
    cursor cur_dates is
    select count(*) n, datum
    from table1
    where datum >= '16/02/2022'
    and calculus > 1
    group by date;
    
begin 
    for r in cur_dates loop
        /*do your stuff here*/ 
        end loop ;
end;