Can I write this without using Cursors?

From: SatarNag <satarnag_at_aol.com>
Date: 1997/07/29
Message-ID: <19970729165201.MAA24016@ladder01.news.aol.com>#1/1

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 Received on Tue Jul 29 1997 - 00:00:00 CDT