mirall: (bubble)
set sqlblanklines on

В смысле, игнорируем пустые строки.
mirall: (job)
PostgreSQL умеет забавную штуку: одним запросом изменять данные сразу двух таблиц. Помимо побочных эффектов, это сильно нагляднее, чем триггер, про который легко забыть.

Официальная документация на этот счёт даёт более чем исчерпывающие пояснения, поэтому подробно расписывать смысла нет. Но пример оттуда приведу, ибо выглядит весьма странно и с непривычки выносит мозг.
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;


Работает для insert, update и delete в блоке with и то же самое плюс select во внешнем блоке. Ограничение, как обычно, есть: изменять одну и ту же строку технически возможно, но, по факту, приводит к непредсказуемым последствиям.
mirall: (job)
Давно собиралась нарисовать запросик. Основная цель - организация каскадного удаления (или свойством on delete cascade внешнего ключа, или последовательностью опрераторов delete, - по желанию).
with refs as (
select uc.constraint_name
     , ucc1.table_name
     , ucc1.column_name
     , ucc2.table_name ref_tab
     , ucc2.column_name ref_col
     , uc.delete_rule
  from user_constraints uc
  join user_cons_columns ucc1 on uc.constraint_name = ucc1.constraint_name
  join user_cons_columns ucc2 on uc.r_constraint_name = ucc2.constraint_name and ucc1.position = ucc2.position
 where uc.constraint_type = 'R'
  order by ucc1.table_name
         , uc.constraint_name
)
select lpad (constraint_name, length(constraint_name) + (level-1)*4, ' '), table_name, column_name, ref_tab, ref_col, level, delete_rule
  from refs
  start with table_name = 'SOME_TBL'
  connect by nocycle ref_tab = prior table_name;

OVERLAPS

Feb. 17th, 2012 01:41 pm
mirall: (job)
Оказывается, в SQL есть оператор OVERLAPS. И это прекрасно.

Правда, это всего лишь частично прекрасно. Ибо в Oracle эта возможность является недокументированной. Тем не менее, она есть.
SQL> select 1 from dual
SQL>  where (sysdate - 1, sysdate + 1)
SQL>        overlaps
SQL>        (sysdate - 2, sysdate + 2);

         1
----------
         1
Зато в PostgreSQL оно существует вполне себе официально. И это ещё прекраснее.
# select ('-infinity', current_timestamp + interval '10 days')
         overlaps
         (current_timestamp - interval '1 days', current_timestamp + interval '1 days');
 overlaps
----------
 t
(1 row)
Кстати, PostgreSQL ещё знает, что такое бесконечность.
mirall: (job)
Дано.
Лес (т.е. набор деревьев) простого алгоритма представлен в виде двух таблиц.

       Table "algorithm_node"
    Column    |       Type        | Modifiers
--------------+-------------------+-----------
 node_id      | ext_id_udt        | not null
 algorithm_id | ext_id_udt        | not null
 node_type    | character varying | not null
Indexes:
    "algorithm_node_pk" PRIMARY KEY, btree (node_id, algorithm_id)

    Table "algorithm_node_link"
       Column        |    Type    | Modifiers
---------------------+------------+-----------
 algorithm_id        | ext_id_udt | not null
 source_node_id      | ext_id_udt | not null
 destination_node_id | ext_id_udt | not null
Indexes:
    "algorithm_node_link_pk" PRIMARY KEY, btree (algorithm_id, source_node_id, destination_node_id)
Foreign-key constraints:
    "alg_node_link_dst__alg_node_fk" FOREIGN KEY (destination_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)
    "alg_node_link_src__alg_node_fk" FOREIGN KEY (source_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)

По алгоритму задаётся некая функциональность. При этом любые ноды алгоритма могут быть в функциональности пропущены.

                   Table "program_node"
         Column         |           Type           | Modifiers
------------------------+--------------------------+-----------
 program_node_id        | id_udt                   | not null
 program_id             | id_udt                   | not null
 algorithm_node_id      | ext_id_udt               | not null
 algorithm_id           | ext_id_udt               | not null
 state                  | ref_id_udt               | not null
Indexes:
    "program_node_pk" PRIMARY KEY, btree (program_node_id)
Foreign-key constraints:
    "prg_node__alg_node_fk" FOREIGN KEY (algorithm_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)

Задача состояла в том, чтобы вернуть корни функциональности. Получилось такое безобразие.

with recursive rec (node_id, parent_id, program_node_id, nx, program_id) as (
  select lb.node_id, lb.parent_id, lb.program_node_id, lb.n nx, lb.program_id program_id
    from link lb
    where lb.parent_id is null
  union
  select l.node_id, l.parent_id, l.program_node_id, rec.nx + l.n, l.program_id
    from link l join rec on (rec.node_id = l.parent_id) 
),
link as (
  select an.node_id node_id, aln.source_node_id parent_id, pnd.program_node_id
       , case when pnd.program_node_id is null then 0 else 1 end n, pnd.program_id
    from algorithm.algorithm_node an
    left join algorithm.algorithm_node_link aln on aln.destination_node_id = an.node_id
    left join task.program_node pnd on an.node_id = pnd.algorithm_node_id
                                and pnd.program_id = 'ALG1' and pnd.state = 0
    where an.algorithm_id = rt_prg.algorithm_id
)
select program_node_id from rec r
  where nx = 1
    and program_node_id is not null

В целом, ничего выдающегося. За исключением того, что я в равной степени плохо разбираюсь в рекурсивных запросах и в PostgreSQL. Список личных мелких открытий:
1) если хочется в одном запросе использовать обычный и рекурсивный WITH, то сначала пишется рекурсивный, а потом только обычный;
2) PostgreSQL поддерживает аналитические функции (здесь не используется, но как-то мимоходом выяснилось);
3) придумывать рекурсивные запросы - это не просто долго, это очень долго, по крайней мере, для мозга, не привыкшего мыслить рекурсивно.
mirall: (job)
    Есть такая достаточно стандартная задача: в процедуру приходит параметр, который используется в условии where. И параметр этот может принимать значение null, тогда, смотря по ситуации, нужно либо сравнить колонку с null, либо вообще не включать пришедший параметр в условие where.
    Решение в лоб — конструкция if-else. Минус: не дай бог таких параметров больше одного.
    Решение позаковыристее — построить динамический запрос. Минусы: медленно работает и не парсится во время компиляции, т.е. есть шанс влететь в ошибку выполнения.
    И последнее на сегодня решение — case в условии where.
create or replace function GetSmth(
  p_SmthID in tst_tbl.smth_id%type
) return sys_refcursor is
  cur sys_refcursor;
begin
  open cur for
    select *
      from tst_tbl dv
     where case
             when (p_SmthID is null) then 1
             when smth_id = p_SmthID then 1
             else 0
           end = 1;

  return (cur);
end GetSmth;
mirall: (job)
    Почему-то в голову не приходило.
select case 
            when exists (select 1 
                         from sales 
                         where sales_type = 'Accessories') 
            then 'Y' 
            else 'N' 
        end as rec_exists
from dual;
mirall: (job)
    Удаление дубликатов. Один из весьма распространённых вопросов на собеседованиях :)

delete from our_table
  where rowid not in (
    select min(rowid) 
      from our_table 
      group by column1, column2, column3... 
    )

    Постраничный вывод данных (по Кайту).
select * 
  from ( select /*+ FIRST_ROWS(n) */ a.*, rownum rn
    from ( 
      your_query_goes_here, 
        with order by
    ) a 
    where ROWNUM <= :MAX_ROW_TO_FETCH ) 
  where rnum  >= :MIN_ROW_TO_FETCH
mirall: (job)
    Для коллекции. Производительность не измеряла и вполне допускаю, что средствами PL/SQL выйдет быстрее. Но страшно неохота возиться с циклами.
Посему запросы под катом )
mirall: (job)
    Добрая Настя подбросила сегодня любопытненькую задачку.
    БД — MySql, отсюда странное ifnull.
    Итак, имеется таблица проектов project.
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| pr_id | int(11) | NO   | PRI | 0       |       |
| title | char(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

    Таблица типов проектов type.
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| type_id | int(11) | NO   | PRI | 0       |       |
| title   | char(2) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

    Эти две таблицы находятся в отношении многие-к-многим, которое реализовано с помощью таблицы-связки proj_type.
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| pr_id   | int(11) | YES  | MUL | NULL    |       |
| type_id | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

    Note. Таблицы — тестовые в моей локальной БД и созданы с достаточной долей условности. В данном случае нас интересуют только поля ID и их отношения между собой.

    Задача.
    Написать запрос таким образом, чтобы на выходе получилась таблица
ID проекта | ID типа | EX (1|0 — тип является типом данного проекта или нет).
    Запрос всегда строится только для одного проекта, т.е. where pr_id = my_pr_id.

    Получилось вот что.

select t.type_id, ifnull(sign(p.pr_id),0) ex, my_pr_id pr_id 
  from type t
  left join 
    (select pr_id, type_id from proj_type where pr_id = my_pr_id) p 
    on p.type_id = t.type_id;

    Забавный запросик потребовал абстрагироваться от получения всех нужных полей "в лоб": флаг сгенерировали из ID проекта, а сам ID проекта ввели в качестве константы. Плюс внесение условие where в подзапрос.
mirall: (job)
    Надо что-то делать со своими знаниями в области администрирования.

    ошибка в строке 1:
    ORA-16014: log 2 sequence# 9390 not archived, no available destinations
    ORA-00312: online log 2 thread 1: '...'

    alter database clear unarchived logfile group 1;




mirall: (job)
    Есть таблица TBL с ID, есть последовательность SEQ для генерации этого ID и триггер для вставки строк не с какими-то там левыми айдишниками, а с теми, которые генерирует последовательность.
    Требуется процедура вставки записи в таблицу, возвращающая ID. Я обычно сначала делаю INSERT INTO TBL (FIELD1, FIELD2) VALUES ('FIELD1', 'FIELD2'), а потом SELECT ID INTO vID FROM TBL WHERE FIELD1='FIELD1' AND FIELD2='FIELD2'. Необходимое условие при этом — ограничение уникальности на комбинацию (FIELD1, FIELD2). В принципе, можно сделать SELECT SEQ.CURRVAL INTO vID FROM DUAL и получить тот же самый свежесгенерированный айдишник.
    Но мне кажется, что в этом случае можно получить и некорректные данные. Насколько я знаю, последовательность щёлкает вне зависимости от того, прошла транзакция или откатилась. Если, к примеру, в этот же момент времени будет совершена попытка вставки ещё одного значения другим пользователем, то SELECT SEQ.CURRVAL может вернуть уже другое значение.

    UPD.Жить стало лучше, жить стало веселее.
    INSERT INTO TBL (FIELD1, FIELD2) VALUES (vField1, vField2) RETURNING ID INTO vID
Page generated Sep. 25th, 2017 06:10 am
Powered by Dreamwidth Studios