Oracle- How to append values inside a loop and access it outside loop?-CodePudding

My cursor records will be like this

Queue	steps
1	A
2	B
3	C
4	D
5	R

Loop
  Fetch cusor1 into rec1
  Exit when cursor1%not found
  strval := 'val =' || rec1.queue||' '||rec1.steps;
End loop;

The output I am expecting is

Val = 1A 2B 3C 4D 5R

CodePudding user response：

Skip the cursor and use LISTAGG:

DECLARE
  strval VARCHAR2(4000);
BEGIN
  SELECT 'Val = ' || LISTAGG(queue || steps, ' ') WITHIN GROUP (ORDER BY queue)
  INTO   strval
  FROM   table_name;

  DBMS_OUTPUT.PUT_LINE(strval);
END;
/

If you must use a cursor then:

DECLARE
  CURSOR cursor1 IS
    SELECT queue, steps
    FROM   table_name
    ORDER BY queue;

  strval  VARCHAR2(4000) := 'Val =';
BEGIN
  FOR rec IN cursor1 LOOP
    strval := strval || ' ' || rec.queue|| rec.steps;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(strval);
END;
/

Which both output:

Val = 1A 2B 3C 4D 5E

db<>fiddle here

CodePudding user response：

Try like below.

Loop 
Fetch cusor1 into rec1 
  Exit when cursor1%not found 
  strval := concat(strval, rec1.queue||''||rec1.steps || ' '); 
End 
loop;

strval := concat('val =', strval)