时间戳

首页 > 解决方案 > Oracle SQL 在列中使用分隔符拆分字符串

sql - Oracle SQL 在列中使用分隔符拆分字符串

问题描述

我有一个粗略的表结构，如下所示：

ID 价值 项目 最后更新
1 井号＃ 测试 123981010
2 我喜欢 行政 123129319

我们有一个非常复杂的机制，它会生成一个需要这 3 列的新表。（有大约 6800 万个条目）由于我不想进入的原因，我们不能使用 join 语句。（很多遗留表）现在我的方法是创建一个分隔字符串，如（使用函数）：

Value#Project#Lastupdate

在这里，分隔符是什么并不重要。但是鉴于 Value 可以在那里存在任何值，我不能只使用一个非常复杂的分隔符，它可能涵盖所有情况的 99.999999%，但在百万分之 1 的情况下它不会。所以我所做的就是逃避字符串中所有出现的分隔符，然后将它们分开。现在在这个例子中它看起来像这样：

Hashtag\##testing#123981010

所以我用''逃脱。现在这一切都有效，但它真的很慢。选择语句

这是我的代码：

CREATE OR REPLACE FUNCTION unescape_internal( i_str IN VARCHAR2, i_idx IN INTEGER) RETURN VARCHAR2 IS
    v_first VARCHAR2(4000);
    v_second VARCHAR2(4000);
    v_third VARCHAR2(4000);
    v_ret VARCHAR2(4000);
    v_curr_str VARCHAR2(4000);
    v_curr_char VARCHAR2(1);
BEGIN
    v_curr_str := '';
    
    FOR i in 1..LENGTH(i_str)
    LOOP
        v_curr_char := substr(i_str, i, 1);
        IF v_curr_char = '#' AND i = 1 THEN
            IF v_first IS NULL THEN
                v_first := ' ';
            END IF;
        ELSIF v_curr_char = '#' AND substr(i_str, i-1, 1) != '\' THEN
            IF v_first IS NULL THEN
                v_first := v_curr_str;
                v_curr_str := '';
            ELSIF v_second IS NULL THEN
                v_second := v_curr_str;
                v_curr_str := '';
            END IF;
        ELSE
            v_curr_str := v_curr_str || v_curr_char;
        END IF;     
    END LOOP;
    
    IF v_third IS NULL THEN
        v_third := v_curr_str;
        v_curr_str := '';
    END IF;
    
    IF( i_idx = 1 ) THEN
        v_ret := TRIM(NVL(v_first,''));
    ELSIF( i_idx = 2 ) THEN
        v_ret := TRIM(NVL(v_second,''));
    ELSIF( i_idx = 3 ) THEN
        v_ret := TRIM(NVL(v_third,''));
    ELSE 
        v_ret := '';
    END IF;
    
    RETURN v_ret;
END unescape_internal;

我用来测试的代码是这样的：

    declare
    v_varchar1 VARCHAR2(4000);
    v_varchar2 VARCHAR2(4000);
    v_varchar3 VARCHAR2(4000);
BEGIN
    FOR i in 1..1000000 
    LOOP
        select unescape_internal('123123123123#1dasdyxcsd113\##test' || i, 1), unescape_internal('123123123123#1dasdyxcsd113\##test' || i, 2), unescape_internal('123123123123#1dasdyxcsd113\##test' || i, 3)  into v_varchar1, v_varchar2, v_varchar3 from dual;
    END LOOP;
END;

这大约需要 40 秒。在这个数据集中听起来并不多，但鉴于我的数据非常少，列值也很小，这将是一个更大的问题。我们尝试将此函数与实际数据集一起使用，在我的更改之前，sql 大约需要 300 秒才能在 sqldeveloper 中返回 250 个条目。随着我的改变，我们不得不在等待 30 分钟后中止。在真实环境中使用 6800 万个条目，这大约需要 45 分钟。代码很慢，因为它多次处理字符串。因为我需要每一列（我们有 3 个），所以我传递了我想要返回的列的索引。这导致对字符串进行 3 次完整处理。不幸的是，对函数使用 DETERMINISTIC 不起作用，因为索引每次都会更改。

我首先尝试使用正则表达式，但不幸的是oracle（12g）不支持前瞻/后瞻正则表达式，因此我现在没有想法，希望有人有某种想法。

标签： sqloracle

解决方案

您可以消除到 PL/SQL 的上下文切换，并使用以下方法在 SQL 上下文中完成所有操作：

SELECT REPLACE( REGEXP_SUBSTR( value, '(\\.|[^#])+', 1, 1 ), '\#', '#' ) AS value1,
       REPLACE( REGEXP_SUBSTR( value, '(\\.|[^#])+', 1, 2 ), '\#', '#' ) AS value2,
       REPLACE( REGEXP_SUBSTR( value, '(\\.|[^#])+', 1, 3 ), '\#', '#' ) AS value3
FROM   table_name

正则表达式(\\.|[^#])+将从字符串的左侧开始并查找：

  • \\.反斜杠字符（您的转义字符）后跟通配符.以匹配转义字符对；或者
  • [^#]任何非散列字符。

该匹配会执行一次或多次（这是+）以匹配整个术语，并且在找到非转义哈希字符时将停止，并将返回由整个非哈希字符和转义字符组成的子字符串。

其中，对于样本数据：

CREATE TABLE table_name ( value ) AS
SELECT 'Hashtag\##testing#123981010' FROM DUAL

输出：

价值1 | 价值2 | 价值3   
:------- | :-------- | :--------
标签# | 测试 | 123981010

db<>在这里摆弄

更新：

另一个 PL/SQL 解决方案是：

CREATE OR REPLACE FUNCTION unescape_internal2(
  i_str IN VARCHAR2,
  i_idx IN INTEGER
) RETURN VARCHAR2 DETERMINISTIC IS
  v_idx        PLS_INTEGER := 0;
  v_escape_pos PLS_INTEGER;
  v_hash_pos   PLS_INTEGER;
  v_start_pos  PLS_INTEGER := 1;
BEGIN
  IF i_str IS NULL OR i_idx < 1 THEN
    RETURN NULL;
  END IF;
  
  LOOP
    v_hash_pos := v_start_pos;
    LOOP
      v_escape_pos := INSTR( i_str, '\#', v_hash_pos );
      v_hash_pos   := INSTR( i_str, '#', v_hash_pos );
      EXIT WHEN v_escape_pos = 0 OR v_hash_pos = 0 OR v_escape_pos > v_hash_pos;
      v_hash_pos := v_escape_pos + 2;
    END LOOP;
    
    v_idx := v_idx + 1;
    
    IF v_idx = i_idx THEN
      IF v_hash_pos = 0 THEN
        RETURN REPLACE( SUBSTR( i_str, v_start_pos ), '\#', '#' );
      ELSE
        RETURN REPLACE( SUBSTR( i_str, v_start_pos, v_hash_pos - v_start_pos ), '\#', '#' );
      END IF;
    ELSIF v_hash_pos = 0 THEN
      RETURN NULL;
    END IF;
    v_start_pos := v_hash_pos + 1;
  END LOOP;
END unescape_internal2;
/

但是，在第一个解决方案中，它似乎没有使用正则表达式和避免从 SQL 到 PL/SQL 的上下文切换那么快，但是您应该分析系统和数据上的所有选项。

db<>在这里摆弄

推荐阅读