存储过程定义调用以及java调用存储过程

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

 

 

存储过程定义：

create or replace procedure text(keyword in varchar2,s out varchar2)
 is
v_restab ctx_doc.highlight_tab;
begin
ctx_doc.highlight('ind_m_high', 1, keyword, v_restab, true);
for i in 1..v_restab.count loop
s:=s||'begin with: ' || v_restab(i).offset || ' length: ' || v_restab(i).length;
end loop;
end;

 

存储过程调用（sql语句）：

 declare
   s varchar2(1000);
 begin   
   text('oracle',s);    
   DBMS_OUTPUT.PUT_LINE(s);
 END; 
 

存储过程java调用并返回参数：

public static void main(String[] args) {
  
  ResultSet rs;
  Connection conn;
  String sql = "Select * from T_DOCNEWS where contains(F_CONTENT,?)>0";
  CallableStatement cstmt = null;
  try {
   conn=DBPool.getConnection();
   cstmt = conn.prepareCall("{call foo.text(?,?)}");
   cstmt.setString(1, "this");
   cstmt.registerOutParameter(2,Types.VARCHAR);
   cstmt.execute();
   String s = cstmt.getString(2);
   System.out.println(s);
//   rs = (ResultSet) cstmt.getObject(1);
   if (cstmt != null) {
    cstmt.close();
   }
   if (conn != null) {
    conn.close();
   }
//   
//   cstmt=conn.prepareCall("{call adddept(?,?)}");
//   cstmt.setInt(1,4);
//   cstmt.setString(2,"这是测试用的");
//   cstmt.executeUpdate();
//   if (cstmt != null) {
//    cstmt.close();
//   }
//   if (conn != null) {
//    conn.close();
//   }
   System.out.println("success");
  } catch (Exception e) {
   e.printStackTrace();
  }
 }