CODEWITHSUNDEEP
oracle-database
Stephanie Monteiro
Stephanie Monteiro

Reputation: 9

how can I increment a variable with the next value of a column

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).

Upvotes: 0

Views: 924

Answers (2)

Stephanie Monteiro
Stephanie Monteiro

Reputation: 9

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;

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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

Upvotes: 1

Related Questions