Oracle存储过程传参并返回列表以及Java调用过程

关注

原创最新推荐文章于 2024-04-03 08:03:37 发布 682 阅读 AI 写同款· GEO 优化 ›

--1、存储过程包的创建，数据类型和存储过程定义
CREATE OR REPLACE PACKAGE htjd_package IS--包创建，面向不同客户或者功能类型
TYPE HT_CURSOR IS REF CURSOR;--自定义游标类型
PROCEDURE HT_GETALLPSINFO (ref_cursor OUT HT_CURSOR);--自定义存储过程
PROCEDURE HT_GETPSINFO(ps_id IN integer,ref_cursor OUT HT_CURSOR);
PROCEDURE HT_INVERTHIS(his_year IN varchar2,ref_cursor OUT HT_CURSOR);
end HTJD_PACKAGE;

--2、包内容和存储过程内容的实现
CREATE OR REPLACE PACKAGE BODY HTJD_PACKAGE IS
  PROCEDURE HT_GETPSINFO(ps_id IN integer, ref_cursor OUT HT_CURSOR) IS
  begin
    OPEN ref_cursor FOR
      select * from mnt_powerstation where psid = '' || ps_id || '';
  end HT_GETPSINFO;

  PROCEDURE HT_GETALLPSINFO(ref_cursor OUT HT_CURSOR) IS
  BEGIN
    OPEN ref_cursor FOR
      SELECT * FROM mnt_powerstation;
  END HT_GETALLPSINFO;

  PROCEDURE HT_INVERTHIS(his_year IN varchar2,ref_cursor OUT HT_CURSOR) IS
  BEGIN
    OPEN ref_cursor FOR
      'SELECT * FROM MNT_INVERTER_HIS'||his_year;
  END HT_INVERTHIS;
END HTJD_PACKAGE;

//3、JAVA代码调用存储过程并读取数据
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			  /**建立数据库通道连接**/
	          DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
	          Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test", "test", "test");
              /**1、获取单电站信息**/
	          //CallableStatement stmt = conn.prepareCall("BEGIN HT_GETPSINFO(?, ?); END;");
	          //CallableStatement stmt = conn.prepareCall("{ call HT_GETPSINFO(?, ?) }");  
	          //stmt.setString(1, "1");
	          //stmt.registerOutParameter(2, -10); //REF CURSOR(OracleTypes.CURSOR==-10)
	          //stmt.execute();
	          //ResultSet rs = (ResultSet) stmt.getObject(1);//1代表OUT在第一个参数，2代表OUT在第二个参数
	          //while (rs.next()) {
	          //  System.out.println(rs.getString("psid") +"--"+rs.getString("psname"));
	          //}	          
	          
	          /**获取多电站信息**/
	          CallableStatement stmt = conn.prepareCall("BEGIN htjd_package.HT_INVERTHIS(?,?); END;");
	          //CallableStatement stmt = conn.prepareCall("{ call htjd_package.HT_INVERTHIS(?,?) }");
	          stmt.setString(1, "2014");//1代表输入参数的位置
	          stmt.registerOutParameter(2, -10); //REF CURSOR(OracleTypes.CURSOR==-10),1代表OUT在第一个参数，2代表OUT在第二个参数
	     
	          stmt.execute();
	          ResultSet rs = (ResultSet) stmt.getObject(2);//1代表OUT在第一个参数，2代表OUT在第二个参数
	          while (rs.next()) {
	            System.out.println(rs.getString("INVERTERID") +"--"+rs.getString("COLLECTTIME")+"--"+rs.getString("DCCURRENT"));
	          }
	          
	          /**关闭通道**/
	          rs.close();
	          rs = null;
	          stmt.close();
	          stmt = null;
	          conn.close();
	          conn = null;
	        }
	        catch (SQLException e) {
	          System.out.println(e.getLocalizedMessage());
	        }
	}

}