How to calculate the average value of all items stored in a cursor

Question

I have a couple of procedures that return a SYS_REFCURSOR. One is called cust_cursor and the other is prod_cursor. The latter stores products' IDs and quantities. So far, I am able to print each individual quantity, as shown in the following anonymous block.

DECLARE
    cust_cursor   SYS_REFCURSOR;
    cust_id       ex1.address_detail.id_customer%TYPE;
    addr_id       ex1.address_detail.id_address%TYPE;
    prod_cursor   SYS_REFCURSOR;
    prod_id       ex1.SALES_ORDER_DETAIL.ID_PROD%TYPE;
    prod_qty      ex1.SALES_ORDER_DETAIL.ORDER_QTY%TYPE;
BEGIN
    ex1.INFO_SALES.TOP_CUSTOMERS (cust_cursor);

    LOOP
        FETCH cust_cursor INTO cust_id, addr_id;

        EXIT WHEN cust_cursor%NOTFOUND;
        ex1.info_sales.top_products (addr_id, prod_cursor);
        DBMS_OUTPUT.put_line (
            'Customer' || CHR (9) || '|' || CHR (9) || 'Address');
        DBMS_OUTPUT.put_line (
            cust_id || CHR (9) || CHR (9) || '|' || CHR (9) || addr_id);
        DBMS_OUTPUT.put_line (
            'Product' || CHR (9) || '|' || CHR (9) || 'Quantity');

        LOOP
            FETCH prod_cursor INTO prod_id, prod_qty;

            EXIT WHEN prod_cursor%NOTFOUND;
            DBMS_OUTPUT.put_line (
                prod_id || CHR (9) || CHR (9) || '|' || CHR (9) || prod_qty);
        END LOOP;

        CLOSE prod_cursor;

        DBMS_OUTPUT.put_line (
            '------------------------------------------------------------');
    END LOOP;

    CLOSE cust_cursor;
END;

How can I calculate the average value of these quantities per customer in prod_cursor?

I am providing an example query to get prod_cursor.

SELECT products.ID_PROD, products.ORDER_QTY
  FROM (SELECT sale.id_sales_order,
               sale.customer_id,
               sale.bill_address_id,
               detail.id_prod,
               detail.unit_price,
               detail.order_qty,
               detail.unit_price * detail.order_qty AS "Total Sales",
               ROW_NUMBER ()
                   OVER (ORDER BY detail.unit_price * detail.order_qty DESC)
                   AS product_rank
          FROM ex1.sales  sale
               INNER JOIN ex1gon.sales_order_detail detail
                   ON sale.ID_SALES_ORDER = detail.ID_SALES_ORDER
         WHERE sale.BILL_ADDRESS_ID = 100) products
 WHERE products.PRODUCT_RANK < 5;

Answer 1

Answer to your comment about single row result of average, the value will be same for all rows in cursor, it depends how you will use it in fetch.

You can do it with analityc function

SELECT products.ID_PROD, products.ORDER_QTY, 
 avg(products.ORDER_QTY) over (partition by products.customer_id) AvgQnty
  FROM (SELECT sale.id_sales_order,
               sale.customer_id,
               sale.bill_address_id,
               detail.id_prod,
               detail.unit_price,
               detail.order_qty,
               detail.unit_price * detail.order_qty AS "Total Sales",
               ROW_NUMBER ()
                   OVER (ORDER BY detail.unit_price * detail.order_qty DESC)
                   AS product_rank
          FROM ex1.sales  sale
               INNER JOIN ex1gon.sales_order_detail detail
                   ON sale.ID_SALES_ORDER = detail.ID_SALES_ORDER
         WHERE sale.BILL_ADDRESS_ID = 100) products
 WHERE products.PRODUCT_RANK < 5;