java连接Oracle以及调用其中的存储过程与存储函数

下面的案例中使用到的存储过程以及存储函数的创建都在上一篇博客中。

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Before;import org.junit.Test;import oracle.jdbc.driver.OracleCallableStatement;import oracle.jdbc.driver.OracleTypes;public class OracleTest {private String driverClass="oracle.jdbc.driver.OracleDriver";private String jdbcUrl="jdbc:oracle:thin:@192.168.58.128:1521:orcl";private String user="scott";private String password="scott";private Connection conn;private PreparedStatement ps;private ResultSet rs;@Beforepublic void getConnection() {try {//注册驱动Class.forName(driverClass);//获取连接conn=DriverManager.getConnection(jdbcUrl, user, password);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Testpublic void findDeptEmp(){try{//获取句柄ps=conn.prepareStatement("select * from emp where deptno=?");//设置参数ps.setInt(1, 20);//执行sqlrs=ps.executeQuery();//处理结果while(rs.next()){System.out.println(rs.getString("ename"));}//关闭连接rs.close();ps.close();conn.close();}catch(Exception e){e.printStackTrace();}}//调用增加工资的存储过程----无返回值@Testpublic void callProcedureAddSal(){try{//获取句柄CallableStatement cs=conn.prepareCall("{call add_sal(?)}");//设置参数cs.setInt(1, 7369);//执行sqlcs.execute();//关闭连接cs.close();conn.close();}catch(Exception e){e.printStackTrace();}}//调用计算年薪的存储过程---有返回结果如果有输出参数需要注册输出@Testpublic void callprocedureCountYearSal(){try{//获取句柄CallableStatement cs=conn.prepareCall("{call count_year_sal(?,?)}");//设置参数cs.setInt(1, 7369);//注册输出参数cs.registerOutParameter(2, OracleTypes.NUMBER);//执行sqlcs.execute();System.out.println(cs.getInt(2));//关闭连接cs.close();conn.close();}catch(Exception e){e.printStackTrace();}}//调用返回部门员工的存储过程--有多行返回结果//deptinfo(deptno  in number,v_emr_cursor out  sys_refcursor)@Testpublic void callProcedureDeptEmp(){try{//获取句柄CallableStatement cs=conn.prepareCall("{call deptinfo(?,?)}");//设置参数cs.setInt(1, 20);//注册输出参数cs.registerOutParameter(2, OracleTypes.CURSOR);//执行sqlcs.execute();OracleCallableStatement ocs=(OracleCallableStatement) cs;rs = ocs.getCursor(2);//处理结果while(rs.next()){System.out.println(rs.getString("ename"));}//关闭连接rs.close();ocs.close();cs.close();conn.close();}catch(Exception e){e.printStackTrace();}}//调用计算年薪的存储函数，有返回值//count_year_sal_fun(v_empno in number)@Testpublic void callfunCountAddsal(){try{//获取句柄CallableStatement cs=conn.prepareCall("{?=call count_year_sal_fun(?)}");//设置参数cs.setInt(2, 7369);//注册输出参数cs.registerOutParameter(1, OracleTypes.NUMBER);//执行sqlcs.execute();System.out.println(cs.getInt(1));//关闭连接cs.close();conn.close();}catch(Exception e){e.printStackTrace();}}}