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