Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using UPDATE with ROWNUM?
On 26 jun, 14:56, darr..._at_yahoo.com wrote:
> > But make sure to look up "bulkcollect" and "forall" updating
> > examples, which D. Morgan of this same newsgroup recently pointed me
> > to! Much higher performance.
>
> TYVM. Useful information for more that just this case!
>
> This post has an example of bulk collect from a sequence (through a
> dummy table):http://groups.google.com/group/comp.databases.oracle.server/browse_th...
> Is this a reasonably efficient method? I probably want to bulk collect
> about 1000 values at a time.
You don't need to bulk collect from the sequence. I suggest you bulk collect the primary key of the table being updated.
Then, using FORALL, you update the records with the sequence's nextval.
As follows (example modified from D. Morgan's page)
CREATE TABLE parent (part_num NUMBER, part_name VARCHAR2(10),seq_no
number);
ALTER TABLE parent ADD PRIMARY KEY (part_num,part_name);
INSERT INTO parent (part_num,part_name)
SELECT 1,'a' FROM dual UNION SELECT 2,'b' FROM dual UNION SELECT 3,'c' FROM dual UNION SELECT 4,'d' FROM dual ;
declare
TYPE array_one IS TABLE OF parent.part_num%TYPE;
TYPE array_two IS TABLE OF parent.part_name%TYPE;
tbl_num array_one;
tbl_name array_two;
CURSOR r IS
SELECT part_num,part_name
FROM parent;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO tbl_num,tbl_name LIMIT 1000;
FORALL i IN 1..tbl_num.COUNT
UPDATE parent SET seq_no=tempseq.NEXTVAL WHERE part_num=tbl_num(i) AND part_name=tbl_name(i);
EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END ;
/
(remember, I didn't know about bulk collect three days ago, so you might want to look it up some more in the manual) Received on Tue Jun 26 2007 - 13:57:52 CDT