在GROUP BY查询中将值聚合到表类型中

有两种方法可以做到这一点。 首先,通过分析得到每一行中该性别的所有记录数。

DECLARE
  CURSOR gender_cursor IS
    SELECT gender, customer_id, count(*) over (partition by gender) cnt_in_gender,
    FROM   CUSTOMER
    ORDER BY gender;
  v_prev_gender varchar2(1) := '?';
BEGIN
  FOR gender IN gender_cursor LOOP
      IF gender.gender != v_prev gender then
        dbms_output.put_line('You will now see '||gender.cnt_in_gender);
        v_prev_gender := gender.gender);
      END IF;
      dbms_output.put_line(gender.customer_ids);
  END LOOP;
END;

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      CAST(COLLECT(customer_id) AS NUMBER_TABLE_TYPE) AS customer_ids
    FROM
      CUSTOMER
    GROUP BY
      gender;

  customer_ids NUMBER_TABLE_TYPE;
BEGIN
  FOR gender IN gender_cursor LOOP
    customer_ids := gender.customer_ids;
    FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
      dbms_output.put_line(customer_ids(i));
    END LOOP;
  END LOOP;
END;