Oracle 层次结构验证-传入层次结构与预期层次结构

嗨，专家们，

我有一个要求来验证平面文件的分层结构，这些文件被加载到数据库中的预期结构是已知的。我已经创建了一些示例数据来演示我想做什么:

CREATE TABLE expected_structure
( 
  file_type            VARCHAR2(3)        NOT NULL,
  transaction_type     VARCHAR2(1)        NOT NULL,
  record_level         NUMBER             NOT NULL,
  min_occurrence       NUMBER             NOT NULL,
  max_occurrence       NUMBER             NOT NULL,
  optionality          VARCHAR2(1)        NOT NULL,
  recid                NUMBER             NOT NULL,
  parent_recid         NUMBER
);

INSERT INTO expected_structure VALUES ('ZZZ', 'A', 1, 1, 1, 'M', 1, NULL);
INSERT INTO expected_structure VALUES ('ZZZ', 'B', 2, 1, 1000, 'O', 2, NULL);
INSERT INTO expected_structure VALUES ('ZZZ', 'C', 3, 1, 1, 'O', 3, 2);
INSERT INTO expected_structure VALUES ('ZZZ', 'D', 3, 1, 1, 'M', 4, 2);
INSERT INTO expected_structure VALUES ('ZZZ', 'E', 2, 1, 1000, 'M', 5, NULL);
INSERT INTO expected_structure VALUES ('ZZZ', 'Z', 1, 1, 1, 'M', 6, NULL);

CREATE TABLE incoming_structure
( 
  file_id             NUMBER,
  line_num            NUMBER,
  transaction_type    VARCHAR2(1),
  col1                VARCHAR2(4000)
);

-- Valid structure
INSERT INTO incoming_structure VALUES (1, 1, 'A','some data');
INSERT INTO incoming_structure VALUES (1, 2, 'B','some data');
INSERT INTO incoming_structure VALUES (1, 3, 'D','some data');
INSERT INTO incoming_structure VALUES (1, 4, 'B','some data');
INSERT INTO incoming_structure VALUES (1, 5, 'C','some data');
INSERT INTO incoming_structure VALUES (1, 6, 'D','some data');
INSERT INTO incoming_structure VALUES (1, 7, 'E','some data');
INSERT INTO incoming_structure VALUES (1, 8, 'E','some data');
INSERT INTO incoming_structure VALUES (1, 9, 'Z','some data');

-- Invalid structure (missing mandatory child D)
INSERT INTO incoming_structure VALUES (2, 1, 'A','some data');
INSERT INTO incoming_structure VALUES (2, 2, 'B','some data');
INSERT INTO incoming_structure VALUES (2, 3, 'D','some data');
INSERT INTO incoming_structure VALUES (2, 4, 'B','some data');
INSERT INTO incoming_structure VALUES (2, 5, 'C','some data');
INSERT INTO incoming_structure VALUES (2, 6, 'E','some data');
INSERT INTO incoming_structure VALUES (2, 7, 'Z','some data');

-- Invalid structure (rogue transaction F)
INSERT INTO incoming_structure VALUES (3, 1, 'A','some data');
INSERT INTO incoming_structure VALUES (3, 2, 'B','some data');
INSERT INTO incoming_structure VALUES (3, 3, 'D','some data');
INSERT INTO incoming_structure VALUES (3, 4, 'F','some data');
INSERT INTO incoming_structure VALUES (3, 5, 'E','some data');
INSERT INTO incoming_structure VALUES (3, 6, 'Z','some data');

COMMIT;
复制

文件由逗号或管道分隔，作为外部表打开。文件中的每一行在开头都有一个 “事务类型”-参见表incoming_structure，在我的示例中，每个文件都由file_id标识。

我们有每个文件类型的预期结构作为站立数据-参见表expected_structure。这将显示每个事务类型的级别，它的父级，无论它是强制性的还是可选的，以及预期的发生次数。在我的例子中，为了简单起见，我只有一个file_type

我需要验证以下内容:
1) 事务类型是先前事务类型的有效子级或同级 (或将级别备份到新的父级)
2) 出现所有强制交易类型
3) 出现次数在下限和上限内 (每个父子实例) *

我们有一些现有的PL/SQL代码可以完成这项工作，但是它的性能很差，并且不能很好地扩展。我知道这在SQL中是可能的，但似乎总是到目前为止，然后碰壁。

我最新的方法是建立一组从每个事务类型到下一个事务类型的 “有效移动”，然后简单地做一个领先/滞后来检查组合是否有效，但是，这对要求2) 没有帮助，因为某些交易可以同时具有强制性子项和可选子项 (并且可选子项可以在强制性子项之前出现)。因此，我如何才能验证所有强制性孩子在应该出现的时候出现 (而不应该在不应该出现的时候出现)。

我已经创建了3个场景的数据:
file_id = 1-传入结构有效
file_id = 2-结构无效 (父B后缺少强制子D)
file_id = 3-结构无效 (流氓事务类型F)

我认为这必须是可能的，无论是分层查询或match_regnize，但他们都让我大吃一惊，说实话。

* 出现次数验证不是一个热门节目，但很不错。

“帮助我Oracle大师，你是我唯一的希望……”

问候，
标记

尝试构建将验证传入行与预期行的SQL语句是困难的。所以我提议别的。

模式匹配!

如果将预期的记录转换为正则表达式，则可以使用match_regnize对其进行测试。

根据您的预期行，我相信您正在寻找:

-1 A
-可选地，后跟1-1,000 Bs，在可选的C和强制性D之后
-然后是1-1,000 Es
-用一个Z完成

它给出了一个正则表达式，就像:

a ( b{1,1000} c{0,1} d )* e{1,1000} z
复制

可悲的是match_regnize对绑定量词有200的上限。因此，您可以查找至少1个B或E，并在以后检查确切的计数。

按文件拆分并按行排序会给出类似的内容:

select * from incoming_structure
match_recognize (
  partition by file_id
  order by line_num
  measures
    match_number() as grp,
    classifier()   as var,
    final count(b.line_num) as b_ct,
    final count(e.line_num) as e_ct
  all rows per match with unmatched rows
  pattern ( a ( b{1,} c{0,1} d )* e{1,} z )
  define
    a as transaction_type = 'A',
    b as transaction_type = 'B',
    c as transaction_type = 'C',
    d as transaction_type = 'D',
    e as transaction_type = 'E',
    z as transaction_type = 'Z'
);

   FILE_ID   LINE_NUM        GRP VAR              B_CT       E_CT T COL1
---------- ---------- ---------- ---------- ---------- ---------- - ----------
         1          1          1 A                   2          2 A some data
         1          2          1 B                   2          2 B some data
         1          3          1 D                   2          2 D some data
         1          4          1 B                   2          2 B some data
         1          5          1 C                   2          2 C some data
         1          6          1 D                   2          2 D some data
         1          7          1 E                   2          2 E some data
         1          8          1 E                   2          2 E some data
         1          9          1 Z                   2          2 Z some data
         2          1                                             A some data
         2          2                                             B some data
         2          3                                             D some data
         2          4                                             B some data
         2          5                                             C some data
         2          6                                             E some data
         2          7                                             Z some data
         3          1                                             A some data
         3          2                                             B some data
         3          3                                             D some data
         3          4                                             F some data
         3          5                                             E some data
         3          6                                             Z some data
复制

grp/var/b_ct/e_ct值的行符合标准。如果需要，可以使用B & E计数列来验证这些列是否为 <= 1,000。

那些没有措施价值的人由于某种原因失败了。因此，您可以在外部查询中过滤这些输入/输出，具体取决于您要报告的内容。