Example 17-12: Procedure to refresh prices of all stocks

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;