从pl/sql游标中的数据中循环经过分组的数据列，然后循环该组的子数据的直接方法是什么

Question

我一直在搜索，但这可能只是因为我不知道在pl/sql中搜索什么术语才能找到它(我习惯了coldfusion(。

在coldfusion中，我可以这样做：

• 带有标准groupby子句的查询
• 然后在组级别循环结果
• 然后循环组中的项目

它看起来像这个简单的代码：

<cfquery>select group_name, person_name from table_name group by group_name</cfquery>
<cfoutput group="group_name">
#group_name#
<cfoutput>
- #person_name#
</cfoutput>
</cfoutput>

假设我有这样的数据：

**group_name**  **person_name**
group 1         person a
group 1         person b
group 1         person c
group 1         person d
group 2         person e
group 2         person f
group 2         person g
group 3         person h

我得到的结果是：

group 1
- person a
- person b
- person c
- person d
group 2
- person e
- person f
- person g
group 3
- person h

在我将数据拉入pl/sql游标后，如果有任何直接的示例可以进行这种类型的输出，我将不胜感激。我的最终目标是为每个组发送一封电子邮件，列出组中的人员，并将存储为blob的文件附加到数据库中(每个人一个文件(。

谢谢！

Answer 1

BEGIN
-- query with a standard group by clause
-- then loop over results at the group level
FOR c1 IN (select group_name from table_name group by group_name)
LOOP

-- << do some work with c1.group_name >>

-- and then loop over items in the group
FOR c2 IN (select person_name from table_name WHERE group_name = c1.group_name)
LOOP
-- << do some work with c2.person_name and/or c1.group_name >>
END LOOP;
END LOOP;
END;
/  

Answer 2

使用匿名PL/SQL块-

DECLARE
group_id    person_group.group_id%TYPE;
persons     person_group%ROWTYPE;
CURSOR c_groups IS
SELECT group_id 
FROM person_group 
GROUP BY group_id
ORDER BY 1; 
CURSOR c_persons(in_group VARCHAR2) IS
SELECT group_id, person_name
FROM person_group
WHERE group_id = in_group
ORDER BY 1;
BEGIN
OPEN c_groups;
LOOP
FETCH c_groups INTO group_id;
EXIT WHEN c_groups%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(group_id);
OPEN c_persons(group_id);
LOOP
FETCH c_persons INTO persons;
EXIT WHEN c_persons%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('  - '||persons.person_name);
END LOOP;
CLOSE c_persons;
END LOOP;
CLOSE c_groups;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('['||SQLERRM||']');
END;