SQL 在 Oracle 数据库中搜索 Long 数据类型的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/769787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to search the Long datatype within an Oracle database?
提问by
I am working with an Oracle database that stores HTML as a Long datatype. I would like to query the database to search for a specific string within the HTML data stored in the Long.
我正在使用将 HTML 存储为 Long 数据类型的 Oracle 数据库。我想查询数据库以搜索存储在 Long 中的 HTML 数据中的特定字符串。
I tried, "select * from TABLE where COLUMN like '%form%'". This causes the following Oracle error because "like" is not supported for Long datatypes.
我试过，“从 TABLE 中选择 *，其中 COLUMN 像 '%form%'”。这会导致以下 Oracle 错误，因为 Long 数据类型不支持“like”。
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-00932: 不一致的数据类型：预期的 NUMBER 变得很长
回答by Martin Selecky
You can use this example without using temp table:
您可以在不使用临时表的情况下使用此示例：
DECLARE
l_var VARCHAR2(32767); -- max length
BEGIN
FOR rec IN (SELECT ID, LONG_COLUMN FROM TABLE_WITH_LONG_COLUMN) LOOP
l_var := rec.LONG_COLUMN;
IF l_var LIKE '%350%' THEN -- is there '350' string?
dbms_output.put_line('ID:' || rec.ID || ' COLUMN:' || rec.LONG_COLUMN);
END IF;
END LOOP;
END;
Of course there is a problem if LONG has more than 32K characters.
当然，如果 LONG 超过 32K 个字符，就会出现问题。
回答by Shea
You can't search LONGs directly. LONGs can't appear in the WHERE clause. They can appear in the SELECT list though so you can use that to narrow down the number of rows you'd have to examine.
您不能直接搜索 LONG。LONG 不能出现在 WHERE 子句中。它们可以出现在 SELECT 列表中，因此您可以使用它来缩小您必须检查的行数。
Oracle has recommended converting LONGs to CLOBs for at least the past 2 releases. There are fewer restrictions on CLOBs.
Oracle 建议至少在过去的 2 个版本中将 LONG 转换为 CLOB。CLOB 的限制较少。
回答by Thomas Aregger
Example:
例子：
create table longtable(id number,text long);
insert into longtable values(1,'hello world');
insert into longtable values(2,'say hello!');
commit;
create or replace function search_long(r rowid) return varchar2 is
temporary_varchar varchar2(4000);
begin
select text into temporary_varchar from longtable where rowid=r;
return temporary_varchar;
end;
/
SQL> select text from longtable where search_long(rowid) like '%hello%';
TEXT
--------------------------------------------------------------------------------
hello world
say hello!
But be careful. A PL/SQL function will only search the first 32K of LONG.
不过要小心。PL/SQL 函数将只搜索 LONG 的前 32K。
回答by nikli
First convert LONGtype column to CLOBtype then use LIKEcondition, for example:
首先将LONG类型列转换为CLOB类型然后使用LIKE条件，例如：
CREATE TABLE tbl_clob AS
SELECT to_lob(long_col) lob_col FROM tbl_long;
SELECT * FROM tbl_clob WHERE lob_col LIKE '%form%';
回答by Neil Kodner
Don't use LONGs, use CLOB instead. You can index and search CLOBs like VARCHAR2.
不要使用 LONG，而是使用 CLOB。您可以索引和搜索 CLOB，如 VARCHAR2。
Additionally, querying with a leading wildcard(%) will ALWAYS result in a full-table-scan. Look into Oracle Text indexesinstead.
此外，使用前导通配符 (%) 进行查询将始终导致全表扫描。而是查看Oracle Text 索引。
