SQL:解析分隔的列(缩短脚本)

Question

我创建了一个查询，它正常工作。但是我还不满意，因为我的代码太长了，我是否可以简化或缩短select语句？

select   
/*GenInfo*/
 id ,name name,
replace(regexp_substr(properties, 'EntityID=[^;]*'), 'EntityID=', '') as EntityID,
replace(regexp_substr(properties, 'deployed=[^;]*'), 'deployed=', '') as deployed,
replace(regexp_substr(properties, 'type=[^;]*'), 'type=', '') as type,
replace(regexp_substr(properties, 'level=[^;]*'), 'level=', '') as "LEVEL",
replace(regexp_substr(properties, 'description=[^;]*'), 'description=', '') as description,
replace(regexp_substr(properties, 'indicator=[^;]*'), 'indicator=', '') as indicator,
replace(regexp_substr(properties, 'Agreement=[^;]*'), 'Agreement=', '') as Agreement,
replace(regexp_substr(properties, 'Activation date to charge=[^;]*'), 'Activation date to charge=', '') as Activationdatetocharge,
replace(regexp_substr(properties, 'id=[^;]*'), 'id=', '') as id,
replace(regexp_substr(properties, 'name=[^;]*'), 'name=', '') as name,
replace(regexp_substr(properties, 'currencyCode=[^;]*'), 'currencyCode=', '') as currencyCode,
replace(regexp_substr(properties, 'saleExpirationDate=[^;]*'), 'saleExpirationDate=', '') as saleExpirationDate,
replace(regexp_substr(properties, 'Product type=[^;]*'), 'Product type=', '') as Producttype,
replace(regexp_substr(properties, 'saleEffectiveDate=[^;]*'), 'saleEffectiveDate=', '') as saleEffectiveDate,
replace(regexp_substr(properties, 'Deactivation date to charge=[^;]*'), 'Deactivation date to charge=', '') as Deactivationdatetocharge
.
.
.
.
.
.

from OFFER
where name = 'PLAN 599'

；

Answer 1

您必须双拆分并生成一个查询：

1. 将行拆分为"ColName=Value"-fields
2. 将田野分割成两个分开的瓦片
3. 创建一个from-对偶查询。 声明输入字符串VARCHAR2 (2000) := 'EntityID=1;deployed=2018-01-01;type=app'； myquery VARCHAR2 (2000) := 'SELECT'；- tmpValue VARCHAR2 (2000)；tmpName VARCHAR2 (2000)； 开始为i IN (选择TRIM (REGEXP_SUBSTR (输入字符串，-拆分输入并循环通过'^；+'，1，LEVEL )l来自<= REGEXP_COUNT (inputstring，';') +1的双重连接 )循环tmpName := REGEXP_SUBSTR (i.l，'^=+'，1，1)；-将列拆分为值并命名为tmpValue := REGEXP_SUBSTR (i.l，'^=+'，1，2)；myquery := myquery‘\x tmpValue \x’‘作为’\x\x‘\ myQuery := SUBSTR (myQuery，0，LENGTH (myQuery) - 1) LENGTH‘FROM DUAL'；- complete DBMS_OUTPUT.put_line (myQuery)；--输出结果--结果:选择'1’as EntityID，'2018-01-01‘作为部署，'app’类型从DUAL选择 结束；

无论如何，如果您想通过代码读取值，那么这个查询就会出现问题。也许你可以告诉我们，你想用这些数据做什么。

我希望，您只想从一个文件中转换一些数据。如果是这样，您可以添加另一个拆分循环来拆分行。

示例-函数

CREATE OR REPLACE FUNCTION MakeSQL (inputstring VARCHAR2)
    RETURN VARCHAR2
IS
    myquery    VARCHAR2 (2000);                                  -- result-var
    tmpValue   VARCHAR2 (2000);
    tmpName    VARCHAR2 (2000);
BEGIN
    FOR x IN (    SELECT TRIM (REGEXP_SUBSTR (inputstring, -- Split input and loop through
                                              '[^' || CHR (10) || ']+',
                                              1,
                                              LEVEL))
                             tmpRow
                    FROM DUAL
              CONNECT BY LEVEL <= REGEXP_COUNT (inputstring, CHR (10)) + 1)
    LOOP
        myquery := myquery || 'SELECT';

        FOR i IN (    SELECT TRIM (REGEXP_SUBSTR (x.tmpRow, -- Split input and loop through
                                                  '[^;]+',
                                                  1,
                                                  LEVEL))
                                 l
                        FROM DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT (x.tmpRow, ';') + 1)
        LOOP
            tmpName :=
                REGEXP_SUBSTR (i.l,
                               '[^=]+',
                               1,
                               1);         -- Split column into value and name
            tmpValue :=
                REGEXP_SUBSTR (i.l,
                               '[^=]+',
                               1,
                               2);
            myquery :=
                myquery || ' ''' || tmpValue || ''' as ' || tmpName || ','; -- build some query
        END LOOP;

        myQuery :=
               SUBSTR (myQuery, 0, LENGTH (myQuery) - 1)
            || ' FROM DUAL UNION ALL'
            || CHR (10);                                -- complete row-select
    END LOOP;

    myQuery := SUBSTR (myQuery, 0, LENGTH (myQuery) - 11);   -- complete query

    DBMS_OUTPUT.put_line (myQuery);                            --output result
    RETURN myQuery;
END MakeSQL;

示例调用

SELECT MakeSQL('EntityID=1;deployed=2018-01-01;type=app
EntityID=2;deployed=2018-02-02;type=app') FROM DUAL;

实例-结果

SELECT '1' as EntityID, '2018-01-01' as deployed, 'app' as type FROM DUAL UNION ALL
SELECT '2' as EntityID, '2018-02-02' as deployed, 'app' as type FROM DUAL