Просмотр количества открытых курсоров для сессий
select a.value, s.program, s.username, s.sid, s.serial#, s.client_identifier from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid = a.sid and b.name = 'opened cursors current' order by 1 desc;
Функция PERCENTILE_DISC
Функция принимает на вход процентное значение (P) и условие сортировки, возвращает элемент из набора — наименьшее значение функции CUME_DIST, которое больше или равно значению P.
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
Пример
with t as (select 1 id, 10 val from dual union all select 2 id, 20 val from dual union all select 3 id, 30 val from dual union all select 4 id, 50 val from dual union all select 5 id, 70 val from dual ) select id, val, cume_dist() over (order by val) "Cume_Dist", percentile_disc(0.50) within group (order by val) over() "percentile_disc" from t order by val; ID VAL Cume_Dist percentile_disc ---------- ---------- ---------- --------------- 1 10 0,2 30 2 20 0,4 30 3 30 0,6 30 4 50 0,8 30 5 70 1 30
Функция CUME_DIST
Аналитический вариант
CUME_DIST( ) OVER ( [ query_partition_clause ] order_by_clause )
Функция CUME_DIST рассчитывает кумулятивное распределение значения в наборе данных. Возвращаемое значение находится в диапазоне от 0 до 1. Функция на вход принимает числовой тип данных, или тип данных, который может неявно преобразовать в числовой.
Определение строк со смешанными языками (русские и английские буквы)
Вытаскиваем записи, у которых есть хотя бы одна нерусская буква
with t as ( select 1 id, 'Сидоров' s from dual union all select 2, 'Cидoров' from dual union all select 3, 'Пирогов' from dual ) select * from t t0 where soundex(t0.s) is not null; ID S ---------- ------- 2 Cидoров
Предположительные совпадения (Пирогов добавлен для показательности неточности подхода)
with t as ( select 1 id, 'Сидоров' s from dual union all select 2, 'Cидoров' from dual union all select 3, 'Пирогов' from dual ) select * from t t0, t t1 where UTL_MATCH.JARO_WINKLER_SIMILARITY(t0.s,t1.s)>=70 and soundex(t0.s) is null and soundex(t1.s) is not null and t0.id<>t1.id; ID S ID S ---------- ------- ---------- ------- 1 Сидоров 2 Cидoров 3 Пирогов 2 Cидoров
Простой translate для вытаскивания совпадающих написаний
with t as ( select 1 id, 'Сидоров' s from dual union all select 2, 'Cидoров' from dual union all select 3, 'Пирогов' from dual ) select * from t t0, t t1 where translate(t1.s,'AaBbCcEegHKkMmOoPpTuXxy','АаВьСсЕедНКкМтОоРрТиХху')=t0.s and t0.id<>t1.id; ID S ID S ---------- ------- ---------- ------- 1 Сидоров 2 Cидoров
Объединение интервалов дат
with t as ( select to_date ('01.01.2009', 'dd.mm.yyyy') beg_date, to_date ('10.01.2009', 'dd.mm.yyyy') end_date from dual union all select to_date ('03.01.2009', 'dd.mm.yyyy') beg_date, to_date ('05.01.2009', 'dd.mm.yyyy') end_date from dual union all select to_date ('10.01.2009', 'dd.mm.yyyy') beg_date, to_date ('12.01.2009', 'dd.mm.yyyy') end_date from dual union all select to_date ('13.01.2009', 'dd.mm.yyyy') beg_date, to_date ('20.01.2009', 'dd.mm.yyyy') end_date from dual union all select to_date ('01.02.2009', 'dd.mm.yyyy') beg_date, to_date ('10.02.2009', 'dd.mm.yyyy') end_date from dual ) -- select min (beg_date) as beg_date, max (end_date) as end_date from ( select beg_date, end_date, sum(strt_grp) over (order by beg_date, end_date ) grp_num from ( select beg_date, end_date, case when beg_date > 1 + max(end_date) over (order by beg_date, end_date rows between unbounded preceding and 1 preceding) then 1 end strt_grp from t ) ) group by grp_num order by beg_date; BEG_DATE END_DATE ----------- ----------- 01-01-2009 20-01-2009 01-02-2009 10-02-2009
Разбить период на месяца
with t as ( select to_date('03-05-2010','dd-mm-yyyy') d1, to_date('26-08-2010','dd-mm-yyyy') d2 from dual ) -- select decode(level,1,d1,trunc(add_months(d1,level-1),'mm')) as date_from, case when add_months(d1,level)>=d2 then d2 else last_day(add_months(d1,level-1)) end date_to from t connect by add_months(d1,level-1) < d2; DATE_FROM DATE_TO ----------- ----------- 03.05.2010 31.05.2010 01.06.2010 30.06.2010 01.07.2010 31.07.2010 01.08.2010 26.08.2010
Создать дубликаты строк
with t as ( select 'a' a, 2 n from dual union all select 'b' a, 3 n from dual union all select 'c' a, 5 n from dual union all select 'd' a, 1 n from dual union all select 'e' a, 0 n from dual ) select * from t, table(select collect(level) from dual connect by level<=t.n); A N COLUMN_VALUE - ---------- ------------ a 2 1 a 2 2 b 3 1 b 3 2 b 3 3 c 5 1 c 5 2 c 5 3 c 5 4 c 5 5 d 1 1 e 0 1 12 rows selected
При n=0, строка останется.
Запретить commit в процедуре
alter session enable commit in procedure; alter session disable commit in procedure;
При попытке выполнить commit произойдет исключение.
Select from PL/SQL table
Пример запроса из коллекции.
-- создаем глобальный тип данных, локальный нельзя использовать create or replace type xx_test_type is object ( id number, descr varchar2(20)); create or replace type xx_test_tab is table of xx_test_type;
Пример заполнения и вывода данных
declare vcollect xx_test_tab := xx_test_tab(); -- инициализация begin -- заполняем данными vcollect.extend; vcollect(vcollect.count) := xx_test_type(1,'test1'); vcollect.extend; vcollect(vcollect.count) := xx_test_type(2,'test2'); -- запрос из коллекции for i in (select * from Table(vcollect) order by id ) loop dbms_output.put_line(i.descr); end loop; end;
Значение строк в одну, через xml функции
with t as ( select 1 as id, 'a' gr from dual union all select 2 as id, 'a' gr from dual union all select 4 as id, 'b' gr from dual ) select rtrim(to_char(sys_xmlagg(xmlelement(id,id ||',')).extract('/ROWSET/ID/text()').getclobval()),',') TEXT from t group by gr; TEXT ------- 1,2 4