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();
		}
	}
}

 

posted @ 2017-03-14 22:35  郭鑫  阅读(1009)  评论(0编辑  收藏  举报