I have an existing Customers table and want to output each row from this table using a stored procedure. There is no input criteria, just need to output all of the records.
The stored procedure should basically be equivalent to: "SELECT C_ID, LAST, FIRST, DOB, DPHONE, EMAIL FROM customers;"
This seems simple but I can't figure it out. All my searches haven't worked out for this case.
How would one accomplish this?
EDIT: Answered my question below. Very simple.
In Oracle your options are:
1. Use a function and return a REF CURSOR
2. Use a procedure and use a REF CURSOR as an OUT parameter
3. Use a PIPELINED function
4. Use a function and return a collection.
read this documentation Overview of Table Functions
see similar question here Return collection from packaged function for use in select
simple sample of such function
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS
out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.ticker := in_rec.Ticker;
out_rec.PriceType := 'O';
out_rec.price := in_rec.OpenPrice;
PIPE ROW(out_rec);
-- second row
out_rec.PriceType := 'C';
out_rec.Price := in_rec.ClosePrice;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
/
Nevermind! Just got it. It is pretty simple. I just did this:
CREATE OR REPLACE PROCEDURE CustomerReport AS
BEGIN
FOR cus IN (SELECT C_ID, LAST, FIRST, DOB, DPHONE, EMAIL FROM customers)
LOOP
dbms_output.put_line(cus.C_ID||' '||cus.FIRST||' '||cus.LAST||' '||cus.DOB||' '||cus.DPHONE||' '||cus.EMAIL);
END LOOP;
END;