Февраль 2005
Профессионалу разработчику
Том Кайт
Том Кайт: о форматах вывода, отрицательных нулях и смещении времени
(On Format, Negation, and Sliding, By Tom Kyte)
Источник: журнал Oracle Magazine, March-April 2004
(http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html).
Наш эксперт модифицирует форматы вывода, делает нули нулями и устраняет смещение времени.
Форматирование вывода значений дат и времени
Как избавиться от начальных нулей в часах (например, 09:30) при форматировании дат?
Имеется подходящий модификатор форматных масок FM (fill mode, режим заполнения), который вы можете использовать при работе с датами и числами. Он подавляет пробелы и другие знаки-заполнители (такие, как этот начальный нуль). Например:
select to_char(dt,'hh:mi am'),
to_char(dt,'fmhh:mi am'),
to_char(dt,'fmhhfm:mi am')
from (
select to_date('09:01 am','hh:mi am') dt
from dual
)
/
TO_CHAR( TO_CHAR( TO_CHAR(
-------- -------- --------
09:01 am 9:1 am 9:01 am
Обратите внимание на три различных варианта вывода. Первый столбец показывает то, что вы извлекли: компоненты времени дополняются начальными нулями до двух символов, если это требуется. Второй столбец показывает влияние модификатора FM: начальные нули подавляются. Однако это, наверное, не то, что вы хотели; значение "9:1" не очень понятное. Третий столбец показывает, вероятно, то, что вы действительно хотите: начальный нуль убирается из обозначения часа, но не из оставшихся компонентов времени. Модификатор FM работает как тумблер – выключает заполнение пустого места (пробелов), когда модификатор встречается в первый раз, и вновь включает, когда появляется снова. Этот модификатор очень удобен для изменения форматирования дат и времени. Рассмотрим отличия при выводе следующих двух столбцов:
select to_char(sysdate,'Day Month dd'),
to_char(sysdate,'fmDay Month dd')
from dual;
NO_FM
-------------------------------------
Sunday November 02
FM
-------------------------------------
Sunday November 2
Второй вариант, в котором используется модификатор FM, позволяет получить хорошо сформатированное обозначение даты. Первый вариант (без модификатора FM) позволяет дополнить названия дней и месяцев пробелами для получения однородного вывода (строки фиксированной длины).
Отрицательный нуль?
Я не понимаю, почему в этом запросе я получаю отрицательные нули:
select c1, c2, c1 -
(trunc(to_date(c2,'YYYYMMDDHH24MISS') + 1) -
to_date(c2,'YYYYMMDDHH24MISS')) * (24*3600) result
from t where c3 != 'N05416776X0001'
C1 C2 RESULT
---- -------------- ------
...
1229 20030731235942 1211
82 20030731235838 -0
Выполнив ваш запрос, я увидел другие результаты. Однако я могу воспроизвести вашу проблему, используя в утилите SQL*Plus команду set numformat (установка формата чисел), которую, как я предполагаю, вы должны были использовать для форматирования вывода. Проблема заключается в том, что результирующий столбец на самом деле не является нулем; это – очень маленькое число, а ваши команды форматирования скрывают от вас этот факт. Рассмотрим следующее:
select c1,
c2,
c1-(trunc(to_date(c2,
'YYYYMMDDHH24MISS')+1)
-to_date(c2,
'YYYYMMDDHH24MISS'))
* (24*3600) result
from (
select 82 c1, 20030731235838 c2
from dual
)
/
C1 C2 RESULT
----------- ---------- ----------
82 2.0031E+13 -2.240E-36
Обратите внимание, как утилита SQL*Plus по умолчанию форматирует числа, используя экспоненциальное представление (оно обеспечивает форматирование данных по их ширине). Вы использовали формат вывода фиксированной длины, поэтому десятичные дроби не выводились:
SQL> set numformat 99999999999999
SQL> /
C1 C2 RESULT
-------- --------------- ----------
82 20030731235838 -0
Итак, результат в действительности не является десятичным нулем, а очень маленьким отрицательным числом. Будьте внимательны, используя форматы; они могут использоваться для хорошего форматирования информации, но также могут и скрывать от вас информацию.
Смещение времени запуска заданий
Я заметил, что значение DBA_JOBS.NEXT_DATE (время следующего запуска задания) каждый день немного смещается. Как мне использовать этот механизм для выполнения критичных для бизнеса операций? Сейчас в одном из моих серверов время смещено более чем на 10 минут. Здесь что-то не так?
Это – ожидаемое и описанное поведение. Легко сделать так, чтобы значение DBA_JOBS .NEXT_DATE не смещалось.
В сервере базы данных задания запускаются в "запланированное или приблизительно запланированное время". Во время запуска задания сервер просто берет строку, заданную вами как интервал повторного запуска задания, и находит ее численное значение (например, выбирает его из таблицы DUAL). Предположим, например, что вы запланировали запуск задания сегодня в полночь с интервалом его повторного запуска, равного SYSDATE+1. Кроме того, предположим, что задание, запланированное для запуска в полночь, на самом деле начнет выполняться в 12:00:20 a.m. (через 20 секунд после полуночи, что очень близко к полуночи, но отличается на какие-то секунды). Программные средства очереди заданий возьмут вашу строку SYSDATE+1 и вычислят ее. Следовательно, следующий запуск задания будет запланирован на завтра в 12:00:20 a.m.! Предположим, запуски процесса автоматически повторяются (всегда со сдвигом приблизительно на 20 секунд). Через две недели задание будет запускаться примерно в 12:05 a.m. Через месяц – в час десять минут и т.д.
Решение этой проблемы заключается в использовании функций дат-времени, которые вычисляют фиксированное время в будущем. Например, предположим, вы хотите запускать задание каждую ночь в 1 a.m. Ваш интервал должен быть следующим:
trunc(sysdate)+1+1/24
Эта функция даты-времени будет всегда выдавать завтрашнее время, равное 1 a.m., независимо от времени ее выполнения.
Установка схемы
В сервере базы данных Oracle8i Release 3 (8.1.7) у меня есть схема, с которой как пользовательский интерфейс работает Oracle Forms 6i. В данный момент имеется только одна схема пользователя, в которой созданы таблицы и т.п.
Я не уверен в выборе лучшего, наиболее надежного, способа, который позволял бы пользователям, подключающимся к серверу под своими именами, использовать мое приложение с помощью встроенных утилит сервера Oracle Database. Мне говорили, что если пользователи должны иметь возможность работы с приложением под своими именами, то мне желательно использовать синонимы, поскольку пользователям для выполнения процедур требуется иметь привилегии <схема>.<процедура>. Действительно ли это так?
Если вы спрашиваете меня, то нет абсолютно ничего неправильного с использованием <схема>.<процедура> в вашем приложении. На самом деле, я думаю, это является хорошей рекомендацией. Но если вы не используете ее, вы можете добавить в ваш код следующее:
alter session set
current_schema=имя_схемы;
Рассмотрим, например, что произойдет после входа в систему пользователя, не являющегося пользователем SCOTT:
SQL> desc dept
ERROR:
ORA-04043: object dept does not exist
SQL> alter session set
2 current_schema=scott;
Session altered.
SQL> desc dept
Name Null? Type
------ -------- --------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Установка текущей схемы сеанса (CURRENT_SCHEMA) используется для замены в вашем сеансе имени схемы по умолчанию. Так всякий раз, когда сервер базы данных должен будет вставить в запрос ваше текущее имя пользователя, он вставит имя SCOTT (или то имя, которое вы ранее установили). Заметим, это никоим образом не влияет на безопасность, а только на используемое имя схемы по умолчанию. Если у вас нет прав доступа к таблице SCOTT.DEPT, выше показанная команда desc (describe – вывод определения столбцов) выдаст ошибку.
Ограничение количества объектов
Меня интересует, может ли сервер Oracle Database ограничивать количество объектов, создаваемых конкретным пользователей (например, до 10). Можно ли реализовать такую потребность?
Триггеры системных событий – отличное средства для реализации таких уникальных специальных правил. Для подсчета количества объектов, существующих в схеме, и принудительного запрета на создание в схеме объектов, если счетчик объектов превышает установленный вами максимум, можно использовать триггер BEFORE CREATE (перед созданием). Листинг 1 показывает пример такого триггера.
|
ЛИСТИНГ 1: триггер BEFORE CREATE для подсчета количества объектов |
SQL> create or replace trigger no_more_than_10
2 before create on database
3 declare
4 l_cnt number;
5 begin
6 if ( user in ( 'A', 'B', 'C' ) )
7 then
8 select count(*) into l_cnt
9 from dba_objects
10 where owner = USER
11 and object_type = 'TABLE';
12
13 if ( l_cnt >= 10 )
14 then
15 raise_application_error ( -20001, 'You can only do 10 things!' );
16 end if;
17 end if;
18 end;
19 /
Trigger created. |
В качестве альтернативы, вы можете создавать триггеры не на уровне базы данных (before create on database), а в каждой схеме, для которой вы хотите задать такое поведение. Чтобы продемонстрировать это в действии, я просто создам тестовую учетную запись:
SQL> drop user a cascade;
User dropped.
SQL> create user a identified by a;
User created.
SQL> grant create session,
2 create table to a;
Grant succeeded.
Теперь я подключусь к серверу базы данных как этот новый пользователь и создам 10 объектов:
SQL> connect a/a
SQL> begin
2 for i in 1 .. 10
3 loop
4 execute immediate
'create table t' || i ||
' (x int)';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
До сих пор все было нормально, но когда я пытаюсь создать 11-й объект, я обнаруживаю следующее:
SQL> create table t11 ( x int );
create table t11 ( x int )
*
ERROR at line 1:
ORA-00604: error occurred at
recursive SQL level 1
ORA-20001: You can only do 10 things!
ORA-06512: at line 13
Вы можете спросить себя, почему отсутствует специальная команда, которая делает это? Дело в том, что в сервере базы данных не могли предусмотреть удовлетворение всех возможных уникальных требований заказчиков, но нам предоставляются необходимые инструментальные средства, с помощью которых мы можем сами приспособить систему для удовлетворения наших потребностей. Например, вместо этого простого правила "ограничить до 10" вы, предположим, имеете набор запросов заказчика, таких, как:
- Хотел бы я знать, как сделать так, чтобы я мог предоставлять некоторому пользователю привилегию изменения паролей только 20 указанных пользователей.
- Обычно предоставление привилегии ALTER USER позволяет изменять пароли любых пользователей, включая администраторов базы данных. Хотелось бы иметь таблицу, в которой описано, какие пользователи могут изменять пароли каких-то других пользователей, например, аналитику справочной службы может быть разрешено изменение паролей только пяти указанных пользователей, но никаких других. Так я хотел бы, чтобы при изменении паролей просматривалась эта таблица для проверки, может ли пользователь George изменить пароль пользователя Mary.
- Хотел бы я знать, как сделать так, чтобы указанный пользователь мог изменять пароли любых учетных записей, имена которых начинаются с APP_.
Все эти правомерные запросы основаны на требованиях справочной службы (потребность предоставления техническому специалисту справочной службы, имеющему относительно небольшие привилегии, права изменения паролей пользователей, но не администраторов базы данных!), но они настолько уникальны и зависят от требований заказчика, что мы не можем все их предвидеть. Но при условии, что сервер базы данных может выполнять хранимые процедуры с правами их владельцев, я легко могу обеспечить выполнение самых специализированных в мире протоколов безопасности. Например, для выполнения первого запроса заказчика я мог бы создать процедуру, показанную на листинге 2.
|
ЛИСТИНГ 2: процедура изменения паролей, требуемая заказчику |
create procedure
reset_password( p_username in varchar2,
p_password in varchar2)
as
begin
if ( USER='GEORGE' and p_username in
( 'MARY', 'BILL', 'SUE' ) )
then
execute immediate
'alter user ' || p_username ||
' identified by ' || p_password;
else
send_email_to_admin
( 'george trying to change ' || p_username );
raise_application_error
( -20001, 'George, you cannot do that' );
end if;
end;
/
grant execute on reset_password to george; |
Я смог за несколько секунд обеспечить выполнение первого требования заказчика, поскольку эта процедура выполняется с первоначальными привилегиями ее владельца (поэтому владелец этой процедуры, не являющийся пользователем GEORGE, должен иметь привилегию ALTER USER)! Столь легко можно удовлетворить второе и третье требования заказчика. Фактически, здесь возможно любое бесконечное число вариаций. И все они настолько безопасны и контролируемы, насколько вы хотите этого.
Элемент формата даты RR
Я пытаюсь использовать RR-формат так, как он поддерживается в СУБД Oracle9i. В соответствии с описанием RR-формата, если текущий год и указанный год находятся в диапазоне 0-49, значение даты будет выдаваться в текущем столетии. Предположим, текущий год – 2003, а указанная дата – 27-oct-17. Означает ли это, что с RR-форматом это будет годом 2017, а с YYYY-форматом – 1917? Будут ли для одной и той же даты выдаваться два разных значения?
В этом случае ответ будет таким: 2017 с форматами RR и YY, год 17 с YYYY-форматом. Рассмотрим следующее:
alter session set
nls_date_format='yyyy';
select
to_date('27-oct-17','dd-mon-yy') c1,
to_date('27-oct-17','dd-mon-rr') c2,
to_date('27-oct-17','dd-mon-yyyy') c3
from dual
/
C1 C2 C3
---- ---- ----
2017 2017 0017
Отклонившись на минуту от темы, я сделаю очень важное замечание. Единственно правильная, надлежащая, профессиональная, толковая, разумная вещь – это делать то, что все мы изучили на собственном горьком опыте в 1999 году. Для надлежащего ввода года требуется четыре символа. Точка. Пользовательский интерфейс должен принимать точно четыре цифры – не больше и не меньше. При обозначении года двумя цифрами возникает масса ситуаций, когда возможно двойное толкование даты. Когда вы видите 27-oct-17, вы не имеете никакого представления, какая это на самом деле дата.
Но, отвечая на ваш вопрос, если вы убираете часть данных, данные теряются. Если вы будете использовать два символа, чтобы показать что-то, что требует минимум четырех, то вы потеряете данные; в некоторый момент вы исказите их.
Используйте YYYY-формат, и вы никогда не должны будете беспокоиться об этом!
Аналитика приходит на помощь (еще один раз)
У меня есть записи, такие как:
Time Amount
------------------- ------
11/22/2003 12:22:01 100
11/22/2003 12:22:03 200
11/22/2003 12:22:04 300
11/22/2003 12:22:06 200
11/22/2003 12:22:45 100
11/22/2003 12:22:46 200
11/22/2003 12:23:12 100
11/22/2003 12:23:12 200
Мне нужно вычислять сумму количества (Amount) в записях, значение времени (Time) в которых находится в пределах трех секунд от предыдущей или последующей записей. Для показанных выше записей я ожидаю получить три строки: первые четыре записи подходят друг другу, затем следующие две и, наконец, последние две. Я ищу "экземпляры", где я определяю экземпляр таким, что записи в нем не отделены друг от друга более чем на три секунды. Я думаю, что я должен использовать функции LAG (запаздывание) и LEAD (опережение).
Это – интересная проблема и вы правы, использование аналитических функций – правильный подход. Я составил список шагов для реализации моего подхода:
- Берем строку, и если предыдущая строка находится в пределах трех секунд, то наша строка присоединяется к этой группе. В противном случае, начинаем новую группу. Для просмотра предыдущей строки и получения разницы во времени в двух строках я могу использовать функцию LAG.
- Как только я замечаю смежные записи, в которых разница во времени превышает три секунды, я должен изменить идентификатор этой группы.
- Затем я могу суммировать данные строк с одинаковыми идентификаторами групп.
Чтобы видеть, что происходит, я буду выполнять каждый из этих шагов по отдельности. Я загрузил ваши данные в небольшую таблицу T и для экономии места буду использовать NLS_DATE_FORMAT (формат даты для сеанса), установленный как hh24:mi:ss, так что я буду видеть в моих датах только компонент времени. Начиная с первого шага, я помечаю начало каждой новой группы (или, как вы называете их, каждого "экземпляра"):
select time, amount,
case
when time-ltime > 3/24/60/60
or ltime is null
then row_num
end rn
from (
select time, amount,
lag(time)
over (order by time) ltime,
row_number()
over (order by time) row_num
from t
)
order by 1
TIME AMOUNT RN
-------- ------ --
12:22:01 100 1
12:22:03 200
12:22:04 300
12:22:06 200
12:22:45 100 5
12:22:46 200
12:23:12 100 7
12:23:12 200
8 rows selected.
В начале каждой группы я вывожу значение аналитической функции ROW_NUMBER (номер строки). Я узнаю первую строку каждой группы, вычитая из значения времени текущей строки значение времени предыдущей строки (извлекается с помощью функции LAG). Если результирующее значение равно NULL (указывает на первую строку всего результирующего набора) или больше трех секунд, я помечаю эту строку. Теперь мне нужно перенести эти уникальные значения во все подходящие друг другу строки. Для этого я могу использовать аналитическую функцию MAX (максимальное значение). Я возьму приведенный выше запрос (ВЫШЕПРИВЕДЕННЫЙ-ЗАПРОС) – без предложения ORDER BY 1 – и выполню следующий запрос:
select time, amount,
over(order by time) max_rn
from (ВЫШЕПРИВЕДЕННЫЙ-ЗАПРОС)
order by 1
/
TIME AMOUNT MAX_RN
-------- ------ ------
12:22:01 100 1
12:22:03 200 1
12:22:04 300 1
12:22:06 200 1
12:22:45 100 5
12:22:46 200 5
12:23:12 100 7
12:23:12 200 7
8 rows selected.
|
Следующие шаги
СПРАШИВАЙТЕ Тома
asktom.oracle.com
Том Кайт – вице-президент группы Oracle Government, Education, and Healthcare – отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке.
ЧИТАЙТЕ
Еще Тома
Effective Oracle by Design
(Проектирование эффективных приложений Oracle) |
Этот код переносит для меня идентификаторы групп. Это работает, поскольку в MAX(RN) OVER (ORDER BY TIME)используется неявное окно диапазона "между текущей строкой и неограниченным количеством предшествующих строк"; это означает, что когда функция MAX(RN) вычислялась для первой строки, то рассматривалась только эта первая строка и строки, расположенные перед ней в наборе строк, который был упорядочен по времени (ORDER BY TIME) – в данном случае никаких строк в этом наборе нет. Для второй строки в этом результирующем наборе значение функции MAX(RN)было выбрано из текущей строки и предшествующей – в этом случае было перенесено значение, равное 1. Аналогично для третьей и четвертой строки – значение функции MAX(RN) снова выбиралось из текущей и предшествующих строк. Когда я добрался до пятой строки, рассматривались новая текущая строка и все предыдущие строки и, конечно, теперь значение функции MAX(RN) было равно 5 и т.д.
Теперь осталось только завершить агрегирование. Это сделать легко, поскольку я пометил все мои группы. Используя приведенный выше запрос (без предложения ORDER BY), я могу сделать следующее:
select min(time),
max(time),
sum(amount)
from (ВЫШЕПРИВЕДЕННЫЙ-ЗАПРОС)
group by max_rn
order by 1
/
MIN(TIME MAX(TIME SUM(AMOUNT)
-------- -------- -----------
12:22:01 12:22:06 800
12:22:45 12:22:46 300
12:23:12 12:23:12 300
3 rows selected.
И вы получили точно то, что хотели. Аналитика – чрезвычайно мощное средство, как только вы его освоите.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в Oracle с 1993 года. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книг "Effective Oracle by Design" (издательство Oracle Press, 2003) – "Проектирование эффективных приложений Oracle" и "Expert One on One: Oracle" (издательство Apress, 2003) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.).
|
