用分隔符分隔一行。 关于CONNECT BY的一些知识

你好

我是PL / SQL开发人员。 有一项任务是收集一些指标数据以跟踪系统负载。 有一些函数由包含ID列表的参数调用。

1,2,3,4…1001,1002,1003…

任务如下。 有必要将此类行分解为元素并将其写入整数集合。

让我们开始吧。

首先，您需要数据才能工作。 让我们编写一个函数，该函数生成一个用逗号分隔的数字字符串。 我们将一个整数参数N传递给函数-序列中的数字数。

我们不会很聪明，我们将使用VARCHAR2类型而不是CLOB进行序列。 接下来，我将解释为什么使用VARCHAR2。

生成序列的功能代码：

 CREATE OR REPLACE FUNCTION createNumbers(v_N IN PLS_INTEGER) RETURN VARCHAR2 IS v_str VARCHAR2(4000) := '0'; too_small EXCEPTION; PRAGMA EXCEPTION_INIT(too_small, -6502); BEGIN FOR i IN 1..v_N LOOP BEGIN v_str := v_str || ',' || TO_CHAR(i); EXCEPTION WHEN too_small THEN EXIT; END; END LOOP; RETURN v_str || ','; END; 

让我们回到我们的任务。

首先想到的是循环遍历该行，从而减少每次迭代时的行长。 由于根据问题的情况，必须将结果放入集合中，因此我们将创建相应的集合。

 DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(4000) := createNumbers(v_N => v_N); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; FOR i IN t_str.FIRST..t_str.LAST LOOP dbms_output.put_line(t_str(i)); END LOOP; t_str.DELETE; END; 

结果：
0
1
2
…
421
422
423
…

createNumber（）函数采用参数v_N =1000。在createNumber（）函数中，您可以看到v_str变量的溢出处理。 通过简单的计算，您可以发现4000字节足以容纳1021个数字。 我们的1000可以适合这个尺寸，没有任何问题。

如您所见，结果就是所需要的。 字符串被分割。

即使Oracle没有内置的split（）函数（如Java或Python），该选项也不适合我，因为我相信对于诸如拆分字符串之类的简单任务会编写过多的代码。

我认为在现阶段，是否可以仅使用SQL拆分字符串？ 我的意思不是经典SQL，而是Oracle提供的SQL。

我记得用于构建分层CONNECT BY查询的设计。



可选的START WITH语句告诉Oracle在哪里开始循环，即 哪一行将成为根。 条件几乎可以是任何情况。 必须指定CONNECT BY之后的条件。 在这里有必要告诉Oracle要继续多长时间。

可以看出，构造分层查询的唯一重要条件是CONNECT BY运算符，其余的则根据需要“串连”。

而且，此构造具有伪列级别，该级别返回当前迭代时的嵌套级别。

乍一看，这种结构似乎不适合用于折线。 这并非完全正确。 如果条件设置正确，则递归遍历可以转换为循环遍历，如while或for循环。

在编写查询之前，请考虑行遍历算法。 从行的开头开始，有必要将一定数量的字符切成分隔符。 上面，我写了关于伪列级别的文章。 我们将其用作当前迭代编号。
原来是这样的：

 SELECT SUBSTR(str, INSTR(str, ',', 1, level - 1) + 1, INSTR(str, ',', 1, level) - INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

但是，如果仔细观察，您会发现该算法在第一次迭代时将不起作用，因为INSTR（）函数的第三个参数不能为0。

因此，我们使用DECODE（）函数添加一个小的条件。

 SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

现在，第一个迭代将正确进行。

是时候应用CONNECT BY构造了。 另外，我们也将排队。

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM TMain CONNECT BY NVL(INSTR(str, ',', 1, level), 0) <> 0; 

我已经写道，在正确的条件下，CONNECT BY构造可以像循环一样工作。 该条件得到满足，直到INSTR（）函数可以找到定界符的第n个位置为止，其中n是当前迭代的编号，并且如我们所记得，伪列级别负责迭代编号。

看来问题解决了吗？ 不行

该代码可能有效，但其可读性为零。 我已经在考虑回到loop选项，但是我想出了如何改善CONNECT BY选项的方法。

Oracle具有像正则表达式这样的强大工具。 具体来说，函数regexp_instr（）和regexp_substr（）。

regexp_instr(_, [, _ [, ] ])开始位置regexp_instr(_, [, _ [, ] ]) -函数返回该字符在模式匹配开始或结束时的位置，以及其模拟INSTR（）。

regexp_substr(_, [, [, ]]) -函数返回与模式匹配的子字符串。

使用正则表达式重写查询：

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT regexp_substr(str, '[^,]+', 1, level) FROM TMain CONNECT BY NVL(regexp_instr(str, '[^,]+', 1, level), 0) <> 0; 

该代码的读取效果比上一个示例好得多。 这个选项适合我。

最后，比较三个选项的运行时解析比较是合乎逻辑的。 上面，我答应了解释为什么我们将使用VARCHAR2类型而不是CLOB类型。 这只是用于比较运行时。 由于Oracle对CLOB类型的处理与对VARCHAR2的处理不同，因此可能会使结果失真。


结果列表如下：

破损选项/号码数	10	100	1000
循环数	0.05毫秒	0.15毫秒	1.52毫秒
连接方式	0.18毫秒	0.68毫秒	18.1毫秒
CONNECT BY +正则表达式	0.25毫秒	12.1毫秒	1秒137毫秒

老实说，结果对我来说是出乎意料的。 我认为第三个选择是最快的。 好吧，这将是一个教训。

感谢您的阅读！

使用的来源清单：

1. 分层（递归）查询