CODEWITHSUNDEEP
sqloracle11gtoad
Wamglind Carmasaic
Wamglind Carmasaic

Reputation: 163

Display Number of Rows based on input parameter

CREATE OR REPLACE PROCEDURE test_max_rows (
    max_rows IN  NUMBER DEFAULT 1000
)
IS
    CURSOR cur_test ( max_rows IN number ) IS
        SELECT id FROM test_table
        WHERE user_id = 'ABC'
        AND ROWNUM <= max_rows;
    id test_table.id%TYPE;
BEGIN
    OPEN cur_test(max_rows) ;
    LOOP
    FETCH cur_test  INTO id;
    EXIT WHEN cur_test%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID:' || id);
    END LOOP;
END;

My requirement is to modify the above code so that when I pass -1 for max_rows, the proc should return all the rows returned by the query. Otherwise, it should limit the rows as per max_rows.

For example:

EXECUTE test_max_rows(-1);

This command should return all the rows returned by the SELECT statement above.

EXECUTE test_max_rows(10);

This command should return only 10 rows.

Upvotes: 0

Views: 961

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can do this with a OR clause; change:

    AND ROWNUM <= max_rows;

to:

    AND (max_rows < 1 OR ROWNUM <= max_rows);

Then passing zero, -1, or any negative number will fetch all rows, and any positive number will return a restricted list. You could also replace the default 1000 clause with default null, and then test for null instead, which might be a bit more obvious:

    AND (max_rows is null OR ROWNUM <= max_rows);

Note that which rows you get with a passed value will be indeterminate because you don't have an order by clause at the moment.

Doing this in a procedure also seems a bit odd, and you're assuming whoever calls it will be able to see the output - i.e. will have done set serveroutput on or the equivalent for their client - which is not a very safe assumption. An alternative, if you can't specify the row limit in a simple query, might be to use a pipelined function instead - you could at least then call that from plain SQL.

CREATE OR REPLACE FUNCTION test_max_rows (max_rows IN NUMBER DEFAULT NULL)
RETURN sys.odcinumberlist PIPELINED
AS
BEGIN
    FOR r IN (
        SELECT id FROM test_table
        WHERE user_id = 'ABC'
        AND (max_rows IS NULL OR ROWNUM <= max_rows)
    ) LOOP
        PIPE ROW (r.id);
    END LOOP;
END;
/

And then call it as:

SELECT * FROM TABLE(test_max_rows);

or

SELECT * FROM TABLE(test_max_rows(10));

Here's a quick SQL Fiddle demo. But you should still consider if you can do the whole thing in plain SQL and PL/SQL altogether.

Upvotes: 2

Related Questions