Для замены N-ного символа в строке, можно обойтись и без SUBSTR — использовать REGEXP_REPLACE.
select regexp_replace('208010100000','.','X', 5, 1) as res from dual
union all
select regexp_replace('208010100000','.','X', 2, 1) as res from dual;
RES
------------
2080X0100000
2X8010100000
LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
Пример агрегатной функции
SQL> select deptno, LISTAGG(t.ename, ', ') WITHIN GROUP (order by t.empno) as ename_list
2 from scott.emp t
3 group by deptno;
DEPTNO ENAME_LIST
------ --------------------------------------------------------------------------------
10 CLARK, KING, MILLER
20 SMITH, JONES, SCOTT, ADAMS, FORD, SMIT2
30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES
Пример аналитической функции
SQL> select deptno,
2 ename,
3 job,
4 hiredate, listagg(t.ename, ', ')
6 WITHIN GROUP(order by t.empno) over(partition by deptno) as ename_list
7 from scott.emp t
8 where hiredate > to_date('01-01-1982','dd-mm-yyyy');
DEPTNO ENAME JOB HIREDATE ENAME_LIST
------ ---------- --------- ----------- -----------------------
10 MILLER CLERK 23.01.1982 MILLER
20 SCOTT ANALYST 09.12.1982 SCOTT, ADAMS, SMIT2
20 ADAMS CLERK 12.01.1983 SCOTT, ADAMS, SMIT2
20 SMIT2 CLERK 25.07.2011 SCOTT, ADAMS, SMIT2
Oracle Database SQL Language Reference 11g Release 2 : LISTAGG
Рассмотрим функции для работы с nested tables и varrays (11G)
Сделаем nested tables для примеров:
create or replace type test_number_table as table of number(20);
CARDINALITY
CARDINALITY возвращает число элеметов в nested table. Тип возращаемого значения NUMBER.
Если вложенная таблица пустая или is null, тогда CARDINALITY вернет NULL.
SQL> select CARDINALITY(test_number_table(1,2,3,4,5,1,2)) as t_cardinality from dual;
T_CARDINALITY
-------------
7
Запрос для вывода информации о существующих проектах Oracle Projects.
Используется таблица PA_PROJECTS_ALL.
select ppa.project_id as project_id -- ID проекта
, ppa.name as project_name -- Имя проекта
, ppa.segment1 as project_num -- Номер проекта
, ppa.project_type as project_type -- Тип проекта
, pps.project_status_name as project_status -- Статус проекта
, org.organization_id as org_id -- ID организации
, org.name as org_name -- Имя организации
, oper.organization_id as oper_id -- ID операционной единицы
, oper.name as oper_name -- Имя операционной единицы
, loc.city as loc_city -- Город, в котором ведется проект
--
from pa_projects_all ppa -- список проектов
, hr_all_organization_units org -- организация, которой принадлежит проект
, hr_operating_units oper -- операционная единица
, pa_locations loc -- расположение, где находится территориально
, pa_project_statuses pps -- статус проекта
where 1=1
-- org
and org.organization_id = ppa.carrying_out_organization_id
-- oper
and ppa.org_id = oper.organization_id
-- loc
and loc.location_id = ppa.location_id
-- pps
and pps.project_status_code = ppa.project_status_code
and pps.status_type = 'PROJECT'
and sysdate between pps.start_date_active and nvl(pps.end_date_active,sysdate+1);
Запрос для вывода списка операционных единиц, используется view hr_operating_units.
select business_group_id,
organization_id,
name,
date_from,
date_to,
short_code,
set_of_books_id,
default_legal_context_id,
usable_flag
from hr_operating_units;
Если зарегистрировать view в OEBS, указав следующие столбцы
, то при использовании данной view в наборе значений автоматически будут отсекаться устаревшие данные.
Для регистрации view в OEBS, используем следующий скрипт.
Читать дальше про “Регистрация view в OEBS” »
Если, при кастомизации стандартной формы OEBS, возникает необходимость сохранить функциональность аттачментов, то реализовать это поможет следующий скрипт:
Поиск значения по всем таблицам
Вариант1:
DECLARE
match_count INTEGER;
query_str VARCHAR2(400);
l_owner VARCHAR2(10) := 'SCOTT';
l_search_text VARCHAR2(100) := '10';
BEGIN
FOR t IN (select table_name, column_name FROM all_tab_columns a
where owner = l_owner
and a.data_type in ('NUMBER', 'VARCHAR2')
)
LOOP
begin
match_count := 0;
query_str := 'SELECT COUNT(*) FROM ' ||l_owner||'.'|| t.table_name ||
' WHERE to_char(' || t.column_name || ') = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_search_text;
IF match_count > 0 THEN
dbms_output.put_line(l_owner||'.'||t.table_name || ' ' || t.column_name || ' ' || match_count);
dbms_output.put_line(' SELECT t.'|| t.column_name || ', t.* from '||l_owner||'.'|| t.table_name||' t where to_char(' || t.column_name || ') = '''||l_search_text||'''');
dbms_output.put_line('');
END IF;
exception
when others then
dbms_output.put_line(t.table_name||'.'||t.column_name||' : '||SQLERRM||' : '||SQLCODE);
dbms_output.put_line(query_str);
dbms_output.put_line('');
/*raise*/
end;
END LOOP;
END;
Вариант2:
--SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE tab_columns_rec_type IS RECORD (
table_name all_tab_columns.table_name%type
,column_name all_tab_columns.column_name%type
,data_type all_tab_columns.data_type%type
);
type tab_columns_tab_type is table of tab_columns_rec_type index by binary_integer;
l_tab_columns_rec tab_columns_tab_type;
match_count INTEGER;
query_str VARCHAR2(400);
l_owner VARCHAR2(10) := 'SCOTT';
l_search_text VARCHAR2(100) := '10'; -- % добавляем здесь, если ищем не по точному вхождению
l_number NUMBER;
is_number NUMBER(1);
l_str_length NUMBER;
/*
l_mode:
1 - NUMBER и VARCHAR2
2 - only NUMBER
3 - only VARCHAR2
*/
l_mode NUMBER := 1;
-- При каком количестве найденные совпадений
-- прекращать поиск
l_found_count NUMBER := 1;
l_exists_count NUMBER;
BEGIN
l_str_length := length(l_search_text);
begin
l_number := to_number(l_search_text);
is_number := 1;
exception
when VALUE_ERROR then
is_number := 0;
end;
if (l_mode = 1 and is_number = 1) then
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and ( a.data_type = 'VARCHAR2' or a.data_type = 'NUMBER' );
elsif (l_mode = 2 and is_number = 1) then
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and a.data_type = 'NUMBER';
else
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and a.data_type = 'VARCHAR2';
end if;
l_exists_count := 0;
if NOT (l_tab_columns_rec is null or l_tab_columns_rec.count = 0) then
for j in l_tab_columns_rec.first..l_tab_columns_rec.last
LOOP
begin
match_count := 0;
if (l_tab_columns_rec(j).data_type = 'VARCHAR2') then
query_str := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
' WHERE rownum = 1 and to_char(' || l_tab_columns_rec(j).column_name || ') = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_search_text;
elsif (l_tab_columns_rec(j).data_type = 'NUMBER') then
query_str := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
' WHERE rownum = 1 and ' || l_tab_columns_rec(j).column_name || ' = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_number;
end if;
IF match_count > 0 THEN
l_exists_count := l_exists_count + 1;
dbms_output.put_line(l_tab_columns_rec(j).data_type||' '||l_owner||'.'||l_tab_columns_rec(j).table_name || ' ' || l_tab_columns_rec(j).column_name );
dbms_output.put_line(' SELECT t.'|| l_tab_columns_rec(j).column_name || ', t.* from '||l_owner||'.'|| l_tab_columns_rec(j).table_name||' t where to_char(' || l_tab_columns_rec(j).column_name || ') like '''||l_search_text||'''');
dbms_output.put_line('');
if (l_exists_count = l_found_count) then
exit;
end if;
END IF;
exception
when others then
dbms_output.put_line(l_tab_columns_rec(j).table_name||'.'||l_tab_columns_rec(j).column_name||' : '||SQLERRM||' : '||SQLCODE);
dbms_output.put_line(query_str);
dbms_output.put_line('');
/*raise*/
end;
END LOOP;
end if;
END;
Квантификация — поиск последовательностей;
Определяет, сколько раз предшествующее выражение может встречаться.
Квантификатор может относиться более чем к одному символу в регулярном выражении, только если это символьный класс или группа.
Таблица 2: Квантификаторы и операторы повтора
| Квантификатор | Описание | Пример |
| * | Встречается 0 и более раз | REGEXP_REPLACE(str, ’11*’, ‘1’) Результат: test11 => test1 11123345 => 123345 |
| ? | Встречается 0 или 1 раз | |
| + | Встречается 1 и более раз | REGEXP_LIKE(str,’5+’) Результат: test11 => false 11123345 => true |
| {m} | Встречается ровно m раз | REGEXP_LIKE(str,’3{2}’) Результат: test11 => false 11123345 => true |
| {m,} | Встречается по крайней мере m раз | |
| {m, n} | Встречается по крайней мере m раз, но не более n раз |
Жадная квантификация — это стремление захватить максимально длинную строку, которая соответствует шаблону.
SQL> with t as ( 2 select '<div><img src="http://apps-oracle.ru/logo2.png" alt=""/></div>' str from dual 3 ) 4 select regexp_substr(str,'<.*>') as res from t; RES -------------------------------------------------------------- <div><img src="http://apps-oracle.ru/logo2.png" alt=""/></div>
Ленивая квантификация — это стремление захватить максимально короткую строку, которая соответствует шаблону. Поддерживается с версии 10gR2.
SQL> with t as ( 2 select '<div><img src="http://apps-oracle.ru/logo2.png" alt=""/></div>' str from dual 3 ) 4 select regexp_substr(str,'<.*?>') as res from t; RES ----- <div>
| Жадный | Ленивый |
| * | *? |
| + | +? |
| {n,} | {n,}? |
Получаем список персонализаций на форме OEBS.
SELECT form_name form,
function_name Function,
description description,
sequence seq,
trigger_event triggerevent,
trigger_object triggerobject,
condition condition,
enabled
FROM fnd_form_custom_rules
ORDER BY form_name, function_name, sequence;
Последние комментарии