Search code examples
sqloracle-databaseplsqlcursor

PL/SQL Cursor rows

I've a question and I hope that I find someone who can help Me: I want to calculate all the rows returned by the cursor into the 'IF' (whitout using a counter because I want to know the number of rows in one time before begin the treatments.

TO EXPLAIN MORE: I have a table that contains measurements, and I want to know if the test (code inside my IF) returns a lot of mesurements or Not. If it returns One I want to send a message, else I send a message that contains a lot of mesurements . Thank you

Solution

  • I think this will depend on what you mean by 'a lot' of measurements. nilsman's approach will get you the answer you want. However you will have to execute the cursor again if you actually want the data.

    I can think of two approaches that might give you the answer you want

    1. open your cursor using BULK COLLECT. You can then use the COUNT method on the collection you fetched into. However normally we would limit the number of rows collected at any one time to a few hundred at the most. If your definition of 'a lot' is bigger than a few hundred then this approach may not be efficient
    2. use the analytic function COUNT(*) OVER () to return the count of all records in-line with each row of the query. This means after the first fetch you'll know the size of the entire result set