在一列中搜索并返回多个通配符值

Question

全部-

我正在查看大量的sql查询历史数据。最终，我需要从每个表执行的查询列表中创建一个不同的表列表。举个例子，我的简化表格是：

create table zwork_example (
username varchar2(50),
sql_text clob);

insert into zwork_example (username, sql_text)
values ('user1', 'schema1.table1, schema1.table2, schema2.table1, schema1.table1');
insert into zwork_example (username, sql_text)
Values ('user2', 'schema1.table3, schema1.table2, schema2.table1, schema1.table6');

有人知道如何搜索schema1*并返回N个属于schema1的表名吗？在这个例子中，我有一个我感兴趣的特定模式，所以我可以明确地声明schema1是我唯一感兴趣返回表名的模式。

给定这个例子，我会寻找的输出是：

User        Schema          Tables
--------    --------        --------
User1       schema1         table1, table2
User2       schema1         table3, table2, table6

Answer 1

CLOB，大数据集。。。听起来不太有希望。阅读：可能需要一些时间才能得到结果。对于这样一个小的数据集，看看这是否有帮助。

SQL> with
2  search_for (schema) as
3    (select 'schema1' from dual),
4  temp as
5    (select distinct
6       username,
7       trim(regexp_substr(dbms_lob.substr(sql_text, 32767), '[^,]+', 1, column_value)) col
8     from zwork_example cross join
9       table(cast(multiset(select level from dual
10                           connect by level <= regexp_count(sql_text, ',') + 1
11                          ) as sys.odcinumberlist))
12    )
13  select
14    t.username,
15    s.schema,
16    listagg(trim(replace(t.col, s.schema ||'.', null)), ', ') within group (order by null) tables
17  from temp t join search_for s on instr(t.col, s.schema) > 0
18  group by t.username, s.schema;
USERNAME   SCHEMA  TABLES
---------- ------- --------------------------------------------------
user1      schema1 table1, table2
user2      schema1 table2, table3, table6
SQL>

它做什么？

• search_forCTE包含您正在搜索的架构(schema1，对吗？(
• tempCTE将sql_text拆分为行
  • 此外，为了获得distinct表列表，我将dbms_lob.substr应用于该列，希望您实际上没有比它长的字符串(32767(
• 最终查询只是聚合它找到的表-模式匹配的行

Answer 2

对于大型数据集，您可以使用：

WITH search_string ( schema_name ) AS (
SELECT 'schema1' FROM DUAL
),
matches ( username, sql_text, schema_name, end_pos, matches ) AS (
SELECT username, 
sql_text,
schema_name,
CASE
WHEN sql_text LIKE schema_name || '%'
THEN INSTR( sql_text, ',' )
WHEN sql_text NOT LIKE ', ' || schema_name || '.'
THEN 0
ELSE INSTR(
sql_text,
', ' || schema_name || '.',
INSTR( sql_text, ', ' || schema_name || '.' ) + 3
)
END,
CASE
WHEN sql_text LIKE schema_name || '%'
THEN EMPTY_CLOB() || SUBSTR(
sql_text,
LENGTH(schema_name || '.') + 1,
INSTR( sql_text, ',' ) - LENGTH(schema_name || '.') - 1
)
WHEN INSTR( sql_text, ', ' || schema_name || '.' ) = 0
THEN NULL
WHEN INSTR( sql_text, ',', INSTR( sql_text, ', ' || schema_name || '.' ) + 3 ) = 0
THEN EMPTY_CLOB() || SUBSTR(
sql_text,
INSTR( sql_text, ', ' || schema_name || '.' )
+ LENGTH(', ' || schema_name || '.')
)
ELSE EMPTY_CLOB() || SUBSTR(
sql_text,
INSTR( sql_text, ', ' || schema_name || '.' )
+ LENGTH(', ' || schema_name || '.'),
INSTR( sql_text, ',', INSTR( sql_text, ', ' || schema_name || '.' ) + 3 )
- INSTR( sql_text, ', ' || schema_name || '.' )
- LENGTH(', ' || schema_name || '.')
)
END
FROM   zwork_example
CROSS JOIN search_string
UNION ALL
SELECT username,
sql_text,
schema_name,
INSTR(
sql_text,
', ' || schema_name || '.',
end_pos + 3
),
matches
|| ', '
|| CASE 
WHEN INSTR( sql_text, ',', end_pos + 1 ) = 0
THEN SUBSTR(
sql_text,
end_pos + LENGTH(', ' || schema_name || '.')
)
ELSE SUBSTR(
sql_text,
end_pos + LENGTH(', ' || schema_name || '.'),
INSTR( sql_text, ',', end_pos + 1 )
- end_pos - LENGTH(', ' || schema_name || '.')
)
END
FROM   matches
WHERE  end_pos > 0

)
SELECT username,
matches
FROM   matches
WHERE  end_pos = 0;

对于样本数据：

create table zwork_example (
username varchar2(50),
sql_text clob
);
DECLARE
v_text CLOB;
BEGIN
insert into zwork_example (username, sql_text)
values ('user1', 'schema1.table1, schema1.table2, schema2.table1, schema1.table1');
insert into zwork_example (username, sql_text)
Values ('user2', 'schema1.table3, schema1.table2, schema2.table1, schema1.table6');
insert into zwork_example (username, sql_text)
Values ('user3', 'schema2.table3, schema3.table2, schema4.table1, schema2.table6');
insert into zwork_example (username, sql_text)
Values ('user4', 'schema2.table3, schema3.table2, schema4.table1, schema1.table6');
v_text := 'schema1.table1';
FOR i IN 2 .. 250 LOOP
v_text := v_text || ', schema1.table' || i;
END LOOP;
insert into zwork_example (username, sql_text) values ( 'user5', v_text );
END;
/

输出：

USERNAME	匹配
用户3
user4	表6
user1	表1、表2、表1
user2	表3、表2、表6
user5	表格1、表格2、表格3、表格4、表格5、表格6、表格7、表格8、表格9、表格10、表格11、表格12、表格13、表格14、表格15、表格16、表格17、表格18、表格19、表格20、表格21、表格22、表格23、表格24、表格25、表格26、表格27、表格28、表格29、表格30、表格31、表格32、表格33、表格34、表格35、表格36、表格37、表格38、表格39、表格40、表格41、表格42、表格43，表44、表45、表46、表47、表48、表49、表50、表51、表52、表53、表54、表55、表56、表57、表58、表59、表60、表61、表62、表63、表64、表65、表66、表67、表68、表69、表70、表71、表72、表73、表74、表75、表76、表77、表78、表79、表80、表81、表82、表83、表84、表85、表86、表87，表88、表89、表90、表91、表92、表93、表94、表95、表96、表97、表98、表99、表100、表101、表102、表103、表104、表105、表106、表107、表108、表109、表110、表111、表112、表113、表114、表115、表116、表117、表118、表119、表120、表121、表122、表123、表124、表125、表126、表127、表128，表129、表130、表131、表132、表133、表134、表135、表136、表137、表138、表139、表140、表141、表142、表143、表144、表145、表146、表147、表148、表149、表150、表151、表152、表153、表154、表155、表156、表157、表158、表159、表160、表161、表162、表163、表164、表165、表166、表167、表168，表169、表170、表171、表172、表173、表174、表175、表176、表177、表178、表179、表180、表181、表182、表183、表184、表185、表186、表187、表188、表189、表190、表191、表192、表193、表194、表195、表196、表197、表198、表199、表200、表201、表202、表203、表204、表205、表206、表207、表208，表209、表210、表211、表212、表213、表214、表215、表216、表217、表218、表219、表220、表221、表222、表223、表224、表225、表226、表227、表228、表229、表230、表231、表232、表233、表234、表235、表236、表237、表238、表239、表240、表241、表242、表243、表244、表245、表246、表247、表248，表249，表250