ROWNUM in Oracle is not like
LIMIT in MySQL.
LIMIT clause can be used to constrain the number of rows returned by the
SELECT statement... With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
For each row returned by a query, the
ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a
ROWNUM of 1, the second has 2, and so on.
FROM (SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
In the preceding example, the
ROWNUM values are those of the top-level
SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.
So the problems of your code is:
WHERE ROWNUM BETWEEN :startrow AND :perpage is logically incorrect, as
ROWNUM is a row index indicator, not a "number of rows" indicator.
ROWNUM BETWEEN a AND b means to return rows ordering from a to b, e.g. row #1 to row #3, NOT row #1 and 3 rows down.
So even if
ROWNUM BETWEEN a AND b works (it actually doesn't), to get "page 2" of the query, logically you want
ROWNUM BETWEEN :startrow AND :endrow, e.g.
ROWNUM BETWEEN 4 AND 6;
ROWNUM BETWEEN :startrow AND :endrow actually wouldn't work for start row larger than 1, because
ROWNUM is a pseudocolumn that represents the order of the row in the selected set,
so when you execute e.g.
SELECT...WHERE ROWNUM BETWEEN 4 AND 6,
- first match, Oracle thinks, "Will the rownum be in 4 to 6?" Because there's no rows selected yet, its
ROWNUM will be 1 if selected, so Oracle abandon it;
- second match, "Will this be in 4 to 6?" No because there's no rows selected so this will still be 1;
- third match, same;
There will be no result at all.
To get the desire output, you'll have to wrap your query in subquery, and check row number outside, like this:
SELECT * FROM
(SELECT ROWNUM rn, t.*
ORDER BY ...) t
WHERE rn>=:startrow AND rn<=:endrow
to guarantee that the row ordering is settled before checking the row order.
- And about coding style, as OCI supports prepared statement and variable binding, you should use it:
Binding allows the database to reuse the statement context and caches from previous executions of the statement,...Binding reduces SQL Injection concerns because the data associated with a bind variable is never treated as part of the SQL statement.
$statement=oci_parse("... WHERE rn>=:start AND rn<=:end");