oracle全文检索简单例子

1.=======为拥有文档表的模式建立文本索引授权

====以system用户连接数据库，为ZYFILEUP授权。

GRANT "CTXAPP" TO "JDCDC";
ALTER USER "JDCDC" DEFAULT ROLE ALL;
GRANT EXECUTE ON "CTXSYS"."CTX_ADM" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_CATSEARCH" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_CONTAINS" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_DOC" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_QUERY" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_ULEXER" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_XPCONTAINS" TO "JDCDC";

2.========建立文档表在BLOB_CONTENT列上的索引=========
=====以JDCDC用户连接数据库，建立文本索引参考项
begin
ctx_ddl.create_preference('mylex','BASIC_LEXER');
ctx_ddl.set_attribute('mylex','printjoins','_-');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
begin
ctx_ddl.create_preference('cnlex','CHINESE_LEXER');
--====ctx_ddl.create_preference('chinalex','chinese_vgram_lexer')===---;
end; 

3.==========建立索引=============
create index name_index on cdc.test(name)
indextype is ctxsys.context
parameters ('DATASTORE CTXSYS.DIRECT_DATASTORE
FILTER CTXSYS.INSO_FILTER
LEXER jdcdc.CNLEX
WORDLIST jdcdc.MYWORDLIST');

<<< 
create index name_index on cdc.test(name)
indextype is ctxsys.context
parameters ('LEXER cdc.CNLEX');
>>>>
==========删除索引=============
drop index name_index;

4.==========索引同步=============
exec ctx_ddl.sync_index('test_index');
exec ctx_ddl.sync_index('name_index');
exec ctx_ddl.sync_index('subject_index');
exec ctx_ddl.sync_index('contents_index');

5.==========文本查询语句格式=====
select name from cdc.test
where contains(name,'aa',1)>0
order by name;

CREATE TABLE zlk_poisoninf (
  datainfoid       NUMBER PRIMARY KEY,
  summarize        clob,
  DIAGNOSE         clob,
  TREATMENT        clob
  );

create index summarize_fullindex on cdc.zlk_poisoninf(summarize)
indextype is ctxsys.context
parameters ('LEXER cdc.CNLEX');

exec ctx_ddl.sync_index('summarize_fullindex');