Returning Count and Resultset from a single stored procedure

I'm fairly new to stored procedures for Oracle. I want to be able to return both the count of hotels as well as the hotel names in a single stored procedure. Problem is I don't know where to start. Any help would be appreciated.

SELECT COUNT(HNAME) FROM HOTELS; //returns 'There are 30 hotels' 
SELECT HNAME FROM HOTELS; //returns a table of 开发者_StackOverflow中文版30 hotel names

You can get it in one pass:

SELECT HNAME, COUNT(*) OVER () FROM HOTELS;

Returns:

HNAME    COUNTOVER
=======  =========
bla             30
daa             30
gaa             30
...

Alternatively, if you're bulk collecting the list into an in-memory array in PL/SQL, you can just get the count from the array with the COUNT function.

Just showing you the example (if that is what you want is just the how, not necessarily the why per your testcase), you can achieve your results quite simply. I put together a test case to show you

create table HOTELS (hname varchar2(50) ) ; --create table for test

declare  --put data into it for test
i number ;
begin
  for i in 1 .. 50 loop
    insert into hotels (hname) values('Hotel: ' || i); 
  end loop ;
  commit ;
end ;
/

--now create a procedure and show the results

set serveroutput on
declare
 resultQuery sys_refcursor ;
 countOfHotels number ;
 hotelName HOTELS.hname%type ;
 procedure getHotels(HotelCount out number ,
                            HotelList  out nocopy sys_refcursor) is
                            begin
                                select count(*) 
                                  into HotelCount
                                  FROM HOTELS ;

                                  open HOTELLIST For
                                   select * 
                                      from HOTELS ;
                            end getHotels;
begin
  getHotels(countOfHotels, resultQuery) ;

        dbms_output.put_line('Count Of Hotels ' || countOfHotels);
      loop
         fetch resultQuery into hotelName;
         exit when resultQuery%notfound;
         dbms_output.put_line('Found Hotel: ' || hotelName);
      end loop;

end ;

And now for the results:

/**
results
Count Of Hotels 50
Found Hotel: Hotel: 1
Found Hotel: Hotel: 2
Found Hotel: Hotel: 3
Found Hotel: Hotel: 4
Found Hotel: Hotel: 5
....

**/

but all the work is done here:

 procedure getHotels(HotelCount out number ,
                            HotelList  out nocopy sys_refcursor) is
    begin
        select count(*) 
          into HotelCount
          FROM HOTELS ;

          open HOTELLIST For
           select * 
              from HOTELS ;
    end getHotels;

You select into variables and then you open REFCURSOR FOR cursors

select 'count', to_char(COUNT(HNAME)) FROM HOTELS
union
select 'Hotel Name', HNAME FROM HOTELS;

There are a lot of options with this. Typically I like Thilo's idea the best, as most situations don't really need to know the exact number of rows returned, so it isn't worth the cost of counting them every time the query runs.

If the row count will be limited to where this isn't as much of a concern you could do any of the options already given, or you could also return a collection that contains the hotel names and then use the .COUNT attribute of the collection you get back to determine the count.