In PLSQL How to put result of conditional aggregation into variable?-CodePudding

I use the following query to count the number of rows labeled as cheap, fair and expansive

select 
    sum(case when price_category = 'CHEAP' then 1 else 0 end) AS cheap,
    sum(case when price_category = 'FAIR' then 1 else 0 end) AS fair,
    sum(case when price_category = 'EXPANSIVE' then 1 else 0 end) AS expansive
from t;

Now how do i put the count value into 3 different variables?

CodePudding user response：

With sample data like this:

WITH t AS
    (
        SELECT 1 "ID", 'CHEAP' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 2 "ID", 'CHEAP' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 3 "ID", 'FAIR' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 4 "ID", 'EXPENSIVE' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 5 "ID", 'FAIR' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 6 "ID", 'CHEAP' "PRICE_CATEGORY" FROM DUAL UNION ALL
        SELECT 7 "ID", 'CHEAP' "PRICE_CATEGORY" FROM DUAL
    )

Using cursor for loop will do the job you are asking for. Result of your Select will be there for you to define the values for your variables. You can use your sql to put the result into 3 different variables like below

SET SERVEROUTPUT ON
Declare
    m_Cheap     Number;
    m_Fair      Number;
    m_Expensive Number;
Begin 
    FOR recCount IN 
                (
                    select 
                        sum(case when price_category = 'CHEAP' then 1 else 0 end) AS cheap,
                        sum(case when price_category = 'FAIR' then 1 else 0 end) AS fair,
                        sum(case when price_category = 'EXPENSIVE' then 1 else 0 end) AS expensive
                    from t  
                ) LOOP
        m_Cheap := recCount.CHEAP;
        DBMS_OUTPUT.PUT_LINE('CHEAP = ' || m_Cheap);
        m_Fair := recCount.FAIR;
        DBMS_OUTPUT.PUT_LINE('FAIR = ' || m_Fair);
        m_Expensive := recCount.EXPENSIVE;
        DBMS_OUTPUT.PUT_LINE('EXPENSIVE = ' || m_Expensive);
    END LOOP;
End;
--  R  e s u l t
--
--  anonymous block completed
--  CHEAP = 4
--  FAIR = 2
--  EXPENSIVE = 1

You can always do it like this also:

select 
                        sum(case when price_category = 'CHEAP' then 1 else 0 end) AS cheap,
                        sum(case when price_category = 'FAIR' then 1 else 0 end) AS fair,
                        sum(case when price_category = 'EXPENSIVE' then 1 else 0 end) AS expensive    
INTO m_Cheap, m_Fair, m_Expensive
                    from t

With the same Declare section of PL/SQL and the same DBMS_OUTPUT.PUT_LINE commands result is the same as above.

CodePudding user response：

Use SELECT ... INTO ... FROM ...:

DECLARE
  v_fair      PLS_INTEGER;
  v_cheap     PLS_INTEGER;
  v_expansive PLS_INTEGER;
BEGIN
  SELECT sum(case when price_category = 'CHEAP'     then 1 else 0 end) AS cheap,
         sum(case when price_category = 'FAIR'      then 1 else 0 end) AS fair,
         sum(case when price_category = 'EXPANSIVE' then 1 else 0 end) AS expansive
  INTO   v_cheap,
         v_fair,
         v_expansive
  FROM   t;

  DBMS_OUTPUT.PUT_LINE( v_cheap || ', ' || v_fair || ', ' || v_expansive );
END;
/

Which, for the sample data:

CREATE TABLE t (price_category) AS
SELECT 'CHEAP'     FROM DUAL CONNECT BY LEVEL <= 42 UNION ALL
SELECT 'FAIR'      FROM DUAL CONNECT BY LEVEL <= 17 UNION ALL
SELECT 'EXPANSIVE' FROM DUAL CONNECT BY LEVEL <= 99;

Outputs:

42, 17, 99

db<>fiddle here