Oracle全文检索建索引，存储过程，以及java方法调用高亮显示

http://chaoji-liangbin.blog.163.com/blog/static/25239212201091434637709/

 

 

--以下开始针对T_DOCNEWS
--先删除引用
begin
ctx_ddl.drop_preference('my_chinese_vgram_lexer');
ctx_ddl.drop_preference('my_chinese_lexer');
end;

--支持中文分词
Begin
ctx_ddl.create_preference('my_chinese_vgram_lexer', 'chinese_vgram_lexer');
ctx_ddl.create_preference('my_chinese_lexer', 'chinese_lexer');
End;

drop index f_content_index;
Create index f_content_index on T_DOCNEWS(F_CONTENT)
indextype is ctxsys.context
parameters('datastore ctxsys.default_datastore filter ctxsys.null_filter section group
ctxsys.html_section_group lexer foo.my_chinese_vgram_lexer');

CREATE or replace TYPE f_content_arr AS OBJECT(
id NUMBER ,
url varchar2(255),
title varchar2(255),
abstractcontent varchar2(255)
);

CREATE or replace type f_content_arr_re as table of f_content_arr;

create or replace procedure f_content_pro (keyword in varchar,v_cfjg out f_content_arr_re) is
v_restab ctx_doc.highlight_tab;
begin
DECLARE
i number;
s clob;
startnum number;
endnum number;
v_res_fun T_DOCNEWS%rowTYPE;
cursor c_fun is
select *  from T_DOCNEWS where contains(F_CONTENT,keyword)>0;
BEGIN
DBMS_OUTPUT.PUT_LINE('111');
i := 0;
v_cfjg := f_content_arr_re();
open c_fun;
LOOP
DBMS_OUTPUT.PUT_LINE('2232');
fetch c_fun
into v_res_fun;
EXIT WHEN c_fun%NOTFOUND;
i := i + 1;
s := v_res_fun.F_CONTENT;
v_cfjg.EXTEND;
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));
ctx_doc.highlight('f_content_index', v_res_fun.F_ID, keyword, v_restab, false);
--只取第一个，没有loop循环
startnum:=v_restab(1).offset;
if v_restab(1).offset > 30 then
   begin
        startnum := v_restab(1).offset-30 ;
   end;
end if;
if v_restab(1).offset <= 30 then
   begin
        startnum := 1 ;
   end;
end if;
if length(s)-v_restab(1).offset > 30 then
   begin
        endnum := v_restab(1).offset+30 ;
   end;
end if;
if length(s)-v_restab(1).offset <= 30 then
   begin
        endnum := length(s) ;
   end;
end if;
v_cfjg(v_cfjg.count) := f_content_arr(v_res_fun.F_ID,v_res_fun.F_URL,v_res_fun.F_TITLE,substr(s,startnum,endnum-startnum));
dbms_output.new_line();
END LOOP;
end;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
end f_content_pro;

--在此，全文检索存储过程定义完毕

 

--以下是sqlplus调用 

declare
   s f_content_arr_re;
 begin   
   f_content_pro('菲律宾',s);    
 END; 

 

--以下是java方法调用

public void callContentPRO() {
  Connection conn = null;
  ResultSet rs = null;
  CallableStatement stmt = null;
  try {
   conn = DBPool.getConnection();
   stmt = null;
   String procName = new StringBuffer().append("{ call f_content_pro(?,?) } ").toString();
   stmt = conn.prepareCall(procName);
   // stmt.setObject(1, array, oracle.jdbc.OracleTypes.ARRAY);
   stmt.setString(1, "菲律宾");
   stmt.registerOutParameter(2, Types.ARRAY, "F_CONTENT_ARR_RE");
   stmt.execute();
   ARRAY arr = (ARRAY) stmt.getArray(2);
   rs = arr.getResultSet();
   while (rs.next()) {
    STRUCT struct = (STRUCT) rs.getObject(2);
    Object[] obs = struct.getAttributes();
    System.out.println("id:" + obs[0] + " ..");
    System.out.println("url：" + obs[1] + " ..");
    System.out.println("标题：" + obs[2] + " ..");
    System.out.println("动态摘要：" + obs[3] + " ..");
   }
   if (stmt != null) {
    stmt.close();
   }
   if (conn != null) {
    conn.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }