jdbc调用oracle存储过程和存储函数

--定义过程

create or replace procedure get_rax(salary in number,rax out number)
as
    --需要交税的钱
    bal number;
begin
    bal := salary - 3500;
    if bal<=1500 then
       rax := bal * 0.03 - 0;
    elsif bal<=4500 then
       rax := bal * 0.1 - 105;
    elsif bal<=9000 then
       rax := bal * 0.2 - 555;
    elsif bal<=35000 then
       rax := bal * 0.25 - 1005;
    elsif bal<=55000 then
       rax := bal * 0.3 - 2755;
    elsif bal<=80000 then
       rax := bal * 0.35 - 5505;
    else
       rax := bal * 0.45 - 13505;
    end if;
end;

--定义函数

create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out number) 
return varchar2
as
    pename emp.ename%type;
begin
    select ename,job,sal into pename,pjob,psal from emp where empno = pempno;
    return pename;
end;
/

JdbcUtil.java

/**
 * JDBC工具类
 * @author AdminTC
 */
public class JdbcUtil {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	public static Connection getConnection() throws Exception{
		return dataSource.getConnection();
	}
}

java-jdbc调用oracle过程

/**
 * 演示java-jdbc调用oracle过程
 * @author AdminTC
 */
public class CallOracleProc {
	public static void main(String[] args) throws Exception{
		String sql = "{call get_rax(?,?)}";
		Connection conn = JdbcUtil.getConnection();
		CallableStatement cstmt = conn.prepareCall(sql);
		//为第一个?号设置值，从1开始
		cstmt.setInt(1,7000);
		//为第二个？注册输出类型
		cstmt.registerOutParameter(2,Types.INTEGER);
		//执行调用过程
		cstmt.execute();
		//接收过程的返回值，即第二个？号
		int rax = cstmt.getInt(2);
		//显示
		System.out.println("7000元需要上交"+rax+"元税收");
		cstmt.close();
		conn.close();
	}
}

java-jdbc调用oracle函数

/**
 * 演示java-jdbc调用oracle函数
 * @author AdminTC
 */
public class CallOracleFunc {
	public static void main(String[] args) throws Exception{
		String sql = "{ ? = call findEmpNameAndJobAndSal(?,?,?) }";
		Connection conn = JdbcUtil.getConnection();
		CallableStatement cstmt = conn.prepareCall(sql);
		
		//为第一个?注册输出类型
		cstmt.registerOutParameter(1,Types.VARCHAR);
		//为第二个?注入值
		cstmt.setInt(2,7788);
		//为第三个?注册输出类型
		cstmt.registerOutParameter(3,Types.VARCHAR);
		//为第四个?注册输出类型
		cstmt.registerOutParameter(4,Types.INTEGER);
		//执行函数调用
		cstmt.execute();
		//分别获取1,3,4占位符的值
		String ename = cstmt.getString(1);
		String job = cstmt.getString(3);
		int sal = cstmt.getInt(4);
		//显示
		System.out.println("7788--"+ename+"--"+job+"--"+sal);
		cstmt.close();
		conn.close();
	}
}