https://pastein.ru/t/2ag
Загрузка данных
create or replace package body umdap_cdu_coal_ton_mining_cost_pkg as
-- ----------------------------------------------------------------------
SHEET_ID pls_integer := 1;
BULK_LIMIT pls_integer := 1000;
DFMT varchar2(32) := 'dd.mm.yyyy hh24:mi:ss';
DECIMAL_CHAR CHAR :='.';
-- ----------------------------------------------------------------------
function fn(p in varchar2) return varchar2 is begin return sys_context('USERENV', 'CURRENT_SCHEMA') || '.' || $$plsql_unit || '.' || p; end;
-- ----------------------------------------------------------------------
function split(p_params in varchar2) return sys.odciVarchar2List is
l_res sys.odciVarchar2List := sys.odciVarchar2List();
l_delim varchar2(1) := ' ';
begin
if p_params is null then return l_res; end if;
for curs in (
select distinct t.param, rownum rn, count(1)over() cnt
from(select trim(regexp_substr(tbl.param, '[^'||l_delim||']+', 1, level)) param
from (select p_params param from dual) tbl
connect by level <= length(tbl.param) - length(replace(tbl.param, l_delim)) + 1
) t where t.param is not null
order by rn
) loop
if curs.rn = 1 then l_res.extend(curs.cnt); end if;
l_res(curs.rn) := curs.param;
end loop;
return l_res;
end;
-- ----------------------------------------------------------------------
function consistentCheck (p_event_id in number) return varchar2 is
invalid_date_format exception;
pragma exception_init(invalid_date_format, -01843);
--
type parseTable is table of xxapps.xxapps_xlsx_parsed_content_t%rowtype;
type loadTable is table of umdap_cdu_coal_ton_mining_cost_xls%rowtype;
l_load_table loadTable := loadTable();
l_parse_table parseTable;
l_cr pls_integer;
l_cv varchar2(4000);
l_filename varchar2(150 char);
l_period varchar2(50 char);
c integer;
qry varchar2(200 char);
l_on_date_str varchar2(1000) := null;
l_on_date date := null;
l_year varchar2(10) := null;
l_month varchar2(100) := null;
l_publication_date date := null;
l_ussi varchar2(1000) := null;
l_region varchar2(1000) := null;
l_company varchar2(1000) := null;
l_coal varchar2(1000) := null;
l_cnt integer;
l_region_cnt integer;
l_company_cnt integer;
l_unit varchar2(1000) := 'руб.';
l_company_id varchar2(128) := null;
l_cost_col varchar2(10) := null;
--
cursor parseCursor(p_sheet_id pls_integer) is
select pc.*
from xxapps.xxapps_xlsx_parsed_content_t pc
where pc.sheet_id = p_sheet_id
and pc.row_id >= LOAD_START_ROW
order by pc.row_id;
--
function filename(p_event_id in number) return varchar2 is
l_filename varchar2(100 char);
begin
select file_name
into l_filename
from xxwh_etl_loader_events_t
where id = p_event_id;
return l_filename;
end;
--
function getMonthNum(p_month in varchar2) return varchar2 is
l_filename varchar2(100 char);
begin
return
case
when p_month = 'январь' then '01'
when p_month = 'февраль' then '02'
when p_month = 'март' then '03'
when p_month = 'апрель' then '04'
when p_month = 'май' then '05'
when p_month = 'июнь' then '06'
when p_month = 'июль' then '07'
when p_month = 'август' then '08'
when p_month = 'сентябрь' then '09'
when p_month = 'октябрь' then '10'
when p_month = 'ноябрь' then '11'
when p_month = 'декабрь' then '12'
end;
end;
--
procedure appendToLoad(pr xxapps.xxapps_xlsx_parsed_content_t%rowtype) is
l_idx pls_integer;
l_type number;
l_cost_val varchar2(4000);
l_sql varchar2(4000);
--
function toStr (s varchar2) return varchar2 is begin l_cv := s; return s; end;
function toDate(s varchar2) return date is begin l_cv := s; return to_date(s, DFMT); end;
function toNum (s varchar2) return number is
begin
l_cv := s;
return to_number(
case DECIMAL_CHAR
when '.' then replace(s, ',', '.')
when ',' then replace(s, '.', ',')
else s
end);
end;
--
begin
l_cr := pr.row_id;
l_sql := 'select ' || l_cost_col || ' from xxapps.xxapps_xlsx_parsed_content_t where sheet_id = :1 and row_id = :2';
execute immediate l_sql into l_cost_val using SHEET_ID, l_cr;
l_load_table.extend();
l_idx := l_load_table.last;
l_load_table(l_idx)."companyId" := toStr (l_company_id);
l_load_table(l_idx)."companyName" := toStr (l_company);
l_load_table(l_idx)."costPerTonCdu" := toNum (l_cost_val);
l_load_table(l_idx)."unit" := toStr (l_unit);
l_load_table(l_idx)."onDate" := l_on_date;
l_load_table(l_idx)."publicationDate" := l_publication_date;
l_load_table(l_idx)."loadEventID" := p_event_id;
end;
begin
select substr(value,1,1) into DECIMAL_CHAR from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';
l_filename := filename(p_event_id);
open parseCursor(SHEET_ID);
loop
fetch parseCursor bulk collect into l_parse_table limit BULK_LIMIT;
exit when l_parse_table.count = 0;
l_ussi := l_parse_table(l_parse_table.count).col_a;
for i in 1..l_parse_table.count loop
if i = 1 and l_parse_table(i).col_a not like '%одной тонны добычи%' then
return 'Файл должен содержать фразу "одной тонны добычи"';
end if;
-- "publicationDate" --
if i = 1 then
l_publication_date := to_date(replace(substr(replace(l_ussi, 'УСОИ: '), 1, 10), '/', '.'), DFMT);
if l_publication_date is null or length(replace(l_ussi, 'УСОИ: ')) <> 19 then
return 'Поле "publicationDate" должно быть заполнено';
end if;
end if;
-- "onDate" --
if i = 2 then
l_on_date_str := replace(replace(l_parse_table(i).col_a, 'за '), ' года');
l_year := regexp_replace(l_on_date_str, '[^[:digit:]]', '');
l_month := lower(trim(replace(l_on_date_str, l_year)));
l_month := getMonthNum(l_month);
l_on_date := to_date('01.'||l_month||'.'||l_year , DFMT);
end if;
-- "costPerTonCdu" --
-- Заголовок "Полная себестоимость" ищем в столбце AF,
-- но значение поля берём из столбца AH
if i = 4 and l_cost_col is null then
if l_parse_table(i).col_a != 'УЭП-3.07.99'
then return 'В шапке таблицы отсутствует заголовок "УЭП-3.07.99"';
end if;
if l_parse_table(i).col_af like '%Полная себестоимость%' then
l_cost_col := 'COL_AH';
end if;
if l_cost_col is null then return 'В шапке таблицы отсутствует заголовок "Полная себестоимость"'; end if;
end if;
if i >= 8 and i < l_parse_table.count and l_parse_table(i).col_a not like '% ФО' then
l_company := l_parse_table(i).col_a;
if l_company is not null then
select max("id") into l_company_id from xxwh."coalaCompanyNames" where "name" = l_company and "sourceType" = 'CDU' and "deletedDate" is null;
appendToLoad(l_parse_table(i));
end if;
end if;
end loop;
forall j in 1..l_load_table.count
insert into umdap_cdu_coal_ton_mining_cost_xls values l_load_table(j);
l_load_table.delete;
end loop;
close parseCursor;
return null;
exception
when invalid_date_format then
rollback;
if parseCursor%isopen then close parseCursor; end if;
return 'Некорректная дата публикации';
when others then
rollback;
if parseCursor%isopen then close parseCursor; end if;
return 'Error on row '||l_cr||' value '||l_cv||' '||sqlerrm;
end;
-- ----------------------------------------------------------------------
end;
/