sql - 无法使用 LISTAGG 写出输出
问题描述
我正在尝试写出所有原始 id 的输出以及它们更新的内容,但它失败了,我不知道为什么。非常欢迎任何帮助。
declare
v_ids varchar2(4000);
begin
--Update tiemline
update time_line tl
set tl.limit_template = 'LIM0178'
where tl.limit_template IN
(select tl.limit_template from base_timeline_section bbts,
base_timeline_org bts,
trmt_timeline tl
where bbts.base_trmt_set_id = bts.base_trmt_set_id
and tl.limit_id = bts.limit_id
and bbts.bse_trmt_st_tplt_id = '720'
);
returning listagg(tl.limit_template, ',') within group (order by
tl.limit_template) tl.limit_template
into v_ids;
dbms_output.put_line('Updated IDs: ' || v_ids || ' to LIM0178');
end;
我收到 ORA-06550 错误,在期待以下之一时遇到 LISTAGG :=.(@%; 符号“:=”被替换为“LISTAGG”继续
解决方案
您可以使用以下内容:
update time_line tl
set tl.limit_template = 'LIM0178'
where tl.limit_template IN
(select tl.limit_template
from base_timeline_section bbts,
base_timeline_org bts,
trmt_timeline tl
where bbts.base_trmt_set_id = bts.base_trmt_set_id
and tl.limit_id = bts.limit_id
and bbts.bse_trmt_st_tplt_id = '720')
returning listagg(tl.limit_template, ',') within group
(order by tl.limit_template) into v_ids;
通过使用RETURNING INTO
子句,我们甚至可以返回连接的多个列。