Oracle PL/SQL: постраничный доступ к данным

При написании Web-приложений часто требуется организовать постраничный просмотр данных. Классический пример - результаты поискового запроса на любом поисковике (Google, Yandex, ...).

Самый примитивный способ - выбрать все необходимые строки из таблицы, (например: SELECT * FROM items), а затем на стороне клиента разбить результат на страницы и вывести нужную страницу. Этот подход я рассматривать не буду, т.к. для больших таблиц он совершенно неприемлем.

Большинство СУБД имеют встроенные средства, для ограничения результатов запроса сверху и снизу. Кроме того стандарт ISO SQL:2003 предусматривает функции ROW_NUMBER() и RANK() для этих целей. Подробнее в этой статье на Wiki.

Для Oracle мне больше всего нравится способ, который предлагает Том Кайт. Он основан на использовании псевдо-колонки ROWNUM, а также того факта, что в Oracle есть специальный механизм повышающий производительность для запросов первых N строк.

PL/SQL код для реализации постраничного доступа к результатам запроса (pagination of query result):

TYPE CItems IS REF CURSOR; -- возвращаемый курсор

CONST_ITEMS_PER_PAGE CONSTANT NUMBER := 25; -- количество элементов на странице

-- возвращает количество страниц в базе данных
FUNCTION get_items_pages_count()
RETURN NUMBER IS

cnt NUMBER; -- количество элементов

BEGIN
-- определяем количество элементов
SELECT COUNT(*) INTO cnt
FROM items t;

-- делим количество_элементов на количество_элементов_на_странице
-- и округляем полученное к большему целому
RETURN CEIL(cnt / CONST_ITEMS_PER_PAGE);
END;

-- возвращает все элементы на данной странице
PROCEDURE get_items_page(
cur OUT CItems, -- возвращаемый курсор
p_page IN NUMBER, -- номер страницы (корректность не проверяется)
) IS

rnum_first NUMBER; -- номер первой записи на странице
rnum_last NUMBER; -- номер последней записи на странице

BEGIN
-- получаем диапазон номеров строк, которые будем возвращать
rnum_first := (((p_page-1)*CONST_ITEMS_PER_PAGE)+1);
rnum_last := (p_page*CONST_ITEMS_PER_PAGE);

-- закрываем курсор, если он уже открыт
IF cur%ISOPEN THEN
CLOSE cur;
END IF;

-- открываем курсор
OPEN cur FOR
-- этот запрос "отрезает" от подзапроса первые rnum_first строк снизу
SELECT t2.*
FROM
(
-- этот запрос вытаскивает первые rnum_last строк из исходного запроса
SELECT t1.*, ROWNUM AS rnum
FROM
(
-- это наш исходный запрос, который будем разбивать на страницы
SELECT t.*
FROM items t
) t1
WHERE ROWNUM <= rnum_last -- верхняя граница
) t2
WHERE t2.rnum >= rnum_first; -- нижняя граница
END;