Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
In case you have too many rows, count(*) can be very slow, am I right? –  dezso May 22 '12 at 16:56
1  
@dezso - The count(*) may be able to use an index, regardless, a determination of very slow will depend on your definition of slow, too many rows, and what is acceptable. Without these definitions a proper answer cannot be given to your question. –  Leigh Riffel May 22 '12 at 17:11
    
@LeighRiffel - This question states that counting 5 million rows would take 30 mins. On the other hand, I live part-time in a PostgreSQL world, and a plain SELECT count(*) FROM table; there involves a sequential scan. –  dezso May 22 '12 at 17:24
    
@dezo - If you have a specific question, perhaps you should create a new question so we don't clutter the comments for this question. –  Leigh Riffel May 22 '12 at 17:26
    
@deszo: without any further details such a statement is completely meaningless. On my (outdated) dual-core desktop from 2004 counting 6 million rows takes less than 2 seconds. So who is right now? –  a_horse_with_no_name May 22 '12 at 17:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.