遍历循环以查找重复的名称

Question

我从查询中得到以下结果，并在plsql块中浏览记录并向客户发送send_email。

匿名块

FOR i IN (SELECT product_no, product_holder,product_catalogue FROM
product_master)
LOOP
mail_send('PRODMASTER',i.product_holder, i.product_no,i.product_catalogue);
END LOOP;

我想知道最好的方法product_holder是重复查询结果，而不是发送多封电子邮件，而是发送一封包含相关详细信息的电子邮件。例如，在上述情况下SMITH重复两次，所以用上面的方法将史密斯拿到两封电子邮件，而不是我想发一封电子邮件SMITH与product_no和product_catalogue

我怎样才能做到这一点？

Answer 1

为此，请不要在PL / SQL的循环内进行循环-使用SQL为您准备好要使用的数据。

首先，我们使用一些测试数据创建表（我正在猜测数据类型-您将使用自己的数据类型进行替换）：

create table product_master (
   product_no        varchar2(10)
 , product_holder    varchar2(10)
 , product_catalogue varchar2(10)
)
/

insert into product_master values ('1', 'SMITH', 'TEMP')
/
insert into product_master values ('2', 'SMITH', 'TEMP')
/
insert into product_master values ('3', 'HARRY', 'ARCH')
/
insert into product_master values ('4', 'TOM'  , 'DEPL')
/
commit
/

我们想要发送给mail_send过程的product_holder是包含product_no和的集合（数组）product_catalogue。因此，首先是一个包含这两个元素的类型：

create type t_prod_cat_no as object (
   product_no        varchar2(10)
 , product_catalogue varchar2(10)
)
/

然后是该类型的嵌套表类型（集合类型）：

create type t_prod_cat_no_table as
   table of t_prod_cat_no
/

mail_send然后，该过程应接受product_holder和的收集类型：

create or replace procedure mail_send (
   p_parameter        in varchar2
 , p_product_holder   in varchar2
 , p_product_cats_nos in t_prod_cat_no_table
)
is
begin
   dbms_output.put_line('-- BEGIN '||p_parameter||' --');
   dbms_output.put_line('Dear '||p_product_holder);
   dbms_output.put_line('Your products are:');
   for i in 1..p_product_cats_nos.count loop
      dbms_output.put_line(
         'Catalogue: '||p_product_cats_nos(i).product_catalogue||
         ' - No: '||p_product_cats_nos(i).product_no
      );
   end loop;
end mail_send;
/

（我只是使用dbms_output来模拟构建邮件。）

然后，您可以在SQL中执行group by product_holder并让SQL生成包含数据的集合：

begin
   for holder in (
      select pm.product_holder
           , cast(
                collect(
                   t_prod_cat_no(pm.product_no,pm.product_catalogue)
                   order by pm.product_catalogue
                          , pm.product_no
                ) as t_prod_cat_no_table
             ) product_cats_nos 
        from product_master pm
       group by pm.product_holder
       order by pm.product_holder
   ) loop
      mail_send(
         'PRODMASTER'
       , holder.product_holder
       , holder.product_cats_nos
      );
   end loop;
end;
/

上面的块的输出将是：

-- BEGIN PRODMASTER --
Dear HARRY
Your products are:
Catalogue: ARCH - No: 3
-- BEGIN PRODMASTER --
Dear SMITH
Your products are:
Catalogue: TEMP - No: 1
Catalogue: TEMP - No: 2
-- BEGIN PRODMASTER --
Dear TOM
Your products are:
Catalogue: DEPL - No: 4

使用SQL在SQL中执行此操作，GROUP BY可以通过PL /
SQL到SQL的单个调用为您提供一切，这比第一个调用获取独特的product_holder，循环遍历，然后每个product_holder获取一个调用的效率要高得多。每个持有人的产品。

更新：

在上面的代码中添加order by到collect函数中，以显示您可以控制集合中数据的填充顺序。