遍历循环以查找重复的名称 [英] Traverse through loop to find repeated names

问题描述

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

I have the following results from query and I a plsql block where I loop through the records and send_email to customers.

匿名屏蔽

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

I would like to know what is the best approach if product_holder is repeating in query result then rather than sending multiple emails, would like to send one email with relevant details. E.g. In above case SMITH is repeated twice, so with above approach SMITH will get two emails, instead I would like to send one email to SMITH with product_noand product_catalogue

我该怎么做?

推荐答案

请勿为此在PL/SQL循环中进行循环-使用SQL为您准备好使用的数据.

Don't do loops within loops in PL/SQL for this - use SQL to give you the data ready for use.

首先，我们用一些测试数据创建表(我猜是数据类型-您将用自己的数据类型替换):

First we create your table with some test data (I'm guessing datatypes - you replace with your own) :

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
/

对于每个product_holder，我们要发送到mail_send过程的是一个包含product_no和product_catalogue的集合(数组).因此，首先是一个包含这两个元素的类型:

What we want to send to mail_send procedure for each product_holder is a collection (array) containing product_no and product_catalogue. So first a type that contains those two elements:

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

然后是该类型的嵌套表类型(集合类型):

And then a nested table type (collection type) of that type:

create type t_prod_cat_no_table as
   table of t_prod_cat_no
/

过程mail_send然后应接受product_holder和集合类型:

The procedure mail_send then should accept the product_holder and the collection type:

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模拟构建邮件.)

(I just use dbms_output to simulate building a mail.)

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

Then you can in SQL do a group by product_holder and let SQL generate the collection containing the data:

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;
/

以上代码块的输出将是:

The output of the above block will be:

-- 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

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

Doing it in SQL with a GROUP BY gives you everything in a single call from PL/SQL to SQL, which is a whole lot more efficient than first one call to get the distinct set of product_holder, loop over that, and then one call per product_holder to get the products for each holder.

更新:

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

Added order by to the collect function in the above code to show you have control over the order that the data is populated in the collection.

这篇关于遍历循环以查找重复的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！