sqloracle-databasestored-proceduresoracle11gmultiple-return-values

SQL Stored Procedure data type for list of rows

What data type can I use to store all rows found by SELECT query?

CREATE OR REPLACE PROCEDURE handleFailedCalls(xNumber in varchar(10)) AS
result {DATA TYPE I WANT};
BEGIN
   select * into result
   from CALLS c1
   where c1.status = 'fail'
END
/
Solution

  • Use BULK COLLECT, example:

    DECLARE
TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
all_employees emp_typ;
BEGIN
SELECT * BULK COLLECT INTO all_employees FROM employees;

    A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.