PL / SQL is referencing another cursor in the cursor?
I would like to create a procedure that selects all the records that have been assigned to any particular user and then send one personalized email address to each assigned user that contains a list of the records that are assigned to them.
So if myTable looks like this:
ID Assigned
1 Joe
2 Joe
3 Shelly
Joe email will display a line-delimited list with entries 1 and 2, and Shelly will display post 3.
I first started creating a procedure using cursors, but 1) wasn't sure if I could reference a cursor inside another cursor and 2) I don't know if a cursor is even the best way to do this.
I thought that cursor 1 would get all the unique Assigned values (Joe, Shelly) and cursor 2 would execute inside cursor 1 loop and get all records assigned to the current value of cursor 1.
Any insight or nudge in the appropriate direction would be greatly appreciated.
source to share
You can refer to another cursor within the first:
declare
cursor c1 is
select distinct Assigned from table_name;
cursor c2(p_Assigned in varchar2) is
select id, Assigned from table_name where Assigned = p_Assigned;
begin
for r1 in c1 loop
dbms_output.put_line('------- start mail --------');
for r2 in c2(r1.Assigned) loop
dbms_output.put_line(r2.id || ' ' || r2.Assigned);
end loop;
dbms_output.put_line('------- end mail -------');
end loop;
end;
But you can do better based on your version.
You can aggregate records, so in one query, you will get one record per user, a column containing records concatenated with newline
:
here are some methods to do this.
And you can use xmlagg as well:
SELECT Assigned, rtrim(XMLAGG(xmlelement(e, id || ' ' || Assigned || chr(13)|| chr(10) )).extract('//text()'))
FROM table_name
GROUP BY Assigned
source to share