PL/SQL loop performance

From: <eric_levin_at_my-deja.com>
Date: Wed, 24 Jan 2001 20:24:42 GMT
Message-ID: <94ndls$ok3$1_at_nnrp1.deja.com>

[Quoted] [Quoted] I am writing a procedure that loops through symbols and their phrases and prints them to an output file. One symbol and many phrases on a line. The cursors run quickly but the entire procedure takes 40 seconds. Is their a way to improve the speed. The procedure can be seen below.

CREATE OR REPLACE PROCEDURE symbols_gram(login2 varchar2) AS
--Variables to input into the file

    v_sym_num  	    lists.symbol.sym_num%TYPE;
	v_ticker 		lists.symbol.ticker%TYPE;
	v_class			lists.symbol.class%TYPE;
	v_exchange		lists.symbol.sym_exchange%TYPE;
	v_sym_num2		lists.symbol_phrase.sym_num%TYPE;
	v_symbol_phrase	lists.symbol_phrase.phrase%TYPE;
	v_sym_num_bind  lists.symbol.sym_num%TYPE;


--File Handle


	v_filehandle UTL_FILE.FILE_TYPE;


--Cursors


	CURSOR c_symbol  IS
	select distinct s.sym_num, s.ticker, s.class, s.sym_exchange
        from
    lists.symbol s,
 	lists.symbol_phrase sp,
	lists.phrase_user_xref xref,
	lists.umevoice_user u
	where
	u.login = login2
	and u.user_num = xref.user_num
	and xref.sym_phrase_num = sp.sym_phrase_num
	and sp.sym_num = s.sym_num;

	CURSOR c_phrase  IS	select sp.sym_num, sp.phrase  from     
lists.symbol_phrase sp, 	lists.phrase_user_xref xref,   
lists.umevoice_user u	where	u.login = login2	and sp.sym_num =
v_sym_num_bind	       and u.user_num = xref.user_num  and

xref.sym_phrase_num = sp.sym_phrase_num; BEGIN --Open file to write v_filehandle := UTL_FILE.FOPEN('c:\grammars','symbols.gram','w'); --Static Data before first loop UTL_FILE.PUT_LINE(v_filehandle,'grammar com.umevoice.uvautex.'||login2||'.symbols;'); UTL_FILE.PUT_LINE(v_filehandle,'<uv_new_symbols>=( ('); --Loop through

c_symbol cursor  OPEN c_symbol; 		  LOOP			     
    FETCH c_symbol INTO v_sym_num, v_ticker, v_class, v_exchange;	     
	      EXIT WHEN c_symbol%NOTFOUND;	       IF c_symbol%ROWCOUNT =
1 THEN				       
UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)||v_class||v_exchange||'>');   
			  ELSIF c_symbol%FOUND THEN			     

    UTL_FILE.PUT(v_filehandle,' |
<'||lower(v_ticker)||v_class||v_exchange||'>');

ELSE EXIT;				END IF; 			     
  UTL_FILE.PUT_LINE(v_filehandle,null); 		  END LOOP;	
CLOSE c_symbol; 	UTL_FILE.PUT_LINE(v_filehandle,') {$TICKER})');


--static data before second loop	UTL_FILE.PUT_LINE(v_filehandle,'{'); 

  UTL_FILE.PUT_LINE(v_filehandle,'symbolAction=setsymbol;'); UTL_FILE.PUT_LINE(v_filehandle,'symbolValue =$TICKER.SYM;'); UTL_FILE.PUT_LINE(v_filehandle,'};'); --Loop through table and input into

file OPEN c_symbol;	       LOOP		       FETCH c_symbol INTO
v_sym_num, v_ticker, v_class, v_exchange;		    EXIT WHEN
c_symbol%NOTFOUND;		     
UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)||v_class||v_exchange||'>=('); 
		  v_sym_num_bind := v_sym_num;			  OPEN
c_phrase;			   LOOP 		   FETCH c_phrase
INTO v_sym_num2, v_symbol_phrase;			 EXIT WHEN
c_phrase%NOTFOUND;	       IF c_phrase%ROWCOUNT = 1 THEN		     
		UTL_FILE.PUT(v_filehandle,v_symbol_phrase);		     
	  ELSE UTL_FILE.PUT(v_filehandle,' | '||v_symbol_phrase);	     
		    END IF;			    END LOOP;		     
      CLOSE c_phrase;				     
UTL_FILE.PUT_LINE(v_filehandle,')');			       

UTL_FILE.PUT_LINE(v_filehandle,'{');

UTL_FILE.PUT_LINE(v_filehandle,'SYM='||v_ticker||':'||v_class||':'||v_exchange||';');

				 UTL_FILE.PUT_LINE(v_filehandle,'};');
	     END LOOP;

CLOSE c_symbol;
--Close file

        UTL_FILE.FCLOSE(v_filehandle);
--Error Messages

EXCEPTION

		 WHEN OTHERS THEN
		 DBMS_OUTPUT.PUT_LINE('ERROR '||to_char(SQLCODE)||SQLERRM);
		 NULL;

END;
/

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 24 2001 - 21:24:42 CET