oracle java source 返回數據集（1）

java调用oracle函数返回结果集

时间:2010-08-27 10:19:50来源:网络 作者:未知 点击:208次

CREATE OR REPLACE PACKAGE MY_STEEL_SOFT
AS
 --存储过程返回数据集的类型
 TYPE ReturnDataSet IS REF CURSOR;

CREATE OR REPLACE PACKAGE MY_STEEL_SOFT
AS
  --存储过程返回数据集的类型
  TYPE ReturnDataSet IS REF CURSOR;

END MY_STEEL_SOFT;

create or replace function bb(beginDate in varchar2)
  return My_steel_soft.ReturnDataSet
as
  ACursor My_steel_soft.ReturnDataSet;
begin
  open ACursor for select * from v_out_orderqry t
          where t.dOrderDate>=beginDate;
                --and t.dOrderDate< endDate;
                --and t.sSalerGUID=orgguid
                --or t.sBuyGUID=orgguid;
  return ACursor;
end bb;

/**
 * TestFun.java
 * create on 2007-5-23
 * Copyright 2010 MySteelSoft All Rights Reserved.
 */
package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestFun{
 
 public static void main(String[] args)
 {
 
     Connection connection = null; 
  try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
  }
  catch(ClassNotFoundException e) {
   System.out.println("数据库驱动类没找到");
  }
  try{
   ResultSet rs = null;
   String serverName = "192.168.18.21";
   String portNumber = "1521";
   String sid = "eTrade";
   String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
   String username = "developer";
   String password = "mysteelsoft";
   connection = DriverManager.getConnection(url,username,password);
   CallableStatement cstmt= null;;
   cstmt = connection.prepareCall("{? = call bb(?)}");

   //cstmt = connection.prepareCall("? = call eTrade.bb(?);");
   cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
   cstmt.setString(2,"2006-12-12");
   System.out.println(cstmt);
   cstmt.execute();
   rs = (ResultSet) cstmt.getObject(1);
   if (rs != null) {
          while (rs.next()) {       
         System.out.print(rs.getString(1)+"   ");
         System.out.print(rs.getString(2)+"   ");               
         System.out.println(rs.getString(3));
          }   
   }
   cstmt.close();  
   connection.close();  
  }catch(SQLException e) {
   e.printStackTrace(); 
  }
 }
}
 

/**
 * TestFun.java
 * create on 2007-5-23
 * Copyright 2010 MySteelSoft All Rights Reserved.
 */
package com.test;

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

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;
public class TestFun{
 
 public static void main(String[] args)
 {
 
     Connection connection = null; 
  try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
  }
  catch(ClassNotFoundException e) {
   System.out.println("数据库驱动类没找到");
  }
  try{
    CachedRowSet crs = new CachedRowSetImpl();
     
   ResultSet rs = null;
   String serverName = "192.168.18.21";
   String portNumber = "1521";
   String sid = "eTrade";
   String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
   String username = "developer";
   String password = "mysteelsoft";
   connection = DriverManager.getConnection(url,username,password);
   CallableStatement cstmt= null;;
   cstmt = connection.prepareCall("{? = call bb(?)}");

   cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
   cstmt.setString(2,"2006-12-12");
   cstmt.execute();
   System.out.println(cstmt);
   rs = (ResultSet) cstmt.getObject(1);
      crs.populate(rs);
  
   if (crs != null) {
          while (crs.next()) {       
         System.out.print(crs.getString("SGUID")+"   ");
         System.out.print(crs.getString("SORDERID")+"   ");               
         System.out.println(crs.getString("SSALERGUID"));
          }   
   }
   cstmt.close();  
   connection.close();  
  }catch(SQLException e) {
   e.printStackTrace(); 
  }
 }
}