情景:假如我有几百条数据要拼接成一条数据发送,这时候要用clob 大对象
存储过程
PROCEDURE maintian_comfirm_doposit_supplier_classify(p_request_id IN NUMBER) IS
message_clob CLOB;
temp_msg VARCHAR2(1000);
BEGIN
--创建一个临时的CLOB对象
dbms_lob.createtemporary(message_clob, true);
--for循环 取数据
FOR temp in(你的子查询数据(slect * from table)) LOOP
IF( temp.id!=null) THEN
--拼接数据
temp_msg := '分类:' || '11111' ||
' 供应商编码:' || '11' ||
' 供应商名称:' || '111' ||
chr(10)||'<br/>'
dbms_lob.writeappend(message_clob, length(temp_msg), temp_msg);
END IF
END LOOP
--循环结束判断clob对象是否为空 空不发邮件
IF (DBMS_LOB.GETLENGTH(message_clob)<> 0) THEN
dbms_output.put_line('发送邮件');
--发送邮件
send_email(1, message_clob);
END IF;
--释放lob
dbms_lob.freetemporary(message_clob);
BEGIN
END maintian_comfirm_doposit_supplier_classify;
--发送邮件
--发送邮件提醒
PROCEDURE send_email(p_type_flag IN NUMBER, p_message CLOB) IS
receive_person VARCHAR2(100) := '123@qq.com';
mail_subject VARCHAR2(50) := NULL;
BEGIN
IF (p_type_flag = 1) THEN
mail_subject := '维护提醒!';
END IF;
IF (p_type_flag = 2) THEN
mail_subject := '保证金补交提醒!';
END IF;
--发邮件
sys_mail_pkg.send_mail(p_mail_to => receive_person,
p_mail_cc => NULL,
p_mail_subject => mail_subject,
p_mail_body => p_message,
p_user_id => 1,
p_mail_source => NULL,
p_mail_source_id => NULL,
p_content_type => NULL
END send_email;
最终成果