寻找更高效的"for loop"SQL

Question

我需要帮助格式化"for循环"的输出。

CREATE OR REPLACE PROCEDURE MYPROCEDURE (MYPARAM VARCHAR2)
AS 
BEGIN
DECLARE
type values_table    IS TABLE OF VARCHAR2(1999);        
MYVALUES                           values_table;
BEGIN
select subscriptionName || ', ' c.name 
BULK COLLECT INTO MYVALUES
from magazine_subscriptions ms
join customers c on ms.customer_id = c.customer_id
where ms.subscription_id > 0;
FOR i IN 1..MYVALUES.COUNT LOOP       
DBMS_OUTPUT.PUT_LINE(MYVALUES(i));
END LOOP;
END;
END;
/

当我运行这个时，我会得到订阅名称的重复。

示例：

Subscription bi-annual, james tills
Subscription bi-annual, jessie beckman
Subscription bi-annual, john sackman
Subscription 6-months, amelia hearts
Subscription 6-months, amanda tillman
Subscription 3-months, sally forth
Subscription 3-months, sadie hawkins
Subscription 3-months, susan beaker

有没有办法得到这个？

Subscription bi-annual
james tills
jessie beckman
john sackman
Subscription 6-months:
amelia hearts
amanda tillman
Subscription 3-months: 
sally forth
sadie hawkins
susan hearst

感谢您阅读本文。

Answer 1

您的代码看起来像Oracle。您可以将这些名称组合成一个字符串：

select subscriptionName, list_agg(c.name, ',') within group (order b c.name) 
from magazine_subscriptions ms join
customers c
on ms.customer_id = c.customer_id
where ms.subscription_id > 0
group by subscriptionName;

分隔符可以是任何内容。这只是用了一个逗号来说明。