/**
 *  Creates a table for holding stockprices 
 */
 
DROP TABLE STOCK_PRICES
/

CREATE TABLE STOCK_PRICES(
  RIC VARCHAR(6) PRIMARY KEY,
  PRICE NUMBER(7,2),
  UPDATED DATE )
  TABLESPACE USERS
/

INSERT INTO STOCK_PRICES(RIC, PRICE, UPDATED)
VALUES('MSFT', 69.20, SYSDATE)
/

INSERT INTO STOCK_PRICES(RIC, PRICE, UPDATED)
VALUES('RSAS', 30.18, SYSDATE)
/

INSERT INTO STOCK_PRICES(RIC, PRICE, UPDATED)
VALUES('AMZN', 15.50, SYSDATE)
/

INSERT INTO STOCK_PRICES(RIC, PRICE, UPDATED)
VALUES('SUNW', 16.25, SYSDATE)
/

INSERT INTO STOCK_PRICES(RIC, PRICE, UPDATED)
VALUES('ORCL', 14.50, SYSDATE)
/

COMMIT
/


CREATE OR REPLACE PACKAGE Types
AS
    TYPE ref_cursor IS REF CURSOR;
END;
/


CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER) 
    RETURN Types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR 
    	SELECT ric,price,updated FROM stock_prices
    	WHERE price < v_price;
    
    RETURN stock_cursor;
END;
/