Returning Row Count in a Stored Procedure in Oracle

Question

I'm mostly SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this:

PROCEDURE SP_INFO_EX_S(
    v_EX_ID    IN NUMBER,
    v_EX_ROWS               OUT NUMBER
    )
AS

SELECT COUNT(*) AS ExCount
FROM EXAMPLE
WHERE EX_ID = v_EX_ID
END SP_INFO_EX;

What I'd like this to do is return v_EX_ROWS as the total number of rows in the example table that match the WHERE Clause, but Google doesn't seem to have the answer to this. Am I missing something? Thanks!

Answer 1

You are almost there, just put the result of the select into the out variable:

SELECT COUNT(*)
  into v_EX_ROWS
FROM EXAMPLE
WHERE EX_ID = v_EX_ID

You might be better of using a function though:

create or replace function SP_INFO_EX_S(v_EX_ID    IN NUMBER)
   return number
AS
  row_count number;
BEGIN
    SELECT COUNT(*) 
      into row_count;
    FROM EXAMPLE
    WHERE EX_ID = v_EX_ID;

    return row_count;
END SP_INFO_EX;
/

Then just do:

select sp_info_ex_s(42) from dual;

Depending on your query tool, this might be easier to deal with than an out parameter.