如何将游标返回的附件结果传入send_smtp_mail存储过程参数？

根据你的需求，我来帮你梳理两种可行的实现方式，核心取决于你的send_smtp_mail存储过程是否支持集合类型的参数：

1. 推荐方案：存储过程支持集合类型参数（同一封邮件带多个附件） #

如果要在同一封邮件中添加多个附件，首先需要确保存储过程的p_attach_name和p_attach_mime参数是集合类型（比如Oracle的嵌套表、VARRAY）。如果还没定义对应的集合类型，先创建：

-- 创建存储过程需要的集合类型
CREATE OR REPLACE TYPE attach_name_list AS TABLE OF VARCHAR2(255);
CREATE OR REPLACE TYPE attach_mime_list AS TABLE OF VARCHAR2(255);

然后通过批量收集游标数据到集合中，再传入存储过程：

DECLARE
  -- 定义游标，替换成你的实际查询逻辑
  CURSOR c_attachments IS
    SELECT attach_name, attach_mime_type
    FROM your_attachment_table;
  
  -- 声明对应类型的集合变量
  v_attach_names attach_name_list := attach_name_list();
  v_attach_mimes attach_mime_list := attach_mime_list();
BEGIN
  -- 批量收集游标数据到集合
  OPEN c_attachments;
  LOOP
    FETCH c_attachments BULK COLLECT INTO v_attach_names, v_attach_mimes LIMIT 100;
    EXIT WHEN v_attach_names.COUNT = 0;
    
    -- 调用存储过程，传入集合参数
    send_smtp_mail(
      p_to => 'xyx@xyz.com',
      p_from => 'xyx@xyz.com',
      p_subject => 'Test',
      p_text_msg => NULL,
      p_attach_name => v_attach_names,
      p_attach_mime => v_attach_mimes
    );
  END LOOP;
  CLOSE c_attachments;
END;
/

也可以用更简洁的BULK COLLECT INTO直接收集：

DECLARE
  -- 定义记录类型和对应的集合类型
  TYPE attach_rec IS RECORD(
    name VARCHAR2(255),
    mime VARCHAR2(255)
  );
  TYPE attach_rec_list IS TABLE OF attach_rec;
  
  v_attach_list attach_rec_list;
BEGIN
  -- 直接批量收集游标数据到记录集合
  SELECT attach_name, attach_mime_type
  BULK COLLECT INTO v_attach_list
  FROM your_attachment_table;
  
  -- 转换记录集合为存储过程需要的参数集合并调用
  send_smtp_mail(
    p_to => 'xyx@xyz.com',
    p_from => 'xyx@xyz.com',
    p_subject => 'Test',
    p_text_msg => NULL,
    p_attach_name => CAST(MULTISET(SELECT a.name FROM TABLE(v_attach_list) a) AS attach_name_list),
    p_attach_mime => CAST(MULTISET(SELECT a.mime FROM TABLE(v_attach_list) a) AS attach_mime_list)
  );
END;
/

2. 兼容方案：存储过程仅支持单个附件参数（发送多封邮件） #

如果你的send_smtp_mail只能处理单个附件，那只能循环游标，每次调用存储过程发送一封带单个附件的邮件：

DECLARE
  -- 定义游标
  CURSOR c_attachments IS
    SELECT attach_name, attach_mime_type
    FROM your_attachment_table;
  
  -- 声明单个附件的变量
  v_attach_name VARCHAR2(255);
  v_attach_mime VARCHAR2(255);
BEGIN
  OPEN c_attachments;
  LOOP
    -- 逐行获取游标数据
    FETCH c_attachments INTO v_attach_name, v_attach_mime;
    EXIT WHEN c_attachments%NOTFOUND;
    
    -- 每次调用发送带单个附件的邮件
    send_smtp_mail(
      p_to => 'xyx@xyz.com',
      p_from => 'xyx@xyz.com',
      p_subject => 'Test',
      p_text_msg => NULL,
      p_attach_name => v_attach_name,
      p_attach_mime => v_attach_mime
    );
  END LOOP;
  CLOSE c_attachments;
END;
/

关键说明 #

• 如果需要在同一封邮件中包含多个附件，必须修改send_smtp_mail存储过程，将p_attach_name和p_attach_mime参数改为集合类型，这是最合理的实现方式。
• 上述代码中的your_attachment_table需要替换为你实际的数据源表或查询逻辑。

内容的提问来源于stack exchange，提问作者Abha