Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: can you make this faster, please

Re: can you make this faster, please

From: Kevin P. Fleming <kfleming_at_access-laserpress.com>
Date: Thu, 10 Jun 1999 00:30:17 GMT
Message-ID: <tGD73.27044$Fz2.3873@news.rdc1.az.home.com>


UPDATE NEW_TABLE A
  SET A.address = (
    SELECT B.address

      FROM CUSTOMERS B,
                   ADDRESSES C,
      WHERE B.cust_id = C.cust_id AND
                      B.customer_number = A.customer_number
    );

All done, no PL/SQL, no looping, very fast (assuming you have the right indexes in place)

Paschal Mushubi <bs260_at_freenet.carleton.ca> wrote in message news:01beb2e0$adc2dd40$dd897586_at_wotrmushubi...
> DECLARE
> CURSOR c_customers is
> SELECT rowid customer_rowid,
> customer_number,
> address
> FROM NEW_TABLE
> v_customers c_customers%ROWTYPE;
> BEGIN
> OPEN c_customers;
> FETCH c_customers INTO v_customers;
> WHILE c_customers%FOUND LOOP
> BEGIN
> SELECT T1.address
> INTO v_customers.address
> FROM CUSTOMERS T1,
> ADDRESES T2
> WHERE T1.customer_number =v_customers.customer_number
> and T1.cust_id = T2.cust_id;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> v_customers.address := null;

>

> END;
> BEGIN
> UPDATE NEW_TABLE
> SET address = v_customers.address
> WHERE rowid = v_customers.customer_rowid;
> END;
> v_customers.address := NULL;
> FETCH c_customers INTO v_customers;
> END LOOP;
> CLOSE c_customers;
> COMMIT;
> END get_customer_address;

> Received on Wed Jun 09 1999 - 19:30:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US