Оглавление:
Форматирование текста SQL.
Рекомендуем вам аккуратно форматировать все запросы, чтобы читать их было удобно не только вам, но и другим. Вот пример отформатированного запроса, возможно такое отображение вас устроит:
SELECT ba.batch_id,
TO_CHAR(ba.rec_date) AS recDate,
bp.parm_code,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp
WHERE ba.type_id = 1
AND ba.status_id IN (1,2,3,5)
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bd.parm_id = bp.parm_id
AND bp.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
Структуры данных для примеров.
Алиасы таблиц.
Мы настроятельно рекомендуем вам при написании запросов использовать алиасы таблиц. Алиас — это краткое обозначение таблицы (или запроса). Использование алиасов облегчает написание запросов, кроме этого в некоторых случаях без алиасов не обойтись, следующий пример при выполнении даст ошибку
ORA-00918: column ambiguously defined
т.е. ‘столбец определен неоднозначно’:
SELECT batch_id,
batch_code,
status_id
FROM sigma.batch
INNER JOIN sigma.batch_data ON batch_id = batch_id
INNER JOIN sigma.batch_parm ON parm_id = parm_id
WHERE type_id = 1
AND status_id IN (1,2,3,5)
В данном запросе причин для таких ошибок несколько, первая ошибка будет относиться к полю BATCH_ID в первом соединении с таблицей BATCH_DATA, потому что СУБД не знает, в какой таблице это поле проверять: BATCH или BATCH_DATA.
Joins (объединение в запросе нескольких таблиц).
Inner join.
Мы настоятельно рекомендуем вам придерживаться стандартного написания запросов, т.е. при соединении таблиц использовать операторы INNER JOIN, LEFT OUTER JOIN и т.д.
SELECT ba.batch_id,
ba.batch_code,
ba.batch_name,
ba.status_id,
bd.parm_id,
bp.parm_code,
bd.value_plan
FROM sigma.batch ba
INNER JOIN sigma.batch_data bd ON ba.batch_id = bd.batch_id
INNER JOIN sigma.batch_parm bp ON bd.parm_id = bp.parm_id
WHERE ba.type_id = 1
AND ba.status_id IN (1,2,3,5)
AND ba.batch_code IS NOT NULL
В данном примере будут соединены три таблицы, основной запрашиваемой таблицей будет BATCH, она имеет алиас ba. При таком соединении для каждой записи таблицы BATCH будет произведен поиск в таблице BATCH_DATA по полю BATCH_ID. Результатом выборки будет набор записей в обоих таблицах у которых значения полей BATCH_ID совпадают. То же самое касается и пары таблиц BATCH_DATA и BATCH_PARM, они будут соединены по полю PARM_ID.
Существует упрощенная конструкция записи такого запроса, которая считается устаревшей (и несовершенной):
SELECT ba.batch_id,
ba.batch_code,
ba.batch_name,
ba.status_id,
bd.parm_id,
bp.parm_code,
bd.value_plan
FROM sigma.batch ba, sigma.batch_data bd, sigma.batch_parm bp
WHERE ba.type_id = 1
AND ba.status_id IN (1,2,3,5)
AND ba.batch_code IS NOT NULL
-- INNER JOIN sigma.batch_data bd ON ba.batch_id = bd.batch_id --
AND ba.batch_id = bd.batch_id
-- INNER JOIN sigma.batch_parm bp ON bd.parm_id = bp.parm_id --
AND bd.parm_id = bp.parm_id
Все соединяемые таблицы перечислены после оператора FROM, а условия соединения прописаны в конструкции WHERE. Для базы данных запросы в первом и втором примере идентичны, второй (компактный) способ записи может применяться при простых соединениях таблиц и запросов.
Left join.
Конструкции LEFT JOIN и LEFT OUTER JOIN для Оракла идентичны, так что слово OUTER можно смело опускать:
SELECT ba.batch_id,
ba.batch_code,
ba.batch_name,
bp.parm_id,
bp.parm_code,
bd.parm_id,
bd.value_plan
FROM sigma.batch ba
JOIN sigma.batch_parm bp ON 1 = 1 -- перемножение таблиц bp и ba
LEFT OUTER JOIN sigma.batch_data bd ON bd.parm_id = bp.parm_id
AND bd.batch_id = ba.batch_id
WHERE ba.type_id = 1
AND ba.status_id = 1
ORDER BY ba.batch_id, bp.parm_id
Данный пример делает следующее: каждую запись из таблицы BATCH он соединяет со всеми записями таблицы BATCH_PARM (конструкция ON 1 = 1), это так называемое «перемножение» записей. Затем для каждой записи из BATCH и BATCH_PARM подыскивается запись в таблице BATCH_DATA. При соединении таблиц, в результирующем наборе можно обратиться к полям записи любой из таблиц. В случае использования OUTER JOIN, если в таблице, где производится поиск, нужная запись не найдена, то соответствующие поля содержат NULL.
Аналог краткой формы запроса построить не удалось. Это связано с тем, что в условии объединения (OUTER JOIN) участвуют две таблицы: ON bd.parm_id = bp.parm_id AND bd.batch_id = ba.batch_id, такое соединение запрещено для краткой формы синтаксиса. Если кто-то предложит решение, то мы будем очень рады и обязательно его опубликуем с ссылкой на автора.
Вот так выглядят результаты запроса в PL/SQL Developer, красным выделены «дыры» в таблице поиска, т.е. таких записей в таблице BATCH_DATA нет:
Вот примеры идентичных запросов, когда можно использовать оператор (+):
SELECT bp.parm_id,
bp.parm_code,
bd.parm_id,
bd.value_plan
FROM sigma.batch_parm bp
LEFT JOIN sigma.batch_data bd ON bd.batch_id = 222313
AND bd.parm_id = bp.parm_id
ORDER BY bp.parm_id
-- аналог в краткой форме --
SELECT bp.parm_id,
bp.parm_code,
bd.parm_id,
bd.value_plan
FROM sigma.batch_parm bp, sigma.batch_data bd
WHERE bp.parm_id = bd.parm_id (+)
AND 222313 = bd.batch_id (+) -- очень нетривиальная конструкция, их надо избегать
ORDER BY bp.parm_id
Смешение стилей оформления запросов допускается, т.е. таблицы можно соединить через ключевое слово JOIN и после ключевого слова WHERE. Однако для подобного смешения существует ограничение: использование упрощенного синтаксиса соединения таблиц (+) и синтаксиса ANSI не допускается, т.е. вот такой запрос приведет к ошибке
ORA-25156 old style outer join (+) cannot be used with ANSI joins
т.е. ‘старый стиль (+) не может быть использован вместе с ANSI joins’:
SELECT ba.batch_id FROM sigma.batch ba, sigma.batch_data bd LEFT OUTER JOIN sigma.batch_parm bp ON bd.parm_id = bp.parm_id LEFT OUTER JOIN sigma.batch_parm bp1 ON bd.parm_id = bp1.parm_id -- алиас bd виден -- LEFT OUTER JOIN sigma.batch_data bd1 ON bd1.batch_id = ba.batch_id -- не видит алиас ba WHERE ba.batch_id = bd.batch_id (+) -- это запрещено
Кроме этого имеется ограничение на видимость таблиц, в конструкциях JOIN видна только последняя таблица из FROM. Таким образом, во избежание путаницы использование смешанного синтаксиса соединений таблиц и запросов не рекомендуется. С большой долей вероятности можно сказать, что в будущих версиях СУБД такое поведение будет изменено, и ваши старые запросы перестанут работать.
Right join.
К простой конструкции с (+) не приводится. Отличается от соединения LEFT (OUTER) JOIN тем, что в запросе будут выведены все записи таблицы стоящей после этих ключевых слов, в то время как из таблицы во FROM только записи удовлетворяющие условию после ключевого слова ON.
SELECT bp.parm_id,
bp.parm_code,
bd.parm_id,
bd.value_plan
FROM sigma.batch_data bd
RIGHT JOIN sigma.batch_parm bp ON bd.batch_id = 222313
AND bd.parm_id = bp.parm_id
ORDER BY bp.parm_id
Full outer join.
К простой конструкции с (+) не приводится. На практике встречается не часто, бывает полезен, когда необходимо объединить строки нескольких таблиц по какому-либо условию. Если структуры таблиц или запросов, участвующих в запросе совпадают или из соединяемых таблиц необходимо вывести одинаковый набор полей, то можно вместо FULL OUTER JOIN использовать оператор UNION.
В приводимом примере объединяются результаты двух запросов к таблице PRODUCT. Целью запроса является получение списка товаров, которые содержатся в двух различных offers. Производится объединение двух запросов, потому что в одном offer может содержаться несколько одних и тех же товаров (GOODS) в наборах или в качестве продаваемого товара и подарка. Для удаления дублей используются запросы с ключевым словом DISTINCT.
SELECT pr1.goods_id AS g1,
pr2.goods_id AS g2,
gd.type_id,
gd.goods_name
FROM (
SELECT DISTINCT
pr11.goods_id
FROM sigma.product pr11
WHERE pr11.offer_id = 28336
AND pr11.parent_id IS NULL
) pr1
FULL OUTER JOIN (
SELECT DISTINCT
pr22.goods_id
FROM sigma.product pr22
WHERE pr22.offer_id = 28590
AND pr22.parent_id IS NULL
) pr2 ON pr2.goods_id = pr1.goods_id
JOIN sigma.goods gd ON gd.goods_id = pr1.goods_id OR gd.goods_id = pr2.goods_id
WHERE gd.type_id = 3
AND (pr1.goods_id IS NULL
OR pr2.goods_id IS NULL)
В приведенном примере выборка ограничивается так называемыми PRIMARY GOODS(gd.type_id = 3), что соответствует реальным товарам, а не наборам и сервисам в каталоге. Результатом выборки будет набор записей из двух запросов со всеми возможными значениями поля GOODS_ID. Если в каком либо запросе goods_id отсутствует, но он есть в другом, то все поля записи первого запроса будут содержать NULL. В приведенном примере выводятся записи, которые уникальны для каждого из запросов. Результат запроса будет выглядеть примерно так:
В данном запросе показано, как можно присоединить к FULL OUTER JOIN другие таблицы. Здесь добавляется таблица GOODS для получения названий товаров и их типов. После ключевого слова ON идут два условия, соединенных через OR. Это позволяет к каждой записи в FULL OUTER JOIN присоединить одну запись из таблицы GOODS.
Таким образом, в окончательном виде запрос, который возвратит список товаров в двух offers, будет выглядеть так:
SELECT NVL(pr1.goods_id, pr2.goods_id) AS goods_id,
gd.type_id,
gd.goods_name
FROM (
SELECT DISTINCT
pr11.goods_id
FROM sigma.product pr11
WHERE pr11.offer_id = 28336
AND pr11.parent_id IS NULL
) pr1
FULL OUTER JOIN (
SELECT DISTINCT
pr22.goods_id
FROM sigma.product pr22
WHERE pr22.offer_id = 28590
AND pr22.parent_id IS NULL
) pr2 ON pr2.goods_id = pr1.goods_id
JOIN sigma.goods gd ON gd.goods_id = pr1.goods_id OR gd.goods_id = pr2.goods_id
WHERE gd.type_id = 3
AND (pr1.goods_id IS NULL
OR pr2.goods_id IS NULL)
ORDER BY 1
Результаты отсортированы по полю GOODS_ID.
Условия отбора после ключевого слова WHERE основного запроса должны быть всегда вторичны и не иметь отношения к самому объединению. Условия объединения таблиц или запросов должно быть полностью указано после ключевого слова ON конструкции FULL OUTER JOIN.
Основные части конструкции SELECT.
Список запрашиваемых полей.
Располагается за ключевым словом SELECT. Получить можно поля таблиц, которые участвуют в запросе, функции Oracle SQL для полей, различные выражения (expressions), результаты выполнения запросов, конструкцию CASE-WHEN, аналитические функции для записей таблиц запроса:
SELECT ba.batch_id, -- поля (SELECT)
ba.rec_date,
ROWNUM, -- псевдоколонки
TO_CHAR(ba.rec_date, 'DD-MM-YY') AS recDate, -- выражения (expressions)
bd.parm_id,
bp.parm_code,
-- CASE --
CASE
WHEN bp.parm_code = 'O_VIRTUAL' THEN 'Виртуальных отправлений'
WHEN bp.parm_code = 'O_PALLET_W' THEN 'К-во паллет'
-- WHEN bp.parm_code = 'O_GOFRO_AMOUNT' THEN 15 -- Запрещено смешение типов
ELSE 'Прочие'
END AS parmName,
-- Запрос --
(SELECT COUNT(*) FROM sigma.batch_data bd1 WHERE bd1.batch_id = ba.batch_id
) AS amount,
-- Аналитическая функция --
ROW_NUMBER() OVER (PARTITION BY ba.batch_id ORDER BY bp.parm_id) AS position
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp -- список таблиц (FROM)
...
Результат подзапроса, как поле.
Бывают случаи, когда в результатах запроса необходимо вывести значение, полученное с помощью другого запроса. Обычно это случается, когда необходимо что-то подсчитать или проанализировать в подчиненной таблице, а группировку (GROUP BY) использовать нецелесообразно. В приведенном в прошлом разделе примере такой запрос используется для подсчета количества значений параметров батча. Подобные подзапросы должны всегда возвращать не более одной записи, в противном случае будет выдано сообщение об ошибке:
ORA-01427: single-row subquery returns more than one row
т.е. ‘подзапрос, который должен возвращать одну запись, возвращает более одной’:
SELECT ba.batch_id, -- поля (SELECT)
(SELECT COUNT(*) FROM sigma.batch_data bd1
WHERE bd1.batch_id = ba.batch_id
) AS amount,
(SELECT bd2.parm_id FROM sigma.batch_data bd2 WHERE bd2.batch_id = -1
) AS thisNull -- NULL
-- (SELECT bd2.parm_id FROM sigma.batch_data bd2 WHERE bd2.batch_id = ba.batch_id
-- ) AS itError -- error
FROM sigma.batch_data bd, sigma.batch ba
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
К сожалению, подобные подзапросы могут вернуть только одно значение, т.е. поле, если вам необходимо получить несколько полей в результате одного запроса, то запросы придется дублировать в виде колонок. В большинстве случаев это может привести к дополнительной нагрузке на сервер.
DISTINCT (UNIQUE) — отбор уникальных записей.
Располагается сразу после SELECT. Использование этого ключевого слова позволяет удалить дубли записей, т.е. для записей, которые по выводимым полям совападают, будет отобрана только одна запись:
SELECT DISTINCT
bd.batch_id,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch_parm bp
WHERE bd.parm_id = bp.parm_id
AND bd.value_plan IS NOT NULL
ORDER BY bd.batch_id, bd.value_plan
На рисунке ниже приведены результаты этого запроса с DISTINCT и без него:
Оператор DISTINCT обрабатывает результаты уже после того, как будут выполнены условия фильтрации в конструкции WHERE.
Псевдоколонка ROWNUM.
Результат запроса всегда содержит определенный набор псевдоколонок, к ним относятся ROWNUM, ROWID, LEVEL. ROWID может присутствовать только в запросах, которые не содержат ключевых слов DISTINCT и GROUP BY.
ROWNUM содержит порядковый номер записи в результатах запроса. Эта псевдоколонка бывает полезна для принудительного ограничения количества результирующих записей. В этом случае она применяется в конструкции после WHERE. Распространенной ошибкой является использование ROWNUM в сочетании с ORDER BY. Надо понимать, что при выполнении запроса Oracle сначала соединит таблицы, потом выполнит условия в конструкции WHERE и только потом отсортирует результаты. Вот пример неверного запроса:
SELECT ROWNUM,
ba.rec_date,
ba.batch_id
FROM sigma.batch ba
WHERE ba.status_id = 1
AND ROWNUM <= 20 -- не выдаст последние 20 записей отсортированных по rec_date
ORDER BY ba.rec_date DESC
Для того, чтобы получить действительно последние 20 записей из таблицы BATCH, необходимо сначала их полностью выбрать, отсортировать и только потом включать ограничение, вот работающий пример:
SELECT ROWNUM,
rn ,
ba1.rec_date,
ba1.batch_id
FROM (
SELECT ROWNUM AS rn,
ba.rec_date,
ba.batch_id
FROM sigma.batch ba
WHERE ba.status_id = 1
ORDER BY ba.rec_date DESC
) ba1
WHERE ROWNUM <= 20
Ниже показаны результаты первого и второго запросов для сравнения:
Группировка - GROUP BY.
Это ключевое слово используется в том случае, когда необходимо произвести некие аналитические вычисления над результатами запроса. Оно позволяет определить поля группировки записей и в дальнейшем произвести над остальными полями или результатами объединения вычисления с помощью специальных агрегатных функций. Ниже пример запроса с GROUP BY:
SELECT ba.batch_id,
ba.batch_code,
2, -- константы используются без ограничений
TO_CHAR(rec_date) AS recDate, -- функции разрешены
CASE WHEN ba.status_id = 1 THEN 'Active'
WHEN ba.status_id = 2 THEN 'In progress'
-- WHEN ba.type_id = 2 THEN 'Not works' -- ошибка, нет в GROUP BY
ELSE 'Others'
END AS status,
COUNT(*) AS amount, -- просто подсчитывает все записи
-- подсчитывает записи с непустыми значениями колонки
COUNT(bd.value_plan) AS am_not_null,
-- подсчитывает уникальные записи для VALUE_PLAN
COUNT(DISTINCT bd.value_plan) AS am_distinct
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp
WHERE ba.type_id = 1
AND ba.status_id IN (1,2,3,5)
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bd.parm_id = bp.parm_id
AND bp.parm_code IN ('O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
GROUP BY ba.batch_id,
ba.batch_code,
ba.rec_date, -- повторение выражения не требуется, только поля в выражении
ba.status_id -- CASE не нужен
Ниже показаны результаты выполнения этого запроса:
Обратите внимание, использование GROUP BY не предполагает сортировки записей по умолчанию, в порядке их группировки.
Ключевое слово HAVING.
Применяется в сочетании с GROUP BY. Позволяет отфильтровать результаты группировки. После него допускается использование агрегатных функций и операции с полями, перечисленными после GROUP BY.
SELECT ba.batch_id,
COUNT(*) AS amount,
COUNT(bd.value_plan) AS amount_not_null
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp
WHERE ba.type_id = 1
AND ba.status_id IN (1,2,3,5)
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bd.parm_id = bp.parm_id
AND bp.parm_code IN ('O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
GROUP BY ba.batch_id
HAVING MIN(bd.value_plan) = '0'
AND ba.batch_id < 222500
-- AND ba.batch_code = '00' -- ошибка, поля нет после GROUP BY
Подзапросы.
Выше был приведен пример использования подзапроса в качестве поля в конструкции SELECT.
Подзапросы в конструкции FROM.
Вот пример использования подзапроса после FROM:
SELECT ba.batch_id,
ba.rec_date,
TO_CHAR(ba.rec_date) AS recDate,
bp.parm_code,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba,
(SELECT bp1.parm_id,
bp1.parm_code,
bp1.parm_name
FROM sigma.batch_parm bp1 -- не видит соседних таблиц во FROM, ("bd" или "ba")
WHERE bp1.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
-- AND ba.batch_id = 1 -- можно раскомментировать и проверить
) bp
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bd.parm_id = bp.parm_id
Подзапрос bp используется в запросе, как обычная таблица. Недостатком такой конструкции является то, что в подзапросе не видны другие таблицы и подзапросы, которые стоят после ключевого слова FROM.
Подзапросы в конструкции WHERE (IN / NOT IN).
В конструкции WHERE подзапросы могут использоваться с ключевыми словами IN или NOT IN, а также с операторами = или <>. Последние два оператора использовать не рекомендуется, так как для них существует ограничение подзапроса, он обязательно должен возвращать не более однго значения, что может приводить к ошибкам. Вот пример предпочтительного использования подзапроса:
SELECT ba.batch_id,
ba.rec_date,
TO_CHAR(ba.rec_date) AS recDate,
bp1.parm_code,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp1
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bp1.parm_id = bd.parm_id
AND bd.parm_id NOT IN (
SELECT bp.parm_id
FROM sigma.batch_parm bp
WHERE bp.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S')
)
В отличие от подзапросов в конструкции SELECT, которые должны возвращать строго одно поле (значение), подзапросы в WHERE могут возвращать несколько:
SELECT ba.batch_id,
ba.rec_date,
TO_CHAR(ba.rec_date) AS recDate,
bp1.parm_code,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba, sigma.batch_parm bp1
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bp1.parm_id = bd.parm_id
AND bd.batch_id = ba.batch_id
AND (bd.parm_id, bd.value_plan) IN ( -- анализ пары полей
SELECT bp.parm_id,
'0' AS value_plan -- псевдоколонка для примера
FROM sigma.batch_parm bp
WHERE bp.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
AND ba.batch_id = 222313 -- видит таблицы из FROM
)
Лучше всего такие подзапросы использовать с ключевым словом IN, в случае с NOT IN такие подзапросы практически бесполезны, потому что проверяется несовпадение всех полей, что аналогично конструкции OR.
Подзапросы в конструкции WHERE видят таблицы и подзапросы после ключевого слова FROM.
Подзапросы в конструкции WHERE (EXISTS / NOT EXISTS).
Иногда возникает необходимость включить или исключить часть записей из результатов основого запроса на основании подзапроса, когда для принятия решения достаточно факта наличия или отсутствия записей в подзапросе. В таком случае бывает полезна конструкция EXISTS / NOT EXISTS:
SELECT ba.batch_id,
ba.rec_date,
bd.parm_id,
TO_CHAR(ba.rec_date) AS recDate,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND NOT EXISTS (
SELECT 'тут может быть что попало'
FROM sigma.batch_parm bp1
WHERE bp1.parm_id = bd.parm_id
AND bp1.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
)
Фильтрация результатов в конструкции WHERE.
Группировка AND и OR.
Oracle позволяет группировать условия отбора с помощью скобок, изолируя тем самым части выражений друг от друга. Это позволяет легко оформлять запросы со сложной логикой не опасаясь смешения результатов действия ключевых слов AND и OR:
SELECT ba.batch_id,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND ( bd.value_plan = '0'
OR bd.value_plan IS NULL
)
BETWEEN.
Данное ключевое слово может быть иногда полезно для более наглядного оформления запроса:
SELECT ba.batch_id,
ba.rec_date,
ba.status_id
FROM sigma.batch ba
WHERE ba.type_id = 1
-- (ba.status_id >= 1 AND ba.status_id <= 3) --
AND ba.status_id BETWEEN 1 AND 3
AND ba.batch_code IS NOT NULL
ORDER BY ba.batch_id
Оно действует как пара операторов >= и <=.
Оператор LIKE.
Используется для работы с текстовыми полями и позволяет проанализировать содержимое части поля, согласно указанной маске сравнения:
SELECT cd.client_id,
cd.last_name,
cd.street_name
FROM sigma.client_address cd
-- WHERE cd.last_name = 'ПЕТРОВ'
-- все, что начинается на "ПЕТРОВ" --
-- WHERE cd.last_name LIKE 'ПЕТРОВ%' AND cd.last_name <> 'ПЕТРОВ'
-- заканчивается на "ТРОВ" --
-- WHERE cd.last_name LIKE '%ТРОВ' AND cd.last_name <> 'ПЕТРОВ'
-- заканчивается с "ТРОВ" в любом месте фамилии --
-- WHERE cd.last_name LIKE '%ТРОВ%'
-- заканчивается на "ТРОВ" и любой последней буквой --
-- WHERE cd.last_name LIKE '%ТРОВ_'
-- определение служебного символа как части поиска --
-- WHERE cd.street_name LIKE '%\_%' ESCAPE '\'
-- кавычка внутри
WHERE cd.street_name LIKE '%''%'
В примере приведены возможные варианты использования LIKE.
Временные таблицы WITH.
Конструкция WITH может описывать набор временных таблиц, которые могут затем использоваться в основном запросе, ниже пример простого использования временной таблицы:
WITH bp AS (
SELECT bp1.parm_id,
bp1.parm_code,
bp1.parm_name
FROM sigma.batch_parm bp1 -- не видит таблиц во FROM, например "bd" или "ba"
WHERE bp1.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
)
SELECT ba.batch_id,
ba.rec_date,
TO_CHAR(ba.rec_date) AS recDate,
bp.parm_code,
bd.value_plan
FROM sigma.batch_data bd, sigma.batch ba,
bp -- обращение к алиасу временной таблицы
WHERE ba.type_id = 1
AND ba.batch_code IS NOT NULL
AND bd.batch_id = ba.batch_id
AND bd.parm_id = bp.parm_id
Каждой временной таблице (это обычный запрос) присваивается алиас, в дельнейщем к нему можно обращаться в основном запросе.
Временных таблиц может быть несколько и они располагаются по порядку через запятую:
WITH bp AS ( -- первая таблица
SELECT bp1.parm_id,
bp1.parm_code,
bp1.parm_name
FROM sigma.batch_parm bp1 -- не видит соседних таблиц во FROM, например "bd" или "ba"
WHERE bp1.parm_code IN (
'O_VIRTUAL', 'O_GOFRO_AMOUNT', 'O_PALLET_W',
'O_TIME', 'O_PROC_LOAD', 'O_PALLET_S'
)
), bd AS ( -- вторая таблица
SELECT bd1.batch_id,
bd1.parm_id,
bd1.value_plan,
bd1.value_fact
FROM sigma.batch_data bd1, bp -- видит первую таблицу
WHERE bd1.parm_id = bp.parm_id
)
SELECT ba.batch_id,
ba.rec_date,
TO_CHAR(ba.rec_date) AS recDate,
bp.parm_code,
bd.value_plan
FROM sigma.batch ba, bp, bd
WHERE ba.batch_id = bd.batch_id
AND bd.parm_id = bp.parm_id
Каждая временная таблица видит другие таблицы, расположенные выше. Такая конструкция удобна, если ваш запрос очень сложен, в таком случае его можно разбить на части.
Аналитические функции.
Аналитические функции похожи на агрегатные и многие из них имеют аналоги у агрегатных функций. Отличие заключается в том, что аналитические функции применяются к результатам запроса и вычисляются для каждой записи.
Аналитических функций очень много, их описание находится в руководстве по SQL Oracle Database.
При работе функции, как правило, выделяется окно (PARTITION) к группе записей которого применяется функция. В рамках этого окна может быть указана сортировка записей (отличная от основной сортировки не запрещается), если она необходима для вычисления значений функции. Ниже приводятся несколько примеров использования аналитических функций.
LAG.
Вычисляет значение полей предыдущей записи на основе определенной в функции сортировки:
SELECT gd.goods_id,
gd.goods_price,
LAG ( gd.goods_price,
1, -- offset
0 -- NULL -- default
) OVER /* ключевое слово*/
(ORDER BY gd.goods_id /* Порядок сортировки записей */ ) AS prev_id,
gd.goods_price -
LAG ( gd.goods_price,
1, -- offset
0 -- NULL -- default
) OVER /* ключевое слово*/
(ORDER BY gd.goods_id /* Порядок сортировки записей */ ) AS diff,
gd.goods_code,
gd.goods_name
FROM sigma.goods gd
WHERE gd.goods_id > 0
ORDER BY gd.goods_id
ROW_NUMBER, MIN, MAX.
ROW_NUMBER определяет номер записи в окне выборки на основе указанной в функции сортировки, MIN и MAX действуют в окне выборки, как обычные соответствующие им агрегатные функции:
SELECT dc.type_id,
ROW_NUMBER() -- порядковый номер записи в диапазоне
OVER (PARTITION BY dc.type_id -- диапазон обрабатываемых записей (окно)
ORDER BY dc.id /*DESC*/) AS row_num,
MIN(dc.id) -- Минимальное значение в диапазоне
OVER (PARTITION BY dc.type_id) AS min_id,
MAX(dc.id) -- Максимальное значение в диапазоне
OVER (PARTITION BY dc.type_id) AS max_id,
dc.id,
dc.val
FROM sigma.dicts dc
ORDER BY dc.type_id,
dc.id -- DESC