java调用ORACLE函数返回结果集

最新推荐文章于 2025-06-25 20:06:35 发布

colincrazy

最新推荐文章于 2025-06-25 20:06:35 发布

阅读量5k

点赞数

CC 4.0 BY-SA版权

分类专栏： java 文章标签： oracle java string import null jdbc

本文链接：https://blog.youkuaiyun.com/colincrazy/article/details/1622441

java 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个使用Oracle存储过程返回数据集的例子，并展示了如何通过Java程序调用该存储过程来获取并显示结果集。具体包括存储过程定义、Java连接配置及结果集处理等关键技术点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

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();
  }
}
}

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();
  }
}
}