Re: PL/SQL Loop performance

if you are running 8.1.5 or higher you can BULK COLLECT the data into varrays or nested tables then iterate over the array.

In article <94ndl1$ojn$1_at_nnrp1.deja.com>,   eric_levin_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 24 2001 - 16:00:07 CST