Функции FIRST и LAST

понедельник, 10 августа 2009 г. 1 коммент.

Очень редко пользуюсь этими функциями, так как область применения их достаточно узкая. Но появился случай, где эти функции можно применить. Особенность этих функций является то, что они как бы создают свое "подокно", внутри "окна" данных, которое определено для этих функций. Давай-те рассмотри на примере. В примере смотрим на табличку EMP в схему SCOTT. Задача такая: в каждом отделе нужно найти сотрудника с максимальным окладом (поле SAL имеет максимальное значение), который дольше всех работает на предприятии (поле HIREDATE имеет минимальное значение).


select emp.deptno, emp.ename, emp.sal, emp.hiredate,
first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
min(emp.hiredate)
keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd1,
count(1)
keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd2
from emp;


DEPTNOENAMESAL HIREDATE FV1 HD1 HD2
1 10 KING 5000,00 17.11.1981 17.11.1981 17.11.1981 1
2 10 CLARK 2450,00 09.06.1981 17.11.1981 17.11.1981 1
3 10 MILLER 1300,00 23.01.1982 17.11.1981 17.11.1981 1
4 20 FORD 3000,00 03.12.1981 03.12.1981 03.12.1981 2
5 20 SCOTT 3000,00 19.04.1987 03.12.1981 03.12.1981 2
6 20 JONES 2975,00 02.04.1981 03.12.1981 03.12.1981 2
7 20 ADAMS 1100,00 23.05.1987 03.12.1981 03.12.1981 2
8 20 SMITH 800,00 17.12.1980 03.12.1981 03.12.1981 2
9 30 BLAKE 2850,00 01.05.1981 01.05.1981 01.05.1981 1
10 30 ALLEN 1600,00 20.02.1981 01.05.1981 01.05.1981 1
11 30 TURNER 1500,00 08.09.1981 01.05.1981 01.05.1981 1
12 30 WARD 1250,00 22.02.1981 01.05.1981 01.05.1981 1
13 30 MARTIN 1250,00 28.09.1981 01.05.1981 01.05.1981 1
14 30 JAMES 950,00 03.12.1981 01.05.1981 01.05.1981 1

C помощью partition by emp.deptno создаем окно по подразделениям (окно, когда когда подразделения равен 20, выделено серым цветом). Внутри этого окна функция dense_rank last order by emp.sal формирует ранк по величине оклада и создает свое “подокно” (оно выделено жирным шрифтом). Уже внутри этого окна применяется функция min(emp.hiredate). Тот же результат можно получить с помощью функции first_value.



Почитать про эти функции можно здесь FIRST и LAST.



P.S. Редкое использование этих функций привело к тому, что Oracle не исправил такой интересный баг. Вместо слова order можно написать любую ерунду.



select emp.deptno, emp.ename, emp.sal, emp.hiredate,
first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
min(emp.hiredate)
keep (dense_rank last TRAMPARARAM by emp.sal) over (partition by emp.deptno) as hd1,
count(1)
keep (dense_rank last QQ by emp.sal) over (partition by emp.deptno) as hd2
from emp;

Читать полностью

,

Отладка с помощью DBMS_PIPE

четверг, 6 августа 2009 г. 0 коммент.

Для отладки и логирования я достаточно часто применяю пакет dbms_pipe, этот способ имеет свои преимущества и недостатки. Обычно для отладки применяется пошаговое выполнение, это удобно, но отладка должна быть разрешена, пакет должен быть скомпилирован с флажком debug, и код можно выполнить в своей сессии.

Для отладки можно использовать dbms_output, но результат виден только после выполнения всего кода, и также должна быть возможность выполнить код из своей сессии.

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

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

Сначала надо дать гранты на выполнения пакета dbms_pipe.


grant execute on dbms_pipe to myuser;

Создадим 2 метода для отсылки и приема сообщений.



create or replace package custom_pipe is

-- Author : USER
-- Purpose : работаем с pipe
-- отправить сообщение через pipe
procedure SendMessage( psPipe in varchar2
,psMessage in varchar2
);

-- получить сообщение через pipe
function ReceiveMessage( psPipe in varchar2
,psKey in varchar2
,psMessage out varchar2
) return integer;

end custom_pipe;

/



create or replace package body custom_pipe is

procedure SendMessage( psPipe in varchar2
,psMessage in varchar2
)
is
i integer;
begin
dbms_pipe.pack_message(psMessage);
i := dbms_pipe.send_message(psPipe, 30);
if i != 0 then
raise_application_error(-20101, 'Ошибка при посылке сообщения клиенту!');
end if;
end SendMessage;

function ReceiveMessage( psPipe in varchar2
,psKey in varchar2
,psMessage out varchar2
) return integer
is
call_stat integer := 0;
next_item integer := 0;
temp_varchar2 VARCHAR2(2000);
temp_date DATE;
temp_number NUMBER;
temp_rowid ROWID;
temp_raw RAW(2000);
vnResult integer := 0;
begin
psMessage := '';
call_stat := DBMS_PIPE.RECEIVE_MESSAGE(psPipe, 0);
if (call_stat = 0) then
next_item := DBMS_PIPE.NEXT_ITEM_TYPE;
while next_item > 0 loop
if next_item = 9 then
DBMS_PIPE.UNPACK_MESSAGE(temp_varchar2);
elsif next_item = 6 then
DBMS_PIPE.UNPACK_MESSAGE(temp_number);
temp_varchar2 := TO_CHAR(temp_number);
elsif next_item = 11 then
DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);
temp_varchar2 := ROWIDTOCHAR(temp_rowid);
elsif next_item = 12 then
DBMS_PIPE.UNPACK_MESSAGE(temp_date);
temp_varchar2 := TO_CHAR(temp_date,'DD.MM.YYYY HH24:MI:SS');
elsif next_item = 23 then
DBMS_PIPE.UNPACK_MESSAGE_RAW(temp_raw);
temp_varchar2 := RAWTOHEX(temp_raw);
else
temp_varchar2 := '';
-- temp_varchar2 := 'Не верный тип элемента: '||TO_CHAR(next_item);
end if;
psMessage := substr(psMessage || temp_varchar2, 1, 4000);
next_item := DBMS_PIPE.NEXT_ITEM_TYPE;
if (upper(temp_varchar2) = upper(psKey) ) then
vnResult := 1;
exit;
end if;
end loop;
elsif (call_stat = 1) then
null;
elsif (call_stat in (2,3) ) then
raise_application_error(-20100, 'Ошибка при получении сообщения!');
end if;
return(vnResult);
end ReceiveMessage;

end custom_pipe;

Воспользуемся пакетом. В одной сессии будем передавать сообщения, а в другой получать.



SQL> declare
vspipe varchar2(30) := 'TEST_PIPE';
begin
for i in 1 .. 20
loop
custom_pipe.sendmessage(pspipe => vspipe,
psmessage => 'Текущее время: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));
dbms_output.put_line('Текущее время: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));
-- задержка 4 секунды
dbms_lock.sleep(3);
end loop;
end;
/

Для получения сообщении можно воспользоваться функцией ReceiveMessage, я так и делаю, когда надо передать сообщение в другую сессию. Но для отладки я пользуюсь возможностью, которая предоставляется средой разработки “PL/SQL Developer” от Allround Automations. Выбираем в меню “Tools->Event Monitor…” и настраиваем на прослушивание своего pipe.


image


Читать полностью