谁帮我修改个存储过程啊 搞了好久没出来
过程是这样的,输出格式如图1,
需求:我想把过程修改一下实现相同的行合并,要实现图2create or replace Procedure EC_Mail_formate
(
in_master_id in varchar2,
in_mail_info_id in varchar2,
in_line number
)
as
i number;
num number;
k number;
v_line_html varchar2(4000);
v_tr varchar2(4000);
begin
v_line_html:=null;
v_tr:='';
i:=1;
select count(1) into num from mds_ec_master where master_id=in_master_id;
if num>0 then
v_tr:='<table style="width:800px; border-width:1px; padding-left: 4px; padding-right: 4px;'||
' padding-top: 1px; padding-bottom: 1px" cellspacing="1" bgcolor="#000000">';
insert into common_mail_html_text values(1,v_tr,in_line,in_mail_info_id);
for c in (select ecr_no,eco_no,mn_no,oem_ec,to_char(creation_date,'yyyy-mm-dd') creation_date,
to_char(issue_date,'yyyy-mm-dd') issue_date,ec_type,responsibility,oem,(select distinct a.model from mgs_model a,mgs_pn b
where a.model_key=b.model_key and a.model_key=c.model_key and b.oem=c.oem ) model,classification,factory_status,site
,re,reject_reason,requested_by,to_char(in_date,'yyyy-mm-dd') in_date ,reason,
replace(replace(replace(subject ,'>','>') ,'<','<') ,chr(10),'<br/>') subject,
replace(replace(replace(description ,'>','>') ,'<','<') ,chr(10),'<br/>') description from mds_ec_master c where master_id=in_master_id) loop
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>MITAC ECR:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.ecr_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>MITAC ECO:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.eco_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>MN No:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.mn_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>OEM ECR:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.oem_ec||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(2,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Add into system date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.creation_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Issue Date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.issue_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>EC Type:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.ec_type||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Responsibility:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.responsibility||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(3,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>OEM:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.oem||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Model:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.model||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Classification:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.classification||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Factory phase in Status:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.factory_status||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(4,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Site:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.site||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Re:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.re||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Reject Reason:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.reject_reason||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Requested By:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.requested_by||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(5,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Phase In Date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.in_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Reason for change:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.REASON||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Subject: </font></td>'
||'<td colspan=3 bgcolor=#FFFFFF><font face=Arial>'||c.subject||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(6,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Description:</font></td>'
||'<td colspan=7 bgcolor=#FFFFFF><font face=Arial>'||substr(replace(c.Description ,chr(10),'<br/>'),1,3850)||'</font></td>'
||'</tr></table>';
insert into common_mail_html_text values(7,v_tr,in_line,in_mail_info_id);
end loop;
else
insert into common_mail_html_text values(1,'',in_line,in_mail_info_id);
end if;
commit;
exception when others then
BEGIN
rollback;
insert into common_mail_notice (MAIL_ID,MAIL_INFO_ID,PARAMS,
MAILED,MAILED_DATE,CREATOR,CREATION_DATE,UPDATER,UPDATE_DATE)
values (COMMON_MAIL_NOTICE_SEQ.nextval,'666',
'[email protected]'||'|||'||'EC_Mail_formate|'||' procedure error;'||'in_master_id:'||in_master_id||';in_mail_info_id:'||in_mail_info_id
,'N','','SYS',sysdate,'SYS',sysdate) ;
commit;
END;
end;图1图2
过程是这样的,输出格式如图1,
需求:我想把过程修改一下实现相同的行合并,要实现图2create or replace Procedure EC_Mail_formate
(
in_master_id in varchar2,
in_mail_info_id in varchar2,
in_line number
)
as
i number;
num number;
k number;
v_line_html varchar2(4000);
v_tr varchar2(4000);
begin
v_line_html:=null;
v_tr:='';
i:=1;
select count(1) into num from mds_ec_master where master_id=in_master_id;
if num>0 then
v_tr:='<table style="width:800px; border-width:1px; padding-left: 4px; padding-right: 4px;'||
' padding-top: 1px; padding-bottom: 1px" cellspacing="1" bgcolor="#000000">';
insert into common_mail_html_text values(1,v_tr,in_line,in_mail_info_id);
for c in (select ecr_no,eco_no,mn_no,oem_ec,to_char(creation_date,'yyyy-mm-dd') creation_date,
to_char(issue_date,'yyyy-mm-dd') issue_date,ec_type,responsibility,oem,(select distinct a.model from mgs_model a,mgs_pn b
where a.model_key=b.model_key and a.model_key=c.model_key and b.oem=c.oem ) model,classification,factory_status,site
,re,reject_reason,requested_by,to_char(in_date,'yyyy-mm-dd') in_date ,reason,
replace(replace(replace(subject ,'>','>') ,'<','<') ,chr(10),'<br/>') subject,
replace(replace(replace(description ,'>','>') ,'<','<') ,chr(10),'<br/>') description from mds_ec_master c where master_id=in_master_id) loop
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>MITAC ECR:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.ecr_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>MITAC ECO:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.eco_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>MN No:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.mn_no||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>OEM ECR:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.oem_ec||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(2,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Add into system date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.creation_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Issue Date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.issue_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>EC Type:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.ec_type||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Responsibility:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.responsibility||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(3,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>OEM:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.oem||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Model:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.model||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Classification:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.classification||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Factory phase in Status:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.factory_status||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(4,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Site:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.site||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Re:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.re||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Reject Reason:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.reject_reason||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Requested By:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.requested_by||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(5,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Phase In Date:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.in_date||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Reason for change:</font></td>'
||'<td bgcolor=#FFFFFF nowrap><font face=Arial>'||c.REASON||'</font></td>'
||'<td bgcolor=#B0C4DE nowrap><font face=Arial>Subject: </font></td>'
||'<td colspan=3 bgcolor=#FFFFFF><font face=Arial>'||c.subject||'</font></td>'
||'</tr>';
insert into common_mail_html_text values(6,v_tr,in_line,in_mail_info_id);
v_tr:='<tr><td bgcolor=#B0C4DE nowrap><font face=Arial>Description:</font></td>'
||'<td colspan=7 bgcolor=#FFFFFF><font face=Arial>'||substr(replace(c.Description ,chr(10),'<br/>'),1,3850)||'</font></td>'
||'</tr></table>';
insert into common_mail_html_text values(7,v_tr,in_line,in_mail_info_id);
end loop;
else
insert into common_mail_html_text values(1,'',in_line,in_mail_info_id);
end if;
commit;
exception when others then
BEGIN
rollback;
insert into common_mail_notice (MAIL_ID,MAIL_INFO_ID,PARAMS,
MAILED,MAILED_DATE,CREATOR,CREATION_DATE,UPDATER,UPDATE_DATE)
values (COMMON_MAIL_NOTICE_SEQ.nextval,'666',
'[email protected]'||'|||'||'EC_Mail_formate|'||' procedure error;'||'in_master_id:'||in_master_id||';in_mail_info_id:'||in_mail_info_id
,'N','','SYS',sysdate,'SYS',sysdate) ;
commit;
END;
end;图1图2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货