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

Question

假设您有一个表（在 Oracle 中）：

CREATE TABLE CUSTOMER
(
  customer_id NUMBER,
  gender      CHAR(1)
);

并假设您有一个表类型：

CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

是否可以编写一个 GROUP BY 查询，以便对于每个组，该组的主键字段存储在NUMBER_TABLE_TYPE？例如：

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      SOME_MAGICAL_AGGREGATE_FUNCTION(customer_id) 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;

我知道这应该通过使用两个游标来完成（一个用于性别组，另一个用于查询每个组的客户）。我只是好奇这是否可以做到。 :)

Answer 1

有两种方法可以实现这一点。
首先，通过分析来获取每行中该性别的所有记录的计数。

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;