Thanks for the question, Nidhi.
Asked: August 05, 2020 - 1:23 pm UTC
Last updated: August 06, 2020 - 10:15 am UTC
Version: PL/SQL Release 11.2.0.4.0
Viewed 1000+ times
UPDATE DUMMY_TABLE SET DUMMY_STATUS = 'ABC' WHERE CURRENT OF DUMMY_CURSOR RETURNING DUMMY_FIELD1, DUMMY_FIELD2 BULK COLLECT INTO TAB_FIELD1, TAB_FIELD2;
declare cursor cur is select * from t for update; rec cur%rowtype; new_val date; begin open cur; loop fetch cur into rec; exit when cur%notfound; new_val := sysdate; update t set c2 = new_val where current of cur; end loop; close cur; end; /
declare cursor cur is select * from t for update; type cur_tab is table of cur%rowtype index by pls_integer; recs cur_tab; dates dbms_sql.date_table; begin open cur; loop fetch cur bulk collect into recs limit 100; exit when recs.count = 0; forall i in 1 .. recs.count update t set c2 = sysdate where c1 = recs (i).c1 returning c2 bulk collect into dates; end loop; close cur; end; /
Nidhi, August 07, 2020 - 5:12 am UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library