java调用oracle存储过程返回值_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 list=new ArrayList();

//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;

*/

运行结果：