Парсим XML средствами Oracle

Думаю можно без комментариев.


Запрос:


WITH
  q AS (SELECT xmltype('
    <xml>
      <head>
        <valueA>A</valueA>
        <valueB>B</valueB>
      </head>
      <repeat1>
        <valueC>C one</valueC>
        <valueD>D one</valueD>
      </repeat1>
      <repeat1>
        <valueC>C two</valueC>
        <valueD>D two</valueD>
      </repeat1>
      <repeat1>
        <valueC>C three</valueC>
        <valueD>D three</valueD>
      </repeat1>
    </xml>
  ') XML FROM dual)
SELECT extractvalue(XML, '//xml/head/valueA') valueA
     , extractvalue(XML, '//xml/head/valueB') valueB
     , repeat1.valueC, repeat1.valueD
  FROM q
     , XMLTable('for $i in //xml/repeat1 return $i'
                 PASSING q.XML
                 COLUMNS valueC VARCHAR2(250) path '//repeat1/valueC'
                       , valueD VARCHAR2(250) path '//repeat1/valueD'
                ) repeat1

Результат:

Список значений в набор строк

Необычная задача - преобразовать список значений разделенных запятыми в набор строк.
Имеем некую таблицу lists, в поле code которой имеем некоторые списки значений через запятую (111,222,333 и т.п.). В результате следующего запроса получаем выборку уникальных значений всех списков из таблицы lists, каждое находится в отдельной строке.

select distinct code
  from
    (select regexp_substr(q.code, '[^,]+', 1, rownum) code
     from dual,
        (select length(regexp_replace(wm_concat(l.code), '[^,]'))+1 l_cnt
           ,wm_concat(l.code) code
      from lists l
       ) q
     connect by rownum <= q.l_cnt
    )
where code is not null;

P.S. используем конструкцию вида select rownum from dual connect by rownum <= :counter для генерации нужного нам числа строк.

Загадочные конструкции в коде

Часто занимаюсь отладкой и чтением чужого кода. Иногда попадаются странные куски. Буду наверное публиковать, а то потом забывается.
- nvl(vStr, '') - между кавычками пробела нет!
- select ... from... where nvl(vNum, 1) = 0 - чем не понравилось сравнивать сразу с 0м непонятно, зато усложнили работу оптимизатора в борьбе с null-ами
- if trunc(abs(vSum)*100)/100 != 0 then - долго ломал голову, пока Amd не подсказал. Тут замаскировано: abs(vSum) < 0.01

Задачка по ораклу с датами

/* задан период между датами. надо получить курсор с датами с разбивкой по месяцам
Пример: дано 13.10.2009 - 17.12.2009
Получаем:
13.10.2009-31.10.2009
01.11.2009-30.11.2009
01.12.2009-17.12.2009
*/

select greatest(add_months(trunc(dat1,'MONTH'), cnt), dat1) as rd1,
       least(add_months(trunc(dat1,'MONTH'), cnt+1)-1, dat2) as rd2
  from (
    select to_date('13.10.2009','DD.MM.YYYY') as dat1, to_date('17.12.2009','DD.MM.YYYY') as dat2 from dual),
      (select rownum-1 cnt from 
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual),
          (select 1 from dual union all select 1 from dual)
        where rownum<=(SELECT trunc(MONTHS_BETWEEN(trunc(to_date('17.12.2009','DD.MM.YYYY'),'MONTH'), 
                                                   trunc(to_date('13.10.2009','DD.MM.YYYY'),'MONTH')))+1 FROM dual));