PL/SQL引用游标中的另一个游标? [英] PL/SQL referencing another cursor in a cursor?

问题描述

我想创建一个过程，该过程选择已分配给任何特定用户的所有记录，然后向每个分配的用户发送一封个性化电子邮件，其中包含分配给他们的记录的列表.

I'd like to create a procedure that selects all records that have been assigned to any particular user, then send one personalized email to each assigned user that contains a list of the records that are assigned to them.

因此，如果myTable看起来像这样:

So if myTable looks like this:

ID    Assigned
1     Joe
2     Joe
3     Shelly

Joe的电子邮件将显示一个以行分隔的列表，其中包含记录1和2，而Shelly的电子邮件将显示记录3.

Joe's email would display a line-delimited list with records 1 and 2 in it, and Shelly's would display record 3.

我首先开始使用游标构建过程，但是1)不确定我是否可以在另一个游标中引用游标，2)不知道游标是否甚至是实现此目标的最佳方法.

I started building a procedure with cursors at first, but 1) wasn't sure if I could reference a cursor within another cursor, and 2) don't know if a cursor is even the best approach for this.

我的想法是，游标1将获得所有唯一的赋值(Joe，Shelly)，而游标2将在游标1循环内执行，并获得分配给当前游标1值的所有记录.

My thought was that cursor 1 would get all unique Assigned values (Joe, Shelly) and cursor 2 would be executed inside the cursor 1 loop and get all records assigned to the current cursor 1 value.

在适当方向上的任何见识或推动将不胜感激.

Any insight or nudges in the appropriate direction would be greatly appreciated.

推荐答案

可以在第一个游标中引用另一个游标:

It is possible to reference another cursor within the first one:

declare
  cursor c1 is
    select distinct Assigned from table_name;

  cursor c2(p_Assigned in varchar2) is
    select id, Assigned from table_name where Assigned = p_Assigned;
begin

  for r1 in c1 loop
    dbms_output.put_line('------- start mail --------');
    for r2 in c2(r1.Assigned) loop
      dbms_output.put_line(r2.id || ' ' || r2.Assigned);
    end loop;
    dbms_output.put_line('------- end mail -------');
  end loop;
end; 

但是您可以做得更好，请视您的版本而定.
您可以汇总记录，以便在一个查询中为每个用户获得一个记录，该记录的一列包含与 newline :
串联的记录.此处是一些解决方法.
您还可以使用xmlagg:

But you can do better, deppends on your version.
You can aggregate the records so in one query you'll get one record for each user with a column that contains the records concatenated with a newline:
here are some methods to this.
And you can also use xmlagg:

SELECT Assigned, rtrim(XMLAGG(xmlelement(e, id || ' ' || Assigned || chr(13)|| chr(10) )).extract('//text()'))
FROM table_name
GROUP BY Assigned

这篇关于PL/SQL引用游标中的另一个游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！