PL / SQL：如何从表中选择数据和输入到包接受数组类型？ [英] PL/SQL: How select data from table and input into package accepting array type?

问题描述

我有一个PL / SQL电子邮件包，如下所示：

 创建或替换包mail_pkg 
 as 
 PRAGMA SERIALLY_REUSABLE; - 这避免了ORA-04068错误
类型数组是varchar2（255）的表; 
 
过程发送（p_sender_email在varchar2中，
 p_from在varchar2中默认为null，
在数组中的p_to默认数组（），
数组中的p_cc默认数组b $ b p_bcc in array default array（），
 p_subject in varchar2 default null，
 p_body in clob default null）; 
  

示例用法如下：

  begin 
 mail_pkg.send（p_sender_email =>'[e-mail]'，
 p_from =>'John Smith< [e-mail]>'， 
 p_to => mail_pkg.array（'[e-mail]'，'[e-mail]'），
 p_cc => mail_pkg.array（'[e-mail]'）， 
 p_bcc => mail_pkg.array（'[e-mail]'），
 p_subject =>'这是我的主题'，
 p_body =>'您好，这是您要求的电子邮件。 
 end; 
  

[注意：对于任何人寻找电子邮件包，这里是我得到它的链接：
http：// asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:255615160805 ]

我要使用它发送电子邮件，但是 p_to 输入中的电子邮件地址必须动态输入。也就是说，我不能硬编码他们像上面。它们需要来自SELECT语句。我熟悉编写select语句，但我不知道如何获得selecte语句的结果，我可以包括在

  p_to => mail_pkg.array（WHAT GOES HERE ???），
  

任何人都知道我可以连接一个SELECT语句（返回一些电子邮件地址）来使用这个包吗？

UPDATE：

根据以下反馈，解决方案为：

 创建或替换过程send_email（
 in_name IN varchar2 
）
 
 AS 
 
 v_body clob; 
 v_to_array mail_pkg.array：= mail_pkg.array（）; 
 v_counter int：= 1; 
 
 BEGIN 
 
 FOR r IN（SELECT person_email FROM email_table WHERE company_name = in_name）LOOP 
 v_to_array.extend; 
 v_to_array（v_counter）：= r.person_email; 
 v_counter：= v_counter +1; 
 END LOOP; 
 
  - 发送电子邮件
 
 mail_pkg.send（p_sender_email =>'[e-mail]'，
 p_from =>'[e-mail] '，
 p_to => v_to_array，
 p_bcc => mail_pkg.array（'[e-mail]'），
 p_subject =>'主题行在这里'，
 p_body =>'这是正文消息。 
 
 END send_email; 
  

解决方案

第一步是在循环中使用 SELECT 语句并填充数组;第二步是在调用 mail_pkg.send 时使用该数组，可能类似这样：

  declare 
 v_counter number：= 1; 
 v_to_arr mail_pkg.array：= mail_pkg.array（）; 
 begin 
 for r选择email_address从table_of_email_addresses循环
 v_to_arr.extend; 
 v_to_arr（v_counter）：= r.email_address; 
 v_counter：= v_counter +1; 
 end loop; 
 
 mail_pkg.send（p_to => v_to_arr，...）; 
 --note：这段代码可能需要一些微调。 
 ... 
 end; 
  

---

可能如下：

  declare 
 v_to_arr mail_pkg.array：= mail_pkg.array ）; 
 begin 
 
选择email_address 
批量从table_of_email_addresses中收集到v_to_arr 
中; 
 
 mail_pkg.send（p_to => v_to_arr，...）; 
 --note：这段代码可能需要一些微调。 
 ... 
 end; 
  

我对批量操作有些生疏，你可能想自己读一下。 / p>

I have a PL/SQL email package that looks like:

create or replace package mail_pkg 
as
PRAGMA SERIALLY_REUSABLE; -- this avoids ORA-04068 error
type array is table of varchar2(255);

procedure send( p_sender_email in varchar2,
            p_from         in varchar2 default null,
            p_to           in array default array(),
            p_cc           in array default array(),
            p_bcc          in array default array(),
            p_subject      in varchar2 default null,
            p_body         in clob default null);

Example usage would be:

 begin
    mail_pkg.send( p_sender_email => '[e-mail]',
               p_from => 'John Smith <[e-mail]>',
               p_to => mail_pkg.array( '[e-mail]','[e-mail]'),
               p_cc => mail_pkg.array( '[e-mail]' ), 
               p_bcc => mail_pkg.array( '[e-mail]' ), 
               p_subject => 'This is my subject', 
               p_body => 'Hello, this is the mail you requested.' );
    end;

[note: for anyone looking for an e-mail package, here's the link I got it from: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:255615160805 ]

I want to use it to send an email, but the email addresses in the p_to input must be input dynamically. That is, I can't hardcode them like above. They need to come from a SELECT statement. I'm familiar with writing select statements, but I don't know how to to get the result of the selecte statement into a format that I can include in the

p_to => mail_pkg.array( WHAT GOES HERE??? ),

code.

Anyone know how I can connect a SELECT statement (which returns some email addresses) to work with this package?

UPDATE:

Based on feedback below, the solution is:

create or replace procedure send_email ( 
  in_name IN varchar2
 )

AS

v_body clob;  
v_to_array mail_pkg.array := mail_pkg.array();
v_counter int := 1;

BEGIN

FOR r IN (SELECT person_email FROM email_table WHERE company_name=in_name) LOOP
  v_to_array.extend;
  v_to_array(v_counter) := r.person_email;
  v_counter := v_counter +1;
END LOOP;

-- send email

mail_pkg.send( p_sender_email => '[e-mail]',
               p_from => '[e-mail]',
               p_to => v_to_array,
               p_bcc => mail_pkg.array( '[e-mail]' ), 
               p_subject => 'the subject line goes here', 
               p_body => 'This is the body message.' );  

END send_email;

解决方案

You could do it in two steps. The first step is to use a SELECT statement in a loop and populate an array; the second step is to use that array in your call to mail_pkg.send, maybe something like this:

declare
  v_counter number := 1;
  v_to_arr mail_pkg.array := mail_pkg.array();
begin
  for r in Select email_address from table_of_email_addresses loop
    v_to_arr.extend;
    v_to_arr(v_counter) := r.email_address;
    v_counter := v_counter +1;
  end loop;

  mail_pkg.send(p_to => v_to_arr, ... );
  --note: this code may require some fine-tuning.
  ...
end ;

---

As suggested in the comments, a bulk collect could probably be used here, might be as simple as:

declare
  v_to_arr mail_pkg.array := mail_pkg.array();
begin

  Select email_address
  bulk collect into v_to_arr
  from table_of_email_addresses;

  mail_pkg.send(p_to => v_to_arr, ... );
  --note: this code may require some fine-tuning.
  ...
end ;

I'm a little rusty on bulk operations, you might want to read up on them yourself.

这篇关于PL / SQL：如何从表中选择数据和输入到包接受数组类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！