Oracle, типичные задачи SQL. Гарантированный выбор

[whitepen]

Увлечение трех-этажными конструкциями oracle, равно как и увлечение oracle формами имеет разумные пределы. Чаще просто пишем на plsql простой if и все. Если конструкция слишком сложна — от нее следует отказаться вовсе.

[romy4]

Это совет никогда не пользоваться сложными конструкциями?

[whitepen]

Что одному сложно, то другому просто. Каждый сам решает. Революционеры говорят, что мы вообще не будет структуризировать данные в базе, а будем кидать в кучу входящие сообщения — дата, источник, текстовое поле. Тупизна побеждает разум, берет не уменьем, а числом процессоров. А данные в куче на C++ обрабатываются на порядки быстрее, чем в оракле. Десяток мелких таблиц каждый с каждым дают астрономические итерации, а в куче вообще в оперативную память помещаются.

[romy4]

Не могу вам плюсик поставить, но совершенно согласен!

[Alhymik]

В эту минуту где-то заплакал один маленький Том Кайт.
В Oracle для профессионалов на эту тему расписано, что к чему.

При разработке приложений баз данных я использую очень простую мантру:

если можно, сделай это с помощью одного оператора SQL;
если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;
если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать...

Том Кайт авторитет, но отчасти я с Вами согласен. Иногда приходится грешить plsql-ем в угоду удобочитаемости.

[whitepen]

Один оператор — одна транзакция, как хорошо. Транзакция — дело темное, изучить ее невозможно, а предвидеть ваще нельзя. Вот напишет оракл новую версию, вот достанет тестовые скрипты от 1975 — вот и оттестировали во всех режимах. Как оно работает — никто не знает.

Мы люди простые, у нас вечером база копируется в «заднюю», а на задней жужжит аналитика без транзакций и изменения базы. Дешево и сердито.

[onehell]

Кто может подсказать реальные business cases для использования такой логики?

[romy4]

Мне кажется, Полковника Кота должен назначать back-end, а не БД. Исключительными ситуациями занимается бизнес-логика. И если б.л. перенесена в базу, то это всё равно должно обрабатываться отдельной процедурой, а не в запросе на выборку из 10000.

[Alhymik]

На счет вынесения с отдельную процедуру — ссылка на Кайта выше.

[romy4]

Я об «ответственности» за то, что основной запрос выдал пустой результат, а Кайт о том, что не надо усложнять себе жизнь.

[Alhymik]

Как-то надо было реализовать отправку платежей от посредника (процессинговой конторы) сторонней организации, скажем мобильному провайдеру. В платежах (упрощенно) было два поля — номер телефона и поступившая на него сумма.
Отправляемых платежей могло быть сотни тысяч, а могло и не быть вовсе (временное прекращение обслуживания и т.п.).
Если платежей не было, то в отправляемом файле должна была быть хоть одна строка. Например с теми же двумя полями; «Платежей нет», 0. На стороне провайдера ПО обрабатывало такие ситуации. Смысл в том, что таким образом провайдер убеждался, что платежей действительно не было. Ведь если бы от процессинговой конторы приходил просто пустой файл — это могла бы быть ошибка его ПО, или потеря данных в ходе отправки.

[stalkerg]

А не проще ли такое сделать через оконные функции? (не знаю про Oracle в Postgres они есть)

[Alhymik]

Не проще.

[CertainMan]

Решения на PostgreSQL в принципе те же, только в запросах нет возможности использовать переменные. И конструкцию с model увидел впервые только сегодня (нет, уже вчера), поэтому вряд ли в postgres есть что-то похожее.
Способы 1 и 2 работают с минимальными изменениями — :bound нужно заменить на конкретное значение, а во втором способе плюс к этому просто удалить «from dual».

Аналог способа 3:

select
  coalesce(e.id, d.id),
  coalesce(e.name, d.name),
  coalesce(e.profession, d.profession),
  coalesce(e.votes, d.votes)
from (values (0, 'Полковник', 'кот', null::integer)) d (id, name, profession, votes)
left join election e on e.votes > 10000
order by e.votes desc;

или для удобочитаемости можно вынести виртуальную таблицу d из подзапроса в with:

with d (id, name, profession, votes) as (
  values (0, 'Полковник', 'кот', null::integer)
)
select 
  coalesce(e.id, d.id),
  coalesce(e.name, d.name),
  coalesce(e.profession, d.profession),
  coalesce(e.votes, d.votes)
from d
left join election e on e.votes > 8000
order by e.votes desc;

«values (0, 'Полковник', 'кот', null::integer)» везде можно заменить на аналогичный select, только придется явно указать тип для текстовых полей, зато имена колонкам можно задать на месте через as. Пример с with:

with d as (
  select
    0 as id,
    'Полковник'::text as name,
    'кот'::text as profession,
    null::integer as votes
)
select 
  coalesce(e.id, d.id),
  coalesce(e.name, d.name),
  coalesce(e.profession, d.profession),
  coalesce(e.votes, d.votes)
from d
left join election e on e.votes > 8000
order by e.votes desc;

[CertainMan]

Везде забыл добавить алиасы столбцов:

select
  coalesce(e.id, d.id) as id,
  coalesce(e.name, d.name) as name,
  coalesce(e.profession, d.profession) as profession,
  coalesce(e.votes, d.votes) as votes
...

[Alhymik]

Интересно, а в PostgreSQL нет аналога nvl2? Это вроде SQL-ный стандарт. Coalesce, я так понял, как и в Oracle, выбирает первое встретившееся не NULL значение — подойдет, если все поля таблицы NOT NULL. Если в таблице у Неподкупного затереть профессию, то вместо прокурора мы получим кота. И еще интересно, может знаете, есть ли в постгрессе аналог ораклового rowid — глобальный уникальный системный идентификатор строки (как в 3-м способе для таблиц без поля с уникальным айдишником)?

[CertainMan]

В PostgreSQL NVL2 нет — это расширение Oracle, как и NVL. Их и в стандарте нет. NVL-то ладно, она заменяется стандартной COALESCE, а вот NLV2, кроме громоздого CASE, по-моему, ничем не заменить. Кстати, про NVL2 до этого вообще не слышал. По тексту не догадался, как она работает, а в документации прочитал только что. Очень удобная функция, хотелось бы видеть подобную в стандарте. Или, может быть, я не вкурсе, и аналог в стандарте все-же есть. В любом случае в PostgreSQL аналога не нашел.

А с COALESCE да, про NULL в отдельных полях не подумал, надо менять на CASE.
Насчет rowid. В таблицах postgres семь штук системных столбцов, один из которых, oid, как раз аналог rowid, и до версии 8.1 oid в пользовательских таблицах создавался по умолчанию. Но так как это 32-битное целое, в больших и долгоживущих бд он уже не гарантирует уникальность, поэтому с версии 8.1 oid-ы есть только в системных таблицах, а в пользовательских по умолчанию не создаются. Хотя можно при создании таблицы указать WITH OIDS или вообще вернуть oid-ы по умолчанию в настройках сервера.
Но для проверки существования строки можно взять и другой системный столбец, например tableoid:

select 
  case when e.tableoid is null then d.id else e.id end as id,
  case when e.tableoid is null then d.name else e.name end as name,
  case when e.tableoid is null then d.profession else e.profession end as profession,
  case when e.tableoid is null then d.votes else e.votes end as votes
...

Только теперь стало громоздко и лично я выбирал бы первый вариант :)

[CertainMan]

Экзотический вариант с применением JSON для PostgreSQL, начиная с 9.3:

select j.*
from (select '{"id":0,"name":"Полковник","profession":"кот","votes":null}'::json as j) d
left join election e on e.votes > 10000,
json_populate_record(null::election, case when e.tableoid is null then d.j else row_to_json(e.*) end) j
order by e.votes desc;

[CertainMan]

Пояснения к решению.
Идея в том, чтобы сначала свернуть запись в один столбец, к которыму можно применить CASE только один раз, а после получения нужной записи развернуть её обратно по столбцам. Функция row_to_json сворачивает запись, функция json_populate_record возвращает значение композитного типа, а если функция, возвращающая композитный тип, вызвана в секции FROM, то postgres разворачивает результат по отдельным столбцам. Но так получается только с функциями. Если обернуть функцию в подзапрос, то есть, вместо json_populate_record(...) написать LATERAL (SELECT json_populate_record(...)), запись не развернётся, а будет выведена как композитный столбец.

Запись можно было свернуть в столбец и без привлечения json, например, «SELECT ROW(election.*) AS e_record FROM election» выведет записи из election в один композитный столбец e_record. То есть, для того, чтобы свернуть запись можно использовать выражение ROW на месте функции row_to_json. Но вот функции, которая просто возвращала бы запись и которую можно было бы вызвать в секции FROM, чтобы запись развернулась, я не нашел.

Не уверен, что изложил понятно. Сам, наверное, не понял бы, если бы не был в теме :)

[CertainMan]

Теперь без json.
Дошло, что для разворачивания записи прекрасно подойдет та же coalesce — она может принять аргумент любого типа (в данном случае типа record) и вернуть его без изменений, а это именно то, что нужно:

select r.*
from (values (0, 'Полковник', 'кот', null::integer)) d
left join election e
  on e.votes > 10000,
coalesce(case when e.tableoid is null
           then row(d.*)
           else row(e.*)
         end) r (id integer, name text, profession text, votes integer)
order by e.votes desc;

[Alhymik]

CertainMan, возможно придется работать с постгресом, все это интересно, наверняка пригодится, спасибо.