Practical Web Programming

Wednesday, September 26, 2007

How To Do Pagination in Oracle

In MySQL, pagination is very easy because of the Limit keyword in the Select statement. But Oracle don't have the Limit keyword as of this writing. So you have to do a little trick using subquery and the pseudo column rownum. Here's a simple tutorial on how to do it.

1. Pagination Formula

This formula is what you will put in your condition using between in the condition section of your Select statement.

Pagination formula, where:
pg = page numner,
lm = limit per page

(pg - 1) * lm to pg * lm;


2. Creating and Populating the Table
We will create the table that we will be using and populate it using PL/SQL.

--CREATE THE TABLE
create table FOO(
ID NUMBER(5),
NAME VARCHAR2(20)
)

--POPULATE THE TABLE
begin
for i in 1..100 loop
insert into foo(name) values(to_char(i));
end loop;
end;


3. Procedure
Now, we will create a procedure to do the querying from the Oracle database. We used the pagination formula in our condition section of our Select statement.

--PAGINATION PROCEDURE
create or replace procedure foo_pagination(
p_pageno in number,
p_limit IN NUMBER) IS

--parameterized cursor
CURSOR c_foo_page(p_pg in number,
p_lm IN NUMBER) IS
SELECT x.id, x.NAME
FROM
(SELECT id, NAME, rownum AS row_num
FROM foo) x
WHERE x.row_num BETWEEN (p_pg - 1) * p_lm AND p_pg * p_lm;
--variable to hold the rows
v_foo_rows foo%ROWTYPE;

BEGIN
dbms_output.put_line('Values in Page #' || p_pageno);

--open cursor
OPEN c_foo_page(p_pageno, p_limit);
LOOP
--fetch record from cursor
FETCH c_foo_page INTO v_foo_rows;
EXIT WHEN c_foo_page%NOTFOUND;
dbms_output.put_line('ID: ' || v_foo_rows.id ||
' - Name: ' || v_foo_rows.NAME);
END LOOP;

--close cursor
CLOSE c_foo_page;
END foo_pagination;


4. Testing
Below is the sourcecode to test our procedure. We will use PL/SQL to test it.

--TESTING THE PROCEDURE ABOVE
begin
foo_pagination(2, 10);
end;


That was it. Using subquery and the pseudo column rownum, you can do pagination in Oracle, just like what you can do in MySQL.

2 comments:

Arjuna said...

hi,joel its me again.try to vist my blog. http://correclan.blogspot.com/
i'm very much amazed of your site.I've been reading stuffs about oracle.buty yours is much easy to understand.thnks again.

Anonymous said...

Lance,

I visited you blog. It's great your created a blog dedicated to your roots. Thanks for the complements.

Recent Post