1076

Глава 12

21 then

22 execute Immediate p max cols query into l max cols;

23 else

24 raise application error(-20001, He могу определить

максимальное количество столбцов) ;

25 end if;

26 27

2 8 -- Теперь создаем запрос, который позволяет ответит на

-- поставленный вопрос. . 29 -- Начинаем со столбцов Cl, C2, ... СХ:

31 l query := select ;

32 for i in 1 .. p anchor.count

34 loop

35 l query := l query И p anchor(i) И ,;

36 end loop;

38 -- Теперь добавляем транспонируемые столбцы С{х+1}... CN:

39 -- Формат: max(decode(rn,l,C{X+l},null)) сх+1 1

41 for i in 1 .. l max cols

42 loop

43 for j in 1 .. p pivot.count

44 loop

45 l query := l query

46 max(decode(rn,Hill,M

47 p pivot(j)M,null)) М

48 p pivot(j) М М i М ,;

49 end loop;

50 end loop;

52 -- Теперь просто добавляем исходный запрос 53

54 l query := rtrim(l query ) from ( p query ) group by ;

56 -- а затем - столбцы, по которым надо группировать...

58 for i in 1 .. p anchor.count

59 loop

60 l query := l query p anchor(i) ,;

61 end loop;

62 l query := rtrim(l query ); 63

64 -- и возвращаем курсор для результирующего множества

66 execute immediate alter session set cursor sharing=force;

67 open p cursor for l query;

68 execute immediate alter session set cursor sharing=exact;

69 end;

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

71 end;

72 /

Package body created.

Понадобилось несколько строковых функций для перезаписи запроса и динамическое открытие курсорной переменной (REF CURSOR). Поскольку вполне вероятно, что в условии запроса есть константы, мы включаем опцию cursor sharing перед анализом запроса, чтобы принудительно использовались связываемые переменные, а затем отключаем ее. (Подробнее об этом см. в главе 10, посвященной настройке производительности). В результате получаем полностью проанализированный запрос, готовый для извлечения данных через курсорную переменную.

Доступ к строкам вокруг текущей строки

Часто необходимо обращаться к данным не только в текущей строке, но и в ближайших предыдущих или последующих. Предположим, необходимо создать отчет, в котором по отделам были бы представлены все сотрудники, причем, для каждого сотрудника выдана дата его приема на работу, за сколько дней до этой даты последний раз принимали сотрудника на работу, и через сколько дней после этого приняли на работу следующего сотрудника. Написание подобного запроса с помощью чистого языка SQL - чрезвычайно сложная задача. Более того, производительность полученного запроса вызывает сомнения. В прошлом я либо пытался применять прием select из select , либо писал PL/SQL-функцию, которая по данным из текущей строки находила предыдущую и следующую строки данных. Это работало, но очень много времени уходило на разработку запроса (приходилось писать больше кода); кроме того, расходовалось большое количество ресурсов при его выполнении.

С помощью аналитических функций это делается быстро и эффективно. Соответствующий запрос будет выглядеть так:

scott@TKYTE816> select deptno, ename, hiredate,

2 lag(hiredate, 1, null) over (partition by deptno

3 order by hiredate, ename) last hire,

4 hiredate - lag(hiredate, 1, null)

5 over (partition by deptno

6 order by hiredate, ename) days last,

7 lead(hiredate, 1, null)

8 over (partition by deptno

9 order by hiredate, ename) next hire,

10 lead(hiredate, 1, null)

11 over (partition by deptno

12 order by hiredate, ename) - hiredate days next

13 from emp

14 order by deptno, hiredate

15 /

DEPTNO ENAME HIREDATE LAST HIRE DAYS LAST NEXT HIRE DAYS NEXT

10 CLARK 09-JUN-81 17-NOV-81 161

KING 17-NOV-81 09-JUN-81 161 23-JAN-82 67

1078

Глава 12

14 rows selected.

Функции LEAD и LAG можно рассматривать как способы индексации в пределах группы. С помощью этих функций можно обратиться к любой отдельной строке. Обратите внимание: в представленн1х выше результатах запись для сотрудника KING включает данные (выделены полужирным) из предыдущей строки (LASTHIRE) и последующей (NEXTHIRE). Можно получить поля предыдущих или последующих записей в упорядоченном фрагменте.

Прежде чем подробно рассматривать функции LAG и LEAD, я хотел бы сравнить этот запрос с аналогичным по результатам запросом, в котором не используются аналитические функции. Для этого я создам необходимые индексы по таблице, чтобы максимально быстро получать ответ:

create table

scott@TKYTE816>

select

object name ename, created hiredate, mod(object id,50) from all objects /

deptno

Table created.

scott@TKYTE816> alter table t modify deptno not null;

Table altered.

scott@TKYTE816> create index t idx on t(deptno,hiredate,ename)

2 /

Index created.

scott@TKYTE816> analyze table t

2 compute statistics

3 for table

4 for all indexes

5 for all indexed columns

Table analyzed.