Q:
BULK COLLECT and FORALL in PL/SQL
How do I update multiple columns in a table using the BULK COLLECT... FORALL methods in PL/SQL.
ASKED:
Apr 8 2009 3:11 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
I found the answer on my own and here is the code I wrote:
-- ts_to_td.sql
--
-- This script is called by Q3.sql
-- and runs after the scripts:
-- 1.) ts_insert_table.sql - creates the TS_INSERT_TABLE
-- and its index;
-- 2.) ts_update_table.sql - creates the TS_UPDATE_TABLE
-- and its index.
--
create or replace procedure TS_TO_TD
IS
type TS_INSERTS is table of TS_INSERT_TABLE%ROWTYPE;
TD_IN TS_INSERTS;
type TS_UPDATES is table of TS_UPDATE_TABLE%ROWTYPE;
type TS_UPDATES_ID is table of TS_UPDATE_TABLE.KEYVAL%TYPE;
TD_UP TS_UPDATES;
TD_UP_ID TS_UPDATES_ID;
BEGIN
--
-- This section inserts rows into TDEST from TSOURCE.
--
select * BULK COLLECT
into TD_IN
from TS_INSERT_TABLE
;
forall
TS_INSERT_NDX
in
TD_IN.FIRST .. TD_IN.LAST
insert into
TDEST
values
TD_IN(TS_INSERT_NDX)
;
--
-- This section updates TDEST table rows with a
-- matching KEYVAL value with the data in TSOURCE.
--
select * BULK COLLECT into
TD_UP
from TS_UPDATE_TABLE
;
select KEYVAL BULK COLLECT into
TD_UP_ID
from TS_UPDATE_TABLE
;
forall
TS_UPDATE_NDX
in
TD_UP.FIRST .. TD_UP.LAST
update TDEST
set
row = TD_UP(TS_UPDATE_NDX)
WHERE
KEYVAL = TD_UP_ID(TS_UPDATE_NDX)
;
END
;
/
Good Luck, RangjungDorje
