CREATE OR REPLACE PROCEDURE RefreshAllLatestQuotes IS --------------------------------------------------- -- Refresh quotes from Yahoo! Quotes for each distinct -- ticker symbol in the latest_quotes table. --------------------------------------------------- n NUMBER := 0; tickercount NUMBER; curstr VARCHAR2(80); BEGIN SELECT COUNT(DISTINCT ticker) INTO tickercount FROM latest_quotes; FOR i IN (SELECT DISTINCT ticker FROM latest_quotes) LOOP n := n + 1; IF curstr IS NOT NULL THEN curstr := curstr ||','|| i.ticker; ELSE curstr := i.ticker; END IF; --------------------------------------------------- -- Use YahooQuotes.StoreLatestQuotesFor to retrieve -- live YahooQuotes in XML and store them in the -- LATEST_QUOTES table. Do it in batches of 10. --------------------------------------------------- IF n mod 10 = 0 or n = tickercount THEN YahooQuotes.StoreLatestQuotesFor(curstr); commit; curstr := null; END IF; END LOOP; END; |