Thanks for the question, Ragunath.
Asked: October 29, 2017 - 7:52 am UTC
Last updated: October 30, 2017 - 2:49 am UTC
Version: 11g
Viewed 1000+ times
SQL> create table t as select rownum x, rownum*10 y 2 from dual 3 connect by level <= 5; Table created. SQL> SQL> select * from t; X Y ---------- ---------- 1 10 2 20 3 30 4 40 5 50 SQL> set serverout on SQL> begin 2 update t set y = y + 1 where x <= 3; 3 4 dbms_output.put_line('rows changed = '||sql%rowcount); 5 end; 6 / rows changed = 3 PL/SQL procedure successfully completed. SQL> declare 2 type pk_list is table of number index by pls_integer; 3 pk pk_list; 4 begin 5 update t set y = y + 1 where x <= 3 6 returning x bulk collect into pk; 7 8 for i in 1 ..pk.count loop 9 dbms_output.put_line('affected x was '||pk(i)); 10 end loop; 11 end; 12 / affected x was 1 affected x was 2 affected x was 3 PL/SQL procedure successfully completed.
Racer I., October 30, 2017 - 9:33 am UTC
create table test (ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, X VARCHAR2(10)); DECLARE type pk_list is table of number index by pls_integer; pk pk_list; type value_list is table of DUAL%ROWTYPE index by pls_integer; vals value_list; BEGIN SELECT Dummy BULK COLLECT INTO vals FROM DUAL CONNECT BY ROWNUM < 4; FORALL i IN 1..vals.COUNT INSERT INTO test (X) VALUES (vals(i).Dummy) RETURNING ID BULK COLLECT INTO pk; for i in 1..pk.count loop dbms_output.put_line('generated ID ' || pk(i)); end loop; END; select * from test generated ID 5 generated ID 6 generated ID 7
Racer I., October 30, 2017 - 9:35 am UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library