java调用oracle存储过程

一、有返回值的

存储过程：

create or replace procedure "PRO_UPDATE_REGION_SORT"
(
       target_id in number,
       target_sort in number,
       source_id in number,     
       source_sort in number,
       p_tid  out number,
       p_sid out number   
)as

begin
select regions_id into p_tid from tbl_regions t where t.regions_id = target_id;
select regions_id into p_sid from tbl_regions t where t.regions_id = source_id;
dbms_output.put_line('---目标id--'||p_tid||'---源id---'||p_sid);
end;

java调用

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class CallProcedure {
	private static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static String DB_USER = "sys";
	private static String DB_PASS = "sys";
	
	public Connection conn;
	public Statement stmt;
	public ResultSet rs;
	
	private void getConnection() throws Exception {
		Class.forName(DB_DRIVER).newInstance();
		this.conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
		this.stmt = conn.createStatement();
//		this.rs = stmt.executeQuery(sql);
		CallableStatement proc = null ;
		try {
			String sql = "{call PRO_UPDATE_REGION_SORT(?,?,?,?,?,?)}"; //参数包括in+out所有的
			proc =  conn.prepareCall(sql); //调用存储过程
			proc.setInt(1, 6);
			proc.setInt(2, 6);
			proc.setInt(3, 6);
			proc.setInt(4, 6);
			proc.execute();
			int a =proc.getInt(5);//前四个是传过去的参数，后两个是传出的参数
			int b = proc.getInt(6);
			System.out.println(a+"============"+b);
		} catch (SQLException e) {			
			e.printStackTrace();
			this.close();
		}
	}
	/**
	 * 清空资源
	 */
	public void close() {
		try {
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs = null;
			stmt = null;
			conn = null;
		}
	}
}

二 、没有返回值，在命令窗口打印输出值

SQL> set serveroutput on;
SQL> exec PRO_UPDATE_REGION_SORT(6,6,12,12);
 
目标id6源id12
 
PL/SQL procedure successfully completed