jdbc oracle 函数,jdbc中调用oracle函数

1、创建表

create table stud(

sid int,

sname varchar2(50),

age number,

score number(4,2))

并插入一些数据(自己手动插入一些吧)

2、创建函数

create or replace function fun_getScores(

v_age in stud.age%type

)

return number

is

v_score number;

begin

select sum(score) into v_score from stud where age > v_age;

return v_score;

--异常处理

exception

when others then

dbms_output.put_line(sqlcode||sqlerrm);

return -1;

end;

3、jdbc中调用

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Types;

public class TestFun {

public void test(){

Connection con = null;

Statement st = null;

ResultSet rs = null;

CallableStatement cst = null;

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

//下面的tan是数据库名，默认是orcl，love是访问密码，默认是tiger,

//1521是端口号，默认是1521

con = DriverManager.getConnection

("jdbc:oracle:thin:@192.168.1.103:1521:tan","scott","love");

String sql =  "{? = call fun_getScores(?)}";

cst = con.prepareCall(sql);

cst.registerOutParameter(1, Types.NUMERIC);

cst.setDouble(2, 2);

cst.execute();

double result = cst.getDouble(1);

System.out.println("年龄超过2岁学生的成绩总和 "+result);

}catch(Exception e){

e.printStackTrace();

}finally{

try{

if(rs !=null){

rs.close();

}

if(st!=null){

st.close();

}

if(cst != null){

cst.close();

}

if(con != null){

con.close();

}

}catch(Exception e){

e.printStackTrace();

}

}

}

public static void main(String[] args) {

// TODO Auto-generated method stub

TestFun tf = new TestFun();

tf.test();

}

}