Oracle存储函数jdbc调用
package com.jckb.procedure; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; public class MainTest2 { /** * 存储函数plsql create or replace function findEmpYearSal(pno in number,pname out varchar2,psal out number) return number as pcomm emp.comm%type; begin select ename,sal,pcomm into pname,psal,pcomm from emp where empno=pno; return psal*12+nvl(pcomm,0); end; * */ public static void main(String[] args) { //1、定义变量 String driverClass = "oracle.jdbc.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String user = "scott"; String password = "tiger"; try{ Class.forName(driverClass); //2、获取连接对象 Connection connection = DriverManager.getConnection(url, user, password); //3、创建执行存储过程的语句对象 String sql = "{?=call findEmpYearSal(?,?,?)}"; CallableStatement callableStatement = connection.prepareCall(sql); //4、设置参数 callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.DOUBLE); callableStatement.setInt(2, 7521); callableStatement.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR); callableStatement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER); //5、执行 callableStatement.execute(); //6、获取数据 double yearSal = callableStatement.getDouble(1); String ename = callableStatement.getString(3); double sal = callableStatement.getDouble(4); System.out.println("姓名:"+ename+" 员工工资:"+sal + "年薪:"+yearSal); //7、释放资源 callableStatement.close(); connection.close(); }catch(Exception e){ e.printStackTrace(); } } }