少年阿宾

那些青春的岁月

  BlogJava :: 首页 :: 联系 :: 聚合  :: 管理
  500 Posts :: 0 Stories :: 135 Comments :: 0 Trackbacks
-- Create table
create table ABIN1
(
  ID1         NUMBER,
  NAME1       NVARCHAR2(100),
  CREATETIME1 DATE default sysdate
)



create or replace function getFunction(myname varchar2)
return sys_refcursor
as

v_sql varchar2(4000);
mylist sys_refcursor;
begin
       v_sql:='select * from abin1 where name1=:1';
       open myList for v_sql using myname;
       return(myList);
end;



package com.abin.lee.db.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleConnection {
 private static final String USER="abin";
 private static final String PWD="abin";
 private static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
 private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
 private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>(){
  protected Connection initialValue(){
   Connection conn=null;
   try {
    Class.forName(DRIVER);
    if(null==conn||conn.isClosed()){
     conn=DriverManager.getConnection(URL, USER, PWD);
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
   return conn;
  }
  
 };
 
 
 public static Connection getConnection() throws ClassNotFoundException, SQLException{
  Connection conn=null;
  if(threadLocal.get()==null){
   Class.forName(DRIVER);
   conn=DriverManager.getConnection(URL, USER, PWD);
   threadLocal.set(conn);
  }else {
   return threadLocal.get();
  }
  return conn;
 }

}




package com.abin.lee.db.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import junit.framework.TestCase;
import oracle.jdbc.OracleTypes;

public class GetFunctionResultSet extends TestCase{
 public void test(){
  Connection conn=null;
  CallableStatement cs=null;
  ResultSet rs=null;
  try {
   conn=OracleConnection.getConnection();
 /*此处是关键,不要写错了。第一个问号代表返回值,getFunction()括号里面是function的输入参数。*/  
   cs=conn.prepareCall("{?=call getFunction(?)}");
   cs.registerOutParameter(1, OracleTypes.CURSOR);
   cs.setString(2, "a1");
   cs.execute();
   rs=(ResultSet)cs.getObject(1);
   while(rs.next()&&rs!=null){
    System.out.println("ID="+rs.getInt("id1"));
    System.out.println("NAME="+rs.getString("name1"));
    System.out.println("NAME="+rs.getString("createtime1"));
   }
   
  } catch (Exception e) {
   e.printStackTrace();
  }
  /**
   * create or replace function getFunction(myname varchar2)
   return sys_refcursor
   as
   v_sql varchar2(4000);
   mylist sys_refcursor;
   begin
          v_sql:='select * from abin1 where name1=:1';
          open myList for v_sql using myname;
          return(myList);
   end;
   */
  
 }
}




参考资料:
http://lanselixiangxc.bokee.com/267623.html

posted on 2012-09-14 12:46 abin 阅读(569) 评论(0)  编辑  收藏 所属分类: oracle

只有注册用户登录后才能发表评论。


网站导航: