clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805464 fiddles created (40828 in the last week).

select * from V$VERSION;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production 0
 hidden batch(es)


create table t as select 1 woid,'abc' words from dual union all select 2 woid,'def' words from dual
2 rows affected
 hidden batch(es)


select * from t
WOID WORDS
1 abc
2 def
 hidden batch(es)


declare type trows is table of t%rowtype; rows trows; totalrows trows := trows (); rowcount int := 0; begin update t set words='qwe' where woid=1 returning woid, words bulk collect into rows; rowcount := rowcount + sql%rowcount; totalrows := totalrows multiset union rows; delete from t where woid=2 returning woid, words bulk collect into rows; rowcount := rowcount + sql%rowcount; totalrows := totalrows multiset union rows; dbms_output.put_line ('totalrows/rowcount='||totalrows.count||'/'||rowcount); if (rowcount = totalrows.count) then dbms_output.put_line ( -- любые проверки вместо вывода 'updated row(1)='||totalrows(1).words||chr(10)|| 'deleted row(2)='||totalrows(2).words); -- вернуть ок, вызывающий сделает commit else raise_application_error (-20000, 'error: totalrows not еqual rowcount'); end if; end; /
1 rows affected dbms_output: totalrows/rowcount=2/2 updated row(1)=qwe deleted row(2)=def
 hidden batch(es)


--после проверки коммит commit;
 hidden batch(es)