I have a function that have this code
for i in cursor
loop
variavel1=i.id
while i.id=variavel1
loop
---- do someting
--- incriment variavel1 with next i.id
end loop;
end loop;
I need to increment the variavel1 with the next id that i have in the i (object that have the date from the cursor).
If concatenation is everything you need, you can use pure sql:
select id, listagg(text) within group (order by text) as list
from (
select 1 id, 'abc' text from dual union all
select 1 id, 'def' text from dual union all
select 1 id, 'ghi' text from dual union all
select 3 id, 'jkl' text from dual union all
select 7 id, 'mno' text from dual union all
select 7 id, 'pqr' text from dual)
group by id;
Other possibility, like in this PLSQL block:
declare
cursor crsr is
select 1 id, 'abc' text from dual union all
select 1 id, 'def' text from dual union all
select 1 id, 'ghi' text from dual union all
select 3 id, 'jkl' text from dual union all
select 7 id, 'mno' text from dual union all
select 7 id, 'pqr' text from dual;
variavel1 number;
variavel2 varchar2(1000);
begin
for i in crsr loop
if variavel1 is null or variavel1 <> i.id then
if variavel1 is not null then
dbms_output.put_line(variavel1||' - '||variavel2);
end if;
variavel1 := i.id;
variavel2 := i.text;
else
variavel2 := variavel2 || i.text;
end if;
end loop;
dbms_output.put_line(variavel1||' - '||variavel2);
end;
You can also define simple type as table of objects (id, text) and in loop add items to variable of this type.
declare
type tob is record (id number, text varchar2(1000));
type ttb is table of tob;
variavel2 ttb := ttb();
cursor crsr is
select 1 id, 'abc' text from dual union all
select 1 id, 'def' text from dual union all
select 1 id, 'ghi' text from dual union all
select 3 id, 'jkl' text from dual union all
select 7 id, 'mno' text from dual union all
select 7 id, 'pqr' text from dual;
begin
for i in crsr loop
if variavel2.count = 0 or variavel2(variavel2.count).id <> i.id then
variavel2.extend();
variavel2(variavel2.count).id := i.id;
end if;
variavel2(variavel2.count).text := variavel2(variavel2.count).text||i.text;
end loop;
-- now we have array of values
for x in 1..variavel2.count loop
dbms_output.put_line(variavel2(x).id||' - '||variavel2(x).text);
end loop;
end;
I assumed that id is not nullable, if it is you need minor changes. Output for all solutions is:
ID LIST
------ --------------
1 abcdefghi
3 jkl
7 mnopqr
open cursor; FETCH cursor INTO cursor_result; WHILE cursor %found loop variavel1=cursor_result.id WHILE( variavel1=cursor_result.id AND cursor %found) LOOP ---dO SOMITHING---- FETCH cursor INTO cursor_result; ----PASS TO NEXT VALUE END LOOP; end loop;
i.idchange inside the while loop at all? I sense a endless loop or some evil sideffects oniincrmenting thevariavel1by +1 might go into an i.id that is no longer there (row deleted with that id) and is missing. Please provide some more information about what you want to achieve with this.