PL/SQL referencing another cursor in a cursor?
It is possible to reference another cursor within the first one:
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, deppends on your version.
You can aggregate the records so in one query you'll get one record for each user with a column that contains the records concatenated with a newline
:
here are some methods to this.
And you can also use xmlagg:
SELECT Assigned, rtrim(XMLAGG(xmlelement(e, id || ' ' || Assigned || chr(13)|| chr(10) )).extract('//text()'))
FROM table_name
GROUP BY Assigned
userDrew
Updated on June 04, 2022Comments
-
userDrew almost 2 years
I'd like to create a procedure that selects all records that have been assigned to any particular user, then send one personalized email 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's email would display a line-delimited list with records 1 and 2 in it, and Shelly's would display record 3.
I started building a procedure with cursors at first, but 1) wasn't sure if I could reference a cursor within another cursor, and 2) don't know if a cursor is even the best approach for this.
My thought was that cursor 1 would get all unique Assigned values (Joe, Shelly) and cursor 2 would be executed inside the cursor 1 loop and get all records assigned to the current cursor 1 value.
Any insight or nudges in the appropriate direction would be greatly appreciated.