Oracle-如何在循环中附加值并访问外部循环？

Question

我的光标记录将就像以下

队列	步骤
1	A
2	B
3	C
4	D
5	R

 循环
  将cusor1获取到rec1
  当找不到cursor1时退出
  strval：='val ='|| rec1.queue ||' '|| rec1.Steps;
结束循环；
 

我期望的输出是

val = 1a 2b 3c 4d 5r

Answer 1

跳过光标并使用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;
/

如果您必须使用光标，则：

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;
/

两者都输出：

  val = 1a 2b 3c 4d 5e
 

db＆lt;＆gt; fiddle

Answer 2

尝试下面尝试。

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

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