Call Oracle functions in JDBC

1. Create a table

Create Table stud (
Sid int,
Sname varchar2 (50 ),
Age number,
Score number (4, 2 ))
And insert some data (manually insert some data)
2. Create a function

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 Handling
Exception
When others then
Dbms_output.put_line (sqlcode | sqlerrm );
Return-1;
End;
3. Call in 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 ");
// The Tan below is the database name, the default is orcl, the love is the access password, the default is Tiger,
// 1521 indicates the port number. The default value is 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.exe cute ();
Double result = Cst. getdouble (1 );

System. Out. println ("total score of students over 2 years old" + 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 ();
}

 

}