CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Madeyedexter
Madeyedexter

Reputation: 1193

Select and count the same records

In my Stored Procedure, I have two queries:

Here rec_count is out parameter and cursor_name is in out parameter.

open cursor_name for
select <col list> from <table1 join table2 inner join...> on <join conditions> where <conditions>;

select count(*) into rec_count from <table1 join table2 inner join...> on <join conditions> where <conditions>;

Upvotes: 1

Views: 78

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

There are bigger issues than you are thinking to be here.

What if another session commits a transaction meanwhile between you open the cursor and select count? Obviously, the count of the rows that of the cursor will not match with your select count(*) query.

Oracle doesn't know the count of rows, until the last row is fetched.

If you want an exact count of rows, then I would insist an analytic count(*) over() in your existing cursor query.

Upvotes: 1

ruudvan
ruudvan

Reputation: 1371

You could do the analytic count over the entire data set like this - 

OPEN cursor_name for
   SELECT <col_list> , 
          count(*) over () as cnt 
   from <tables> <join conditions> <where clauses>;

That way the cursor would have a column with the count of all rows in each row.

Upvotes: 1

Related Questions