Re: Can I write this without using Cursors?

SatarNag wrote:
>
> 29-July-1997
>
> Hi,
> We are using Oracle 7.3, and are doing huge transactions (updates,
> inserts, ect..). I was wondering if there was a way to re-write this
> program without using cursors, So that we may increase the efficiency.
> Thank You in Advance,
> Satar Naghshineh
> (very) entry-level Oracle DBA
>
> DECLARE
>
> CURSOR get_all IS
> SELECT ani
> FROM callers;
>
>
> c1 phonebook%ROWTYPE;
> c2 get_all%ROWTYPE;
>
> old_ani callers.ani%TYPE;
> banch_count number;
>
> test_count number;
>
> BEGIN
>
> set transaction use rollback segment r05;
>
> old_ani := '9999999999';
> banch_count := 0;
>
> test_count := 0;
>
> OPEN get_all;
> LOOP
>
> -- EXIT WHEN test_count = 20000 ;
> test_count := test_count + 1;
>
> FETCH get_all INTO c2;
> EXIT WHEN get_all%NOTFOUND;
>
> IF c2.ani != old_ani THEN
>
> BEGIN
> SELECT * INTO c1
> FROM phonebook
> WHERE phone =
> SUBSTR(c2.ani,1,3)||'-'||SUBSTR(c2.ani,4,3)||'-'||SUBSTR(c2.ani,7,4)
> AND rownum < 2;
>
>
> UPDATE callers SET
> lname = c1.lastn,
> fname = c1.firstn,
> address = c1.address,
> city = c1.city,
> state = c1.state,
> zip = c1.zip,
> verified = 'Y',
> ver_date = TRUNC(SYSDATE)
> WHERE ani = c2.ani AND
> (lname = c1.lastn OR lname is NULL) AND
> verified is NULL;
> banch_count := banch_count + 10;
>
> IF banch_count = 10 THEN
> insert into junk values(sysdate,test_count);
> END IF;
>
> IF banch_count > 10000 THEN
> UPDATE junk SET
> cdate = sysdate,
> countrows = test_count;
>
> COMMIT;
> banch_count := 0;
> set transaction use rollback segment r05;
> END IF;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> NULL;
> WHEN OTHERS THEN
> NULL;
> END;
> END IF;
> old_ani := c2.ani;
>
> END LOOP;
> CLOSE get_all;
> COMMIT;
> END;
> /
> QUIT
Satar,

There appears to be a potentially serious design problem here.

In cursor C1, the predicate "where rownum < 2" seems to indicate that the table PHONEBOOOK is not unique on the column PHONE.

If it is *not* unique, then the update is, shall we say, arbitrary and you have no sensible alternative to the way you are doing things now.

If PHONEBOOK.phone *is* unique then the above predicate is unnecessary. In this case, the statements given (excluding the "junk" postings) are equivalent to the single update statement:

      update CALLERS c2
      set (lname, fname, address, city
          ,state, zip, verified, ver_date)
          = (select c1.lastn, c1.firstn, c1.address, c1.city
                    c1.state, c1.zip, 'Y', trunc(sysdate)
             from   PHONEBOOK c1
             where  c1.phone = substr(c2.ani,1,3)||'-'||
                               substr(c2.ani,4,3)||'-'||
                               substr(c2.ani,7,4)	-- This must select
only one row
            )
      where  c2.verified is null
       and  (c2.lname is null
             or (c2.lname is not null and exists
                (select null from PHONEBOOK where lastn = c2.lname))
            )

Note that, if each value of CALLERS.lname is either null or exists in the set of PHONEBOOK.lastn, then the second half of the OR'd predicate is unnecessary.
In this case, the execution consists of a single serial pass through CALLERS with a unique indexed access to PHONEBOOK for each qualifying row of CALLERS.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"