SQL为可能具有特定值的所有记录选择 [英] SQL select for all records that may holds specific value

问题描述

如何选择所有可能包含已知特定值的记录， 在SQL表达式中不引用特定列 ?

How to select all records,that may contain specific value that is known, without referring to specific column in SQL expression?

例如，我知道某些 未知 列的值为"xxx"，并且有 许多列 >并记录在表中.

For instance, i know,that some unknown column holds value 'xxx' and there are many columns and records in table.

谢谢.

推荐答案

---

因此，您想对数据库进行类似Google的免费文本搜索.可以做到，但表演将是Teh Suck！ Google之所以快速，是因为它在其索引上具有索引，重复的数据存储，并且通常针对这种搜索而对所有内容进行了优化.

---

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

无论如何，这是使用动态SQL和Oracle数据字典的概念证明.请注意，我将列限制为要搜索的数据类型，即字符串.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL> set serveroutput on size unlimited
SQL> declare
  2      dummy varchar2(1);
  3  begin
  4      for r in ( select table_name, column_name from user_tab_cols
  5                 where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7          begin
  8              execute immediate 'select null from '||r.table_name
  9                      ||' where '||r.column_name||' like ''%&search_value%'' '
 10                      ||' and rownum = 1'
 11                 into dummy;
 12              dbms_output.put_line('Found it in >>>'
 13                     ||r.table_name||'.'||r.column_name);
 14          exception
 15              when others then
 16                  -- bad practice ahoy!
 17                  null;
 18          end;
 19      end loop;
 20  end;
 21  /
Enter value for search_value: MAISIE
old   9:                ||' where '||r.column_name||' like ''%&search_value%'' '
new   9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in >>>T23.NAME

PL/SQL procedure successfully completed.

SQL>

一个更健壮的实现可能需要处理大小写，整个单词等.如果您的文件大小为10g或更高，则正则表达式可能会有用，但是将regex和动态SQL结合起来会很有趣， em>前景.

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

我再说一遍，表演将是Teh Suck！在大数据集上.几乎不可能进行调整，因为我们无法为每一列编制索引，并且当然不支持LIKE或类似的模糊匹配.一种替代方法是使用XQuery生成数据的XML表示，然后使用Text对其进行索引.维护这样的存储库将是开销，但是如果您需要定期使用此功能(尤其是在生产环境中)，那么这将是一笔可观的投资.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

我们可以改为使用all_tab_cols在我们拥有权限的所有表上进行更广泛的搜索.

We can conduct a broader search across all the tables we have privileges on by using all_tab_cols instead.

for r in ( select owner, table_name, column_name from all_tab_cols
                   where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )

显然，我们需要在生成的语句中为拥有的模式添加前缀.

Obviously we need to prefix the owning schema in the generated statement.

execute immediate 'select null from '||r.owner||'.'||r.table_name
                       ||' where '||r.column_name||' like ''%

这篇关于SQL为可能具有特定值的所有记录选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！