Bulk Bind help required

[Kenton]

Hello

I am trying to gain some performance on a DW ETL process and would like to use Bulk Collect and/or FORALL.

I have spent quite some hours reading up but I am totally lost.

Here's what I'm trying to do:

[tt]DECLARE
CURSOR c1 IS SELECT key1, key2, value1 FROM table1@db1;
asofdt NUMBER := 20080103;
v_key1 NUMBER;
v_key2 NUMBER;
v_value1 VARCHAR2 (10);
BEGIN
OPEN c1;

LOOP
FETCH c1
INTO v_key1, v_key2,v_value1;

EXIT WHEN c1%NOTFOUND;

UPDATE table2 t2
SET t2.enddate = to_char(to_date(asofdt,'YYYYMMDD') - 1,'YYYYMMDD')-- Age off open records one day prior
WHERE t2.key1 = v_key1 AND t2.key2 = v_key2 AND t2.enddate = 99991231;

INSERT INTO table2 (key1, key2, value1, startdate, enddate)
VALUES (v_key1, v_key2, v_value1, asofdt, 99991231); -- Insert new records
END LOOP;

CLOSE c1;
END;

commit;[/tt]

Do I use BULK COLLECT for the cursor or do I use FORALL?

thanks in advance

Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[Dagon]

You use both - bulk collect for the cursor with a limit to fill up your arrays to a pre-defined size and forall with the updates and inserts.

Here is a simplified example:

DROP TABLE TEST_TAB;

create table test_tab (a number, b number);

BEGIN
  FOR I IN 1..503 LOOP
    INSERT INTO TEST_TAB VALUES (I, NULL);
  end loop;
end;

DECLARE
   CURSOR c1 IS SELECT  a FROM test_tab;
   type t_a is table of test_tab.a%type;
   v_a  t_a;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 BULK COLLECT
       INTO v_a LIMIT 100;
      if v_a.count > 0 then
      	  forall i in v_a.first..v_a.last 
          UPDATE test_tab
           SET B = v_a(i)
		   where a = v_a(i);
      end if;
     EXIT WHEN c1%NOTFOUND; 
   END LOOP;
   CLOSE c1;
END;

You may also be able to achieve what you want using a MERGE statement.

 

[jimirvine]

Do I use BULK COLLECT for the cursor or do I use FORALL?

Neither As Dagon says, this can be achieved with straight SQL.

 

[Kenton]

How do I do an update AND insert during the match part of a MERGE?

Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[Kenton]

Thank you for the example, I have seen quite a few similar to this but still have some questions.

1) You declare a type of test_tab.a%type. How do you cope with cursors that use multiple tables?
2) You use one key from the cursor. How do you code this for multiple keys as per my example?
3) Is it possible to perform multiple statements after the FORALL (say and update and an insert)?



Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[Dagon]

1) You just use a different table name. All I'm doing there is using the table name to specify the type of the variable. I could easily have 10 such statements, all with different table names.

2) You declare variables for each type and use them in the statement in exactly the same way. You only need to use one of them in the forall statement (any of them will do).

forall i in v_a.first..v_a.last
UPDATE test_tab
SET B = v_a(i),
c = v_c(i)
where a = v_a(i)
and d = v_d(i);

3) You just have multiple FORALL statements.