如何在 SQL 中编写搜索查询 [英] how to write a search query in SQL

问题描述

我想编写一个搜索查询.我有以下场景.

I want to write a search query. I have the following scenario.

搜索结果有 3 个条件(ID、NAME、CITY).例子:

There are 3 criteria (ID, NAME , CITY) for searching the result. example:

• 如果用户输入 ID = 123，那么所有 ID = 123 的行都应该无论名称和城市如何都可以获取
• 如果用户输入 ID = 123和名称 = 'SAM'，然后所有 ID = 123 且名称 = 'SAM' 的行应该不分城市获取
• 如果用户输入 ID =123,name = 'SAM' and city = 'NY'，然后所有行应该获取匹配项.

推荐答案

Gordon Linoff 提供的答案是绝对好，可行且直接，但它不能解决像您这样的情况下的性能问题.

The answer provided by Gordon Linoff is definitely good and workable and straight-forward, but it does not address the performance issues in cases like yours.

也就是说，您要搜索的表可能非常大并且有各种索引(例如，在您的情况下，NAME 上的一个索引和 CITY 上的另一个索引).在这种情况下，一个简单的静态 SQL 方法可能会使用一个或另一个索引，并且在没有提供它所使用的索引的标准的情况下表现不佳.(我知道 Oracle 11g 引入了自适应游标共享——不过我还没有看到它真的很有帮助.我想相信它可能会使我的答案过时并且应该使我的答案过时.但我只是没有看到它真的很好用.我欢迎评论).

Namely, the table you are searching against could be very large and have various indexes (say, in your case, an index on NAME and another index on CITY). In that case, a simple, static SQL approach may use one index or another and perform poorly in cases where the criteria for the index it is using is not supplied. (I know Oracle 11g introduced adaptive cursor sharing -- I have yet to see it be really helpful though. I'd like to believe it could make my answer outdated and it should make my answer outdated. But I just haven't seen it really work very well yet. I'd welcome comments about it).

无论如何，如果您没有 11g 和/或不想严重依赖自适应游标共享，我认为当前编写此类搜索查询的最佳做法是使用 REF CURSOR.像这样:

Anyway, if you don't have 11g and/or do not want to rely to heavily on adaptive cursor sharing, I think the current best practice for writing such search queries to to use a REF CURSOR. Like this:

-- Create a table to query from
CREATE TABLE matt1 ( id number, name varchar2(30), city varchar2(30) );

CREATE OR REPLACE PACKAGE matt_query_pkg AS
  FUNCTION get_results ( p_id NUMBER, p_name VARCHAR2, p_city VARCHAR2 ) RETURN SYS_REFCURSOR;
END matt_query_pkg;

CREATE OR REPLACE PACKAGE BODY matt_query_pkg AS
  FUNCTION get_results ( p_id NUMBER, p_name VARCHAR2, p_city VARCHAR2 )RETURN SYS_REFCURSOR IS
    l_rc SYS_REFCURSOR;
    l_sql VARCHAR2(32000);
  BEGIN
    l_sql := 'SELECT id, name, city FROM matt1 WHERE 1=1';
    if p_id IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_id IS NULL)';
    else
      l_sql := l_sql || ' AND (id = :b_id)';
    end if;

    if p_name IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_name IS NULL)';
    else
      l_sql := l_sql || ' AND (name = :b_name)';
    end if;

    if p_city IS NULL THEN
      l_sql := l_sql || ' AND (1=1 OR :b_city IS NULL)';
    else
      l_sql := l_sql || ' AND (id = :b_city)';
    end if;

    dbms_output.put_line('Executing:');
    dbms_output.put_line(l_sql);

    OPEN l_rc FOR l_sql USING p_id, p_name, p_city;

    RETURN l_rc;
  END get_results;
END matt_query_pkg;

在仅给出 ID 和 NAME 条件的示例情况下，它将生成这样的 SQL:

In an example case where only the ID and NAME criteria is given, it will generate a SQL like this:

SELECT id, name, city 
FROM matt1 
WHERE 1=1 
AND (id = :b_id) 
AND (name = :b_name) 
AND (1=1 OR :b_city IS NULL)

Oracle 的优化器会分解出 (1=1 OR :b_city IS NULL) 子句(因为它知道它总是 true)，留下一个它可以的 SQL然后专门针对给定的标准进行优化.

Oracle's optimizer will factor out the (1=1 OR :b_city IS NULL) clause (because it knows it is always true), leaving a SQL that it can then optimize specifically for the criteria given.

注意:放入 (1=1 OR :b_city IS NULL) 子句的目的是保持绑定变量的数量不变，因此您始终可以使用以下方法运行搜索:

NOTE: the point of putting in the (1=1 OR :b_city IS NULL) clause at all is to keep the number of bind variables constant, so you can always run the search using:

OPEN l_rc FOR l_sql USING p_id, p_name, p_city;

如果你没有加入那个 (1=1 OR...) 子句，你就必须有一个不同的 OPEN..FOR 语句空/非空输入参数的所有可能组合.

If you didn't put in that (1=1 OR...) clause, you'd have to have a different OPEN..FOR statement for every possible combination of null/not null input parameters.

这里是一些测试它的代码，供参考:

Here is some code to test it, for reference:

-- Test it
insert into matt1 values (1, 'Fred', 'New York');
insert into matt1 values (2, 'Fred', 'Philadelphia');
insert into matt1 values (3, 'John', 'Philadelphia');
insert into matt1 values (4, 'Mark', 'Philadelphia');
insert into matt1 values (5, 'Mark', 'Chicago');
commit;

declare
  l_rc SYS_REFCURSOR;
  l_id NUMBER;
  l_name VARCHAR2(30);
  l_city VARCHAR2(30);   
begin
  l_rc := matt_query_pkg.get_results (NULL, 'Fred', NULL);
  loop
    fetch l_rc INTO l_id, l_name, l_city;
    exit when l_rc%NOTFOUND;
    dbms_output.put_line ('Found: ' || l_id || ', ' || l_name || ', ' || l_city); 
  end loop;
end;

这篇关于如何在 SQL 中编写搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！