Введение

Примерно полтора года назад я сдал экзамены на OCP Advanced PL/SQL Developer, далее специфика работы несколько изменилась, и после стандартного производственного использования Oracle я занимался разработкой архитектуры двухуровневой клиент-серверной системы на основе Oracle для нужд компьютерной лингвистики. Далее был этап развития системы и решения наукоемких задач на ее основе, пришлось заниматься использованием иерархических запросов в решении нестандартных задач и другими специфическими вещами. Результатом углубления в специфику стало некоторое «проседание» базы, а значит, наступило время снова просмотреть материалы, использовавшиеся для подготовки к экзаменам.

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

Несколько слов о специфике оператора order by

Как вы думаете, будет ли ошибка в результате выполнения данного запроса?

--Запрос №1
select * from dual
order by 1+2||dummy

Ответ: ошибки не будет, т.к. спецификация order by определена следующим образом:
ORDER BY {col(s)|expression|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST];
Где expression — A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression.
Подробнее, например, здесь download.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

Таким образом, мы отсортировали таблицу dual по выражению «3X», что бессмысленно, однако в качестве выражения для сортировки можно, например, использовать выражение с функцией substr. Важна сама возможность использования выражений.

Как мы знаем, таблица dual содержит один столбец, вернет ли ошибку такой запрос:

--Запрос №2
select * from dual
order by 2

А такой?

--Запрос №3
select * from dual
order by 2+0

В первом случае мы имеем дело с позиционным указанием колонок – т.е. ссылкой на колонку №2, которой не существует, соответственно, получим ошибку.

Во втором случае мы имеем дело с выражением, т.е. это уже не номер колонки, а сортировка по числу 2, аналогичная сортировке по строке «3X» в запросе №1. Поэтому ошибки не будет.

Посмотрите на следующий пример. Какой порядок строк мы получим в результате выполнения этого запроса? Будет ли ошибка?

--Запрос №4
select 3, dummy from dual
union all
select 2, dummy from dual
order by "3"

Union all не сортирует строки объединяемых множеств (в отличии от union), т.е. без order by мы получим строки в указанном в запросе порядке (union all гарантирует сохранение исходного порядка строк). Теперь настала очередь order by, главный вопрос, что такое «3» в этом случае? Поскольку у нас использованы двойные кавычки ", а не одинарные ', то «3» – это алиас колонки. Как известно, операции с множествами требуют использования сходных типов данных, а имена колонок берутся из первого запроса, поскольку мы явно не указали имя первой колонки, то, по умолчанию, она получила имя выражения, т.е. «3». Работа с такими алиасами показана, например, в запросе №5 (главное не забывать про аппер-кейс).

Сортировка по умолчанию – всегда asc, т.е. результаты запроса №4 сортируются по первой колонке по возрастанию. Результат: строка «2, Х», потом «3, Х».

--Запрос №5
select substr(dummy,1,1) from dual
order by "SUBSTR(DUMMY,1,1)" 

Повторим эксперимент из запроса №3 на множествах. Каким будет результат запроса?

--Запрос №6
select 3, dummy from dual
union all
select 2, dummy from dual
order by 2+0

Может показаться, что результат запроса будет следующим: строка «3, Х» потом «2, Х». Предпосылки для этого следующие: данный порядок гарантирует union all, а выражение 2+0 не повлияет на сортировку записей (что показано в запросе №7). Так бы оно и было, если бы не одно «но»: в операциях с множествами order by можно использовать только в конце составного запроса с именами или номерами столбцов первого запроса, выражения не допускаются:
For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query. download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998

Поэтому запрос №6 вернет ошибку.

--Запрос №7
select * from
        (select 3, dummy from dual
         union all
         select 2, dummy from dual)
order by 2+0

Использование not in

Наверняка, большинство людей, прошедших различные курсы, помнят, что следует избегать оператора not in, а сходную функциональность можно получить, используя операторы in либо exists. Причина такого негативного отношения к not in кроется в специфике его работы с null-значениями.

Определите результат выполнения следующего запроса:

--Запрос №1
select * from dual
where 1 not in (select 2 from dual union select null from dual) 

Для начала рассмотрим такой запрос:

--Запрос №2
select * from dual
where 2 in (select 2 from dual union select null from dual) 

Ничего необычного в запросе №2 нет: подзапрос возвращает множество из двух строк со значениями «2» и «null», условие where принимает значение true, весь запрос №2 возвращает 1 строку – стандартное поведение.

Теперь рассмотрим разницу в поведении in и not in, здесь нам понадобятся знания о null-арифметике:
1) Любые арифметические операции с null возвращают null
2) Булевые операторы с null работают следующим образом:

• null AND false = false
• false AND null = false
• null AND true = null
• true AND null = null
• null OR true = true
• true OR null = true
• null OR false = null
• false OR null = null

Пока все логично. Теперь рассмотрим, как работает IN в запросе №2:
2 IN (2,null) => (2=2) OR (2=null) => true OR null => true
Рассмотрим работу NOT IN в запросе №1:
1 NOT IN (2, null) => (1!=2) AND (1!=null) => true AND null => null

Таким образом, условие where в запросе №1 преобразуется в null, если хотя бы один операнд null, поэтому весь запрос №1 приблизительно эквивалентен следующему запросу:

--Запрос №3
select * from dual
where null=null

Очевидно, что запрос №3 не вернет ни одной строки, соответственно, запрос №1 также не вернет ни одной строки.

Неявное преобразование типов

Тема явного и неявного преобразования типов очень обширна, поэтому, не пытаясь охватить ее в целом, я хотел бы рассмотреть лишь один пример. Пускай сегодня 10.09.11 10:00:00 и Оракл сконфигурирован так, что формат DD.MM.RR распознается по умолчанию, какой из запросов вернет одну строку?

--Запрос №1
select * from dual where sysdate>'10.09.11';
--Запрос №2
select * from dual where sysdate||''='10.09.11'; 

Ответ – оба. Почему так и как это, вообще, возможно? Ответ лежит в механизме неявного преобразования типов.

Рассмотрим запрос №1: в where мы сравниваем дату со строкой, в этом случае Оракл пытается преобразовать строку в дату, если формат строки соответствует одному из форматов даты по умолчанию (формат даты по умолчанию для сессии можно посмотреть в параметре NLS_DATE_FORMAT, выполнив запрос
select * from nls_session_parameters). Если формат строки не отвечает формату даты по умолчанию, то получим ошибку. В нашем случае форматы соответствуют и строка '10.09.11' преобразуется в дату 10.09.11 00:00:00, поскольку sysdate = 10.09.11 10:00:00, то запрос №1 вернет 1 строку.

Рассмотрим запрос №2: в where происходит конкатенация даты со строкой, результатом такой операции будет строка, причем при неявном преобразовании даты в строку происходит урезание данных до формата DD.MM.RR, т.е. мы сравниваем две строки '10.09.11' и '10.09.11'. В результате условие where выполняется, и запрос возвращает одну строку.

Заключение

Приведенный обзор не претендует на полноту освещения какой-либо функциональности Oracle, создан «for fun» и призван продемонстрировать несколько случаев нестандартного использования возможностей синтаксиса sql.