Oracle отдает значения элементов XML файла посредством пакетных функций.
Задача
Часто "программисты" и "базисты" - иногда с веселой взаимной иронией, иногда довольно жестко - спорят о том, что есть база. Для ортодоксальных программистов база - это лишь хранилище данных, а вся обработка ведется в программном коде; для ортодоксальных базистов база - это всё, а программный код над ней - это что-то не очень-то и нужное, благо Oracle APEX дал им в руки мощный козырь в этом споре. Намедни пришлось решить задачу, подход к которой идеологически показался еще более решительным.
XML документ
Есть XML файл, который пришел по FTP с другого сервера и лег в папку нашего Oracle/PHP сервера. Поскольку доступа к PHP пространству в связи с "самоизоляцией" не было, а к пространству Oracle доступ был, решено было полностью решить задачу в базе.
XML документ выглядит примерно так и необходимо получать поля по, скажем, заданному значению target_id:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<TARGET_ID>1793</TARGET_ID>
<IS_SUCCESS>1</IS_SUCCESS>
<IS_NEW>0</IS_NEW>
<CREATE_DATE>25.06.2019 13:05:23</CREATE_DATE>
<UPDATE_DATE>25.06.2019 13:05:23</UPDATE_DATE>
... смысловые элементы, которые нам сейчас не важны ...
</ROW>
...
</ROWSET>
Директория
XML документ ложится в некоторую папку, путь к которой нам известен, и имеет имя, которое нам тоже известно. Oracle умеет читать файлы, но несколько специфическим образом - у него есть понятие директории (directory). Это по сути синоним пространства Oracle пути к папке в пространстве операционной системы:
create directory yk_export_dir as '/var/www/html/ora_export'
;
После создания директории следует дать схеме-исполнителю права на пользование ей:
grant read on directory yk_export_dir to xmlreader
;
SQL для XML
Далее мы уже можем средствами SQL прочитать XML документ и представить его в виде, напоминающем представление (view) базы данных:
select * from
(select
xmltype(
bfilename('YK_EXPORT_DIR', :file), NLS_CHARSET_ID ('AL32UTF8')
) as xml_data
from dual) x
, xmltable(
'/ROWSET/ROW'
passing x.xml_data
columns
target_id number
, is_success number
, is_new number
, create_date varchar2(20)
, update_date varchar2(20)
---, ... смысловые поля ...
) xt
;
Обратите внимание, что даты передаются строковым типом данных. XML - это текст, и при его формировании даты были превращены в строки; нам необходимо обратить на это внимание, поскольку далее нам потребуется обратное преобразование.
PL/SQL пакет
Далее мы записываем PL/SQL пакет, в который включаем пару типов данных, пару конвейерных функций и комплект обычных функций по числу смысловых элементов читаемого XML документа.
Спецификация пакета
create or replace package xmlreader_pkg
as
-- Директория Oracle, указывающая на папку операционной системы, в которой лежит XML файл:
c_dir constant varchar2(200) := 'YK_EXPORT_DIR'
;
-- Формат текстового представления дат:
c_date_format constant varchar(21) := 'dd.mm.yyyy hh24:mi:ss'
;
type t_row is record (
xml_data xmltype
, target_id number
, is_success number
, is_new number
, create_date varchar2(20)
, update_date varchar2(20)
--, ... смысловые поля ...
)
;
type t_tab is table of t_row
;
-- Получение всего XML документа (даты - varchar2, формат выше)
-- select * from table(xmlreader_pkg.read_xml(:p_file))
function read_xml(p_file varchar2) return t_tab pipelined
;
-- Получение строки "представления" из XML документа (даты - varchar2, формат выше)
-- select * from table(xmlreader_pkg.read_xml_line(:p_file, :p_target_id))
function read_xml_line(p_file varchar2, p_target_id number) return t_tab pipelined
;
function get_is_success (p_file varchar2, p_target_id number) return number
;
function get_create_date (p_file varchar2, p_target_id number) return date
;
-- ... смысловые функции
end xmlreader_pkg;
Элементы тела пакета
create or replace package body xmlreader_pkg
as
function read_xml(p_file varchar2) return t_tab pipelined
as
begin
for c in (
select *
from
(select xmltype(bfilename(c_dir, p_file), NLS_CHARSET_ID ('AL32UTF8')) as xml_data from dual) x
, xmltable(
'/ROWSET/ROW'
passing x.xml_data
columns
target_id number
, is_success number
, is_new number
, create_date varchar2(20)
, update_date varchar2(20)
-- ... смысловые поля
) xt
) loop
pipe row (c);
end loop;
return;
end read_xml;
-- ...
-- ...
function get_is_success(p_file varchar2, p_target_id number) return number
as
v_is_success number;
begin
select is_success into v_is_success from table(xmlreader_pkg.read_xml_line(p_file, p_target_id));
return v_is_success;
end get_is_success;
-- ...
function get_create_date(p_file varchar2, p_target_id number) return date
as
v_create_date date;
begin
select to_date(create_date, c_date_format) into v_create_date from table(xmlreader_pkg.read_xml_line(p_file, p_target_id));
return v_create_date;
end get_create_date;
-- ...
end xmlreader_pkg;
Пользование
Теперь мы можем стандартным образом получать значения необходимых нам элементов XML документа в любом пространстве, имеющем доступ к Oracle:
SQL
Мы можем включить вызов функций в SQL выражение:
select xmlreader_pkg.get_create_date(:p_file, :p_target_id) from dual
;
PL/SQL
Мы можем включить вызов функций в анонимный блок или хранимую процедуру:
begin
dbms_output.put_line(xmlreader_pkg.get_create_date(:p_file, :p_target_id));
end;
Oracle APEX
Мы можем использовать вызов функций в процессах, вычислениях, динамических действиях и т.п.:
begin
:P1_CREATE_DATE := xmlreader_pkg.get_create_date(
:P1_FILE, :P1_TARGET_ID
);
end;
PHP
Мы можем включить вызов функций в стандартные конструкции обращения к базе данных:
$conn = oci_connect(..., ..., ...);
$sql = " select xmlreader_pkg.get_create_date(:p_file, :p_target_id) from dual ";
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);
oci_fetch($stmt);
$create_date = oci_result($stmt, 1);
oci_free_statement($stmt);
oci_close($conn);
Выводы
Позиция "программиста": база хранит данные в таблицах, всё остальное делает код над ней. Позиция "базиста": база выполняет все действия по хранению данных в таблицах и манипулированию ими.
Мы предложили решение, в котором Oracle выступает универсальным читателем XML документа, при этом не используя ни одной таблицы. Он - база данных - не хранит никаких данных, а предлагает свои мощные средства по их обработке.