I need to return the results of a query (multiple records) using a stored procedure. I know I can do it using a cursor and walking through each record one by one. It seems like there would be an easier way to return a "record set" using a stored procedure. I've tried "Select into" statement can only return one record.
Create Procedure GetChildren
(
lngParentID IN tblTree.ParentID%type,
p_ParentID OUT tblTree.ID%type,
p_ID OUT tblTree.ID%type,
p_Description OUT tblTree.Description%type,
p_NodeType OUT tblTree.NodeType%type
)
IS
BEGIN
SELECT ID, ParentID, Description, NodeType
INTO p_ParentID, p_ID, p_Description, p_NodeType
FROM tblTree
WHERE ParentID=lngParentID;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'No records found');
END IF;
END;