Oracle如何对CLOB行字段来执行全文检索

最新推荐文章于 2023-11-14 15:57:48 发布

最新推荐文章于 2023-11-14 15:57:48 发布 · 188 阅读

文章标签：

#全文检索 #Oracle #SQL Server #SQL #工作

本文介绍如何在Oracle数据库中对存储于CLOB字段的数据执行全文检索。由于CLOB字段存储大量文本，直接使用Like操作不可行。文章提供了一个实用的方法，并列举了DBMS_LOB包中的相关函数。

本文讲解Oracle如何对CLOB行字段来执行全文检索。

大家知道，超过4000字的文本一般存储在CLOB中（MSQL、Sysbase是存放在Text中），在Oracle8i版本，对大字段CLOB仍然不支持在where子句直接的Like操作，如何实现对存储在CLOB字段中的内容进行Like查找呢？

下面的文章或则能给你帮助。虽然在SQL*PLUS中能实现用Select直接看到CLOB的内容，但是如何通过DBMS_LOB包实现对中文环境下的CLOB内容的读取我一直没有找到好的方法（使用Documents中提到的Samples只适用英文字符集），这极大的限制了使用第3方软件开发工作的自由度。

表结构：

create table products( 
productid number(10) not null ,
name varchar2(255) ,
description CLOB) ;

);

方法：

SELECT productid, name FROM products 
WHERE dbms_lob.instr(products.description,'some text',1,1) > 0;

下面列出了DBMS_LOB包中的过程函数：

APPEND procedure Appends the contents of 
the source LOB to the destination LOB. 

CLOSE procedure Closes a previously 
opened internal or external LOB. 

COMPARE function Compares two entire 
LOBs or parts of two LOBs. 

COPY procedure Copies all, or part, 
of the source LOB to the destination LOB. 

CREATETEMPORARY procedure Creates a temporary 
BLOB or CLOB and its corresponding index 
in the user's default temporary tablespace. 

ERASE procedure Erases all or part of a LOB. 

FILECLOSE procedure Closes the file. 

FILECLOSEALL procedure Closes all previously opened files. 

FILEEXISTS function Checks if 
the file exists on the server. 

FILEGETNAME procedure Gets the 
directory alias and file name. 

FILEISOPEN function Checks if the 
file was opened using the input BFILE locators. 

FILEOPEN procedure Opens a file. 

FREETEMPORARY procedure Frees the 
temporary BLOB or CLOB in the user's default temporary tablespace. 

GETCHUNKSIZE function Returns the amount 
of space used in the LOB chunk to store the LOB value.

GETLENGTH function Gets the length of the LOB value. 

INSTR function Returns the matching position 
of the nth occurrence of the pattern in the LOB. 

ISOPEN function Checks to see if the LOB 
was already opened using the input locator. 

ISTEMPORARY function Checks if the locator 
is pointing to a temporary LOB. 

LOADFROMFILE procedure Loads 
BFILE data into an internal LOB. 

OPEN procedure Opens a LOB 
(internal, external, or temporary) in the indicated mode. 

READ procedure Reads data from 
the LOB starting at the specified offset.

SUBSTR function Returns part of the 
LOB value starting at the specified offset. 

TRIM procedure Trims the LOB 
value to the specified shorter length. 

WRITE procedure Writes data 
to the LOB from a specified offset. 

WRITEAPPEND procedure Writes a buffer to the end of a LOB.