Java执行Oracle存储过程并接收返回值

Java执行Oracle存储过程并接收多个返回值(集合/数值)

Java代码：

package com.pure.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBHelper {

	public Connection getConnection(){
		Connection conn=null;
		try{
			//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//SqlServer
			//conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test", "sa", "123");
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test","root","123");

		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	public void close(ResultSet rs,PreparedStatement ps,Connection conn){
		try{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
			if(conn!=null){
				conn.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		DBHelper db=new DBHelper();
		System.out.println(db.getConnection());
	}
}

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import com.util.DBHelper;

public class Test {
	/**
	 * jar运行Test类  -- cmd中运行test.jar
	 * java -jar test.jar 14 
	 * @param args
	 */
	public static void main(String[] args) {
		//System.out.println("数量="+testCall(parseInt(args[0]))); //jar运行
		System.out.println("数量="+testCall(14));
	}
	public static int testCall(int param){
		DBHelper db=new DBHelper();
		Connection conn= db.getConnection();
		
		int count = 0;
		try{
			CallableStatement call=conn.prepareCall("{call sp_test_1(?,?,?)}");//运行sp_test_1存储过程(注：存储过程接收参数和输出参数Java都有打？问号)
			call.setInt(1, param);//设置参数
			call.registerOutParameter(2, OracleTypes.INTEGER);//接收返回值
			call.registerOutParameter(3,OracleTypes.CURSOR);//接收返回值
			call.execute();//执行
		
			count=call.getInt(2); //拿取存储过程返回值 
			
			
			List<Map> list=new ArrayList<Map>(); 
			//ResultSet rs=call.getResultSet();
			ResultSet rs=(ResultSet)call.getObject(3);//拿取存储过程返回的集合
			while(rs.next()){
				Map map=new HashMap();
				map.put("id", rs.getInt("ID"));
				map.put("user_name", rs.getString("USER_NAME"));
				map.put("login_name", rs.getInt("LOGIN_NAME"));
				list.add(map);
			}
			
			System.out.println("listSize="+list.size());
			
		
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
		}
		return count;
	}
	public static int parseInt(Object obj){
		try{
			return Integer.parseInt(obj.toString());
		}catch(Exception e){
			return 0;
		}
	} 
}

oracle存储过程：

/*
 * oracle存储过程测试 
*/
CREATE OR REPLACE PROCEDURE sp_test_1(
   param1 in int,     --输入参数
   out_int out int,     --返回值int
   out_list out SYS_REFCURSOR  --返回集合
)
is 
   val_1 int; --全局变量

begin

     select  count(1) into val_1 from pd_system_user where id = param1;
     out_int := val_1;
     --设置结果集
     open out_list for select * from pd_system_user where id <= param1;
     
     --dbms_output.put_line(out_return); --打印
end sp_test_1;

-- 演示存储过程
/*
declare --声明变量
param1 varchar2(100);
val_1 int;

begin 
      param1 := 14; --变量赋值
      --select val_1 := count(1) from pd_system_user where id = param1; --不行
      select  count(1) into val_1 from pd_system_user where id = param1;
      --val_1 := param1;
      dbms_output.put_line(val_1);--打印语句
end;
*/

运行结果：