ORACLE全文检索文件路径版.docVIP

一、创建示例表 CREATE TABLE T_FULLTEXT_DEMO(  ID NUMBER NOT NULL, TITLE VARCHAR2(100) NULL, REMARK VARCHAR2(200) NULL, FILE_NAME VARCHAR2(200) NULL, CONSTRAINT T_FULLTEXT_DEMO_PK PRIMARY KEY ( ID ));二、对TITLE和REMARK建立索引BEGIN CTXSYS.CTX_DDL.CREATE_PREFERENCE(MYMDS,MULTI_COLUMN_DATASTORE);  CTXSYS.CTX_DDL.SET_ATTRIBUTE(MYMDS, COLUMNS, TITLE, REMARK);END;CREATE INDEX T_FULLTEXT_DEMO_IDX ON T_FULLTEXT_DEMO(TITLE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(DATASTORE MYMDS);三、建立存储选项参数、语法分析器BEGIN --CTX_DDL.DROP_PREFERENCE(MY_LEXER);  --CTX_DDL.DROP_PREFERENCE(MY_DATASTORE_PREFS); CTX_DDL.CREATE_PREFERENCE (MY_LEXER, CHINESE_VGRAM_LEXER);  CTX_DDL.CREATE_PREFERENCE (MY_DATASTORE_PREFS, FILE_DATASTORE);  CTX_DDL.SET_ATTRIBUTE (MY_DATASTORE_PREFS, PATH, F:\TEMP);END;四、对文档建立全文检索索引CREATE INDEX T_FULLTEXT_DEMO_FILE_IDX ON T_FULLTEXT_DEMO(FILE_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(DATASTORE MY_DATASTORE_PREFS LEXER MY_LEXER);五、插入测试数据INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(1,德鲁克的《管理：任务、责任、实践》,管理类书籍,WordDoc1.doc);INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(2,PB控件的属性事件和函数,PB技术书籍,PB控件的属性事件和函数.pdf);INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(3,微软产品报价,微软软件产品报价,微软产品报价.xls);INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(4,JavaScript 5.5,JavaScript语言帮助文档,JavaScript 5.5.CHM);INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(5,ASCII码,ASCII码的各种进制表示方式和代表的意思,ASCII码.htm);INSERT INTO T_FULLTEXT_DEMO(ID,TITLE,REMARK,FILE_NAME) VALUES(6,UML数据库建模,UML数据库建模的相关知识,UML数据库建模.ppt); 六、同步索引和优化索引若此时检索，应没有数据。select * from T_FULLTEXT_DEMOwhere contains(TITLE,管理)0; 需要将索引同步 BEGIN CTXSYS.CTX_DDL.SYNC_INDEX(T_FULLTEXT_DEMO_FILE_IDX); CTXSYS.CTX_DDL.SYNC_INDEX(T_FULLTEXT_DEMO_IDX); END; 七、测试全文索引 select * from T_FULLTEXT_DEMO where contains(TITLE,管理)0;select * from T_FULLTEXT_DEMO where contains(FILE_NAME,