Есть таблица в Oracle: имя (если есть), адрес почты (обязательно); приходит длинный список на пополнение через запятую - что делать?
В таблице адресов для автоматической рассылки уже несколько сотен записей. Пришедший список на пополнение тоже не "три с половиной" записи, да через запятую, и примерно в таком формате:
ivan@some.com <ivan@some.com>, Степан Петрович Обормотов <spobor@another.com>, ...
- забивать руками через интерфейс Oracle APEX (а как иначе?) лень категорически. Что делать?
Старт стандартный: загрузим исходную информацию в пространство Oracle - ну хоть как-нибудь. Дальше уже легче.
Список здоровенный, офисный товарищ постарался на славу, в 4000 символов для varchar2 отнюдь не лезет. Что делать, создаём таблицу clob_tmp из одного столбца clob_data типа clob, куда всё махом и грузим руками. Всё. Дальше проще.
Подсчитываем количество запятых в загруженном CSV списке:
select regexp_count(clob_data, ',') from clob_tmp
;
106. Т.е. элементов в списке 107. Записываем иерархический разбор списка:
select trim(regexp_substr(clob_data, '[^,]+', 1, level)) str
from clob_tmp
connect by level <= 107 --regexp_count(clob_data, ',')+1
;
- и замечаем, что разбор, даже несмотря на замену выражения константой, оказывается весьма длительным (успели кофе налить). Понимаем, что работать с этим в рамках одного запроса будет долго и мучительно, сохраняем результаты разбора в виде таблицы:
create table mail_tmp as
select trim(regexp_substr(clob_data, '[^,]+', 1, level)) str
from clob_tmp
connect by level <= 107
;
Спокойно пьём кофе - это надолго.
Далее делим полученные строки при помощи регулярных выражений на два столбца - потенциальное имя получателя и обязательный адрес электронной почты:
select
to_char(trim(regexp_replace(str, '<.*$', ''))) as foreword
, to_char(trim(regexp_replace(regexp_substr(str, '<.*>'), '<|>', ''))) as addr
from mail_tmp
;
Проверяем, что получили нечто вменяемое, и оформляем результат в виде представления - ну правда, не в блокноте же записывать запрос! При этом проверяем: если потенциальное имя идентично адресу почты, то это никакое не имя, а ещё раз написанный адрес почты:
create view mail_tmp2_vi as
select case when foreword = addr then null else foreword end as fw, addr
from
(
select
to_char(trim(regexp_replace(str, '<.*$', ''))) as foreword
, to_char(trim(regexp_replace(regexp_substr(str, '<.*>'), '<|>', ''))) as addr
from mail_tmp
)
;
Всё. У нас есть представление, содержащее два столбца: fw - потенциальное имя получателя и addr - адрес его электронной почты. Это всё мы проделывали в схеме test. Даем из неё смысловой схеме grant select на только что созданное представление и переходим в смысловую схему. В ней главный справочник адресов, который нам надо пополнить, называется address_list_tab и имеет главные поля email, name. Проверим, что адреса в новом списке не повторяются, что их ещё нет в главном справочнике, и запишем запрос, генерирующий insert скрипт для пополнения главного справочника из только что разобранного списка. Результаты оформим представлением - ну не в блокноте же записывать запрос!
create view add_addr_script_vi as
select
'insert into address_list_tab (email, name) values ('''||addr||''', '''||fw||''');' as scr
from
(
select t.*, count(*) over (partition by t.addr) as cou
from test.mail_tmp2_vi t
where not exists (select 1 from address_list_tab where email = t.addr)
)
where cou = 1
;
Всё, задача решена. Далее нам остаётся только выполнить
select * from add_addr_script_vi
;
- и мы получаем готовый скрипт для внесения адресов - и более-менее валидных имён - в главный справочник. Без повторов.
