1082 Глава 12

scott@TKYTE816> begin

open :x for select mgr, ename, row number()

rn from emp; end; /

(partition by mgr order by ename)

PL/SQL procedure successfully completed. MGR ENAME RN

14 rows selected.

Мы обманули синтаксический анализатор PL/SQL, не разрешая ему анализировать конструкции, которые он не понимает, в данном случае - вызов функции ROW NUMBER(). Для этого достаточно использовать динамически открываемые курсорные переменные. После открытия они работают аналогично об1чн1м курсорам: из них извлекаются данные, потом курсорные переменные закрываются и т.д., но PL/SQL-машина не пытается анализировать операторы ни во время компиляции, ни при выполнении, поэтому можно использовать новые синтаксические конструкции языка SQL.

Можно также создать представление на базе запроса с аналитическими функциями, а затем обращаться в PL/SQL-блоке к этому представлению. Например:

scott@TKYTE816> create or replace view

emp view as

select mgr, ename, row number()

from emp

(partition by mgr order by ename) rn

View created.

scott@TKYTE816> begin

2 open :x for

3 select mgr, ename,

over

over

Аналитические функции

4 from emp view;

5 end;

PL/SQL procedure successfully completed.

MGR ENAME RN

7566 FORD 1

7566 SCOTT 2

Аналитические функции в конструкции WHERE

Следует учитывать, что аналитические функции применяются по ходу выполнения запроса почти в самом конце (после них обрабатывается только окончательная конструкция ORDER BY). Это означает, что аналитические функции нельзя непосредственно использовать в условиях (т.е. применять в конструкциях WHERE и HAVING). Если необходимо включать данные в результирующее множество на основе результатов аналитической функции, придется использовать подставляемое представление. Аналитические функции могут использоваться только в списке выбора или в конструкции ORDER BY запроса.

В этой главе приводилось много примеров использования подставляемых представлений, в частности в разделе, посвященном выбору первых N строк. Например, чтобы найти группу сотрудников каждого отдела с тремя наибольшими зарплатами, мы выполняли следующий запрос:

scott@TKYTE816> select *

2 from (select deptno, ename, sal,

3 dense rank() over (partition by deptno

4 order by sal desc) dr

5 from emp)

6 where dr <= 3

7 order by deptno, sal desc

Поскольку функцию DENSE RANK нельзя использовать в конструкции where непосредственно, приходится скрывать ее в подставляемом представлении под псевдонимом DR, чтобы в дальнейшем можно было использовать столбец DR в условии для получения необходимых строк. Такой прием часто используется при работе с аналитическими функциями.

Значения NULL и сортировка

Значения NULL могут влиять на результат работы аналитических функций, особенно при использовании сортировки по убыванию. По умолчанию значения NULL считаются больше любых других значений. Рассмотрим следующий пример:

scott@TKYTE816> select ename, comm from emp order by comm desc;

ENAME COMM

SMITH JONES

1084

Глава 12

14 rows selected.

Выбрав первые N строк, получим:

scott@TKYTE816> select ename, comn, dr

ENAME

from

(select ename/ comm,

denserankO over (order by comm desc) dr from emp) where dr <= 3 order by comm /

COM4

SMITH JONES CLARK BLAKE SCOTT KING JAMES MILLER FORD ADAMS

MARTIN 1400

HARD 500

12 rows selected.

Хотя формально это верно, но вряд ли соответствует желаемому результату. Значения NULL либо вообще не должны учитываться, либо интерпретироваться как наименьшие в данном случае. Поэтому надо либо исключить значения NULL из рассмотрения, добавив условие where comm is not null:

scott@TKYTE816> select ename, comm, dr

from

(select ename, comm,

dense rank() over (order by comm desc) dr from emp

where comm is not null) where dr <= 3 order by comm desc