Return Multiple Rows With PL/SQL with nested loops
782404Aug 2 2010 — edited Aug 2 2010Hi friends...
I failed to execute this procedure....
CREATE OR REPLACE PROCEDURE A.validation AS
NO number;
CURSOR emp_cur is
select * from tableA ;
emp_rec emp_cur%rowtype;
email_to varchar2(200);
id_no varchar2(200);
person_name varchar2(200);
order_no varchar2(200);
invoice_detail varchar2(200);
order_detail varchar2(200);
default_email varchar2(200);
mesg varchar2(4000);
subj varchar2(4000);
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
BEGIN
default_email:='@private.com';
FOR emp_rec in emp_cur
LOOP
email_to:=emp_rec.staff_id||default_email;
select b.order_no into order_no from tableB b where b.order_id = emp_rec.order_id;
Select distinct(b.id_no) into id_no from TableB b where c.staff_id=emp_rec.staff_id;
Select distinct(c.person_name) into person_name from TableC c where c.staff_id=emp_rec.staff_id;
Select distinct(d.invoice_detail) into invoice_detail from TableD d where d.staff_id=emp_rec.staff_id;
Select distinct(d.order_detail) into order_detail from TableE e where d.staff_id=emp_rec.staff_id;
subj:='Test: '||order_no||' Test1 '||to_char(emp_rec.send_date, 'DD-MM-YYYY');
mesg:='Sir/Mdm.,'||CHR(10)||CHR(10)||'Test'||
lpad('ID NO : ', 50)||id_no||CHR(10)||
lpad('PERSON NAME : ', 50)||person_name||CHR(10)||
loop
lpad('INVOICE DETAIL : ', 50)||invoice_detail||CHR(10)||
lpad('ORDER DETAIL : ', 50)||order_detail||CHR(10)||
end loop;
'Test End.';
END LOOP;
exception when others then
dbms_output.put_line(sqlerrm);
END;
/
ID NO and PERSON NAME must appear once, but INVOICE DETAIL and ORDER DETAIL be displayed as many as possible according to the ID NO and PERSON NAME
I want the output to be like this:
Sir/Madam.,
Test
ID NO: ABC
PERSON NAME: George
INVOICE DETAIL: AAA6
ORDER DETAIL: Stationaries
INVOICE DETAIL: AAA88
ORDER DETAIL: Cookies
ID NO: CDE
PERSON NAME: Samantha
INVOICE DETAIL: RRR56
ORDER DETAIL: Sundry
Please help...Thanks....