SQL Using cursors to loop through table

Question

I'm trying to loop through a table, and increase the count of a variable if a certain case is met. It doesn't provide any errors, but it doesn't display any number for the cheap, fair, exp counters.

CREATE OR REPLACE PROCEDURE product_price_report AS
C_LIST_PRICE PRODUCTS.LIST_PRICE%TYPE;
LIST_PRICE PRODUCTS.LIST_PRICE%TYPE;
avg_price products.list_price%TYPE;
max_price products.list_price%TYPE;
min_price products.list_price%TYPE;
CURSOR PROD_CURSOR IS
    SELECT LIST_PRICE
    FROM PRODUCTS;
cheap_count NUMBER;
fair_count NUMBER;
exp_count NUMBER;
BEGIN

SELECT AVG(LIST_PRICE) INTO avg_price FROM PRODUCTS;
SELECT MAX(LIST_PRICE) INTO max_price FROM PRODUCTS;
SELECT MIN(LIST_PRICE) INTO min_price FROM PRODUCTS;

BEGIN
    OPEN PROD_CURSOR;
    LOOP
        FETCH PROD_CURSOR INTO C_LIST_PRICE;
        EXIT WHEN PROD_CURSOR%NOTFOUND;
        CASE 
            WHEN LIST_PRICE < (AVG_PRICE - MIN_PRICE) / 2 THEN CHEAP_COUNT := CHEAP_COUNT + 1;
            WHEN LIST_PRICE > (MAX_PRICE - AVG_PRICE) / 2 THEN FAIR_COUNT := FAIR_COUNT + 1;
            ELSE EXP_COUNT := EXP_COUNT + 1;
        END CASE;
    END LOOP;
    CLOSE PROD_CURSOR;
    
    DBMS_OUTPUT.PUT_LINE('Cheap: ' || CHEAP_COUNT);
    DBMS_OUTPUT.PUT_LINE('Fair: ' || FAIR_COUNT);
    DBMS_OUTPUT.PUT_LINE('Expensive: ' || EXP_COUNT);
END;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many rows');  
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Undefined error');     
END;
/

BEGIN
product_price_report;
END;
/

Why is this? Thanks

Answer 1

Beyond not having set server output your procedure has other errors which will prevent you from getting meaningful output. First off you did not initialize your counters either in the declaration nor in the execution section. As a result they contain NULL (plsql default value for all uninitialized variables), thus the results of each counter increment is itself NULL ( cheap_count + 1 is the same as NULL+1 which is NULL). Secondly you fetch your cursor into the variable c_price_list, but then your case when conditions refer to the variable list_price, which is also NULL thus every row falls into the else condition of the case. Either of those alone is sufficient to insure incorrect results.
While not causing incorrect results, there is no reason for you to use separate selects to get the min,avg, and max values - a single select can get all 3. Further, there is no reason to loop over a cursor even, the entire process can be resolved via a single query:

with base_line( min_price, avg_price, max_price) as 
    (select min(list_price), avg(list_price), max(list_price)
       from products
    )  
select sum(cheap) chaep, sum(fair) fair, sum(expensive) expensive
  from (select case when list_price < (avg_price - min_price) / 2 then 1 else 0 end as cheap   
             , case when list_price > (max_price - avg_price) / 2 then 1 else 0 end as fair  
             , case when not list_price < (avg_price - min_price) / 2 
                     and not list_price > (max_price - avg_price) / 2 then 1 else 0 end as expensive 
         from products
         cross join base_line 
      ) w;

See fiddle. In addition to the above query the fiddle also contains a revised version of your original post, and for comparison, a procedure with a using the above query in a cursor (in order to generate dbms_output).

Finally, you may want to review your formulas. Using test rows with list_price of 25, 50, and 75 (easy to tell min,avg, max). The necessary value for cheap classification is way less the the minimum. But that is just one test case and perhaps a special one at that. But you did not provide test date and expected results.

Answer 2

Welcome,

1. you have to enable serveroutput on/termout on; if using sqlplus
2. dbms_out has to be used inside loop for this context

try and let us know.